본문 바로가기
IT/MyBatis

[MyBatis] 마이바티스 날짜 조건으로 조회

by dya0 2019. 6. 19.

member.xml

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
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 
<mapper namespace="Member1">
 
    <select id="selectdate" parameterType="model.date"
        resultType="model.Employee">
        select * from emp
        where hiredate between #{startday} and #{endday}
    </select>
    <select id="termlist" parameterType="map"
        resultType="model.Employee">
        select * from emp
        where hiredate between #{startday} and #{endday}
    </select>
    <select id="termlist2" parameterType="map"
        resultType="model.Employee">
 
        <if test='startday=="" and endday ==""'>
            select * from emp
        </if>
        <if test='startday!="" and endday !=""'>
            select * from emp where hiredate between #{startday} and #{endday}
        </if>
<!--둘 다 됨 따로 써야 함-->
        select * from emp
        <where>
            <if test='startday!="" and endday !=""'>
                hiredate between #{startday} and #{endday}
            </if>
        </where>
 
    </select>
</mapper>
 
 

 date1.jsp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>Insert title here</title>
</head>
<body>
<form action="<%=request.getContextPath()%>/term.net" method ="post">
    <label>조회기간</label><br>
    <input type="date" name="startday" min="1980-01-01" max="1987-12-31" required>
    ~
    <input type="date" name="endday" min="1980-01-01" max="1987-12-31" required>
    <input type="submit" value="검색"/>
 
</form>
</body>
</html>
 
 

DateAction.java

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
package jsp;
 
 
 
import dao.EmpDao;
import model.Employee;
import model.date;
 
public class DateAction implements Action {
 
    @Override
    public ActionForward execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
        EmpDao ed= new EmpDao();
        date d= new date();
        Employee emp = new Employee();
        List<Employee> list= new ArrayList<Employee>();
        String startday = request.getParameter("startday");
        String endday = request.getParameter("endday");
        
 
        d.setEndday(endday);
        d.setStartday(startday);
        list= ed.search(d);
        
        ActionForward forward = new ActionForward();
        
        forward.setPath("question/emplist.jsp");
        forward.setRedirect(false);
        request.setAttribute("emp", list);
        System.out.println("list : " +list);
        return forward;
    }
 
}
 
http://colorscripter.com/info#e" target="_blank" style="color:#e5e5e5; text-decoration:none">Colored by Color Scripter
 http://colorscripter.com/info#e" target="_blank" style="text-decoration:none; color:white">cs

 EmoDao.java

 

 

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
package dao;
 
 
 
import model.Employee;
import model.date;
 
public class EmpDao {
    private SqlSession getSession() {
        SqlSession session = null;
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader("sqlMapConfig.xml");
            SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(reader);
            session = sf.openSession(true);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return session;
    }
 
    public List<Employee> search(date d) {
        SqlSession session = null;
        Employee e = new Employee();
        List<Employee> list = null;
        try {
            session = getSession();
            // selectOne 결과가 없는 경우 mem 은 null namespace Member1
            list = session.selectList("selectdate", d);
            // object 형이니까 반드시 캐스팅
            // 채우기
            if (e != null) { // 널 체크 안하면 nullExpcetion 오류 발생
 
            
            } else {
                System.out.println("조회 결과가 null");
            }
            // 여려개면 map으로 넘겨서 사용
 
        } catch (Exception e1) {
            e1.printStackTrace();
        } finally {
            if (session != null) {
                session.close();
            }
        }
        return list;
    }
 
    public List<Employee> getList(Map<String, String> m) {
        SqlSession session = null;
        Employee e = new Employee();
        List<Employee> list = null;
        try {
            session = getSession();
            list = session.selectList("termlist", m);
            if (e != null) { // 널 체크 안하면 nullExpcetion 오류 발생
                System.out.println("잘 갔어요 히히");
            } else {
                System.out.println("조회 결과가 null");
            }
        } catch (Exception e1) {
            e1.printStackTrace();
        } finally {
            if (session != null) {
                session.close();
            }
        }
        return list;
    }
 
    
}
 
http://colorscripter.com/info#e" target="_blank" style="color:#e5e5e5; text-decoration:none">Colored by Color Scripter
 http://colorscripter.com/info#e" target="_blank" style="text-decoration:none; color:white">cs

 FrontController.java

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
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
package jsp;
 
 
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
 
@WebServlet("*.net")
public class FrontController extends HttpServlet {
    private static final long serialVersionUID = 1L;
 
