지구정복

[myBatis] 1/20 | myBatis(개념및특징, 설치및설정, 실습(select, join, insert, delete, update, drop, create), jsp에서 myBatis사용(우편번호검색기)) 본문

데이터 엔지니어링 정복/HTML-CSS-JavaScript-Spring-Node.js

[myBatis] 1/20 | myBatis(개념및특징, 설치및설정, 실습(select, join, insert, delete, update, drop, create), jsp에서 myBatis사용(우편번호검색기))

nooh._.jl 2021. 1. 21. 00:13
728x90
반응형

1. myBatis

더보기 
java / JSP framework
myBatis( iBatis )
	https://blog.mybatis.org

	myBatis사용에 필요한 4개의 파일
	* XML 파일 - 태그로서 구성된 데이터 -> 설정에 많이 사용
	1. 데이터베이스 설정
	2. SQL Mapper
		* 로그(중간에 에러여부나 진행과정) 출력에 필요한 프레임워크
		 일반적으로 개발자가 System.out.println()을 사용했음
		 3. log4j라는 프레임워크 사용하면 로그출력이 쉬워진다.
	4. 라이브러리

1. 개념 및 특징

개념

객체 지향 언어인 자바의 관계형 데이터베이스 프로그래밍을 좀 더 쉽게 할 수 있게 도와주는 개발 프레임워크이다. JDBC를 통해 데이터베이스에 엑세스하는 작업을 캡슐화하고 일반 SQL쿼리, 저장프로시저 및 고급매핑을 지원하며 모든 JDBC 코드 및 매개변수의 중복작업을 제거한다. 

즉 자바파일 중에서 SQL관련 코드를 따로 빼서 저장해놓다가 필요할 때 특별한 이름으로 매핑하고 사용하는 것을 의미한다.

 

특징

복잡한 쿼리나 다이나믹한 쿼리에 유용하다.

프로그램 코드와 SQL 쿼리의 분리로 코드의 간결성 및 유지보수성 향상

빠른 개발이 가능하여 생산성이 향상된다.

 

2. myBatis 설치 및 설정

blog.mybatis.org

 

The MyBatis Blog

A blog about the the MyBatis data mapper framework.

blog.mybatis.org

 그리고 압축을 푼다.

이클립스에서 워크스페이스를 jsp워크스페이로 바꿔준다.

 

그리고 다이나믹 웹프로젝트말고 그냥 자바프로젝트를 만들어준다.

아래 사진에서 mybatis-3.5.6.jar과 lib폴더안 파일 모두를 이클립스에 복사해준다.

마리아디비 라이브러리도 추가해준다.

 아래와 같이 집어넣는다.

다음으로 아래 3가지 xml파일을 다운로드받는다.

log4j.xml
0.00MB
mapper.xml
0.00MB
myBatisConfig.xml
0.00MB

 

 위 3개의 파일을 자바프로젝트 src 폴더 안에 집어넣는다.

 

이제 각종 설정을 한다.

-log4j.xml (로그 출력 설정)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "http://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의 형태로 로그를 출력하겠다는 코드  -->
               <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>

 -myBatisConfig.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>
	<environments default="mariadb">
		<!-- environment는 여러개 들어갈 수 있다.  -->
		<environment id="maraidb">
			<transactionManager type="JDBC" /><!-- 사용할 드라이버  -->
			<dataSource type="POOLED"><!-- 연동방법 풀링  -->
				<!-- 데이터베이스 연동에 필요한 설정값  -->
				<property name="driver" value="org.mariadb.jdbc.Driver"/>
				<property name="url" value="jdbc:mysql://localhost:3307/sample"/>
				<property name="username" value="root"/>
				<property name="password" value="!123456"/>
			</dataSource>
		</environment>
	</environments>
    <mappers>
		<mapper resource="mapper.xml" />
	</mappers>
</configuration>

 

3. myBatis 실습

SqlSessionFactory는 SqlSession 인스턴스를 만들 수 있다. SqlSession은 데이터베이스에 대해 SQL명령어를 실행하기 위한 필요한 모든 메서드를 가지고 있다. 

 -MyBatisEx01.java

