JAvaweb学習1_mvcケース(part 4)ファジイクエリの設計と実装
20771 ワード
1.クエリー操作:
1)考え方:
サーブレット:クエリーメソッドで、CustomerDAOオブジェクトを作成し、getAll()メソッドを呼び出し、Listコレクションを取得し、保証を利用してrequset、転送、取得、遍歴
//1. CustomerDAOのgetAll()を呼び出して、Customerの集合List customers=customerDAOを得る.getAll();
//2. Customerの集合をrequestに入れるrequest.setAttribute("customers", customers);
//3. ページをindexに転送します.jsp(リダイレクトは使用できません)request.getRequestDispatcher("/index.jsp").forward(request, response);
JSP
リクエストのcustomersプロパティの取得
ループ表示
2)コード:
CustomerServlet2.java
サーブレットのprivate void query(HttpServeretRequest request,HttpServeretResponse response)throwsサーブレットException,IOException{}メソッド
index.jsp:listを取得し、スクリプトでlistセットを巡り、テーブルに出力する
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クラスと異なる場合が多いので、個別のクラスにします
>CustomerDAOJdbcImplでCustomerDAOのgetForListWithCriteriaCustomerを書き換える(CriteriaCustomer criteriaCustomer)
>つづりSQL:「SELECT id,name,address,phone FROM customers WHERE」+「name LIKE?AND address LIKE?ANDphone LIKE?」
>プレースホルダを正しく埋め込むためにCriteriaCustomerのgetterメソッドを書き換えた
>サーブレットの変更:要求パラメータの取得;要求パラメータをCriteriaCustomerオブジェクトにカプセル化し、getForListWithCriteriaCustomer(CriteriaCustomer cc)メソッドを呼び出す
フロント:上記のJSP
リクエストのcustomersプロパティの取得
ループ表示
2)コード:
CustomerServlet2.java
CustomerDAOJdbcImpl.java
index.jspは上と同じです
まとめ:
1)コードの階層を理解する
2)フロントからパラメータを取得する場合はrequestを必ず設定.setCharacterEncoding("UTF-8"); .私がテストを走っている間、符号化フォーマットが設定されていなかったので、debugが実行されている間に取得したname、address、phoneは文字化けしていました.必ず注意する
1)考え方:
サーブレット:クエリーメソッドで、CustomerDAOオブジェクトを作成し、getAll()メソッドを呼び出し、Listコレクションを取得し、保証を利用してrequset、転送、取得、遍歴
//1. CustomerDAOのgetAll()を呼び出して、Customerの集合List customers=customerDAOを得る.getAll();
//2. Customerの集合をrequestに入れるrequest.setAttribute("customers", customers);
//3. ページをindexに転送します.jsp(リダイレクトは使用できません)request.getRequestDispatcher("/index.jsp").forward(request, response);
JSP
リクエストのcustomersプロパティの取得
ループ表示
2)コード:
CustomerServlet2.java
サーブレットのprivate void query(HttpServeretRequest request,HttpServeretResponse response)throwsサーブレットException,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 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
3
4
5
6
7
8
9
10
11 mve_index
12
13
14
15
35
36
37 lists = (List)request.getAttribute("list");
39 if(lists != null && lists.size() > 0 ){
40 %>
41
42
43
44
45
46
47
48
49
50
51
52
53
56
57
65
66
67
70
71
72 ID CustomerName Address Phone Update\Delete
58
59
60
61
62 Update
63 Delete
64
73
76
77
78 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 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 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 }>サーブレットの変更:要求パラメータの取得;要求パラメータを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 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
リクエストの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 lists = customerDAO.getForListWithCriteriaCustomer(criteriaCustomer);
88
89
90 // //1. CustomerDAO getAll() Customer
91 // List 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 implements CustomerDAO {
16
17 @Override
18 public List 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 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 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 getForListWithCriteriaCustomer(CriteriaCustomer criteriaCustomer);
19 //
20 public List 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は文字化けしていました.必ず注意する