    public FrontController() {
        super();
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doProcess(request, response);
    }
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doProcess(request, response);
    }
    private void doProcess(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException{
        String RequestURI = request.getRequestURI();
        System.out.println("RequestURI = " + RequestURI);
        
        String contextPath = request.getContextPath();
        String command = RequestURI.substring(contextPath.length());
        System.out.println("command = "+command);
        
        ActionForward forward = null;
        Action action = null;
        if(command.equals("/main.net")) {
            forward = new ActionForward();
            forward.setRedirect(false);
            forward.setPath("jsp/main.jsp");
        }
        else if(command.equals("/join.net")) {
            forward = new ActionForward();
            forward.setRedirect(false);
            forward.setPath("jsp/join.jsp");
        }
        else if(command.equals("/login.net")) {
            forward = new ActionForward();
            forward.setRedirect(false);
            forward.setPath("jsp/login.jsp");
        }else if(command.equals("/joinPro.net")) {
            //forward = new ActionForward();
            action = new JoinAction();
            try {
                forward=action.execute(request, response);
            }catch(Exception e) {
                e.printStackTrace();
            }
        }else if(command.equals("/loginPro.net")) {
            action = new LoginAction();
            try {
                forward=action.execute(request, response);
            }catch(Exception e) {
                e.printStackTrace();
            }
        }else if(command.equals("/List.net")) {
            action = new ListAction();
            try {
                forward=action.execute(request, response);
            }catch(Exception e) {
                e.printStackTrace();
            }
        }
        else if(command.equals("/delete.net")) {
            action = new deleteAction();
            try {
                forward=action.execute(request, response);
            }catch(Exception e) {
                e.printStackTrace();
            }
        }else if(command.equals("/modify.net")) {
            action = new modifyAction();
            try {
                forward=action.execute(request, response);
            }catch(Exception e) {
                e.printStackTrace();
            }
        }
        else if(command.equals("/updateForm.net")) {
            action = new updateAction();
            try {
                forward=action.execute(request, response);
            }catch(Exception e) {
                e.printStackTrace();
            }
        }else if(command.equals("/Logout.net")) {
            forward = new ActionForward();
            forward.setRedirect(false);
            forward.setPath("jsp/login.jsp");
        }else if(command.equals("/term.net")) {
//            action = new DateAction();
            action = new Term();
            try {
                forward=action.execute(request, response);
            }catch(Exception e) {
                e.printStackTrace();
            }
        }else if(command.equals("/term2.net")) {
            action = new Term2();
            try {
                forward=action.execute(request, response);
            }catch(Exception e) {
                e.printStackTrace();
            }
        }else if(command.equals("/term3.net")) {
            action = new Term3();
            try {
                forward=action.execute(request, response);
            }catch(Exception e) {
                e.printStackTrace();
            }
        }else if(command.equals("/check2.net")) {
            action = new check();
            try {
                forward=action.execute(request, response);
            }catch(Exception e) {
                e.printStackTrace();
            }
        }
 
 
        
        
        //하단
        if(forward != null) {// 페이지 처리 
            if(forward.isRedirect()) { // 리다이렉트 됩니다.  
                response.sendRedirect(forward.getPath());
            }else {
                RequestDispatcher dispatcher=
                        request.getRequestDispatcher(forward.getPath());
                        dispatcher.forward(request,response);
            }
        }
        
        
    }//doProcess
//내가 페이지 하나 처리한 후에 다음에 어디갈지를 컨트롤러에서제어 
    //request 객체
}
 
http://colorscripter.com/info#e" target="_blank" style="color:#e5e5e5; text-decoration:none">Colored by Color Scripter
 http://colorscripter.com/info#e" target="_blank" style="text-decoration:none; color:white">cs

 

 Term.java

 

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
package jsp;
 
 
 
import dao.EmpDao;
import model.Employee;
 
public class Term implements Action {
 
    @Override
    public ActionForward execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
        EmpDao dao = new EmpDao();
        Map<String, String> m = new HashMap<>();
        Employee emp = new Employee();
        m.put("startday", request.getParameter("startday"));
        m.put("endday", request.getParameter("endday"));
        
        List <Employee> list= new ArrayList<Employee>();
 
        list = dao.getList(m);
        ActionForward forward = new ActionForward();
        forward.setPath("question/emplist.jsp");
        request.setAttribute("emp", list);
        return forward;
    }
 
}
 
http://colorscripter.com/info#e" target="_blank" style="color:#e5e5e5; text-decoration:none">Colored by Color Scripter
 http://colorscripter.com/info#e" target="_blank" style="text-decoration:none; color:white">cs

emplist.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
<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
 
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<title>사원조회</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet"
<script
<script
 
</head>
<body>
    <script>
        console.log("${emp}");
    </script>
    <div class="container">
        <c:if test="${emp.size() > 0 }">
            <table class="table">
                <thead>
                    <tr>
                        <th>이름</th>
                        <th>사원번호</th>
                        <th>직업</th>
                        <th>입사일</th>
                        <th>부서번호</th>
                    </tr>
                </thead>
                <tbody>
 
                    <c:forEach var="l" items="${emp}">
 
                        <tr>
                            <td>${l.ename }</td>
                            <td>${l.empno }</td>
                            <td>${l.job }</td>
                            <td>${l.hiredate }</td>
                            <td>${l.deptno }</td>
 
                        </tr>
                    </c:forEach>
                </tbody>
            </table>
        </c:if>
        <c:if test="${emp.size() == 0 }">
        <h1>조회 결과가 없습니다.</h1>
        </c:if>
    </div>
</body>
</html>
 

결과

'IT > MyBatis' 카테고리의 다른 글

[MyBatis] 조인  (1) 2019.06.20
[MyBatis]  (0) 2019.06.19
[MyBatis] DB 연결  (0) 2019.06.17
[MyBatis]  (0) 2019.06.17

관련글

티스토리툴바