package myBatisEx01;

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 MyBatisEx02 {

	public static void main(String[] args) {
		String resource = "myBatisConfig.xml";
		
		InputStream is = null;
		
		try {
			is = Resources.getResourceAsStream( resource );
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build( is );
			System.out.println( "호출 성공" );
			
		} catch (IOException e) {
			System.out.println( "error: " + e.getMessage() );
		} finally {
			if ( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}
	
}

-DeptTO.java

public class DeptTO {
	private String deptno;
	private String dname;
	private String loc;
	
	public String getDeptno() {
		System.out.println( "getDeptno() 호출" );
		return deptno;
	}
	public void setDeptno(String deptno) {
		System.out.println( "setDeptno() 호출" );
		this.deptno = deptno;
	}
	public String getDname() {
		return dname;
	}
	public void setDname(String dname) {
		this.dname = dname;
	}
	public String getLoc() {
		return loc;
	}
	public void setLoc(String loc) {
		this.loc = loc;
	}
}

-MyBatisEx02.java

package myBatisEx01;

import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisEx01 {

	public static void main(String[] args) {
		String resource = "myBatisConfig.xml";
		
		Reader reader = null;
		
		try {
			reader = Resources.getResourceAsReader( resource );
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build( reader );
			System.out.println( "호출 성공" );
			
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if ( reader != null ) try { reader.close(); } catch( IOException e ) {}
		}
	}
}

 

아래는 SqlSession을 호출하는 XML 기반의 매핑 구문이다.

한 개의 매퍼xml 파일에는 많은 매핑 구문을 정의할 수 있다. 

-mapper.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="mybatis">
	<!-- select문 사용한다는 코드  -->
	<select id="select1" resultType="DeptTO">
		select deptno, dname, loc
		from dept
		where deptno=10;
	</select>
</mapper>

-MyBatisEx03.java

package myBatisEx01;

import java.io.IOException;
import java.io.InputStream;

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 MyBatisEx03 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		String resource = "myBatisConfig.xml";
		
		InputStream is = null;
		//Reader reader = null;
		SqlSession sqlSession = null;
		
		try {
			//reader = Resources.getResourceAsReader( resource );
			is = Resources.getResourceAsStream( resource );
			//SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build( reader );
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build( is );
			System.out.println( "호출 성공" );
			
			sqlSession = SqlSessionFactory.openSession();
			System.out.println("연결성공");
			
			DeptTO to = (DeptTO)sqlSession.selectOne( "select1" );
			
			System.out.println( to.getDeptno() );
			System.out.println( to.getDname() );
			System.out.println( to.getLoc() );
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			//if ( reader != null ) try { reader.close(); } catch( IOException e ) {}
			if ( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}
	

}

특정 where절이 있는 경우 mapper.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="mybatis">
	<!-- select문 사용한다는 코드  -->
	<select id="select1" resultType="DeptTO">
		select deptno, dname, loc
		from dept
		where deptno=10;
	</select>
	
	<!-- where절이 있는 경우 -->
	<select id="select2" parameterType="String" resultType="DeptTO">
		select deptno, dname, loc
		from dept
		where deptno=#{deptno};
	</select>
</mapper>
package myBatisEx01;

import java.io.IOException;
import java.io.InputStream;

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 MyBatisEx03 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		String resource = "myBatisConfig.xml";
		
		InputStream is = null;
		//Reader reader = null;
		SqlSession sqlSession = null;
		
		try {
			//reader = Resources.getResourceAsReader( resource );
			is = Resources.getResourceAsStream( resource );
			//SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build( reader );
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build( is );
			System.out.println( "호출 성공" );
			
			sqlSession = sqlSessionFactory.openSession();
			System.out.println("연결성공");
			
			//DeptTO to = (DeptTO)sqlSession.selectOne( "select1" );
			DeptTO to = (DeptTO)sqlSession.selectOne( "select2", "10" );
			
			System.out.println( to.getDeptno() );
			System.out.println( to.getDname() );
			System.out.println( to.getLoc() );
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			//if ( reader != null ) try { reader.close(); } catch( IOException e ) {}
			if ( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}
	

}

 

 만일 데이터 2개 이상 가지고 들어가고 select문의 결과를 보고싶다면 아래와 같이 수정한다.

-mapper.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="mybatis">
	<select id="select1" resultType="DeptTO">
		select deptno, dname, loc
		from dept
		where deptno=10;
	</select>
	<!-- 데이터 1개일 때  -->
	<select id="select2" parameterType="String" resultType="DeptTO">
		select deptno, dname, loc
		from dept
		where deptno=#{deptno};
	</select>
	
	<!-- 데이터 2개 이상일 때  -->
	<select id="select3" parameterType="DeptTO" resultType="DeptTO">
		select deptno, dname, loc
		from dept
		where deptno=#{deptno} and dname=#{dname};
	</select>
</mapper>

 -MyBatisEx04.java

import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;

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 MyBatisEx04 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		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();
			System.out.println( "연결 성공" );
			
			//DeptTO to = (DeptTO)sqlSession.selectOne( "select1" );
			//DeptTO to = (DeptTO)sqlSession.selectOne( "select2", "10" );
			
			DeptTO pto = new DeptTO();
			pto.setDeptno("10");
			pto.setDname("ACCOUNTING");
			DeptTO to = (DeptTO)sqlSession.selectOne( "mybatis.select3", pto );
			
			System.out.println( to.getDeptno() );
			System.out.println( to.getDname() );
			System.out.println( to.getLoc() );
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println( "[에러] " + e.getMessage() );
		} finally {
			if( sqlSession != null ) sqlSession.close();
			if( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}

}

 

 TO를 통해서 가져오지 말고 다른방법으로 데이터를 가져오자.

-mapper.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="mybatis">
	<select id="select1" resultType="DeptTO">
		select deptno, dname, loc
		from dept
		where deptno=10;
	</select>
	<!-- 데이터 1개일 때  -->
	<select id="select2" parameterType="String" resultType="DeptTO">
		select deptno, dname, loc
		from dept
		where deptno=#{deptno};
	</select>
	
	<!-- 데이터 2개 이상일 때  -->
	<select id="select3" parameterType="DeptTO" resultType="DeptTO">
		select deptno, dname, loc
		from dept
		where deptno=#{deptno} and dname=#{dname};
	</select>
	
	<!-- TO타입말고 hashmap형태로 가져오기  -->
	<select id="select4" resultType="java.util.HashMap">
		select deptno, dname, loc
		from dept
		where deptno=10;
	</select>
</mapper>

-MyBatisEx04.java

import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.util.Map;
import java.util.Set;

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 MyBatisEx04 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		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();
			System.out.println( "연결 성공" );
			
			Map map = sqlSession.selectOne( "select4" );
			
			System.out.println( map.size() );
			Set<String> keys = map.keySet();
			for( String key : keys ) {
				System.out.println( key + " : " + map.get(key) );
			}
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println( "[에러] " + e.getMessage() );
		} finally {
			if( sqlSession != null ) sqlSession.close();
			if( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}

}

 

