Spring MyBatis

2023. 9. 25. 12:47

위로 아래

MyBatis

--PreparedStatement
String sql = "UPDATE user SET name=?, number=? WHERE id=?";
pstmt.setInt(1, name);
pstmt.setInt(2, number);
pstmt.setInt(3, id);


--MyBtis
UPDATE user SET name=#{name}, number=#{number} WHERE id=#{id}

DB를 쉽게 다루도록 도와주는 오픈소스 프레임워크
JDBC 방식과 달리 xml 파일에 쿼리를 작성하게 해주어서 가독성 좋고 편하게 해주는 것.
동적 쿼리 작성 가능
SQL문과 프로그램 코드의 분리
SQL 실행 결과를 사용자 정의 DTO, Map 등에 매핑할 수 있다
사용 방법 순서 : 의존성 설정 -> DB 설정 -> MyBatis 설정 -> Mapper 인터페이스 작성 -> XML 작성 -> MyBatis 사용

장점

유연성 : SQL 쿼리를 동적으로 직접 작성할 수 있어서 유연
간결성 : 프로그래밍 언어 코드와 SQL 쿼리를 분리해서 코드를 간결하게 바꿈
성능 : 캐시 기능을 제공하여 데이터베이스 연산 속도 높임
다양한 데이터베이스에 지원

동작 순서

애플리케이션 실행 시 프로세스

SqlSessionFactoryBean이 SqlSessionFactoryBuilder를 위해 SqlSessionFactory를 빌드하도록 요청
애플리케이션이 SqlSessionFactoryBuilder를 사용해 빌드된 SqlSessionFactory에서 SqlSession을 가져온다
SqlSessionFactoryBuilder가 Mybatis 구성 파일의 정의에 따라 SqlSessionFactory를 생성(생성된 SqlSessionFactory는 스프링 DI 컨테이너에 의해 저장)
MapperFactoryBean이 안전한 SqlSession(SqlSessionTemplate) 및 스레드 안전 매퍼 개체를 생성

클라이언트 요청 시 프로세스

클라이언트가 애플리케이션에 프로세스를 요청
애플리케이션이 DI 컨테이너에 주입한 mapper 객체의 메서드를 호출
mapper 객체가 호출된 메서드에 해당하는 SqlSession 메서드를 호출
SqlSession이 SqlSession 메서드를 호출
프록시 사용 및 스레드 안전 SqlSession이 트랜잭션에 할당된 MyBatis 표준 SqlSession을 사용
SqlSessionFactory가 Mybatis 표준 SqlSession을 반환
Mybatis 표준 SqlSession이 mapping 파일에서 실행할 SQL을 가져와 실행

Datasource-context.xml 설정

데이터베이스 연결

MyBatis는 JDBC로 DB를 연결
스프링 설정(XML, Properties)에 DB 연결정보(DataSource) 등록

<!-- datasource-context.xml -->
<bean id="dataSourceOR" class="org.apache.commons.dbcp.BasicDataSource" 
      destroy-method="close">
  <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/> 
  <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:xe"/>
  <property name="username" value="c##scott"/>   
  <property name="password" value="1234"/>
</bean>

MyBatis 설정

XML 설정 파일 생성. 설정 추가

<!-- db connection info. -->
<!-- 1.mybatis f/w db connect  2. sql file location setting-->
<bean id="sqlSessionFactory" 
       class="org.mybatis.spring.SqlSessionFactoryBean" 
          lazy-init="true">
     <property name="dataSource" ref="dataSource" />
     <property name="configLocation" 
            value="/WEB-INF/spring/mybatisConfig/Mybatis-Config.xml"/>
     <property name="mapperLocations" 
           value="classpath*:**/mapper/*Mapper.xml" />
</bean>
<!-- use DAO  -->
<bean id="sqlsession" class="org.mybatis.spring.SqlSessionTemplate">
  <constructor-arg ref="sqlSessionFactory"/>
</bean>		
</beans>

전체 코드

