검색조건 (select 쿼리문) >>>>> 검색결과
-다양한 경우의 수 존재
m_search_form.jsp에 검색 화면을 생성
<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<form action="<%=request.getContextPath()%>/msearch/m_search_list.jsp" method="post">
<select name="sk"> <!-- sk=m_id & sv=입력한값 -->
<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" name="검색버튼">
</form>m_search_list.jsp에 변수 sk / sv를
<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ include file = "/msearch/m_search_form.jsp" %>
<%
request.setCharacterEncoding("euc-kr");
String sk = request.getParameter("sk");
String sv = request.getParameter("sv");
System.out.println(sk + "<-sk");
System.out.println(sv + "<-sv");
%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ include file = "/msearch/m_search_form.jsp" %>
<%
request.setCharacterEncoding("euc-kr");
String sk = request.getParameter("sk");
String sv = request.getParameter("sv");
System.out.println(sk + "<-sk");
System.out.println(sv + "<-sv");
if(sk == null && sv == null){
System.out.println("1-1 sk, sv 둘 다 null");
} else if (sk != null & sv.equals("")){
System.out.println("1-2 sk O / sv 공백");
} else if (sk != null & sv != null){
System.out.println("1-3 sk, sv 둘 다 있음");
}
%><%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ 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" %>
<a href="<%= request.getContextPath() %>/minsert/m_insert_form.jsp">회원가입버튼</a><br><br>
<%@ include file="/msearch/m_search_form.jsp" %>
<br>
회원 리스트 <br>
<table width="100%" border="1">
<tr>
<td>아이디</td><td>비번</td><td>권한</td><td>이름</td><td>이메일</td><td>수정</td><td>삭제</td>
</tr>
<%
request.setCharacterEncoding("euc-kr");
String sk = request.getParameter("sk");
String sv = request.getParameter("sv");
System.out.println(sk + "<-- sk ");
System.out.println(sv + "<-- sv ");
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Class.forName("com.mysql.jdbc.Driver");
try{
String jdbcDriver = "jdbc:mysql://localhost:3306/dev35db?" +
"useUnicode=true&characterEncoding=euckr";
String dbUser = "dev35id";
String dbPass = "dev35pw";
conn = DriverManager.getConnection(jdbcDriver, dbUser, dbPass);
System.out.println(conn + "<-- conn m_list.jsp");
String selectQuery = "select * from tb_member";
if(sk == null & sv == null){
System.out.println("01_01 둘다 null 조건");
pstmt = conn.prepareStatement(selectQuery);
}else if(sk != null & sv.equals("")){
System.out.println("01_02 sk있고 sv는 공백 조건");
pstmt = conn.prepareStatement(selectQuery);
}else if(sk != null & sv != null){
System.out.println("01_03 sk있고 sv있는 조건");
pstmt = conn.prepareStatement(selectQuery + " WHERE " + sk + " = ?");
pstmt.setString(1, sv);
}
out.println(pstmt + " <br><br>");
System.out.println(pstmt + "<-- pstmt m_list.jsp");
rs = pstmt.executeQuery();
System.out.println(rs + "<-- rs m_list.jsp");
//System.out.println(rs.next() + "<-- rs.next() m_list.jsp");
//if(rs.next()){
while(rs.next()){
System.out.println("while 조건문 통과완료");
%>
<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>
<!-- http://localhost:8007/mysqljsp35/mupdate/m_update_form.jsp?send_id=id005 -->
</td>
<td>
<a href="<%= request.getContextPath() %>/mdelete/m_delete_pro.jsp?send_id=<%= rs.getString("m_id")%>">삭제버튼</a>
</td>
</tr>
<%
}
} catch(SQLException ex) {
System.out.println(ex.getMessage());
ex.printStackTrace();
} finally {
if (rs != null) try { rs.close(); } catch(SQLException ex) {}
if (pstmt != null) try { pstmt.close(); } catch(SQLException ex) {}
if (conn != null) try { conn.close(); } catch(SQLException ex) {}
}
%>
</table>
pstmt = conn.prepareStatement("SELECT * FROM tb_member WHERE ? = ?");
pstmt.setString(1, sk);
pstmt.setString(2, sv);
conn.prepareStatement 메서드에 쿼리문을 위 처럼 설정했을 경우
쿼리문장이 ex) SELECT * FROM tb_member WHERE 'm_id' = 'id' 이렇게 입력되어 오류가 발생한다.
"select * from tb_member where "+ sk + "= ? "때문에 위와 같이 문자열 연결 연산자를 통해 sk를 연결해주었다.
검색이 잘 된다!
'JSP' 카테고리의 다른 글
| [JSP + MySQL] 200416 dto dao // select 쿼리 분리 (0) | 2020.04.16 |
|---|---|
| [JSP + MySQL] Layout과 처리화면에 함께 출력하기 (0) | 2020.03.27 |
| [JSP + MySQL] PreparedStatement vs. Statement / Execute vs. ExecuteQuery vs. ExecuteUpdate (0) | 2020.03.26 |
| [JSP + MySQL] list 화면→수정화면 (0) | 2020.03.26 |