[Servlet] Servlet 정리8 - Mybatis

11. Mybatis : ORM 라이브러리

    * ORM(Object-relational mapping) : SQL문을 객체로 만들어 제공.

    * jar의 모임 -> 라이브러리의 모임 -> 프레임워크

     - MyBatis-3-User-Guide_ko 문서 확인.

 

 

    [xml 이용 mybatis 설정하기]

    ① mybatis-3.5.6.jar 추가(하기 사이트에서 다운로드)

     blog.mybatis.org/p/products.html

 

 

    * 보통 Mybatis 파일끼리 package로 묶어서 사용

      - SqlMapConfig.java

      - Configuration.xm

      - DataMapper.xml

      - db.properties

 

    ② Configuration.xml : 설정파일 작성

    Mapper 파일 추가 <mapper resource="mapper 경로">태그 추가.

<?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>
 <properties resource="pack/db.properties" />
 <environments default="dev">
  <environment id="dev">
   <transactionManager type="JDBC" />
   <dataSource type="POOLED">
    <property name="driver" value="${driver}" />
    <property name="url" value="${url}" />
    <property name="username" value="${username}" />
    <property name="password" value="${password}" />
   </dataSource>
  </environment>
 </environments>
 <mappers>
  <mapper resource="pack/DataMapper.xml" />
 </mappers>
</configuration>

    * Configuration.xml에 별칭 추가(<configuration>내부)

 <typeAliases>
 	<typeAlias type="pack.business.DataDto" alias="dto"/>
 	<typeAlias type="pack.business.DataFormBean" alias="formBean"/>
 </typeAliases>

 

 

    ③ db.properties : 계정정보 파일

driver=org.mariadb.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test
username=root
password=123

 

    ④ DataMapper.xml 추가

    - SQL문을 작성.

    - value값을 매개변수로 가질 경우 #{code}사용.

    - 칼럼값을 매개변수로 가질 경우${code} 사용.

                                 select : List 리턴.

                                 insert : 0, 1 리턴 - 1개씩만 update 가능.
                                 update : 0, n 리턴.
                                 delete : 0, n 리턴.