이번에는 여러개 데이터를 가져와보자.

-myBatisConfig.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>
	<environments default="maraidb">
		<environment id="maraidb">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="org.mariadb.jdbc.Driver" />
				<property name="url" value="jdbc:mariadb://localhost:3307/sample" />
				<property name="username" value="root" />
				<property name="password" value="!123456"/>
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="mapper2.xml" />
	</mappers>
</configuration>

 -mapper2.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="mybatis">
	<select id="selectList1" resultType="DeptTO">
		select deptno, dname, loc
		from dept;
	</select>
</mapper>

 -MyBatisEx05.java

import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.util.List;
import java.util.Map;
import java.util.Set;

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 MyBatisEx05 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		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();
			System.out.println( "연결 성공" );
			
			List<DeptTO> lists = sqlSession.selectList( "selectList1" );
			System.out.println( lists.size() );
			
			for( DeptTO list : lists ) {
				System.out.print( list.getDeptno() + " / " );
				System.out.print( list.getDname() + " / " );
				System.out.println( list.getLoc() );
			}
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println( "[에러] " + e.getMessage() );
		} finally {
			if( sqlSession != null ) sqlSession.close();
			if( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}

}

 

 

 ArrayList로 가져오기

import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

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 MyBatisEx05 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		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();
			System.out.println( "연결 성공" );
			
			//List<DeptTO> lists = sqlSession.selectList( "selectList1" );
			ArrayList<DeptTO> lists = (ArrayList)sqlSession.selectList( "selectList1" );
			System.out.println( lists.size() );
			
			for( DeptTO list : lists ) {
				System.out.print( list.getDeptno() + " / " );
				System.out.print( list.getDname() + " / " );
				System.out.println( list.getLoc() );
			}
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println( "[에러] " + e.getMessage() );
		} finally {
			if( sqlSession != null ) sqlSession.close();
			if( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}

}

 

실습) emp테이블의 deptno가 10번인 사원을 조회해서 empno, ename, job, mgr, hiredate, sal, comm, deptno의 정보를 출력해보자. (myBatis 사용)

-mariaDB emp테이블

-결과

 

-EmpTO.java

public class EmpTO {
	private String empno;
	private String ename;
	private String job;
	private String mgr;
	private String hiredate;
	private String sal;
	private String comm;
	private String deptno;
	
	public String getEmpno() {
		return empno;
	}
	public String getEname() {
		return ename;
	}
	public String getJob() {
		return job;
	}
	public String getMgr() {
		return mgr;
	}
	public String getHiredate() {
		return hiredate;
	}
	public String getSal() {
		return sal;
	}
	public String getComm() {
		return comm;
	}
	public String getDeptno() {
		return deptno;
	}
	
