5단계 JSP만 검색처리

728x90

5단계 JSP만 검색처리

1. 검색 조건 화면 작성

검색키(select태그)

검색값(input type text태그) : 유사검색어는 X

m_search_form.jsp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>회원 검색 화면</title>
</head>
<body>
    <form action="<%=request.getContextPath()%>/msearch/m_search_list.jsp" method="post">
        <select name="sk"><!-- 검색키 : search key -->
            <option value="m_id">아이디</option>
            <option value="m_level">권한</option>
            <option value="m_name">이름</option>
            <option value="m_email">이메일</option>
        </select>
        <input type="text" name="sv"><!-- 검색값 : search value -->
        <input type="submit" value="검색버튼">
    </form>
</body>
</html>
Colored by Color Scripter
cs

검색창 화면

2. 검색처리를 위해 검색키와 검색값을 넘긴다.

m_search_form.jsp(검색키, 검색값) -> m_search_list.jsp(검색키, 검색값)

1
<form action="<%=request.getContextPath()%>/msearch/m_search_list.jsp" method="post">
cs

3. 넘어온 검색키와 검색값을 받는다.

m_search_list.jsp(검색키와 검색값으로 리스트 출력)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import = "java.sql.DriverManager"%>
<%@ page import = "java.sql.Connection"%>
<%@ page import = "java.sql.PreparedStatement"%>
<%@ page import = "java.sql.ResultSet"%>
<%@ page import = "java.sql.SQLException"%>
 
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>회원정보 검색 리스트</title>
</head>
<body>
    <h4>회원 리스트</h4>
    <table width="100%" border="1">
        <tr>
            <td>아이디</td>
            <td>비밀번호</td>
            <td>권한</td>
            <td>이름</td>
            <td>이메일</td>
            <td>수정</td>
            <td>삭제</td>
        </tr>
    <%
    request.setCharacterEncoding("utf-8");
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    //JDBC 프로그램 순서
    //01단계 :드라이버 로딩 시작
    Class.forName("com.mysql.jdbc.Driver");
    String sk = request.getParameter("sk");
    String sv = request.getParameter("sv");
    System.out.println(sk + "<-- sk m_search_list.jsp");
    System.out.println(sv + "<-- sv m_search_list.jsp");
    
    
    try{
        String jdbcDriver = "jdbc:mysql://localhost:3306/doublesdb?" +
                "useUnicode=true&characterEncoding=euckr";
        String dbUser = "doublesid";
        String dbPass = "doublespw";
        conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPass);
        if(sk == null & sv == null){
            out.println("01 sk null, sv null인 조건");
            pstmt = conn.prepareStatement("select * from tb_member");
            //select * from tb_member; 
        }else if(sk != null & sv.equals("")){
            out.println("02 sk 값O, sv 공백 조건");
            pstmt = conn.prepareStatement("select * from tb_member");
            //select * from tb_member; 
        }else if(sk != null & sv != null){
            out.println("03 sk, sv 둘다 값 O");
            pstmt = conn.prepareStatement("select * from tb_member where "+sk+"=?");
            pstmt.setString(1, sv);
            //select * from tb_member where m_id='id001'; 
            //select * from tb_member where m_level='관리자'; 
            //select * from tb_member where m_name='홍02'; 
            //select * from tb_member where m_email='email03';  
        }
        out.println("<br>"+pstmt + "<--- pstmt");
        rs = pstmt.executeQuery();
        System.out.println(rs + "<-- rs m_list.jsp");
        while(rs.next()){
   %>
        <tr>
            <td><%= rs.getString("m_id")%></td>
            <td><%= rs.getString("m_pw")%></td>
            <td><%= rs.getString("m_level")%></td>
            <td><%= rs.getString("m_name")%></td>
            <td><%= rs.getString("m_email")%></td>
            <td>
                <a href="<%= request.getContextPath() %>
                /mupdate/m_update_form.jsp?send_id=<%= rs.getString("m_id")%>">수정클릭</a>            
            </td>
            <td>
                <a href="<%= request.getContextPath() %>
                /mdelete/m_delete_pro.jsp?send_id=<%= rs.getString("m_id")%>">삭제클릭</a>            
            </td>
        </tr>
        
    <%        
        }    
    
    }catch(SQLException ex) {
        out.println(ex.getMessage());
        ex.printStackTrace();
    
    } finally {
        // 6. 사용한 Statement 종료
        if (rs != null) try { rs.close(); } catch(SQLException ex) {}
        if (pstmt != null) try { pstmt.close(); } catch(SQLException ex) {}
        
        // 7. 커넥션 종료
        if (conn != null) try { conn.close(); } catch(SQLException ex) {}
    }
   %>
    </table>
</body>
</html>
Colored by Color Scripter
cs

4. 조건별로 쿼리문장 중 하나만 완성

1) 검색된 내용을 화면에 출력하기 위해 검색키와 검색값의 유무에 따른 조건 분기

검색키 X , 검색값 X

검색키 O , 검색값 X

검색키 O , 검색값 O

2) 검색키가 있을 경우 조건분기

검색키가 m_id 인 경우

검색킥가 m_pw 인 경우

검색키가 m_level 인 경우

검색키가 m_email 인경우

1), 2) 조건문 분기

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
    if(sk == null & sv == null){
        out.println("01 sk 널 sv 널인 조건");
        pstmt = conn.prepareStatement("select * from tb_member");
        //select * from tb_member; 
    }else if(sk != null & sv.equals("")){
        out.println("02 sk 값있고 sv 공백 조건");
        pstmt = conn.prepareStatement("select * from tb_member");
        //select * from tb_member; 
    }else if(sk != null & sv != null){
        out.println("03 sk sv 둘다 있는 조건");
        if(sk.equals("m_id")){
            pstmt = conn.prepareStatement("select * from tb_member where m_id=?");
        }else if(sk.equals("m_level")){
            pstmt = conn.prepareStatement("select * from tb_member where m_level=?");
        }else if(sk.equals("m_name")){
            pstmt = conn.prepareStatement("select * from tb_member where m_name=?");
        }else if(sk.equals("m_email")){
            pstmt = conn.prepareStatement("select * from tb_member where m_email=?");
        }
        pstmt.setString(1, sv);
    }
Colored by Color Scripter
cs

검색키 값을 select문의 where절에 변수로 대입하면 위의 조건보다 간결하게 코드를 작성할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
if(sk == null & sv == null){
        out.println("01 sk null, sv null인 조건");
        pstmt = conn.prepareStatement("select * from tb_member");
    }else if(sk != null & sv.equals("")){
        out.println("02 sk 값O, sv 공백 조건");
        pstmt = conn.prepareStatement("select * from tb_member");
    }else if(sk != null & sv != null){
        out.println("03 sk, sv 둘다 값 O");
        pstmt = conn.prepareStatement("select * from tb_member where "+sk+"=?");
        pstmt.setString(1, sv);
    }
Colored by Color Scripter
cs