[SPRING] lombok + mybatis

< 프로젝트 만들기 전 설정 >

1. Dynamic project -> Maven project 혹은 Maven project생성

2. pom.xml의 dependency설정하기 + mapping.xml / log4j.xml / myBatisConfig.xml 작성하기 ( src / main / java에 위치)

3. src / main / java에 com.exam.model1 패키지 만든 후 DAO / DTO 작성하기

4. webapp에 jsp파일 작성하여 데이터 조작 / 연결하기

---

Dept 테이블 내용 insert / select하기

<!-- pom.xml -->
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.exam</groupId>
  <artifactId>mybatis</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>war</packaging>

	<dependencies>
		<!-- mariadb JDBC 라이브러리 추가 -->
		<!--https://mvnrepository.com/artifact/org.mariadb.jdbc/mariadb-java-client -->
		<dependency>
			<groupId>org.mariadb.jdbc</groupId>
			<artifactId>mariadb-java-client</artifactId>
			<version>3.1.4</version>
		</dependency>

		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.17</version>
		</dependency>

		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.5.13</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.18.30</version>
			<scope>provided</scope>
		</dependency>

	</dependencies>
	
	<build>
      <plugins>
         <plugin>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>3.8.1</version>
            <configuration>
               <release>17</release>
            </configuration>
         </plugin>
         <plugin>
            <artifactId>maven-war-plugin</artifactId>
            <version>3.2.3</version>
         </plugin>
      </plugins>
   </build>

	
</project>

-> dependency를 설정하여 라이브러리 다운은 성공하지만 target안의 내용을 얻는 과정을 알지 못함

 