	public void setEmpno(String empno) {
		this.empno = empno;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public void setMgr(String mgr) {
		this.mgr = mgr;
	}
	public void setHiredate(String hiredate) {
		this.hiredate = hiredate;
	}
	public void setSal(String sal) {
		this.sal = sal;
	}
	public void setComm(String comm) {
		this.comm = comm;
	}
	public void setDeptno(String deptno) {
		this.deptno = deptno;
	}
}

-mapper3.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="mybatis">
	<select id="selectList2" parameterType="String" resultType="EmpTO">
		select empno, ename, job, mgr, hiredate, sal, comm, deptno
		from emp
		where deptno=#{deptno};
	</select>
</mapper>

-myBatisConfig.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>
	<environments default="maraidb">
		<environment id="maraidb">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="org.mariadb.jdbc.Driver" />
				<property name="url" value="jdbc:mariadb://localhost:3307/sample" />
				<property name="username" value="root" />
				<property name="password" value="!123456"/>
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="mapper2.xml" />
		<mapper resource="mapper3.xml" />
	</mappers>
</configuration>

-MyBatisEx06.java

import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

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 MyBatisEx06 {

	public static void main(String[] args) {
		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();
			System.out.println( "연결 성공" );
			
			ArrayList<EmpTO> lists = (ArrayList)sqlSession.selectList( "selectList2", "20" );
			
			for( EmpTO list : lists ) {
				System.out.print( list.getEmpno() + " / " );
				System.out.print( list.getEname() + " / " );
				System.out.print( list.getJob() + " / " );
				System.out.print( list.getMgr() + " / " );
				System.out.print( list.getHiredate() + " / " );
				System.out.print( list.getSal() + " / " );
				System.out.print( list.getComm() + " / " );
				System.out.println( list.getDeptno() );
			}
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println( "[에러] " + e.getMessage() );
		} finally {
			if( sqlSession != null ) sqlSession.close();
			if( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}
}

 

 이번에는 Like를 사용해보자.

-mapper3.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="mybatis">
	<select id="selectList2" parameterType="String" resultType="EmpTO">
		select empno, ename, job, mgr, hiredate, sal, comm, deptno
		from emp
		where deptno=#{deptno};
	</select>
	
	<select id="selectList3" parameterType="String" resultType="EmpTO">
		select empno, ename, job, mgr, hiredate, sal, comm, deptno
		from emp
		where ename like #{ename};
	</select>
</mapper>

-MyBatisEx07.java

import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

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 MyBatisEx07 {

	public static void main(String[] args) {
		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();
			System.out.println( "연결 성공" );
			
			ArrayList<EmpTO> lists = (ArrayList)sqlSession.selectList( "selectList3", "S%" );
			
			for( EmpTO list : lists ) {
				System.out.print( list.getEmpno() + " / " );
				System.out.println( list.getEname() + " / " );
			}
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println( "[에러] " + e.getMessage() );
		} finally {
			if( sqlSession != null ) sqlSession.close();
			if( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}
}

 

혹은 아래와 같이 쓸 수 있다.

<?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="mybatis">
	<select id="selectList2" parameterType="String" resultType="EmpTO">
		select empno, ename, job, mgr, hiredate, sal, comm, deptno
		from emp
		where deptno=#{deptno};
	</select>
	
	<select id="selectList3" parameterType="String" resultType="EmpTO">
		select empno, ename, job, mgr, hiredate, sal, comm, deptno
		from emp
		where ename like #{ename};
	</select>
	
	<select id="selectList4" parameterType="String" resultType="EmpTO">
		select empno, ename, job, mgr, hiredate, sal, comm, deptno
		from emp
		where ename like concat( #{ename}, '%' );
	</select>
</mapper>
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

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 MyBatisEx07 {

	public static void main(String[] args) {
		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();
			System.out.println( "연결 성공" );
			
			//ArrayList<EmpTO> lists = (ArrayList)sqlSession.selectList( "selectList3", "S%" );
			ArrayList<EmpTO> lists = (ArrayList)sqlSession.selectList( "selectList4", "S" );
			
			for( EmpTO list : lists ) {
				System.out.print( list.getEmpno() + " / " );
				System.out.println( list.getEname() + " / " );
			}
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println( "[에러] " + e.getMessage() );
		} finally {
			if( sqlSession != null ) sqlSession.close();
			if( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}
}

 

실습) 우편번호 검색기 myBatis로 만들기

-실행결과

 

-mariaDB Zipcode 테이블

-mapper.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="mybatis">
	<select id="selectList1" parameterType="String" resultType="ZipcodeTO">
		select zipcode, sido, gugun, dong, ri, bunji
		from zipcode
		where dong like #{dong};
	</select>
</mapper>

-myBatisConfig.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>
	<environments default="maraidb">
		<environment id="maraidb">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="org.mariadb.jdbc.Driver" />
				<property name="url" value="jdbc:mariadb://localhost:3307/sample" />
				<property name="username" value="root" />
				<property name="password" value="!123456"/>
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="mapper.xml" />
	</mappers>
</configuration>

-ZipcodeTO.java

public class ZipcodeTO {
	private String zipcode;
	private String sido;
	private String gugun;
	private String dong;
	private String ri;
	private String bunji;
	
	public String getZipcode() {
		return zipcode;
	}
	public String getSido() {
		return sido;
	}
	public String getGugun() {
		return gugun;
	}
	public String getDong() {
		return dong;
	}
	public String getRi() {
		return ri;
	}
	public String getBunji() {
		return bunji;
	}
	
	public void setZipcode(String zipcode) {
		this.zipcode = zipcode;
	}
	public void setSido(String sido) {
		this.sido = sido;
	}
	public void setGugun(String gugun) {
		this.gugun = gugun;
	}
	public void setDong(String dong) {
		this.dong = dong;
	}
	public void setRi(String ri) {
		this.ri = ri;
	}
	public void setBunji(String bunji) {
		this.bunji = bunji;
	}
}

-ZipcodeEx01.java

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;

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 ZipcodeEx01 {

	public static void main(String[] args) {
		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();
			System.out.println( "연결 성공" );
			
			ArrayList<ZipcodeTO> lists = (ArrayList)sqlSession.selectList( "selectList1", "개포%" );
			for( ZipcodeTO list : lists ) {
				System.out.print( list.getZipcode() + " / " );
				System.out.print( list.getSido() + " / " );
				System.out.print( list.getGugun() + " / " );
				System.out.print( list.getDong() + " / " );
				System.out.print( list.getRi() + " / " );
				System.out.println( list.getBunji()  );
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if( sqlSession != null ) sqlSession.close();
			if( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}
}

 

ㅇ조인을 통해서 조회하기

사원테이블에서 사원번호, 사원이름, 급여, 등급(호봉)에 대해서 조회해보자.

-mapper.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="mybatis">
	<select id="selectList2" parameterType="String" resultType="EmpTO">
		select empno, ename, job, mgr, hiredate, sal, comm, deptno
		from emp
		where deptno=#{deptno};
	</select>
	
	<select id="selectList3" parameterType="String" resultType="EmpTO">
		select empno, ename, job, mgr, hiredate, sal, comm, deptno
		from emp
		where ename like #{ename};
	</select>
	
	<select id="selectList4" parameterType="String" resultType="EmpTO">
		select empno, ename, job, mgr, hiredate, sal, comm, deptno
		from emp
		where ename like concat( #{ename}, '%' );
	</select>
	
	<select id="selectList5" resultType="java.util.HashMap">
		select empno, ename, sal, grade
		from emp e, salgrade s
		where e.sal between s.losal and s.hisal; 
	</select>
</mapper>

-MyBatisEx08.java

import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

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 MyBatisEx08 {

	public static void main(String[] args) {
		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();
			System.out.println( "연결 성공" );
			
			List<Map> lists = sqlSession.selectList( "selectList5" );
			System.out.println( lists.size() );
			
			for( int i=0; i<lists.size(); i++ ) {
				Map map = lists.get( i );
				System.out.print( map.get("empno") + " / " );
				System.out.print( map.get("ename") + " / " );
				System.out.println( map.get("grade") );
			}
		
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println( "[에러] " + e.getMessage() );
		} finally {
			if( sqlSession != null ) sqlSession.close();
			if( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}
}

 

만약 between을 부등호로 표현하면 다음과 같다.

하지만 >=, <= 기호는 xml에서 태그 부호와 충돌해서 사용할 수 없다.

&gt; 와 &lt; 로 사용해야 한다.

<?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="mybatis">
	<select id="selectList2" parameterType="String" resultType="EmpTO">
		select empno, ename, job, mgr, hiredate, sal, comm, deptno
		from emp
		where deptno=#{deptno};
	</select>
	
	<select id="selectList3" parameterType="String" resultType="EmpTO">
		select empno, ename, job, mgr, hiredate, sal, comm, deptno
		from emp
		where ename like #{ename};
	</select>
	
	<select id="selectList4" parameterType="String" resultType="EmpTO">
		select empno, ename, job, mgr, hiredate, sal, comm, deptno
		from emp
		where ename like concat( #{ename}, '%' );
	</select>
	
	<select id="selectList5" resultType="java.util.HashMap">
		select empno, ename, sal, grade
		from emp e, salgrade s
		where e.sal between s.losal and s.hisal; 
	</select>
	
	<select id="selectList6" resultType="java.util.HashMap">
		select empno, ename, sal, grade
		from emp e, salgrade s
		where e.sal &gt; s.losal and e.sal &lt; s.hisal; 
	</select>
</mapper>
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

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 MyBatisEx08 {

	public static void main(String[] args) {
		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();
			System.out.println( "연결 성공" );
			
			List<Map> lists = sqlSession.selectList( "selectList6" );
			System.out.println( lists.size() );
			
			for( int i=0; i<lists.size(); i++ ) {
				Map map = lists.get( i );
				System.out.print( map.get("empno") + " / " );
				System.out.print( map.get("ename") + " / " );
				System.out.println( map.get("grade") );
			}
		
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println( "[에러] " + e.getMessage() );
		} finally {
			if( sqlSession != null ) sqlSession.close();
			if( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}
}

 

이번에는 두 개의 mapper1.xml과 mapper2.xml을 동시에 사용할 때는 앞에 mapper namespace를 적어준다.

mapper1.select1

 

그리고 mapper.xml에서 중복되는 컬럼값을 태그로 묶어서 사용할 수 있다.

-mapper1.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="mybatis1">
	<!-- 중복되는 컬럼명 하나로 묶기  -->
	<sql id="userColumns">deptno, dname</sql>

	<select id="select1" resultType="DeptTO">
		select <include refid="userColumns"/>, loc
		from dept
		where deptno=10;
	</select>
	
	<select id="select2" resultType="DeptTO">
		select <include refid="userColumns"/>, loc
		from dept
		where deptno=20;
	</select>
</mapper>

-mapper2.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="mybatis2">
	<select id="select1" resultType="DeptTO">
		select deptno, dname, loc
		from dept
		where deptno=20;
	</select>
</mapper>

-MyBatisEx01.java

import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

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 MyBatisEx01 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		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();
			System.out.println( "연결 성공" );
			
			DeptTO to = (DeptTO)sqlSession.selectOne( "mybatis1.select1" );
			System.out.println( to.getDeptno() );
			System.out.println( to.getDname() );
			System.out.println( to.getLoc() );
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println( "[에러] " + e.getMessage() );
		} finally {
			if( sqlSession != null ) sqlSession.close();
			if( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}
}

 

ㅇDML 사용하기

-mapper.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="mybatis">
	<insert id="insert1" parameterType="DeptTO">
		insert into dept( deptno, dname, loc )
		values ( #{deptno}, #{dname}, #{loc} );
	</insert>
</mapper>

-MyBatisEx01.java

import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

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 MyBatisEx01 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		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();
			System.out.println( "연결 성공" );
			
			DeptTO to = new DeptTO();
			to.setDeptno( "60" );
			to.setDname( "개발" );
			to.setLoc( "부산" );
			
			int result = sqlSession.insert( "insert1", to );
			if( result == 1 ) {
				System.out.println( "성공" );
				//트랜잭션의 마무리는 commit
				sqlSession.commit();
			} else {
				System.out.println( "실패" );
			}
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println( "[에러] " + e.getMessage() );
		} finally {
			if( sqlSession != null ) sqlSession.close();
			if( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}
}

꼭 commit을 해줘야한다.

 

애초에 autocommit을 해보자.

sqlSession = sqlSessionFactory.openSession( true );
System.out.println( "연결 성공" );
			
DeptTO to = new DeptTO();
to.setDeptno( "70" );
to.setDname( "연구" );
to.setLoc( "대구" );

 

delete 사용

-mapper1.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="mybatis">
	<insert id="insert1" parameterType="DeptTO">
		insert into dept( deptno, dname, loc )
		values ( #{deptno}, #{dname}, #{loc} );
	</insert>
	
	<delete id="delete1" parameterType="String">
		delete from dept
		where deptno = #{deptno};
	</delete>
</mapper>

-MyBatisEx02.java

import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

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 MyBatisEx02 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		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( "연결 성공" );
			
			int result = sqlSession.delete( "delete1", "70");
			if( result == 1 ) {
				System.out.println( "성공" );
				//트랜잭션의 마무리는 commit
				//sqlSession.commit();
			} else {
				System.out.println( "실패" );
			}
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println( "[에러] " + e.getMessage() );
		} finally {
			if( sqlSession != null ) sqlSession.close();
			if( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}
}

 

 

update 사용하기

<?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="mybatis">
	<insert id="insert1" parameterType="DeptTO">
		insert into dept( deptno, dname, loc )
		values ( #{deptno}, #{dname}, #{loc} );
	</insert>
	
	<delete id="delete1" parameterType="String">
		delete from dept
		where deptno = #{deptno};
	</delete>
	
	<update id="update1" parameterType="DeptTO">
		update dept 
		set loc = #{loc} 
		where deptno = #{deptno};
	</update>
</mapper>
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

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 MyBatisEx03 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		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.setLoc( "광주" );
			to.setDeptno( "60" );
			
			int result = sqlSession.update( "update1", to );
			if( result == 1 ) {
				System.out.println( "성공" );
				//트랜잭션의 마무리는 commit
				//sqlSession.commit();
			} else {
				System.out.println( "실패" );
			}
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println( "[에러] " + e.getMessage() );
		} finally {
			if( sqlSession != null ) sqlSession.close();
			if( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}
}

 

 

ㅇDDL 사용하기 

${value}구문에 곧바로 sql문을 집어넣는다.

<?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="mybatis">
	<update id="createTable1" parameterType="String">
		${value}
	</update>
</mapper>
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

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 MyBatisEx01 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		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( "연결 성공" );
			
			String sql = "create table tbl( col1 varchar(10) )";
			int result = sqlSession.update( "createTable1", sql );
			if( result == 0 ) {
				System.out.println( "성공" );
			} else {
				System.out.println( "실패" );
			}
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println( "[에러] " + e.getMessage() );
		} finally {
			if( sqlSession != null ) sqlSession.close();
			if( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}
}

 

만약 기존에 테이블이 있을 수도 있으니 아래와 같이 변경한다.

 

${value}를 사용해야 한다.

<?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="mybatis">
	<update id="createTable1" parameterType="String">
		${value}
	</update>
	
	<update id="createTable2" parameterType="String">
		create table if not exists ${value} (
			col1 varchar(10)
		)
	</update>
</mapper>
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

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 MyBatisEx01 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		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( "연결 성공" );
			
			//String sql = "create table tbl( col1 varchar(10) )";
			//int result = sqlSession.update( "createTable1", sql );
			
			int result = sqlSession.update( "createTable2", "tbl2" );
			
			if( result == 0 ) {
				System.out.println( "성공" );
			} else {
				System.out.println( "실패" );
			}
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println( "[에러] " + e.getMessage() );
		} finally {
			if( sqlSession != null ) sqlSession.close();
			if( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}
}

 

 

 drop 사용하기

<?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="mybatis">
	<update id="createTable1" parameterType="String">
		${value}
	</update>
	
	<update id="createTable2" parameterType="String">
		create table if not exists ${value} (
			col1 varchar(10)
		)
	</update>
	
	<update id="droptable1" parameterType="String">
		drop table if exists ${value}
	</update>
</mapper>
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

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 MyBatisEx02 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		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( "연결 성공" );
			
			int result = sqlSession.update( "droptable1", "tbl2" );
			
			if( result == 0 ) {
				System.out.println( "성공" );
			} else {
				System.out.println( "실패" );
			}
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println( "[에러] " + e.getMessage() );
		} finally {
			if( sqlSession != null ) sqlSession.close();
			if( is != null ) try { is.close(); } catch( IOException e ) {}
		}
	}
}

 

 

 4. JSP에서 myBatis 사용하기

새로운 다이나믹 웹프로젝트를 만들고 웹콘텐츠 lib폴더에 mybatis라이브러리,

자바리소스 폴더의 src폴더에 log4j.xml, myBatisConfig.xml파일을 넣어준다.

 -DeptTO.java

package model1;

public class DeptTO {
	private String deptno;
	private String dname;
	private String loc;
	
	public String getDeptno() {
		return deptno;
	}
	public String getDname() {
		return dname;
	}
	public String getLoc() {
		return loc;
	}
	
	public void setDeptno(String deptno) {
		this.deptno = deptno;
	}
	public void setDname(String dname) {
		this.dname = dname;
	}
	public void setLoc(String loc) {
		this.loc = loc;
	}
}

 -dept.xml (전에 mapper.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="mybatis">
	<select id="select1" resultType="model1.DeptTO">
		select deptno, dname, loc
		from dept
		where deptno = 10;
	</select>
</mapper>

 -myBatisConfig.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>
	<environments default="maraidb">
		<environment id="maraidb">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="org.mariadb.jdbc.Driver" />
				<property name="url" value="jdbc:mariadb://localhost:3307/sample" />
				<property name="username" value="root" />
				<property name="password" value="!123456"/>
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="model1/dept.xml"/>
	</mappers>
</configuration>

 -ex02.jsp

<%@ 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="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 );
	DeptTO to = (DeptTO)sqlSession.selectOne( "select1" );
	
	System.out.println( to.getDeptno() );
	System.out.println( to.getDname() );
	
	
	} catch( IOException e ) {
		System.out.println( "에러 : " + e.getMessage() );
	} finally {
		if( is != null ) is.close();
	}
	
%>

 

톰캣을 통해서 데이터베이스 풀링 작업을 한다.

-context.xml

<?xml version="1.0" encoding="utf-8" ?>
<Context>
	<Resource
		name = "jdbc/mariadb2"
		auth = "Container"
		type = "javax.sql.DataSource"
		driverClassName = "org.mariadb.jdbc.Driver"
		url = "jdbc:mysql://localhost:3307/sample"
		username = "root"
		password = "!123456"
	/>
</Context>

 

실습) 우편번호 검색기 웹버전으로 만들기

* 하드코딩

* model1 / model2

model2로 만들어보자. 먼저 아래와 같이 프로젝트 안에 라이브러리를 넣어준다.

그리고 자바리소스 폴더에 model2와 servlet 패키지를 만들어준다.

 먼저 servlet 패키지이다.

-Controller.java

package servlet;

import java.io.IOException;
import java.io.UnsupportedEncodingException;

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 model2.ActionModel;
import model2.ZipcodeAction;
import model2.ZipcodeOkAction;

/**
 * Servlet implementation class Controller
 */
@WebServlet("/Controller")
public class Controller extends HttpServlet {
	private static final long serialVersionUID = 1L;

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		this.doProcess(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		this.doProcess(request, response);
	}

	protected void doProcess(HttpServletRequest request, HttpServletResponse response) {
		try {
			request.setCharacterEncoding( "utf-8" );
			
			String action = request.getParameter( "action" );
			String url = "";
			
			ActionModel model = null;
			
			if( action == null || action.equals("") || action.equals( "zipcode" ) ) {
				model = new ZipcodeAction();
				model.execute(request, response);
				url = "/WEB-INF/view/zipcode.jsp";
				
			} else if ( action.equals( "zipcode_ok" ) ) {
				model = new ZipcodeOkAction();
				model.execute(request, response);
				url = "/WEB-INF/view/zipcode_ok.jsp";
			}
			
			RequestDispatcher dispatcher = request.getRequestDispatcher( url );
			dispatcher.forward(request, response);
			
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ServletException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
				
			
	} 
}

그리고 model2 패키지와 데이터베이스 연동설정이다.

-context.xml

<?xml version="1.0" encoding="utf-8" ?>
<Context>
	<Resource
		name = "jdbc/mariadb1"
		auth = "Container"
		type = "javax.sql.DataSource"
		driverClassName = "org.mariadb.jdbc.Driver"
		url = "jdbc:mysql://localhost:3307/sample"
		username = "root"
		password = "!123456"
	/>
</Context>

-myBatisConfig.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>
	<environments default="maraidb2">
		<environment id="maraidb1">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="org.mariadb.jdbc.Driver" />
				<property name="url" value="jdbc:mariadb://localhost:3307/sample" />
				<property name="username" value="root" />
				<property name="password" value="!123456"/>
			</dataSource>
		</environment>
		
		<environment id="maraidb2">
			<transactionManager type="JDBC" />
			<dataSource type="JNDI">
				<property name="data_source" value="java:comp/env/jdbc/mariadb1" />
			</dataSource>
		</environment>
	</environments>
	
	<mappers>
		<mapper resource="model2/zipcode.xml" />
	</mappers>
</configuration>

-zipcode.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="mybatis">
	<select id="select" resultType="model2.ZipcodeTO">
		select zipcode, sido, gugun, dong, ri, bunji
		from zipcode
		where dong like #{dong};
	</select>
</mapper>

-ZipcodeTO.java

package model2;

public class ZipcodeTO {
	private String zipcode;
	private String sido;
	private String gugun;
	private String dong;
	private String ri;
	private String bunji;
	private String seq;
	
	public String getZipcode() {
		return zipcode;
	}
	public void setZipcode(String zipcode) {
		this.zipcode = zipcode;
	}
	public String getSido() {
		return sido;
	}
	public void setSido(String sido) {
		this.sido = sido;
	}
	public String getGugun() {
		return gugun;
	}
	public void setGugun(String gugun) {
		this.gugun = gugun;
	}
	public String getDong() {
		return dong;
	}
	public void setDong(String dong) {
		this.dong = dong;
	}
	public String getRi() {
		return ri;
	}
	public void setRi(String ri) {
		this.ri = ri;
	}
	public String getBunji() {
		return bunji;
	}
	public void setBunji(String bunji) {
		this.bunji = bunji;
	}
	public String getSeq() {
		return seq;
	}
	public void setSeq(String seq) {
		this.seq = seq;
	}
}

-ZipcodeDAO.java

package model2;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

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 = null;

	public ZipcodeDAO() {
		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();
		}
	}
	
	public ArrayList<ZipcodeTO> searchLists( ZipcodeTO to ) {
		String strDong = to.getDong() + "%";
		
		ArrayList<ZipcodeTO> lists = (ArrayList)sqlSession.selectList( "select", strDong );
		
		return lists; 
	}	
}

그리고 jsp파일의 액션클래스와 액션클래스가 구현할 인터페이스를 설정한다.

-ActionModel.java (인터페이스)

package model2;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public interface ActionModel {
	public void execute( HttpServletRequest request, HttpServletResponse response );
}

-ZipcodeAction.java

package model2;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class ZipcodeAction implements ActionModel {

	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) {
	}
}

-ZipcodeOkAction.java

package model2;

import java.io.UnsupportedEncodingException;
import java.util.ArrayList;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class ZipcodeOkAction implements ActionModel {

	@Override
	public void execute(HttpServletRequest request, HttpServletResponse response) {
		try {
			request.setCharacterEncoding( "utf-8" );

			String strDong = request.getParameter("strDong");
			
			ZipcodeTO to = new ZipcodeTO();
			to.setDong( strDong );
			
			ZipcodeDAO dao = new ZipcodeDAO();
			ArrayList<ZipcodeTO> lists = dao.searchLists( to );
			
			

			request.setAttribute( "lists", lists );
			
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}

그리고 view폴더에 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>
<script type="text/javascript">
	const checkfrm = function() {
		if( document.frm.strDong.value.trim() == '' ) {
			alert( '동이름을 입력해 주세요' );
			return;
		}
		document.frm.submit();
	};
</script>
</head>
<body>

<form action="./Controller" method="post" name="frm">
<input type="hidden" name="action" value="zipcode_ok" />
동이름 <input type="text" name="strDong" />
<input type="button" value="동이름 검색" onclick="checkfrm()" />
</form>

</body>
</html>

-zipcode_ok.jsp

<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%@ page import="model2.ZipcodeTO" %>

<%
	request.setCharacterEncoding( "utf-8" );
	ArrayList<ZipcodeTO> lists = (ArrayList)request.getAttribute( "lists" );

	StringBuffer html = new StringBuffer();
	if( lists != null ) {
		html.append( "<table width='600' border='1'>" );
		for(ZipcodeTO list : lists) {
			html.append( "<tr>" );
			html.append( "	<td>" + list.getZipcode() + "</td>" );
			html.append( "	<td>" + list.getSido() + "</td>" );
			html.append( "	<td>" + list.getGugun() + "</td>" );
			html.append( "	<td>" + list.getDong() + "</td>" );
			html.append( "	<td>" + list.getRi() + "</td>" );
			html.append( "	<td>" + list.getBunji() + "</td>" );
			html.append( "</tr>" );
		}
		html.append( "</table>" );
	} else {
		System.out.println( "에러: lists null" );
	}

%>

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

 

 

 

 

 

 

728x90
반응형
저작자표시 동일조건

'데이터 엔지니어링 정복 > HTML-CSS-JavaScript-Spring-Node.js' 카테고리의 다른 글

[Maven&Spring] 1/22 | Maven(myBatis사용), Spring(개념 및 특징, 이클립스에서 설치방법, 수동 라이브러리 추가, Maven으로 라이브러리 추가, DI사용하기, 실습)  (0) 2021.01.22
[myBatis] 1/21 | Model1 게시판 myBatis로 만들기, 어노테이션(기본sql문, 게시판만들기), Maven(개념, 사용)  (0) 2021.01.21
[Bootstrap] 1/19 | Bootstrp(각종 클래스)  (0) 2021.01.19
[Bootstrap] 1/18 | SPA개념 및 특징, W2UI사용하기(레이아웃, 이벤트, 영역 안에 데이터넣기, 그리드, 우편번호검색기), BootStrap사용하기(반응성, typography, mark, blockquote, dl)  (0) 2021.01.18
[JavaScript-jQuery, Ajax] 1/15 | jQuery UI 사용하기(DatePicker, Dialog, Menu, Tabs, Tab으로 구구단만들기, Tooltip), Model2 게시판 jQuery UI으로 구현하기  (0) 2021.01.15
'데이터 엔지니어링 정복/HTML-CSS-JavaScript-Spring-Node.js' Related Articles more
Comments