<?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="dev">
 <select id="selectDataAll" resultType="pack.DataDto">
  select * from sangdata order by code asc
 </select>
 
 <select id="selectDataById" parameterType="string" resultType="pack.DataDto">
  select code,sang,su,dan from sangdata where code = #{code}
 </select>
 
 <insert id="insertData" parameterType="pack.DataDto">
  insert into sangdata(code,sang,su,dan) values(#{code},#{sang},#{su},#{dan})
 </insert>
 
 <update id="updateData" parameterType="pack.DataDto">
  update sangdata set sang=#{sang} where code=#{code}
 </update>
 
 <delete id="deleteData" parameterType="int">
  delete from sangdata where code=#{code}
 </delete>
</mapper>

 => Configuration.xml의 별칭을 resultType에 사용가능.

 

    * DataMapper.xml에  재사용 구문 별도 작성하여 재사용성을 높인다.

 <sql id="my1">id, name, passwd, reg_date</sql>
 <sql id="my2">from membertab where id = #{id}</sql>
 
 <select id="selectDataPart" parameterType="string" resultType="dto" statementType="PREPARED">
  select
  <include refid="my1"/>
  <include refid="my2"/>
 </select>

 

    ⑤ SqlMapConfig : 메뉴얼 확인하여 작성.

public class SqlMapConfig {
	public static SqlSessionFactory sqlSession;
	// DB의 SQL 명령 실행 메소드를 가진 객체.
	static {
		String resource = "pack/Configuration.xml";
		try {
			Reader reader = Resources.getResourceAsReader(resource);
			// Configuration.xml을 읽는다.
			sqlSession = new SqlSessionFactoryBuilder().build(reader);
			// sqlSession 객체를 생성한다.
			reader.close();
		} catch (Exception e) {
			System.out.println("SqlMapConfig err"+e);
		}
	}
	public static SqlSessionFactory getSqlSession() {
		return sqlSession;
	}
}

 * 보통 business 파일끼리 package로 묶어서 사용

  - DataDto.java

  - FormBean.java

  - ProcessDao.java

 

* 용도에 따라 FormBean과 Dto 파일로 데이터를 관리한다.

  - FormBean 용도 : 클라이언트의 정보를 레코드 단위로 받아 처리 (update, delete)
  - DataDTO 용도 : DB에서 넘어오는 정보를 레코드 단위로 받아 처리 (select)

 

    ⑥ DTO작성 - DataDto.java

 

    ⑦ FormBean작성

    ⑧ ProcessDao : MyBatis와 연결해서 SQL을 실행하고 그 결과를 얻는 역할

public class ProcessDao {
	private SqlSessionFactory factory = SqlMapConfig.getSqlSession();
	
	public List<DataDto> selectDataAll() throws Exception{
		SqlSession sqlSession = factory.openSession(); // 세션 열기(DB처리를 위한 작업단위)
		List<DataDto> list = sqlSession.selectList("selectDataAll"); // DataMapper의 id를 가져온다.
		// selectList() : 여러개 리턴 
		// selectOne() : 1개 리턴
		sqlSession.close();
		return list; // sql의 결과를 가진다.
	}
	
	public DataDto selectDataPart(String code) throws Exception {
		SqlSession sqlSession = factory.openSession();
		DataDto dto = sqlSession.selectOne("selectDataById", code);
		sqlSession.close();
		return dto;
	}
	
	public int insertData(DataDto dto) throws Exception{
		//SqlSession sqlSession = factory.openSession(); // 수동 commit.
		SqlSession sqlSession = factory.openSession(true); // 자동 commit.
		int result = sqlSession.insert("insertData",dto);
		if(result == 0) {
			sqlSession.rollback();
		}else {
			//sqlSession.commit();
		}
		sqlSession.close();
		return result;
	}
	public int upData(DataDto dto) throws Exception{
		SqlSession sqlSession = factory.openSession();
		int result = sqlSession.update("updateData", dto);
		sqlSession.commit();
		sqlSession.close();
		return result;
	}
	public boolean delData(int code){
		boolean result = false; 
		SqlSession sqlSession = factory.openSession();
		try {
			int cou = sqlSession.delete("deleteData", code);
			if(cou>0) {
				result=true;
			}
			sqlSession.commit();
		} catch (Exception e) {
			System.out.println("dlData err"+e);
			sqlSession.rollback();
		}finally {
			if(sqlSession != null) sqlSession.close();
		}		
		return result;
	}
}

 

 * 보통 jsp파일에 작성.

    ⑨-1) java main

ProcessDao dao = new ProcessDao();
try {
	System.out.println("자료 추가");
	DataDto dataDto = new DataDto();
	dataDto.setCode("11");
	dataDto.setSang("초록양말");
	dataDto.setSu("7");
	dataDto.setDan("4000");
	//DataDto dataDto = new DataDto("11","초록양말","7","4000");
	int result = dao.insertData(dataDto);
	System.out.println(result+"개 자료 추가");
	
	System.out.println("자료 수정");
	DataDto dataDto = new DataDto();
	dataDto.setCode("11");
	dataDto.setSang("빨간양말");
	int result = dao.upData(dataDto);
	System.out.println(result+"개 자료 업데이트");
	
	System.out.println("자료삭제");
	boolean result = dao.delData(5);
	if(result) {
		System.out.println("삭제 성공");
	}else {
		System.out.println("삭제 실패");
	}
	
	System.out.println("\n전체자료 출력");
	List<DataDto> list = dao.selectDataAll();
	//ArrayList<DataDto> list = (ArrayList)dao.selectDataAll();
	for(DataDto s:list) {
		 System.out.println(s.getCode() +"\t"+
							s.getSang() +"\t"+
							s.getSu() +"\t"+
							s.getDan());
	}
	System.out.println("전체 건수:" + list.size());
	
	System.out.println("\n부분자료 출력");
	DataDto dto = dao.selectDataPart("3");
	System.out.println(dto.getCode() +"\t"+
						dto.getSang() +"\t"+
						dto.getSu() +"\t"+
						dto.getDan());
} catch (Exception e) {
	System.out.println("Main err "+e);
}

⑨-2) jsp

- main html

<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>Insert title here</title>
</head>
<body>
	* 홈페이지<br>
	인사관리 자재관리 영업관리 <a href="list.jsp">회원관리</a>
	<hr>
	<pre>입력값 출력
	</pre>
	<footer>
	꼬리말
	</footer>
</body>
</html>

 - 모든 list 출력 jsp (select all)

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<jsp:useBean id="processDao" class="pack.business.ProcessDao"/>