<!--dept.xml-->

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "httpS://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mybatis">


	<insert id="insert1" parameterType="com.exam.model1.DeptTO">    <!-- 파라미터 타입 패키지명 확인하기 -->
		insert into dept2 (deptno, dname, loc)
		values ( #{deptno}, #{dname}, #{loc} )
	</insert>
	
	<select id="selectlist" resultType="com.exam.model1.DeptTO">
		select deptno, dname, loc from dept2
	</select>
	
	
</mapper>

=> 데이터 조작을 위한 쿼리문 작성 xml파일이다

 

<!--log4j.xml-->
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "httpS://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/xml/doc-files/log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
     <appender name="console" class="org.apache.log4j.ConsoleAppender">
          <layout class="org.apache.log4j.PatternLayout">
               <param name="ConversionPattern" value="%d{yyyy-MM-dd HH:mm:ss} [%-5p](%-35c{1}:%-3L) %m%n" />
          </layout>
     </appender>
     
     <root>
     	<level value="DEBUG" />
     	<appender-ref ref="console" />
     </root>
     
</log4j:configuration>

=> 로그를 확인할 수 있도록 하는 xml이다.

 

<!--myBatisConfig.xml-->
<?xml version= "1.0" encoding ="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "httpS://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<environments default="mariadb1">
		<environment id="mariadb1">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="org.mariadb.jdbc.Driver" />
				<property name="url" value="jdbc:mariadb://localhost:3306/sample" />
				<property name="username" value="root" />
				<property name="password" value="!123456" />
			</dataSource>
		</environment>
	
		<environment id="mariadb2">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="org.mariadb.jdbc.Driver" />
				<property name="url" value="jdbc:mariadb://localhost:3306/project" />
				<property name="username" value="project" />
				<property name="password" value="123456" />
			</dataSource>
		</environment>
	</environments>
	
	<mappers>
		<!-- <mapper resource="com/exam/model1/zipcode.xml" />-->
		 <mapper resource="com/exam/model1/dept.xml" />
	</mappers>
</configuration>

---

//DeptTO.java

package com.exam.model1;

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class DeptTO {
	private String deptno;
	private String dname;
	private String loc;
}

 

//DeptDAO.java
package com.exam.model1;

import java.io.IOException;
import java.io.InputStream;
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;

public class DeptDAO {

	private SqlSession sqlSession;
	
	public DeptDAO() {
		String resource = "myBatisConfig.xml";
		
		InputStream is = null;
		
		try {
			
			is = Resources.getResourceAsStream(resource);
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
			
			this.sqlSession = sqlSessionFactory.openSession(true);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if(is!=null)try {is.close();}catch(IOException e) {};
		}
	}	
	
	
	public List<DeptTO> selectList(){
		List<DeptTO> lists = sqlSession.selectList("selectlist");
		if(sqlSession!=null)sqlSession.close();
		return lists;
	}
	
	
	
}

- sqlSession.selectList("selectlist"); 에서 selelctlist는 dept.xml의 <select></select>의 id와 같아야 한다.

---

<!--insert.jsp-->

<%@page import="com.exam.model1.DeptTO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.io.IOException" %>
<%@ page import="java.io.InputStream" %>
    
<%@ page import="org.apache.ibatis.io.Resources" %>
<%@ page import="org.apache.ibatis.session.SqlSession" %>
<%@ page import="org.apache.ibatis.session.SqlSessionFactory" %>
<%@ page import="org.apache.ibatis.session.SqlSessionFactoryBuilder" %>
<%@ page import ="com.exam.model1.DeptTO"%>
 
<%
	String resource = "myBatisConfig.xml";

	InputStream is = null;
	SqlSession sqlSession = null;

	try{
		is = Resources.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory 
		= new SqlSessionFactoryBuilder().build(is);
		System.out.println("환경설정 성공");
	
		sqlSession = sqlSessionFactory.openSession(true);
		System.out.println("연결 성공");
		
		
		DeptTO to = new DeptTO();
		to.setDeptno("70");
		to.setDname("세발");
		to.setLoc("밀양");
		
		int result = sqlSession.insert("insert1",to);
		if(result>0){
			System.out.println("입력성공");
		}else
		{
			System.out.println("입력실패");
		}
		
	}catch(IOException e){
		System.out.println("[에러]:"+e.getMessage());
		
	}finally{
		if(sqlSession!=null)sqlSession.close();
		if(is!=null)is.close();
	}
%>
    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<!-- insert.jsp -->


</body>
</html>

 

<!--select.jsp-->
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import = "com.exam.model1.DeptDAO" %>
<%@ page import ="com.exam.model1.DeptTO"%>
<%@ page import ="java.util.List"%>

<%
	DeptDAO dao = new DeptDAO();
	List<DeptTO> lists = dao.selectList();
	
	StringBuilder sbHtml = new StringBuilder();
	
	sbHtml.append("<table width ='600' border='1'>");
	for(DeptTO to:lists){
		sbHtml.append("<tr>");
		sbHtml.append("<td>"+to.getDeptno()+"</td>");
		sbHtml.append("<td>"+to.getDname()+"</td>");
		sbHtml.append("<td>"+to.getLoc()+"</td>");
		sbHtml.append("</tr>");
	}
	sbHtml.append("</table>");
	
%>



<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%=sbHtml %>
</body>
</html>

결과 (insert후 select함)

---

* pom.xml, log4j.xml은 동일

<!--myBatisConfig.xml-->

<?xml version= "1.0" encoding ="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "httpS://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<environments default="mariadb2">
		<environment id="mariadb2">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="org.mariadb.jdbc.Driver" />
				<property name="url" value="jdbc:mariadb://localhost:3306/project" />
				<property name="username" value="project" />
				<property name="password" value="123456" />
			</dataSource>
		</environment>
	</environments>
	
	<mappers>
		<mapper resource="com/exam/model1/zipcode.xml" />
	</mappers>
</configuration>

 

<!--zipcode.xml-->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "httpS://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mybatis">

	<select id="selectlist" resultType="com.exam.model1.ZipcodeTO">
		select zipcode, sido, gugun, dong, ri, bunji from zipcode where dong like concat( #{ dong }, '%' )
	</select>
	
</mapper>

---

//ZipcodeDAO.java

package com.exam.model1;

import java.io.IOException;
import java.io.InputStream;
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;

public class ZipcodeDAO {
	private SqlSession sqlSession;
	
	public ZipcodeDAO() {
		// TODO Auto-generated constructor stub
		
		String resource = "myBatisConfig.xml";

		InputStream is = null;
		
		try {	
			is = Resources.getResourceAsStream( resource );
			SqlSessionFactory sqlSessionFactory
			= new SqlSessionFactoryBuilder().build( is );
			
			this.sqlSession = sqlSessionFactory.openSession( true );
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println( "[에러] " + e.getMessage() );
		} finally {
			if( is != null ) try { is.close(); } catch( IOException e ) {}
		}
		
	}
	
	public List<ZipcodeTO> selectList(String strdong) {
		List<ZipcodeTO> lists = sqlSession.selectList( "selectlist", strdong );
		if( sqlSession != null ) sqlSession.close();
		return lists;
	}
}

 

//ZipcodeTO.java

package com.exam.model1;

import lombok.Getter;
import lombok.Setter;

@Setter
@Getter
public class ZipcodeTO {
	private String zipcode;
	private String sido;
	private String gugun;
	private String dong;
	private String ri;
	private String bunji;
}

---

jsp

<!--zipcode.jsp-->
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

<form action="zipcode_ok.jsp" method="post">
동이름 <input type="text" name="strdong" />
<input type="submit" value="우편번호 찾기" />
</form>
</body>
</html>

<!--zipcode_ok.jsp-->
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%@ page import="com.exam.model1.ZipcodeTO" %>
<%@ page import="com.exam.model1.ZipcodeDAO" %>

<%@ page import="java.util.List" %>

<%
	request.setCharacterEncoding( "utf-8" );

	String strdong = request.getParameter( "strdong" );
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
	ZipcodeDAO dao = new ZipcodeDAO();
	List<ZipcodeTO> lists = dao.selectList( strdong );
			
	out.println( "<table border='1'>" );
	for( int i=0 ; i<lists.size() ; i++ ) {
		ZipcodeTO to = lists.get(i);

		out.println( "<tr>" );
		out.println( "<td>"
			+ to.getZipcode() + to.getSido() + to.getGugun()
			+ to.getDong() + to.getRi() + to.getBunji()
			+ "</td>");
		out.println( "</tr>" );
	}
	out.println( "</table>" );		
%>
</body>
</html>