jdbc - 검색하기

728x90

1. 일단 검색 폼부터 만듬만듬만두...만두... 배고파...

<%@ 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">
		<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>

2. 그러고나서 검색 결과를 불러올 리스트를 만든다.

검색한 내용을 불러오기 위해 코드를 작성하고

<%
	request.setCharacterEncoding("euc-kr");
	String sk = request.getParameter("sk");
	String sv = request.getParameter("sv");
	System.out.println(sk + " <- 이거슨 sk");
	System.out.println(sv + " <- 이거슨 sv");
 %>

잘 받아와지는지 확인 해봄

3. 그러고 나서 이걸로 어뜨케 검색할 지 생각한다.

"생각하라 인간"

4. 조건에 대해 생각하기

<%@ 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">


<%
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("01_01 조건없이 검색");
}else if(sk != null & sv.equals("")){
	System.out.println("01_02 sk 있고 sv는 공백 조건");
}else if(sk != null & sv != null){
	System.out.println("01_03 sk 있고 sv 있는 조건");
}

① 아무 조건 없이 검색하기

② 카테고리 정하고 검색어 없이 검색하기

③ 카테고리 정하고 검색어 있이 검색하기

5. 조건에 대해 생각했으면 이제 이걸 어떻게 활용 할 것인가?

방법 3가지로 정리 해보자.

방법 1] 조원의 도움으로 성공한 나의 코드 무식하게 때려돌림ㅋㅋㅋ

<%@ 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" %>

<%@ 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>
</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");

if(sk == null & sv == null){
	System.out.println("01_01 둘다 null 조건");
}else if(sk != null & sv.equals("")){
	System.out.println("01_02 sk 있고 sv는 공백 조건");
}else if(sk != null & sv != null){
	System.out.println("01_03 sk 있고 sv 있는 조건");

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;

Class.forName("com.mysql.jdbc.Driver");
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");


	if(sk == null & sv == null){
		pstmt = conn.prepareStatement("SELECT * FROM tb_member");
	}else 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);
	System.out.println(pstmt + "<-- 검색");
	rs = pstmt.executeQuery();
	System.out.println(rs + "<-- rs m_list.jsp");
	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>
</tr>	
<%
	}
pstmt.close();
conn.close();
}
%>
</table>

방법 2] 조금덜 무식하게 때려 돌림

<%@ 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" %>

<%@ 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");
	
	if(sk == null & sv == null){
	System.out.println(pstmt + "<-- pstmt m_list.jsp");
	pstmt = conn.prepareStatement("select * from tb_member");
	}else if(sk != null & sv.equals("")){
		System.out.println("01_02 sk 있고 sv는 공백 조건");
		pstmt = conn.prepareStatement("select * from tb_member");
	}else if(sk != null & sv != null){
		System.out.println("01_03 sk 있고 sv 있는 조건");
		if(sk.equals("m_id")){
			System.out.println("02_01 sk변수에 담긴 값이 m_id 인 조건");
			pstmt = conn.prepareStatement("select * from tb_member where m_id =?");
		}else if(sk.equals("m_level")){
			System.out.println("02_02 sk변수에 담긴 값이 m_level 인 조건");
			pstmt = conn.prepareStatement("select * from tb_member where m_level =?");
		}else if(sk.equals("m_name")){
			System.out.println("02_03 sk변수에 담긴 값이 m_name 인 조건");
			pstmt = conn.prepareStatement("select * from tb_member where m_name =?");
		}else if(sk.equals("m_email")){
			System.out.println("02_04 sk변수에 담긴 값이 m_email 인 조건");
			pstmt = conn.prepareStatement("select * from tb_member where m_email =?");
		}
		pstmt.setString(1, sv);
	}
	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>

3] 변수 선언해서 조금 덜 지저분해 보이기

<%@ 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>

<%@ 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(pstmt + "<-- pstmt m_list.jsp");
	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>");
	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>

저작자표시

'Backend > JAVA' 카테고리의 다른 글

JAVA txt로 작성하여 cmd로 컴파일 및 실행하기 (0)	2020.04.14
jdbc 로그인 처리하기 (0)	2020.03.27
jdbc update 쿼리 실행 (0)	2020.03.26
jdbc SELECT 쿼리 실행 (0)	2020.03.20
jsp -db 연결 중 error (0)	2020.03.19

#006888

jdbc - 검색하기

'Backend > JAVA' 카테고리의 다른 글

댓글

티스토리툴바

jdbc - 검색하기

'Backend > JAVA' 카테고리의 다른 글

관련글

댓글

티스토리툴바