<%
	ArrayList<DataDto> slist = (ArrayList)processDao.selectDataAll();
%>
<a href="ins.html">상품추가</a><br>
<span style="color:red">코드를 클릭하면 삭제 품명을 클릭하면 수정처리</span>
<table border="1">
	<tr>
		<th>상품명</th><th>품명</th><th>수량</th><th>단가</th>
	</tr>
	
	<c:forEach var ="s" items="<%=slist%>">
	<tr>
		<td><a href="delete.jsp?code=${s.code }">${s.code}</a></td>
		<td><a href="update.jsp?code=${s.code }">${s.sang}</a></td>
		<td>${s.su}</td>
		<td>${s.dan}</td>
	</tr>
	</c:forEach>		
</table>

 

<%@page import="pack.business.DataDto"%>
<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="func" uri="http://java.sun.com/jsp/jstl/functions" %>
<jsp:useBean id ="processDao" class="pack.business.ProcessDao"/>
<!DOCTYPE html>
<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
	<title>Insert title here</title>
</head>
<body>
	<h2>* 회원정보(Mybatis)</h2>
	<a href="ins.jsp">회원추가</a>
	<table border="1">
		<tr>
			<th>id</th><th>name</th><th>pwd</th><th>date</th>
		</tr>
		<% ArrayList<DataDto> list = (ArrayList)processDao.selectDataAllMember(); %>
		<c:set var="list" value="<%=list %>"/>
		<c:if test="${empty list}">
			<tr>
				<td colspan="4">자료없음</td>
			</tr>
		</c:if>
		<c:forEach var="m" items="<%=list%>">
			<tr>
				<td><a href="del.jsp?id=${m.id}">${m.id}</a></td>
				<td><a href="up.jsp?id=${m.id}">${m.name}</a></td>
				<td>${m.passwd}</td>
				<td>${func:substring(m.reg_date,0,10)}</td>
			</tr>
		</c:forEach>
		<tr>
			<td colspan="4">id클릭은 삭제 name클릭은 수정</td>
		</tr>
	</table>
</body>
</html>

* function 액션태그

<%@ taglib prefix="func" uri="http://java.sun.com/jsp/jstl/functions" %>
${func:substring(m.reg_date,0,10)}

 

 - insert html

<form action="ins.jsp" method="post">
	코드 : <input type="text" name="code"><br>
	품명 : <input type="text" name="sang"><br>
	수량 : <input type="number" name="su"><br>
	단가 : <input type="number" name="dan"><br>
	<input type="submit" value="등록">
</form>

 - insert jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<% request.setCharacterEncoding("utf-8"); %>

<jsp:useBean id="bean" class="pack.business.FormBean"></jsp:useBean>
// java bean객체 생성 액션태그
<jsp:setProperty property="*" name="bean"/>
// 생성 객체에 프로퍼티 값 설정

<jsp:useBean id="processDao" class="pack.business.ProcessDao" />
<%
	processDao.insertData(bean);// Mybatis 호출
	response.sendRedirect("list.jsp");
%>

자바빈 property는 하기와 동일
<%= bean.getCode() %>
<%= bean.getSang() %>

* 수정/ 삭제 후 Redirect 진행해야함(client) forward 진행 시 새로고침시 해당 동작이 중복 수행된다.(수정/삭제에서 실행)

<jsp:forward page="list.jsp" /> insert에선 사용하지않음

* servlet에 하기와 같이 구현가능

// 내부적인 처리만 구현. 비지니스 로직. servlet으로 구현 가능
request.setCharacterEncoding("utf-8");
String code = request.getParameter("code");
String sang = request.getParameter("sang");
...

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
	<title>Insert title here</title>
</head>
<body>
	<h2>회원가입</h2>
	<form action="insok.jsp" method="post">
	id : <input type="text" name="id"><br>
	name : <input type="text" name="name"><br>
	passwd : <input type="text" name="passwd"><br>
	<br>
	<input type="submit" value="등록">
	</form>
</body>
</html>

 - insert ok jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<% request.setCharacterEncoding("utf-8"); %>
<jsp:useBean id="fbean" class="pack.business.DataFormBean"/>
<jsp:setProperty property="*" name="fbean"/>
<jsp:useBean id="processDao" class="pack.business.ProcessDao"/>
<%
	boolean b = processDao.insertData(fbean);
	if(b){
		response.sendRedirect("list.jsp");
	}else{
		response.sendRedirect("fail.jsp");
	}
