23_jsp만_search처리_jsp
2022. 2. 2. 11:55ㆍksmart_jsp/05_jsp_mysql
728x90
| 폴더명 | msearch |
| 검색화면 | m_search_form.jsp |
| 검색후리스트화면 | m_search_list.jsp |
1. 검색 조건 화면
<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<form action="<%=request.getContextPath()%>/msearch/m_search_list.jsp" method="post">
<select name="sk">
<option value="">::선택::</option>
<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">
<input type="submit" value="검색버튼">
</form>
//m_search_list.jsp
<%@ include file="/msearch/m_search_form.jsp" %>2. 조건별 SELECT 쿼리
1번 조건 ) sk 값X & sv 값X
null <-- sk
null <-- sv
2번 조건) sk 값O & sv 값X
m_id <-- sk
null <-- sv
3번 조건 ) sk 값O & sv 값O
m_id <-- sk
id001 <-- sv
m_level <-- sk
관리자 <-- sv
m_name <-- sk
홍01 <-- sv
m_email <-- sk
test03 <-- sv
//m_search_list.jsp
if (sk.equals("") && sv.equals("")) {
System.out.println("1-1 sk 값X & sv 값X");
} else if (sk != null && sv.equals("")) {
System.out.println("1-2 sk 값X & sv 값O");
} else {
System.out.println("1-3 sk 값O & sv 값O");
}
3. 조건별 쿼리 준비
if (sk.equals("") && sv.equals("")) {
System.out.println("1-1 sk 값X & sv 값X");
pstmt = conn.prepareStatement("SELECT * FROM tb_member");
} else if (sk != null && sv.equals("")) {
System.out.println("1-2 sk 값X & sv 값O");
pstmt = conn.prepareStatement("SELECT * FROM tb_member");
} else {
System.out.println("1-3 sk 값O & sv 값O");
if (sk.equals("m_id")) {
System.out.println("sk가 m_id 일때");
pstmt = conn.prepareStatement("SELECT * FROM tb_member WHERE m_id=?");
} else if (sk.equals("m_level")) {
System.out.println("sk가 m_level 일때");
pstmt = conn.prepareStatement("SELECT * FROM tb_member WHERE m_level=?");
} else if (sk.equals("m_name")) {
System.out.println("sk가 m_name 일때");
pstmt = conn.prepareStatement("SELECT * FROM tb_member WHERE m_name=?");
} else {
System.out.println("sk가 m_email 일때");
pstmt = conn.prepareStatement("SELECT * FROM tb_member WHERE m_email=?");
}
pstmt.setString(1, sv);
}
rs = pstmt.executeQuery();//m_search_form.jsp
<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<form action="<%=request.getContextPath()%>/msearch/m_search_list.jsp" method="post">
<select name="sk">
<option value="">::선택::</option>
<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">
<input type="submit" value="검색버튼">
</form>//m_search_list.jsp
<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<%@ page import="java.sql.DriverManager"%>
<%@ page import="java.sql.Connection"%>
<%@ page import="java.sql.PreparedStatement"%>
<%@ page import="java.sql.SQLException"%>
<%@ page import="java.sql.ResultSet"%>
<%@ include file="/msearch/m_search_form.jsp"%>
<table border="1" width="100%">
<tr>
<th>아이디</th>
<th>비밀번호</th>
<th>권한</th>
<th>이름</th>
<th>이메일</th>
<th>수정</th>
<th>삭제</th>
</tr>
<%
PreparedStatement pstmt = null;
Connection conn = null;
ResultSet rs = null;
String sk = request.getParameter("sk");
String sv = request.getParameter("sv");
System.out.println(sk + "<--sk");
System.out.println(sv + "<--sv");
request.setCharacterEncoding("euc-kr");
Class.forName("com.mysql.jdbc.Driver");
String jdbcDriver = "jdbc:mysql://localhost:3306/dev42db?" + "useUnicode=true&characterEncoding=euckr";
String dbUser = "dev42id";
String dbPass = "dev42pw";
conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPass);
if (sk.equals("") && sv.equals("")) {
System.out.println("1-1 sk 값X & sv 값X");
pstmt = conn.prepareStatement("SELECT * FROM tb_member");
} else if (sk != null && sv.equals("")) {
System.out.println("1-2 sk 값X & sv 값O");
pstmt = conn.prepareStatement("SELECT * FROM tb_member");
} else {
System.out.println("1-3 sk 값O & sv 값O");
if (sk.equals("m_id")) {
System.out.println("sk가 m_id 일때");
pstmt = conn.prepareStatement("SELECT * FROM tb_member WHERE m_id=?");
} else if (sk.equals("m_level")) {
System.out.println("sk가 m_level 일때");
pstmt = conn.prepareStatement("SELECT * FROM tb_member WHERE m_level=?");
} else if (sk.equals("m_name")) {
System.out.println("sk가 m_name 일때");
pstmt = conn.prepareStatement("SELECT * FROM tb_member WHERE m_name=?");
} else {
System.out.println("sk가 m_email 일때");
pstmt = conn.prepareStatement("SELECT * FROM tb_member WHERE m_email=?");
}
pstmt.setString(1, sv);
}
rs = pstmt.executeQuery();
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_action.jsp?send_id=<%=rs.getString("m_id")%>">삭제버튼</a></td>
</tr>
<%
}
rs.close();
pstmt.close();
conn.close();
%>728x90
'ksmart_jsp > 05_jsp_mysql' 카테고리의 다른 글
| 25_jsp만_layout포함(로그인처리전)_jsp (0) | 2022.02.02 |
|---|---|
| 24_include_redirect_리스트화면연결_jsp (0) | 2022.02.02 |
| 22_jsp만_delete처리_jsp (0) | 2022.02.02 |
| 21_02_jsp만_ update처리_jsp (0) | 2022.02.02 |
| 21_01_jsp만_ update화면_jsp (0) | 2022.02.02 |