<!-- datasource-context.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd">
	<!-- database setting -->
	<bean id="dataSourceOR" class="org.apache.commons.dbcp.BasicDataSource" 
	          destroy-method="close">
	      <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/> 
	      <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:xe"/>
	      <property name="username" value="c##scott"/>   
	      <property name="password" value="1234"/>
	</bean>
	<bean id="dataSource" class="net.sf.log4jdbc.Log4jdbcProxyDataSource">
	  <constructor-arg ref="dataSourceOR"/>
	  <property name="logFormatter">
	     <bean class="net.sf.log4jdbc.tools.Log4JdbcCustomFormatter">
	       <property name="loggingType" value="MULTI_LINE"/> 
	       <property name="sqlPrefix" value="[SQL]: "/>
	     </bean>
	  </property>
	</bean>
	
	<!-- db connection info. -->
	<!-- 1.mybatis f/w db connect  2. sql file location setting-->
	<bean id="sqlSessionFactory" 
	       class="org.mybatis.spring.SqlSessionFactoryBean" 
	          lazy-init="true">
		 <property name="dataSource" ref="dataSource" />
		 <property name="configLocation" 
		        value="/WEB-INF/spring/mybatisConfig/Mybatis-Config.xml"/>
		 <property name="mapperLocations" 
		       value="classpath*:**/mapper/*Mapper.xml" />
  	</bean>
  	<!-- use DAO  -->
  	<bean id="sqlsession" class="org.mybatis.spring.SqlSessionTemplate">
  	  <constructor-arg ref="sqlSessionFactory"/>
  	</bean>		
</beans>

pom.xml 설정

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.13</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>2.1.0</version>
</dependency>

