MVC案例之模糊查询与删除

查询操作：

Servlet
//1. 调用 CustomerDAO 的 getAll() 得到 Customer 的集合

List<Customer> customers = customerDAO.getAll();

//2. 把 Customer 的集合放入 request 中

request.setAttribute("customers", customers);

//3. 转发页面到 index.jsp(不能使用重定向)

request.getRequestDispatcher("/index.jsp").forward(request, response);

JSP

获取 request 中的 customers 属性

遍历显示

1.模糊查询

1).根据传入的 name, address, phone 进行模糊查询

例子: name: a address: b phone: 3

则 SQL 语句的样子为: SELECT id, name, address, phone FROM customers WHERE name LIKE ‘%a%’

AND address LIKE ‘%b%’ AND phone LIKE ‘%3%’

需在CustomerDAO接口中定义一个 getForListWithCriteriaCustomer(CriteriaCustomer cc)

其中 CriteriaCustomer 用于封装查询条件：name, address, phone。

因为查询条件很多时候和 domain 类并不相同，所以要做成一个单独的类拼 SQL：

SQL: "SELECT id, name, address, phone FROM customers WHERE name LIKE ?

AND address LIKE ? ANDphone LIKE ?";

为了正确的填充占位符时，重写了 CriteriaCustomer 的 getter：

2).修改 Servlet：获取请求参数；把请求参数封装为CriteriaCustomer 对象，

再调用 getForListWithCriteriaCustomer(CriteriaCustomer cc) 方法

2.删除操作

1)超链接：delete.do?id=<%=customer.getId()%>
Servlet 的 delete 方法:
获取 id

调用 DAO 执行删除

重定向到 query.do（若目标页面不需要读取当前请求的 request 属性，就可以使用重定向），

将显示删除后的 Customer 的 List

2).JSP 上的 jQuery 提示：确定要删除 xx 的信息吗？

CustomerDAO

package com.aff.mvcapp.dao;

import java.util.List;

import com.aff.mvcapp.domian.Customer;

public interface CustomerDAO {
    public List<Customer> getForListWithCriteriaCustomer(CriteriaCustomer c);

    public List<Customer> getAll();

    public void save(Customer customer);

    public Customer get(Integer id);

    public void delete(Integer id);

    /**
     * 返回和 name 相等的记录数
     * 
     * @param name
     * @return
     */
    public long getCountWithName(String name);

    public void update(Customer customer);
}

CriteriaCustomer

package com.aff.mvcapp.dao;
public class CriteriaCustomer {
    private String name;
    private String address;
    private String phone;

    public CriteriaCustomer() {
        super();
    }
    public CriteriaCustomer(String name, String address, String phone) {
        super();
        this.name = name;
        this.address = address;
        this.phone = phone;
    }
    public String getName() {
        if (name == null) {
            name = "%%";
        }else 
            name = "%"+name+"%";
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getAddress() {
        if (address == null) {
            address = "%%";
        }else 
            address = "%"+address+"%";
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public String getPhone() {
        if (phone == null) {
            phone = "%%";
        }else 
            phone = "%"+phone+"%";
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    @Override
    public String toString() {
        return "CriteriaCustomer [name=" + name + ", address=" + address + ", phone=" + phone + "]";
    }
}

CustomerDAOImpl

package com.aff.mvcapp.dao.impl;
import java.util.List;

import com.aff.mvcapp.dao.CriteriaCustomer;
import com.aff.mvcapp.dao.CustomerDAO;
import com.aff.mvcapp.dao.DAO;
import com.aff.mvcapp.domian.Customer;

public class CustomerDAOImpl extends DAO<Customer> implements CustomerDAO {

    @Override
    public List<Customer> getForListWithCriteriaCustomer(CriteriaCustomer c) {
        String sql = "select  id, name, address, phone from  customers where name like ? and address like ? and phone like  ?";
        return getForList(sql, c.getName(), c.getAddress(), c.getPhone());
    }

    @Override
    public List<Customer> getAll() {
        String sql = "select  id, name, address, phone from  customers";
        return getForList(sql);
    }

    @Override
    public void save(Customer customer) {
        String sql = "insert into customers(name,address,phone)values(?,?,?)";
        update(sql, customer.getName(), customer.getAddress(), customer.getPhone());
    }

    @Override
    public Customer get(Integer id) {
        String sql = "select id,name,address,phone from customers where id =?";
        return get(sql, id);
    }

    @Override
    public void delete(Integer id) {
        String sql = "delete from customers where id  = ?";
        update(sql, id);

    }

    @Override
    public long getCountWithName(String name) {
        String sql = "select count(id) from customers where name =?";
        return getForValue(sql, name);
    }

    @Override
    public void update(Customer customer) {
        String sql = "update customers set name = ?,address = ? ,phone = ? where id = ?";
        update(sql, customer.getName(), customer.getAddress(), customer.getPhone(), customer.getId());
    }
}

CustomerServlet

package com.aff.mvcapp.servlet;

import java.io.IOException;
import java.lang.reflect.Method;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.aff.mvcapp.dao.CriteriaCustomer;
import com.aff.mvcapp.dao.CustomerDAO;
import com.aff.mvcapp.dao.impl.CustomerDAOImpl;
import com.aff.mvcapp.domian.Customer;

@WebServlet("/customerServlet")
public class CustomerServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    private CustomerDAO customerDAO = new CustomerDAOImpl();

    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        doPost(request, response);
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // 1. 获取ServletPath: /edit.do 或 addCustomer.do
        String servletPath = request.getServletPath();
        // 2.去除 / 和 .do 得到类似于 edit 或 addCustomer 这样的字符串
        String methodName = servletPath.substring(1);
        methodName = methodName.substring(0, methodName.length() - 3);

        try {
            // 3.利用反射获取 methodName 对应的方法
            Method method = getClass().getDeclaredMethod(methodName, HttpServletRequest.class,
                    HttpServletResponse.class);
            // 4.利用反射调用对应的方法
            method.invoke(this, request, response);
        } catch (Exception e) {
            // e.printStackTrace();
            // 可以有一些响应
            response.sendRedirect("error.jsp");
        }
    }

    private void edit(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        System.out.println("edit");
    }

    private void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        System.out.println("update");
    }

