JAVA
17.MVC/mybatis
네스이
2022. 8. 5. 16:48
2022.08.05.금
1.MVC
-게시판 검색
package com.controller;
import java.io.IOException;
import java.util.List;
import javax.servlet.RequestDispatcher;
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 com.dto.BoardDTO;
import com.service.BoardService;
import com.service.BoardServiceImpl;
@WebServlet("/list")
public class BoardListController extends HttpServlet
{
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
//검색 파라미터 얻기
String searchName = request.getParameter("serachName");
String searchValue = request.getParameter("searchValue");
//비즈니스 로직 처리
BoardService service = new BoardServiceImpl();
List<BoardDTO> list = service.list(searchName, searchValue);
//데이터 전달 => scope 이용
request.setAttribute("board_list", list);
//jsp에게 요청(위임)
RequestDispatcher dispatch = request.getRequestDispatcher("list.jsp");
dispatch.forward(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
doGet(request, response);
}
}//list.jsp
<%@page import="com.dto.BoardDTO"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>게시판 목록보기</title>
</head>
<body>
<table border="1">
<!-- html 주석 -->
<!-- 검색 기능 추가 -->
<tr>
<td colspan="5">
<form action="list">
<select name="searchName">
<option value="title">제목</option>
<option value="author">작성자</option>
</select>
<input type="text" name="searchValue">
<input type="submit" value="검색">
</form>
</td>
</tr>
<tr>
<th>글번호</th>
<th>제목</th>
<th>작성자</th>
<th>작성일</th>
<th>조회수</th>
</tr>
<%
List<BoardDTO> list = (List<BoardDTO>)request.getAttribute("board_list");
for(BoardDTO dto : list)
{
int num = dto.getNum();
String author = dto.getAuthor();
String writeday = dto.getWriteday();
int readcnt = dto.getReadcnt();
%>
<tr>
<td><%= num %></td>
<td><a href="retrieve?num=<%= num %>"><%= dto.getTitle() %></a></td>
<td><%= author %></td>
<td><%= writeday %></td>
<td><%= readcnt %></td>
</tr>
<%
}
%>
</table>
<a href="writeUI">글쓰기</a>
</body>
</html>-Paging 처리
1)Servlet
package com.controller;
import java.io.IOException;
import java.util.List;
import javax.servlet.RequestDispatcher;
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 com.dto.BoardDTO;
import com.dto.PageDTO;
import com.service.BoardService;
import com.service.BoardServiceImpl;
@WebServlet("/list")
public class BoardListController extends HttpServlet
{
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
//현재 페이지 번호
String curPage = request.getParameter("curPage");
if(curPage == null) curPage = "1";
//검색 파라미터 얻기
String searchName = request.getParameter("searchName");
String searchValue = request.getParameter("searchValue");
//비즈니스 로직 처리
BoardService service = new BoardServiceImpl();
PageDTO pageDTO =
service.list(searchName, searchValue, Integer.parseInt(curPage));
//데이터 전달 => scope 이용
request.setAttribute("pageDTO", pageDTO);
request.setAttribute("searchName", searchName);
request.setAttribute("searchValue", searchValue);
//jsp에게 요청(위임)
RequestDispatcher dispatch = request.getRequestDispatcher("list.jsp");
dispatch.forward(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException
{
doGet(request, response);
}
}2)JSP
//list.jsp
<%@page import="com.dto.PageDTO"%>
<%@page import="com.dto.BoardDTO"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>게시판 목록보기</title>
</head>
<body>
<table border="1">
<!-- html 주석 -->
<!-- 검색 기능 추가 -->
<tr>
<td colspan="5">
<form action="list">
<select name="searchName">
<option value="title">제목</option>
<option value="author">작성자</option>
</select>
<input type="text" name="searchValue">
<input type="submit" value="검색">
</form>
</td>
</tr>
<tr>
<th>글번호</th>
<th>제목</th>
<th>작성자</th>
<th>작성일</th>
<th>조회수</th>
</tr>
<%
PageDTO pageDTO = (PageDTO)request.getAttribute("pageDTO");
for(BoardDTO dto : pageDTO.getList())
{
int num = dto.getNum();
String title = dto.getTitle();
String author = dto.getAuthor();
String writeday = dto.getWriteday();
int readcnt = dto.getReadcnt();
%>
<tr>
<td><%= num %></td>
<td><a href="retrieve?num=<%= num %>"><%= title %></a></td>
<td><%= author %></td>
<td><%= writeday %></td>
<td><%= readcnt %></td>
</tr>
<%
}
%>
</table>
<%
String searchName = (String)request.getAttribute("searchName");
String searchValue = (String)request.getAttribute("searchValue");
//페이지 링크 번호 출력
int curPage = pageDTO.getCurPage();
int perPage = pageDTO.getPerPage();
int totalRecord = pageDTO.getTotalRecord();
int totalPage = totalRecord / perPage;
if(totalRecord % perPage != 0) totalPage++;
for(int i = 1; i <= totalPage; i++)
{
if(i == curPage)
{
%>
<%= i %>
<%
}
else
{
%>
<a href="list?curPage=<%= i %>&searchName=<%= searchName %>&searchValue=<%= searchValue %>"><%= i %></a>
<%
}
}
%>
<br>
<a href="writeUI">글쓰기</a>
</body>
</html>3)DAO
package com.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.dto.BoardDTO;
import com.dto.PageDTO;
public class BoardDAO
{
//글삭제
public int delete(Connection con, int n)
{
int num = 0;
PreparedStatement pstmt = null;
try
{
String sql = "delete board where num = ?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, n);
num = pstmt.executeUpdate();
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(pstmt != null) pstmt.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
return num;
}
//글수정
public int update(Connection con, BoardDTO dto)
{
int num = 0;
PreparedStatement pstmt = null;
try
{
String sql = "update board set title = ?, author = ?, content = ? "
+ "where num = ?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, dto.getTitle());
pstmt.setString(2, dto.getAuthor());
pstmt.setString(3, dto.getContent());
pstmt.setInt(4, dto.getNum());
num = pstmt.executeUpdate();
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(pstmt != null) pstmt.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
return num;
}
//조회수 증가(retrieve메서드에서만 사용)
private void readCntUpdate(Connection con, int num)
{
PreparedStatement pstmt = null;
try
{
String sql = "update board set readcnt = readcnt + 1 where num = ?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, num);
pstmt.executeUpdate();
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(pstmt != null) pstmt.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
//글쓰기
public int write(Connection con, BoardDTO dto)
{
int num = 0;
PreparedStatement pstmt = null;
try
{
String sql = "INSERT INTO board (num, title, author, content) "
+ "values(board_seq.nextval, ?, ?, ?)";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, dto.getTitle());
pstmt.setString(2, dto.getAuthor());
pstmt.setString(3, dto.getContent());
num = pstmt.executeUpdate();
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(pstmt != null) pstmt.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
return num;
}
//글자세히 보기
public BoardDTO retrieve(Connection con, int num)
{
//readCntUpdate() 호출
readCntUpdate(con, num);
BoardDTO dto = new BoardDTO();
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
String sql = "SELECT num, title, author, content, to_char(writeday, 'yyyy/mm/dd') as writeday, readcnt"
+ " FROM board where num = ?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, num);
rs = pstmt.executeQuery();
if(rs.next())
{
String title = rs.getString("title");
String author = rs.getString("author");
String content = rs.getString("content");
String writeday = rs.getString("writeday");
int readcnt = rs.getInt("readcnt");
dto.setNum(num);
dto.setTitle(title);
dto.setAuthor(author);
dto.setContent(content);
dto.setWriteday(writeday);
dto.setReadcnt(readcnt);
}
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(rs != null) rs.close();
if(pstmt != null) pstmt.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
return dto;
}
public PageDTO list(Connection con, String searchName, String searchValue, int curPage)
{
PageDTO pageDTO = new PageDTO();
int perPage = pageDTO.getPerPage();
int offSet = (curPage - 1) * perPage + 1;
////////////////////////////////////////////////////////
List<BoardDTO> list = new ArrayList<BoardDTO>();
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
StringBuffer buffer = new StringBuffer();
buffer.append("SELECT num, title, author, content, to_char(writeday, 'YYYY/MM/dd') as writeday , readcnt");
buffer.append(" FROM");
buffer.append(" (");
buffer.append(" SELECT SEQ, num, title, author, content, writeday, readcnt");
buffer.append(" FROM");
buffer.append(" (");
buffer.append(" SELECT ROWNUM AS SEQ, num, title, author, content, writeday, readcnt");
buffer.append(" FROM");
buffer.append(" (");
buffer.append(" SELECT num, title, author, content, writeday , readcnt");
buffer.append(" FROM board");
if("title".equals(searchName) && searchValue != null)
{
buffer.append(" where title LIKE ?");
buffer.append(" ORDER BY num DESC");
buffer.append(" )");
buffer.append(" )");
buffer.append(" WHERE SEQ >= " + offSet);
buffer.append(" )");
buffer.append(" WHERE ROWNUM <= " + perPage);
pstmt = con.prepareStatement(buffer.toString());
pstmt.setString(1, "%"+searchValue+"%");
}
else if("author".equals(searchName) && searchValue != null)
{
buffer.append(" where author LIKE ?");
buffer.append(" ORDER BY num DESC");
buffer.append(" )");
buffer.append(" )");
buffer.append(" WHERE SEQ >= " + offSet);
buffer.append(" )");
buffer.append(" WHERE ROWNUM <= " + perPage);
pstmt = con.prepareStatement(buffer.toString());
pstmt.setString(1, "%"+searchValue+"%");
}
else
{
buffer.append(" ORDER BY num DESC");
buffer.append(" )");
buffer.append(" )");
buffer.append(" WHERE SEQ >= " + offSet);
buffer.append(" )");
buffer.append(" WHERE ROWNUM <= " + perPage);
pstmt = con.prepareStatement(buffer.toString());
}
rs = pstmt.executeQuery();
while(rs.next())
{
int num = rs.getInt("num");
String title = rs.getString("title");
String author = rs.getString("author");
String writeday = rs.getString("writeday");
int readcnt = rs.getInt("readcnt");
// 나중에 Builder 패턴
BoardDTO dto = new BoardDTO();
dto.setNum(num);
dto.setTitle(title);
dto.setAuthor(author);
dto.setWriteday(writeday);
dto.setReadcnt(readcnt);
list.add(dto);
}
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(rs!=null)rs.close();
if(pstmt!=null)pstmt.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
////////////////////////////////////////////////////////
pageDTO.setList(list);
pageDTO.setCurPage(curPage);
pageDTO.setTotalRecord(totalRecord(con, searchName, searchValue));
return pageDTO;
}
//전체 레코드 반환
private int totalRecord(Connection con, String searchName, String searchValue)
{
int count = 0;
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
String sql="select count(*) from board";
if("title".equals(searchName) && searchValue != null)
{
sql += " where title LIKE ?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, "%"+searchValue+"%");
}
else if("author".equals(searchName) && searchValue != null)
{
sql += " where author LIKE ?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, "%"+searchValue+"%");
}
else
{
pstmt = con.prepareStatement(sql);
}
rs = pstmt.executeQuery();
if(rs.next()) count = rs.getInt(1);
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(rs!=null)rs.close();
if(pstmt!=null)pstmt.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
return count;
}
}4)DTO
package com.dto;
import java.util.List;
public class PageDTO
{
//4개의 정보 저장
private int totalRecord; //전체레코드
private int curPage; //현재 페이지 번호
private int perPage = 3; //페이지 당 보여줄 갯수
private List<BoardDTO> list; //list .jsp에서 보여줄 데이터
public int getTotalRecord() {
return totalRecord;
}
public void setTotalRecord(int totalRecord) {
this.totalRecord = totalRecord;
}
public int getCurPage() {
return curPage;
}
public void setCurPage(int curPage) {
this.curPage = curPage;
}
public int getPerPage() {
return perPage;
}
public void setPerPage(int perPage) {
this.perPage = perPage;
}
public List<BoardDTO> getList() {
return list;
}
public void setList(List<BoardDTO> list) {
this.list = list;
}
}5)Service
package com.service;
import java.util.List;
import com.dto.BoardDTO;
import com.dto.PageDTO;
public interface BoardService
{
public PageDTO list(String searchName, String seachValue, int curPage);
public int write(BoardDTO dto);
public BoardDTO retrieve(int num);
public int update(BoardDTO dto);
public int delete(int n);
}package com.service;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import com.dao.BoardDAO;
import com.dto.BoardDTO;
import com.dto.PageDTO;
public class BoardServiceImpl implements BoardService
{
//4가지 정보 설정
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String userid = "SCOTT";
String passwd = "TIGER";
public BoardServiceImpl()
{
try { Class.forName(driver); }
catch (ClassNotFoundException e) { e.printStackTrace(); }
}
@Override
public int delete(int n)
{
Connection con = null;
int num = 0;
try
{
con = DriverManager.getConnection(url, userid, passwd);
BoardDAO dao = new BoardDAO();
num = dao.delete(con, n);
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(con != null) con.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
return num;
}
@Override
public int update(BoardDTO dto)
{
Connection con = null;
int num = 0;
try
{
con = DriverManager.getConnection(url, userid, passwd);
BoardDAO dao = new BoardDAO();
num = dao.update(con, dto);
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(con != null) con.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
return num;
}
@Override
public int write(BoardDTO dto)
{
Connection con = null;
int num = 0;
try
{
con = DriverManager.getConnection(url, userid, passwd);
BoardDAO dao = new BoardDAO();
num = dao.write(con, dto);
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(con != null) con.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
return num;
}
@Override
public BoardDTO retrieve(int num)
{
Connection con = null;
BoardDTO dto = null;
try
{
con = DriverManager.getConnection(url, userid, passwd);
BoardDAO dao = new BoardDAO();
dto = dao.retrieve(con, num);
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(con != null) con.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
return dto;
}
@Override
public PageDTO list(String searchName, String seachValue, int curPage)
{
Connection con = null;
PageDTO PageDTO = null;
try
{
con = DriverManager.getConnection(url, userid, passwd);
BoardDAO dao = new BoardDAO();
PageDTO = dao.list(con, searchName, seachValue, curPage);
}
catch(SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(con != null) con.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
return PageDTO;
}
}2.mybatis(SQL Mapper)
: 순수한 JDBC를 편리하고 쉽게 사용 가능하도록 제공
-외부 라이브러리(*.jar 필요) : mybatis.org
-자바 프로젝트(비웹 환경)
→buildpath : ojdbc.g.jar + mybatis.jar
-jdbc.properties
jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:xe
jdbc.userid=SCOTT
jdbc.passwd=TIGER-Configuration.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>
<!-- jdbc.properties 파일 설정 -->
<properties resource="com/config/jdbc.properties"></properties>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.userid}"/>
<property name="password" value="${jdbc.passwd}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/config/DeptMapper.xml"/>
</mappers>
</configuration>-DeptMapper.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="org.mybatis.example.BlogMapper">
<select id="list" resultType="com.dto.DeptDTO">
<![CDATA[
select deptno, dname, loc
from dept
order by deptno desc
]]>
</select>
<select id="selectByDeptno"
resultType="com.dto.DeptDTO" parameterType="int">
<![CDATA[
select deptno, dname, loc
from dept
where deptno < #{deptno}
order by deptno desc
]]>
</select>
<select id="selectByDeptnoAndDname"
resultType="com.dto.DeptDTO" parameterType="com.dto.DeptDTO">
<![CDATA[
select deptno, dname, loc
from dept
where deptno < #{deptno} and dname = #{dname}
order by deptno desc
]]>
</select>
<select id="selectByDeptnoAndDnameMap"
resultType="com.dto.DeptDTO" parameterType="hashmap">
<![CDATA[
select deptno, dname, loc
from dept
where deptno < #{xxx} and dname = #{yyy}
order by deptno desc
]]>
</select>
</mapper>-DeptDTO
package com.dto;
public class DeptDTO
{
private int deptno;
private String dname;
private String loc;
public DeptDTO() {}
public DeptDTO(int deptno, String dname, String loc)
{
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
public String toString()
{
return "DeptDTO [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + "]";
}
//getter
public int getDeptno() { return deptno; }
public String getDname() { return dname; }
public String getLoc() { return loc; }
//setter
public void setDeptno(int deptno) { this.deptno = deptno; }
public void setDname(String dname) { this.dname = dname; }
public void setLoc(String loc) { this.loc = loc; }
}-Main
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.dto.DeptDTO;
public class DeptMain {
public static void main(String[] args)
{
//Mybatis DB 연동
//1.Configuration.xml 읽기
String resource = "com/config/Configuration.xml";
InputStream inputStream = null;
try
{
inputStream = Resources.getResourceAsStream(resource);
}
catch (IOException e)
{
e.printStackTrace();
}
SqlSessionFactory x = new SqlSessionFactoryBuilder().build(inputStream);
//2.sqlSessionFactory 에서 SqlSession 얻기
SqlSession session = x.openSession();
/*
SqlSession 메서드
1)select
//여러 레코드 반환시
List list = Session.selectList(String id);
List list = Session.selectList(String id, Object objs); objs : where절에 사용할 값
List list = Session.selectList(String id, Object objs, RowBounds k); RowBounds : 페이징 처리시 사용
//하나 레코드 반환시 ==> PK 조건 지정, 그룹함수 등
T x = session.selectOne(String id);
T x = session.selectOne(String id, Object dbj);
*/
//1.id="list" 전체목록
List<DeptDTO> list = session.selectList("list");
System.out.println(list);
//2.id="selectByDeptno" 호출
int deptno = 40;
List<DeptDTO> list2 = session.selectList("selectByDeptno", deptno);
System.out.println(list2);
//3.id="selectByDeptnoAndDname" 호출
DeptDTO dto = new DeptDTO();
dto.setDeptno(40);
dto.setDname("SALES");
List<DeptDTO> list3 = session.selectList("selectByDeptnoAndDname", dto);
System.out.println(list3);
//4.id="selectByDeptnoAndDnameMap" 호출
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("xxx", 40);
map.put("yyy", "SALES");
dto.setDeptno(40);
dto.setDname("SALES");
List<DeptDTO> list4 = session.selectList("selectByDeptnoAndDnameMap", map);
System.out.println(list4);
// //5.id="listPage" 호출
// //List list = Session.selectList(String id, Object objs, RowBounds k);
// //Paging 처리
// DeptDTO dto = new DeptDTO();
// dto.setDeptno(40);
// dto.setDname("SALES"); //offset(시작위치), limit(갯수)
// List<DeptDTO> list4 = session.selectList("listPage", null, new RowBounds(1, 2));
// System.out.println(list4);
//6.id="selectOneByDeptno"
DeptDTO dto = session.selectOne("selectOneByDeptno", 40);
System.out.println(dto);
//최종적으로 session을 close해야 됨
session.close();
}
}-웹 프로젝트(웹 환경)
→lib에 복사 : ojdbc.g.jar + mybatis.jar