%>

 - update jsp

<%@page import="pack.business.DataDto"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<jsp:useBean id="processDao" class="pack.business.ProcessDao"></jsp:useBean>
<%
	String code = request.getParameter("code");
	DataDto dto = processDao.selectDataPart(code);
%>

<!DOCTYPE html>
<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
	<title>Insert title here</title>
	<script type="text/javascript">
	</script>
</head>
<body>

	<form action="updateOk.jsp" method="post">
		코드 : <%= dto.getCode() %><br>
		<input type="hidden" name="code" value="<%=dto.getCode()%>">
		//값은 전달이 필요하지만 변경이 필요하지않는다면 input태그의 hidden type으로 구현한다.
		품명 : <input type="text" name="sang" value="<%= dto.getSang()%>"><br>
		수량 : <input type="number" name="su" value="<%= dto.getSu()%>"><br>
		단가 : <input type="number" name="dan" value="<%= dto.getDan()%>"><br>
		<input type="submit" value="수정">
		<input type="button" onclick="history.back()" value="이전">
	</form>
    
</body>
</html>

<%@page import="pack.business.DataDto"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<jsp:useBean id="processDao" class="pack.business.ProcessDao"></jsp:useBean>
<% request.setCharacterEncoding("utf-8");
String id = request.getParameter("id");
DataDto dto = processDao.selectDataPart(id);
%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h2>*회원정보 수정</h2>
	<form action="upok.jsp" method="post">
	id : <%=dto.getId()%><br>
	<input type="hidden" name="id" value="<%=dto.getId()%>"/>
	name : <input type="text" name="name" value="<%=dto.getName()%>"/><br>
	pwd : <input type="text" name="passwd"/><br>
	<input type="submit" value="수정"/>
	</form>
</body>
</html>

 - update ok jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<% request.setCharacterEncoding("utf-8"); %>

<jsp:useBean id="bean" class="pack.business.FormBean"/>
<jsp:setProperty property="*" name="bean"/>
// formbean 값을 가져온다.
<jsp:useBean id="processDao" class="pack.business.ProcessDao"/>
// sql문 사용 객체 선언
<%
	processDao.upData(bean);
	response.sendRedirect("list.jsp");
%>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<% request.setCharacterEncoding("utf-8");%>
<jsp:useBean id="fbean" class="pack.business.DataFormBean"></jsp:useBean>
<jsp:setProperty property="*" name="fbean"/>
<jsp:useBean id="processDao" class="pack.business.ProcessDao"></jsp:useBean>
<%
	boolean b = processDao.updateData(fbean);

	if(b){
		response.sendRedirect("list.jsp");
	}else{
		response.sendRedirect("fail.jsp");
	}
%>

 

 - delete jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<jsp:useBean id="processDao" class="pack.business.ProcessDao"></jsp:useBean>
<%
	int code = Integer.parseInt(request.getParameter("code"));
	boolean b = processDao.delData(code);
	
	
	if(b){
		response.sendRedirect("list.jsp");
	}else{%>
		<script >
			alert("삭제를 실패하였습니다.");
			location.href="list.jsp";
			//response.sendRedirect("list.jsp");
		</script>
	<%}%>

<%@page import="pack.business.DataDto"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<% request.setCharacterEncoding("utf-8");
%>
<jsp:useBean id="processDao" class="pack.business.ProcessDao"></jsp:useBean>
<%
	String id = request.getParameter("id");
	boolean b = processDao.deleteData(id);
	
	if(b){
		response.sendRedirect("list.jsp");
	}else{
		response.sendRedirect("fail.jsp");
	}
%>

 - fail jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
	<title>Insert title here</title>
</head>
<body>
	<h2>작업실패</h2>
	<a href="list.jsp">회원정보 보기</a>
</body>
</html>

 

---

* log4j를 이용해 java + MyBatis log 작성
    ① Root에 log4j.properties 생성

log4j.rootLogger=DEBUG, console
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.Encoding=UTF-8
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%d{ISO8601}] [%-12t] %-5p %m%n

 

    ② Context/WEB_INF/lib에 log4j-1.2.17.jar 추가

 

<tip>

* WEB_INF는 외부에서 접근 불가하다.(보안)

* ctrl + 함수명 : 선언부로 이동.