    private void query(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        String name = request.getParameter("name");
        String address = request.getParameter("address");
        String phone = request.getParameter("phone");

        CriteriaCustomer cc = new CriteriaCustomer(name, address, phone);

        // 1.调用 CustomerDAO 的 getForListWithCriteriaCustomer() 得到 Customer 的集合
        List<Customer> customers = customerDAO.getForListWithCriteriaCustomer(cc);

        // 2.把 Customer 的集合放入 request 中
        request.setAttribute("customers", customers);

        // 3.转发页面到 index.jsp 中( 不能使用重定向)
        request.getRequestDispatcher("/index.jsp").forward(request, response);

    }

    private void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String idstr = request.getParameter("id");
        int id = 0;
        // try-catch的作用 ， 防止恶意的输入， idStr 不能转为int类型，若出异常 id直接为0
        try {
            id = Integer.parseInt(idstr);
            customerDAO.delete(id);
        } catch (Exception e) {
        }
        response.sendRedirect("query.do");
    }
}

index.jsp

<%@page import="com.aff.mvcapp.domian.Customer"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script type="text/javascript" src="scripts/jquery-1.12.3.js"></script>
<script type="text/javascript">
    $(function() {
        $(".delete").click(function() {
            //找到tr的第二个td也就是name，再获取它的value值
            var content = $(this).parent().parent().find("td:eq(1)").text();
            var flag = confirm("确定要删除" + content + "的信息吗")
            return flag;
        });
    });
</script>
</head>
<body>


    <form action="query.do" method="post">
        <table>
            <tr>
                <td>CustomerNam:</td>
                <td><input type="text" name="name" /></td>
            </tr>
            <tr>
                <td>Address:</td>
                <td><input type="text" name="address" /></td>
            </tr>
            <tr>
                <td>Phone:</td>
                <td><input type="text" name="phone" /></td>
            </tr>
            <tr>
                <td><input type="submit" value="Query" /></td>
                <td><a href="">Add New Customer</a></td>
            </tr>
        </table>
    </form>

    <br />
    <br />
    <br />
    <br />


    <%
        List<Customer> customers = (List<Customer>) request.getAttribute("customers");
        if (customers != null && customers.size() > 0) {
    %>
    <hr>
    <br>
    <br>
    <table border="1" cellpadding="10" cellspacing="0">
        <tr>
            <td>ID</td>
            <td>CustomersName</td>
            <td>Address</td>
            <td>Phone</td>
            <td>UPDATEDELETE</td>
        </tr>

        <%
            for (Customer customer : customers) {
        %>

        <tr>
            <td><%=customer.getId()%></td>
            <td><%=customer.getName()%></td>
            <td><%=customer.getAddress()%></td>
            <td><%=customer.getPhone()%></td>
            <td><a href="">UPDATE</a> <a
                href="delete.do?id=<%=customer.getId()%>" class="delete">DELETE</a>
            </td>

        </tr>

        <%
            }
        %>

    </table>


    <%
        }
    %>

</body>
</html>

All that work will definitely pay off