<?xml version="1.0" encoding="UTF-8"?>
<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/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.ecom4</groupId>
	<artifactId>hi</artifactId>
	<name>springBoard</name>
	<packaging>war</packaging>
	<version>1.0.0-BUILD-SNAPSHOT</version>
	<properties>
		<java-version>1.11</java-version>
		<org.springframework-version>4.3.4.RELEASE</org.springframework-version>
		<org.aspectj-version>1.6.10</org.aspectj-version>
		<org.slf4j-version>1.6.6</org.slf4j-version>
	</properties>
	
	<repositories>
		<repository>
		   <id>oracle</id>
		   <name>ORACLE JDBC Repository</name>
		   <url>http://mesir.googlecode.com/svn/trunk/mavenrepo</url>
		</repository>
	</repositories>
	
	<dependencies>
		<!-- Spring -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${org.springframework-version}</version>
			<exclusions>
				<!-- Exclude Commons Logging in favor of SLF4j -->
				<exclusion>
					<groupId>commons-logging</groupId>
					<artifactId>commons-logging</artifactId>
				 </exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>
				
		<!-- AspectJ -->
		<dependency>
			<groupId>org.aspectj</groupId>
			<artifactId>aspectjrt</artifactId>
			<version>${org.aspectj-version}</version>
		</dependency>	
		
		<!-- Logging -->
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>${org.slf4j-version}</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>jcl-over-slf4j</artifactId>
			<version>${org.slf4j-version}</version>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-log4j12</artifactId>
			<version>${org.slf4j-version}</version>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.15</version>
			<exclusions>
				<exclusion>
					<groupId>javax.mail</groupId>
					<artifactId>mail</artifactId>
				</exclusion>
				<exclusion>
					<groupId>javax.jms</groupId>
					<artifactId>jms</artifactId>
				</exclusion>
				<exclusion>
					<groupId>com.sun.jdmk</groupId>
					<artifactId>jmxtools</artifactId>
				</exclusion>
				<exclusion>
					<groupId>com.sun.jmx</groupId>
					<artifactId>jmxri</artifactId>
				</exclusion>
			</exclusions>
			<scope>runtime</scope>
		</dependency>

		<!-- @Inject -->
		<dependency>
			<groupId>javax.inject</groupId>
			<artifactId>javax.inject</artifactId>
			<version>1</version>
		</dependency>
				
		<!-- Servlet -->
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>servlet-api</artifactId>
			<version>2.5</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet.jsp</groupId>
			<artifactId>jsp-api</artifactId>
			<version>2.1</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
			<version>1.2</version>
		</dependency>
		<!-- db setting -->
       <dependency>
		    <groupId>org.bgee.log4jdbc-log4j2</groupId>
		    <artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
		    <version>1.16</version>
		</dependency>
		 <dependency>  
            <groupId>org.lazyluke</groupId>  
             <artifactId>log4jdbc-remix</artifactId>  
              <version>0.2.7</version>  
        </dependency>  
        <dependency>  
              <groupId>org.slf4j</groupId>  
             <artifactId>slf4j-log4j12</artifactId>  
            <version>1.6.1</version>  
        </dependency>
	<!-- db setting -->
		<dependency>
		    <groupId>com.oracle.database.jdbc</groupId>
		    <artifactId>ojdbc8</artifactId>
		    <version>23.2.0.0</version>
		</dependency>
		<dependency>
		    <groupId>org.springframework</groupId>
		    <artifactId>spring-jdbc</artifactId>
		    <version>${org.springframework-version}</version>
		</dependency>
		<!-- connection pool -->
		<dependency>
		    <groupId>commons-dbcp</groupId>
		    <artifactId>commons-dbcp</artifactId>
		    <version>1.4</version>
		</dependency>
		<dependency>
		    <groupId>org.mybatis</groupId>
		    <artifactId>mybatis</artifactId>
		    <version>3.5.13</version>
		</dependency>
		<dependency>
		    <groupId>org.mybatis</groupId>
		    <artifactId>mybatis-spring</artifactId>
		    <version>2.1.0</version>
		</dependency>
	<!-- db setting end -->
	  <!-- lombok -->
	  <dependency>
	    <groupId>org.projectlombok</groupId>
	    <artifactId>lombok</artifactId>
	    <version>1.18.28</version>
	    <scope>provided</scope>
	  </dependency>
		<!-- Test -->
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.7</version>
			<scope>test</scope>
		</dependency>        
	</dependencies>
    <build>
        <plugins>
            <plugin>
                <artifactId>maven-eclipse-plugin</artifactId>
                <version>2.9</version>
                <configuration>
                    <additionalProjectnatures>
                        <projectnature>org.springframework.ide.eclipse.core.springnature</projectnature>
                    </additionalProjectnatures>
                    <additionalBuildcommands>
                        <buildcommand>org.springframework.ide.eclipse.core.springbuilder</buildcommand>
                    </additionalBuildcommands>
                    <downloadSources>true</downloadSources>
                    <downloadJavadocs>true</downloadJavadocs>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.5.1</version>
                <configuration>
                    <source>1.6</source>
                    <target>1.6</target>
                    <compilerArgument>-Xlint:all</compilerArgument>
                    <showWarnings>true</showWarnings>
                    <showDeprecation>true</showDeprecation>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>1.2.1</version>
                <configuration>
                    <mainClass>org.test.int1.Main</mainClass>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

MyBatis-Config.xml 설정

직접 정의한 VO 객체로 결과를 받아오기 위해 자주 사용하는 타입의 별칭을 설정.

경로 : webapp > WEB-INF > spring > mybatisConfig > MyBatis-Config.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>
	<settings>
		<setting name="cacheEnabled" value="false" />
		<setting name="useGeneratedKeys" value="true" />
		<setting name="defaultExecutorType" value="REUSE" />
		<setting name="jdbcTypeForNull" value="NULL" />
	</settings>
	<!-- aliaes - short name setting bvo, pvo-->
    <typeAliases>
         <typeAlias alias="hashMap" type="java.util.HashMap" />
         <typeAlias alias="map" type="java.util.Map" />
         <typeAlias alias="bvo" type="com.ecom4.hi.board.model.BoardDTO" />
     </typeAliases>
</configuration>

typeAliases : 매칭할 mapper의 sql 태그의 parameter Type 별칭 설정
1. mapper의 sql 태그의 type(parameter type)을 alias로 설정
environments : DB 연결 설정
mappers : mapper.xml 파일 연결 (mapper.xml 경로를 resourse로 연결)

Mapper.xml 작성

SQL 문을 담고 있는 xml 문서

