[原创]java WEB学习笔记23：MVC案例完整实践(part 4)---模糊查询的设计与实现
本博客为原创：综合 尚硅谷（http://www.atguigu.com）的系统教程（深表感谢）和 网络上的现有资源(博客，文档，图书等)，资源的出处我会标明
本博客的目的：①总结自己的学习过程，相当于学习笔记 ②将自己的经验分享给大家，相互学习，互相交流，不可商用
内容难免出现问题，欢迎指正，交流，探讨，可以留言，也可以通过以下方式联系。
本人互联网技术爱好者，互联网技术发烧友
微博：伊直都在0221
QQ：951226918
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1.查询操作：
1）思路：
Servlet:在查询方法中，创建CustomerDAO 对象，调用getAll() 方法，获得List<Customer> 集合，利用保证为requset，转发，获取，遍历
//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 属性
遍历显示
2）代码：
CustomerServlet2.java
Servlet中的 private void query(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{} 方法
1 package com.jason.mvcapp.servlet;
2
3 import java.io.IOException;
4 import java.lang.reflect.Method;
5 import java.util.List;
6
7 import javax.servlet.ServletException;
8 import javax.servlet.annotation.WebServlet;
9 import javax.servlet.http.HttpServlet;
10 import javax.servlet.http.HttpServletRequest;
11 import javax.servlet.http.HttpServletResponse;
12
13 import com.jason.mvcapp.dao.CustomerDAO;
14 import com.jason.mvcapp.dao.impl.CustomerDAOJdbcImpl;
15 import com.jsaon.mvcapp.domain.CriteriaCustomer;
16 import com.jsaon.mvcapp.domain.Customer;
17
18 /**
19 * Servlet implementation class CustomerServlet2
20 */
21 @WebServlet("*.do")
22 public class CustomerServlet2 extends HttpServlet {
23 private static final long serialVersionUID = 1L;
24
25 //创建一个CustomerDAO对象，多态
26 private CustomerDAO customerDAO = new CustomerDAOJdbcImpl();
27
28 protected void doGet(HttpServletRequest request,
29 HttpServletResponse response) throws ServletException, IOException {
30 doPost(request, response);
31 }
32
33 protected void doPost(HttpServletRequest request,
34 HttpServletResponse response) throws ServletException, IOException {
35 //1.获取servletPath：/add.do 或者 query.do
36 String serveltPath = request.getServletPath();
37
38 // System.out.println(serveltPath);
39 //2.去除/ 和 .do 得到对应的方法，如 add query
40 String methodName = serveltPath.substring(1);
41 methodName = methodName.substring(0, methodName.length() - 3);
42 // System.out.println(methodName);
43
44 try {
45 //3.利用反射获取methodName对应的方法
46 Method method = getClass().getDeclaredMethod(methodName,
47 HttpServletRequest.class, HttpServletResponse.class);
48
49 //4.利用反射调用方法
50 method.invoke(this, request, response);
51 } catch (Exception e) {
52
53 e.printStackTrace();
54 }
55 }
56
57
58
59 private void update(HttpServletRequest request, HttpServletResponse response)
60 throws ServletException, IOException {
61 System.out.println("update");
62
63 }
64
65 private void editeCustomer(HttpServletRequest request,
66 HttpServletResponse response) throws ServletException, IOException {
67 System.out.println("edit");
68
69 }
70
71 private void deleteCustomer(HttpServletRequest request,
72 HttpServletResponse response) throws ServletException, IOException {
73 System.out.println("delete");
74
75 }
76
77 private void query(HttpServletRequest request, HttpServletResponse response)
78 throws ServletException, IOException {
79
80 // //1.调用CustomerDAO 的getAll()方法的到Customer 集合
81 // List<Customer> lists = customerDAO.getAll();
82 //2.把Customer 集合放入request
83 request.setAttribute("list", lists);
84 //3.转发页面到index.jsp
85 request.getRequestDispatcher("/index.jsp").forward(request, response);
86
87 }
88
89 private void addCustomer(HttpServletRequest request,
90 HttpServletResponse response) throws ServletException, IOException {
91 System.out.println("add");
92 }
93
94 }
index.jsp：获取list, 通过脚本遍历list集合，输出到表格中
1 <%@ page language="java" contentType="text/html; charset=UTF-8"
2 pageEncoding="UTF-8"%>
3
4 <%@ page import="com.jsaon.mvcapp.domain.Customer" %>
5 <%@ page import="java.util.List" %>
6
7 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
8 <html>
9 <head>
10 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
11 <title>mve_index</title>
12 </head>
13 <body>
14
15 <form action="query.do" method="post">
16 <table>
17 <tr>
18 <td>CustomerName:</td>
19 <td><input type="text" name="name"/></td>
20 </tr>
21 <tr>
22 <td>Address:</td>
23 <td><input type="text" name="address"/></td>
24 </tr>
25 <tr>
26 <td>Phone:</td>
27 <td><input type="text" name="phone"/></td>
28 </tr>
29 <tr>
30 <td><input type="submit" value="Query"/></td>
31 <td><a href="">Add New Customer</a></td>
32 </tr>
33 </table>
34 </form>
35 <br><br>
36
37 <%
38 List<Customer> lists = (List<Customer>)request.getAttribute("list");
39 if(lists != null && lists.size() > 0 ){
40 %>
41 <hr>
42 <br><br>
43
44 <table border="1" cellpadding="10" cellspacing="0">
45 <tr>
46 <th>ID</th>
47 <th>CustomerName</th>
48 <th>Address</th>
49 <th>Phone</th>
50 <th>Update\Delete</th>
51 </tr>
52
53 <%
54 for(Customer customer : lists){
55 %>
56 <tr>
57 <td><%= customer.getId() %></td>
58 <td><%= customer.getName() %></td>
59 <td><%= customer.getAddress() %></td>
60 <td><%= customer.getPhone() %></td>
61 <td>
62 <a href="">Update</a>
63 <a href="">Delete</a>
64 </td>
65 </tr>
66
67 <%
68 }
69 %>
70
71
72 </table>
73 <%
74 }
75 %>
76
77 </body>
78 </html>
2.模糊查询的设计和 实现
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 criteriaCustomer)。
其中 CriteriaCustomer 用于封装查询条件：name, address, phone。因为查询条件很多时候和 domain 类并不相同，所以要做成一个单独的类
1 package com.jsaon.mvcapp.domain;
2
3
4 /**
5 * @author: jason
6 * @time:2016年5月27日上午9:31:46
7 * @description:封装查询信息的类
8 */
9
10 public class CriteriaCustomer {
11
12 private String name;
13
14 private String address;
15
16 private String phone;
17
18 public String getName() {
19 if(name == null){
20 name = "%%";
21 }else{
22 name = "%"+ name +"%";
23 }
24
25 return name;
26 }
27
28 public void setName(String name) {
29
30 this.name = name;
31 }
32
33 public String getAddress() {
34
35 if(address == null){
36 address = "%%";
37 }else{
38 address = "%"+ address +"%";
39 }
40
41 return address;
42 }
43
44 public void setAddress(String address) {
45 this.address = address;
46 }
47
48 public String getPhone() {
49 if(phone == null){
50 phone = "%%";
51 }else{
52 phone = "%"+ phone +"%";
53 }
54 return phone;
55 }
56
57 public void setPhone(String phone) {
58 this.phone = phone;
59 }
60
61 public CriteriaCustomer(String name, String address, String phone) {
62 super();
63 this.name = name;
64 this.address = address;
65 this.phone = phone;
66 }
67
68 public CriteriaCustomer() {
69 super();
70 }
71
72
73 }
>在CustomerDAOJdbcImpl 中重写CustomerDAO 中的getForListWithCriteriaCustomer(CriteriaCustomer criteriaCustomer)
1 @Override
2 public List<Customer> getForListWithCriteriaCustomer(
3 CriteriaCustomer criteriaCustomer) {
4 //sql语句
5 String sql = "SELECT id,name,address,phone FROM customers WHERE name LIKE ? AND address LIKE ? AND phone LIKE ?";
6 //调用DAO<T> 中的方法getForList(),并且为占位符设置参数
7 return getForList(sql, criteriaCustomer.getName(),criteriaCustomer.getAddress(),criteriaCustomer.getPhone());
8 }
> 拼 SQL： "SELECT id, name, address, phone FROM customers WHERE " + "name LIKE ? AND address LIKE ? ANDphone LIKE ?";
> 为了正确的填充占位符时，重写了 CriteriaCustomer 的 getter方法
1 //获取Name
2 public String getName() {
3 if(name == null){
4 name = "%%";
5 }else{
6 name = "%"+ name +"%";
7 }
8
9 return name;
10 }
11
12 //获取Address
13 public String getAddress() {
14
15 if(address == null){
16 address = "%%";
17 }else{
18 address = "%"+ address +"%";
19 }
20
21 return address;
22 }
23
24 //获取Phone
25 public String getPhone() {
26 if(phone == null){
27 phone = "%%";
28 }else{
29 phone = "%"+ phone +"%";
30 }
31 return phone;
32 }
> 修改 Servlet：获取请求参数；把请求参数封装为 CriteriaCustomer 对象，再调用 getForListWithCriteriaCustomer(CriteriaCustomer cc) 方法
1 private void query(HttpServletRequest request, HttpServletResponse response)
2 throws ServletException, IOException {
3 //设置编码格式
4 request.setCharacterEncoding("UTF-8");
5
6 //获取相应的参数
7 String name = request.getParameter("name");
8 String address = request.getParameter("address");
9 String phone = request.getParameter("phone");
10 //封装请求参数
11 CriteriaCustomer criteriaCustomer = new CriteriaCustomer(name,address,phone);
12
13 //1.调用CustomerDAO 的getForListWithCriteriaCustomer(criteriaCustomer)方法的到Customer 集合
14 List<Customer> lists = customerDAO.getForListWithCriteriaCustomer(criteriaCustomer);
15 //2.把Customer 集合放入request
16 request.setAttribute("list", lists);
17 //3.转发页面到index.jsp
18 request.getRequestDispatcher("/index.jsp").forward(request, response);
19
20 }
前台：同上述的JSP
获取 request 中的 customers 属性
遍历显示
2）代码：
CriteriaCustomer.java
1 package com.jsaon.mvcapp.domain; 2 3 4 /** 5 * @author: jason 6 * @time:2016年5月27日上午9:31:46 7 * @description:封装查询信息的类 8 */ 9 10 public class CriteriaCustomer { 11 12 private String name; 13 14 private String address; 15 16 private String phone; 17 18 public String getName() { 19 if(name == null){ 20 name = "%%"; 21 }else{ 22 name = "%"+ name +"%"; 23 } 24 25 return name; 26 } 27 28 public void setName(String name) { 29 30 this.name = name; 31 } 32 33 public String getAddress() { 34 35 if(address == null){ 36 address = "%%"; 37 }else{ 38 address = "%"+ address +"%"; 39 } 40 41 return address; 42 } 43 44 public void setAddress(String address) { 45 this.address = address; 46 } 47 48 public String getPhone() { 49 if(phone == null){ 50 phone = "%%"; 51 }else{ 52 phone = "%"+ phone +"%"; 53 } 54 return phone; 55 } 56 57 public void setPhone(String phone) { 58 this.phone = phone; 59 } 60 61 public CriteriaCustomer(String name, String address, String phone) { 62 super(); 63 this.name = name; 64 this.address = address; 65 this.phone = phone; 66 } 67 68 public CriteriaCustomer() { 69 super(); 70 } 71 72 73 }
CustomerServlet2.java
1 package com.jason.mvcapp.servlet; 2 3 import java.io.IOException; 4 import java.lang.reflect.Method; 5 import java.util.List; 6 7 import javax.servlet.ServletException; 8 import javax.servlet.annotation.WebServlet; 9 import javax.servlet.http.HttpServlet; 10 import javax.servlet.http.HttpServletRequest; 11 import javax.servlet.http.HttpServletResponse; 12 13 import com.jason.mvcapp.dao.CustomerDAO; 14 import com.jason.mvcapp.dao.impl.CustomerDAOJdbcImpl; 15 import com.jsaon.mvcapp.domain.CriteriaCustomer; 16 import com.jsaon.mvcapp.domain.Customer; 17 18 /** 19 * Servlet implementation class CustomerServlet2 20 */ 21 @WebServlet("*.do") 22 public class CustomerServlet2 extends HttpServlet { 23 private static final long serialVersionUID = 1L; 24 25 //创建一个CustomerDAO对象，多态 26 private CustomerDAO customerDAO = new CustomerDAOJdbcImpl(); 27 28 protected void doGet(HttpServletRequest request, 29 HttpServletResponse response) throws ServletException, IOException { 30 doPost(request, response); 31 } 32 33 protected void doPost(HttpServletRequest request, 34 HttpServletResponse response) throws ServletException, IOException { 35 //1.获取servletPath：/add.do 或者 query.do 36 String serveltPath = request.getServletPath(); 37 38 // System.out.println(serveltPath); 39 //2.去除/ 和 .do 得到对应的方法，如 add query 40 String methodName = serveltPath.substring(1); 41 methodName = methodName.substring(0, methodName.length() - 3); 42 // System.out.println(methodName); 43 44 try { 45 //3.利用反射获取methodName对应的方法 46 Method method = getClass().getDeclaredMethod(methodName, 47 HttpServletRequest.class, HttpServletResponse.class); 48 49 //4.利用反射调用方法 50 method.invoke(this, request, response); 51 } catch (Exception e) { 52 53 e.printStackTrace(); 54 } 55 } 56 57 58 59 private void update(HttpServletRequest request, HttpServletResponse response) 60 throws ServletException, IOException { 61 System.out.println("update"); 62 63 } 64 65 private void editeCustomer(HttpServletRequest request, 66 HttpServletResponse response) throws ServletException, IOException { 67 System.out.println("edit"); 68 69 } 70 71 private void deleteCustomer(HttpServletRequest request, 72 HttpServletResponse response) throws ServletException, IOException { 73 System.out.println("delete"); 74 75 } 76 77 private void query(HttpServletRequest request, HttpServletResponse response) 78 throws ServletException, IOException { 79 request.setCharacterEncoding("UTF-8"); 80 String name = request.getParameter("name"); 81 String address = request.getParameter("address"); 82 String phone = request.getParameter("phone"); 83 84 85 86 CriteriaCustomer criteriaCustomer = new CriteriaCustomer(name,address,phone); 87 List<Customer> lists = customerDAO.getForListWithCriteriaCustomer(criteriaCustomer); 88 89 90 // //1.调用CustomerDAO 的getAll()方法的到Customer 集合 91 // List<Customer> lists = customerDAO.getAll(); 92 //2.把Customer 集合放入request 93 request.setAttribute("list", lists); 94 //3.转发页面到index.jsp 95 request.getRequestDispatcher("/index.jsp").forward(request, response); 96 97 } 98 99 private void addCustomer(HttpServletRequest request, 100 HttpServletResponse response) throws ServletException, IOException { 101 System.out.println("add"); 102 } 103 104 }
CustomerDAOJdbcImpl.java
1 package com.jason.mvcapp.dao.impl;
2
3 import java.util.List;
4
5 import com.jason.mvcapp.dao.CustomerDAO;
6 import com.jason.mvcapp.dao.DAO;
7 import com.jsaon.mvcapp.domain.CriteriaCustomer;
8 import com.jsaon.mvcapp.domain.Customer;
9
10 /**
11 * @author: jason
12 * @time:2016年5月25日下午3:45:06
13 * @description:对CustomerDAO 的实现
14 */
15 public class CustomerDAOJdbcImpl extends DAO<Customer> implements CustomerDAO {
16
17 @Override
18 public List<Customer> getAll() {
19 String sql = "SELECT * FROM customers";
20 return getForList(sql);
21 }
22
23 @Override
24 public void save(Customer customer) {
25 String sql = "INSERT INTO customers(name, address, phone) VALUES(?,?,? )";
26 update(sql,customer.getName(),customer.getAddress(),customer.getPhone());
27 }
28
29
30 @Override
31 public Customer get(Integer id) {
32 String sql = "SELECT id, name, address, phone FROM customers WHERE id = ?";
33 return get(sql,id);
34
35 }
36
37 @Override
38 public void delete(Integer id) {
39 String sql = "DELETE FROM customers WHERE id = ?";
40 update(sql, id);
41 }
42
43 @Override
44 public long getCountWithName(String name) {
45 String sql = "SELECT count(id) FROM customers WHERE name = ?";
46 return getForValue(sql, name);
47 }
48
49 @Override
50 public List<Customer> getForListWithCriteriaCustomer(
51 CriteriaCustomer criteriaCustomer) {
52 //sql语句
53 String sql = "SELECT id,name,address,phone FROM customers WHERE name LIKE ? AND address LIKE ? AND phone LIKE ?";
54 //调用DAO<T> 中的方法getForList(),并且为占位符设置参数
55 return getForList(sql, criteriaCustomer.getName(),criteriaCustomer.getAddress(),criteriaCustomer.getPhone());
56 }
57
58 }
CustomerDAO.java
1 package com.jason.mvcapp.dao;
2
3 import java.util.List;
4
5 import com.jsaon.mvcapp.domain.CriteriaCustomer;
6 import com.jsaon.mvcapp.domain.Customer;
7
8
9 /**
10 * @author: jason
11 * @time:2016年5月25日下午3:28:00
12 * @description:
13 */
14
15 public interface CustomerDAO {
16
17 //带参数的模糊查询
18 public List<Customer> getForListWithCriteriaCustomer(CriteriaCustomer criteriaCustomer);
19 //查询所有
20 public List<Customer> getAll();
21
22 //保存操作
23 public void save(Customer coustomer);
24
25 //更新前，先查询
26 public Customer get(Integer id);
27
28 //删除用户
29 public void delete(Integer id);
30
31 //查看与参数相同的名字有多少个记录数
32 public long getCountWithName(String name);
33
34 }
index.jsp 同上面的一样
总结：
1）理解代码的层次
2）在从前台获取参数的时候，一定要设置 request.setCharacterEncoding("UTF-8"); 。我在跑测试的时候，由于没有设置编码格式，在debug 运行的时候，获取到的 name，address，phone都是乱码。一定注意
