[[ Dynamic query ]]
- 동적으로 조건이 변해야 하는 경우 Mybatis에서 사용 예제
- 두가지 :
(1) select 로 하나만 선택하는 경우
(2) checkbox로 여러개 선택하는 경우
(1) main.jsp검색( select ) ,검색2 (checkbox)
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<a href="MemberCon?cmd=list1">검색</a><br/>
<a href="MemberCon?cmd=list2">검색2</a><br/>
</body>
</html>
(2) list1.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<div><!-- 조회된 목록이 보여질 div -->
<table border="1" width="600">
<tr>
<th>아이디</th><th>비밀번호</th><th>이메일</th>
<th>전화번호</th><th>가입일</th>
</tr>
<c:forEach var="vo" items="${list }">
<tr>
<td>${vo.id }</td>
<td>${vo.pwd }</td>
<td>${vo.email }</td>
<td>${vo.phone }</td>
<td>${vo.regdate }</td>
</tr>
</c:forEach>
</table>
</div>
<div>
<form method="post" action="MemberCon?cmd=list1">
<select name="field">
<option value="id">아이디</option>
<option value="email">이메일</option>
<option value="phone">전화번호</option>
</select>
<input type="text" name="keyword"/>
<input type="submit" value="검색"/>
</form>
</div>
</body>
</html>
(3) list2.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<div>
<table border="1" width="600">
<tr>
<th>아이디</th><th>비밀번호</th><th>이메일</th>
<th>전화번호</th><th>가입일</th>
</tr>
<c:forEach var="vo" items="${list }">
<tr>
<td>${vo.id }</td>
<td>${vo.pwd }</td>
<td>${vo.email }</td>
<td>${vo.phone }</td>
<td>${vo.regdate }</td>
</tr>
</c:forEach>
</table>
</div>
<div>
<form method="post" action="MemberCon?cmd=list2">
<input type="checkbox" name="chkId" value="id" checked="checked">아이디
<input type="checkbox" name="chkEmail" value="email" >이메일
<input type="checkbox" name="chkPhone" value="phone" >전화번호
<input type="text" name="keyword"/>
<input type="submit" value="검색"/>
</form>
</div>
</body>
</html>
---------------------------------------------------------------------------------
: lib에 jar파일 복사
----------------------------------------------------------------------------------
: controller, Dao, vo, sqlsession생성, xml, log4j 기본파일
----------------------------------------------------------------------------------
(1) test.orm.mybatis-config.xml
;
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--//// 접속할 DB서버에 대한 정보 설정 ////-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="oracle.jdbc.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />
<property name="username" value="scott" />
<property name="password" value="tiger" />
</dataSource>
</environment>
</environments>
<!--/////// sql구문이 들어있는 SQL Mapper파일 포함 ////////// -->
<mappers>
<mapper resource="test/orm/BoardMapper.xml" />
</mappers>
</configuration>
(2) test.orm.BoardMapper.xml
;
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="test.orm.BoardMapper">
<!--
where id like '%aa%'
where ${field} like '%'||#{keyword}||'%'
${}는 미리 컴파일 됨.컬럼명등은 ?로 쓸수 없으므로 ${}로 처리해야 함
-->
<select id="findList1" resultType="test.vo.MembersVo"
parameterType="hashmap">
select * from members
<if test="field != null">
where ${field} like '%'||#{keyword}||'%'
</if>
</select>
<!--
String ss =" select * from members ";
if(id!=null){
ss+="where id=keyword";
}
if(pwd!=null){
ss+="or pwd=keyword";
}
이와 같이 경우
-->
<select id="findList2" parameterType="hashmap"
resultType="test.vo.MembersVo">
select * from members
<where>
<!--
where 다음에 if문에 해당하는 조건이 하나라도 있으면 select뒤에 where가 붙는다.
or는 붙이며, 이전 항목이 없으면 자동으로 빼고 실행한다.
-->
<if test="id!=null">
id=#{keyword}
</if>
<if test="email!=null">
or email=#{keyword}
</if>
<if test="phone!=null">
or phone=#{keyword}
</if>
</where>
</select>
</mapper>
(3) test.orm.SqlSessionFactoryService
;
package test.orm;
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class SqlSessionFactoryService {
private static SqlSessionFactory sqlSessionFactory;
//static멤버변수를 초기화할때는 static블록을 사용한다.(생성자x)
static{
try{
InputStream is=
Resources.getResourceAsStream("test/orm/mybatis-config.xml");
sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
}catch(IOException ie){
System.out.println(ie.getMessage());
}
}
public static SqlSessionFactory getSqlSessionFactory() {
return sqlSessionFactory;
}
}
(4) test.vo.MembersVo
; getter, setter
package test.vo;
import java.sql.Date;
public class MembersVo {
private String id;
private String pwd;
private String email;
private String phone;
private Date regdate;
public MembersVo() {}
public MembersVo(String id, String pwd, String email, String phone,
Date regdate) {
super();
this.id = id;
this.pwd = pwd;
this.email = email;
this.phone = phone;
this.regdate = regdate;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public Date getRegdate() {
return regdate;
}
public void setRegdate(Date regdate) {
this.regdate = regdate;
}
}
(5) test.dao.MembersDao
;
package test.dao;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import test.orm.SqlSessionFactoryService;
import test.vo.MembersVo;
public class MembersDao {
private SqlSessionFactory sqlSessionFactory;
public MembersDao(){
sqlSessionFactory=SqlSessionFactoryService.getSqlSessionFactory();
}
public List<MembersVo> getList(Map<String,String> map){
SqlSession sqlSession=null;
try{
sqlSession=sqlSessionFactory.openSession();
return sqlSession.selectList("findList1",map);
}finally{
if(sqlSession!=null) sqlSession.close();
}
}
public List<MembersVo> getList1(HashMap<String,String> map){
SqlSession sqlSession = null;
try{
sqlSession=sqlSessionFactory.openSession();
return sqlSession.selectList("findList2", map);
}finally{
if(sqlSession!=null) sqlSession.close();
}
}
}
(6) test.controller.MemberController
;
package test.controller;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import test.dao.MembersDao;
import test.vo.MembersVo;
@WebServlet("/MemberCon")
public class MemberController extends HttpServlet{
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
String cmd=req.getParameter("cmd");
if(cmd.equals("list1")){
list1(req,resp);
}else if(cmd.equals("list2")){
list2(req,resp);
}
}
// 체크박스로 선택된 조건 ( 여러개 인 경우 )
protected void list2(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
// 전송된 체크박스의 value값들 얻어오기.
// 체크가 안된것은 null이 들어온다.
String id = req.getParameter("chkId");
String email = req.getParameter("chkEmail");
String phone = req.getParameter("chkPhone");
String keyword=req.getParameter("keyword");
//검색조건을 HashMap에 담기.
HashMap<String,String> map=new HashMap<>();
map.put("id", id);
map.put("email", email);
map.put("phone", phone);
map.put("keyword", keyword);
// dao를 통해서 조건에 해당하는 데이터 얻어오기.
MembersDao dao = new MembersDao();
List<MembersVo> list=dao.getList1(map);
//결과값을 가지고 뷰페이지로 이동하기.
req.setAttribute("list", list);
req.getRequestDispatcher("/list2.jsp").forward(req, resp);
}
// 셀렉트로 선택한 조건 ( 하나 )
protected void list1(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
//검색필드 얻어오기
String field=req.getParameter("field");
//검색어 얻어오기
String keyword=req.getParameter("keyword");
//검색조건을 HashMap에 담기
Map<String,String> map=new HashMap<>();
map.put("field",field);
map.put("keyword",keyword);
//DAO를 통해서 DB에서 조회하기
MembersDao dao=new MembersDao();
List<MembersVo> list=dao.getList(map);
//결과값을 가지고 뷰페이지로 이동하기
req.setAttribute("list",list);
req.getRequestDispatcher("/list1.jsp").forward(req, resp);
}
}
(7) log4j.properties
; 로그 확인용
# \uC804\uC5ED \uB85C\uAE45 \uC124\uC815
log4j.rootLogger=ERROR, stdout
# MyBatis \uB85C\uAE45 \uC124\uC815...
log4j.logger.org.apache.ibatis=DEBUG
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
log4j.logger.java.sql.ResultSet=TRACE
# Console \uCD9C\uB825...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