경로 : src/main/resources > board > mapper > BoardSpMapper.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 = "board.BoardDAO">
<select id="getArticles" resultType="bvo">
SELECT * 
FROM(SELECT ROWNUM AS rn, A.* 
FROM(SELECT bno, bref, bstep, blevel, readcount, 
subject, content, writer, regdate, ip, passwd 
FROM board 
ORDER BY bref DESC, bno, bstep) A) 
WHERE rn BETWEEN #{start} AND #{end}
</select>

<select id="getTotalCnt" resultType="int">
	SELECT COUNT(bno) FROM BOARD
</select>

<insert id="writeAction" parameterType="bvo">
	<selectKey keyProperty="nbno" resultType="int" order="BEFORE">
		SELECT NVL(MAX(bno),0)+1 AS nbno FROM board
	</selectKey>
	INSERT INTO board
		(bno, bref, bstep, blevel, readcount, 
		subject, content, writer, regdate, ip, passwd) 
	VALUES (
		#{nbno},
		<choose>
			<when test="bno>0">
				#{bno},
				#{bstep}+1,
				#{blevel}+1,
			</when>
		</choose>
		<choose>
			<when test="bno==0 and bref==0">
				#{nbno},
				0,
				0,
			</when>
		</choose>
		#{readcount}, #{subject}, #{content}, #{writer}, SYSDATE, #{ip}, #{passwd}
	)
</insert>

</mapper>

namespace : 연결된 파일
id : 해당 쿼리문을 사용하는 DAO의 메소드 이름
parameterType
1. 패키지 포함 클래스 풀네임으로 설정 (config 파일에서 별칭을 설정 typeAliases했다면 별칭 이용 가능)
2. 자바 타입의 내장된 별칭 사용 가능
${ } : Statement 방식
#{ } : PreparedStatement (위치홀더) 방식

select

<select id="getArticles" resultType="bvo">
    SELECT * 
    FROM(SELECT ROWNUM AS rn, A.* 
    FROM(SELECT bno, bref, bstep, blevel, readcount, 
    subject, content, writer, regdate, ip, passwd 
    FROM board 
    ORDER BY bref DESC, bno, bstep) A) 
    WHERE rn BETWEEN #{start} AND #{end}
</select>

<select id="getTotalCnt" resultType="int">
	SELECT COUNT(bno) FROM BOARD
</select>

parameterType : 파라미터로 넣을 값의 타입
resultType : 결과로 받을 값의 타입

insert, update, delete

<insert id="writeAction" parameterType="bvo">
	<selectKey keyProperty="nbno" resultType="int" order="BEFORE">
		SELECT NVL(MAX(bno),0)+1 AS nbno FROM board
	</selectKey>
	INSERT INTO board
		(bno, bref, bstep, blevel, readcount, 
		subject, content, writer, regdate, ip, passwd) 
	VALUES (
		#{nbno},
		<choose>
			<when test="bno>0">
				#{bno},
				#{bstep}+1,
				#{blevel}+1,
			</when>
		</choose>
		<choose>
			<when test="bno==0 and bref==0">
				#{nbno},
				0,
				0,
			</when>
		</choose>
		#{readcount}, #{subject}, #{content}, #{writer}, SYSDATE, #{ip}, #{passwd}
	)
</insert>

parameterType : 구문에 전달될 파라미터의 패키지 경로를 포함한 전체 클래스명이나 별칭
keyProperty :

동적 쿼리 태그

<insert id="writeAction" parameterType="bvo">
	<selectKey keyProperty="nbno" resultType="int" order="BEFORE">
		SELECT NVL(MAX(bno),0)+1 AS nbno FROM board
	</selectKey>
	INSERT INTO board
		(bno, bref, bstep, blevel, readcount, 
		subject, content, writer, regdate, ip, passwd) 
	VALUES (
		#{nbno},
		<choose>
			<when test="bno>0">
				#{bno},
				#{bstep}+1,
				#{blevel}+1,
			</when>
		</choose>
		<choose>
			<when test="bno==0 and bref==0">
				#{nbno},
				0,
				0,
			</when>
		</choose>
		#{readcount}, 
		#{subject},
		#{content},
		#{writer},
		SYSDATE,
		#{ip},
		#{passwd}
	)
</insert>

<if>
<choose>
<when>
<otherwise>
<foreach>

저작자표시

개 발소리 춉춉