[B -2-6] 스프링과의 연동 처리

마이바티스(MyBatis) - 스프링(Spring)

매퍼(Mapper)를 XML과 인터페이스 + 어노테이션의 형태로 작성한다.

 

---

매퍼(Mapper) 인터페이스 작성

 

src/main/java

com.spms.mapper

TimeMapper.java 인터페이스

 

package com.spms.mapper;

public interface TimeMapper {
	
	public String getTime();
}

 

매퍼(Mapper) 작성완료 시 마이바티스가 동작할 때 매퍼를 인식할 수 있도록 RootConfig 클래스에 알려야한다.

 

---

루트 환경설정 수정

 

src/main/java

com.spms.config

RootConfig.java

@MapperScan

dataSource()

package com.spms.config;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

@Configuration
@MapperScan(basePackages = {"com.spms.mapper"})
public class RootConfig {
	
	@Bean
	public DataSource dataSource() {
		HikariConfig hikariConfig = new HikariConfig();
		// hikariConfig.setDriverClassName("oracle.jdbc.driver.OracleDriver");
		// hikariConfig.setJdbcUrl("jdbc:oracle:thin:@호스트 주소:포트번호:SID");
		
		hikariConfig.setDriverClassName("net.sf.log4jdbc.sql.jdbcapi.DriverSpy");
		hikariConfig.setJdbcUrl("jdbc:log4jdbc:oracle:thin:@호스트 주소:포트번호:SID");
		hikariConfig.setUsername("아이디");
		hikariConfig.setPassword("패스워드");
		HikariDataSource dataSource = new HikariDataSource(hikariConfig);
		return dataSource;
	}
	
	@Bean
	public SqlSessionFactory sqlSessionFactory() throws Exception {
		SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
		sqlSessionFactory.setDataSource(dataSource());
		return sqlSessionFactory.getObject();
	}
	
}

매퍼(Mapper) 작성완료 시 마이바티스가 동작할 때 매퍼를 인식할 수 있도록 RootConfig 클래스에 알려야한다.

@MapperScan 으로 매퍼 인터페이스의 패키지를 지정해야한다.

 

dataSource()

마이바티스의 내부적인 SQL문 처리문의 파라미터는 ? 로 치환되어서 처리된다.

이러한 경우 SQL문을 확인하기가 까다롭다.

이 문제를 해결하려면 log4jdbc-log4j2 라이브러리를 사용하면 SQL 로그를 확인할 수 있게 된다.

기존에 사용했던 jdbc는 코멘트 처리한다.

 

드라이버 클래스 수정 : 직접 해당 라이브러리 목록에 들어가서 DriverSpy 의 풀 경로를 지정한다.

log4jdbc

JDBC Url 수정 : jdbc:log4jdbc:oracle:thin

 

---

매퍼 XML 작성

 

src/main/resources

com 패키지

spms 폴더

mapper 폴더

TimeMapper.xml

(*) com 패키지, spms 폴더, mapper 폴더는 한개씩 생성해야 한다.

<?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="com.spms.mapper.TimeMapper">

	<select id="getTime" resultType="String">
		SELECT sysdate
		FROM dual
	</select>

</mapper>

XML 매퍼에서 신경써야 하는 부분은 <mapper namespace> 속성값이다.

마이바티스는 매퍼 인터페이스와 XML을 인터페이스의 이름과 namespace 속성값을 가지고 판단한다.

---

log4jdbc-log4j2 설정파일 작성

 

src/main/resources

log4jdbc.log4j2.properties

 

log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator

log4jdbc.spylogdelegator.name 의 클래스를 해당 라이브러리 풀 경로를 지정한다.

Slf4jSpyLogDelegator

 

---

테스트 코드 작성

 

src/test/java

com.spms.persistence

TimeMapperTests.java

 

package com.spms.persistence;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.spms.mapper.TimeMapper;

import lombok.Setter;
import lombok.extern.log4j.Log4j;

@Log4j
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {com.spms.config.RootConfig.class})
public class TimeMapperTests {
	
	@Setter(onMethod_ = @Autowired)
	private TimeMapper timeMapper;
	
	@Test
	public void testGetTime() {
		// TODO Auto-generated method stub
		log.info("getTime");
		log.info(timeMapper.getTime());
	}
}

 

---

테스트 코드 실행 결과

 

INFO : org.springframework.test.context.support.DefaultTestContextBootstrapper - Loaded default TestExecutionListener class names from location [META-INF/spring.factories]: [org.springframework.test.context.web.ServletTestExecutionListener, org.springframework.test.context.support.DirtiesContextBeforeModesTestExecutionListener, org.springframework.test.context.support.DependencyInjectionTestExecutionListener, org.springframework.test.context.support.DirtiesContextTestExecutionListener, org.springframework.test.context.transaction.TransactionalTestExecutionListener, org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener]
INFO : org.springframework.test.context.support.DefaultTestContextBootstrapper - Using TestExecutionListeners: [org.springframework.test.context.web.ServletTestExecutionListener@7d9d1a19, org.springframework.test.context.support.DirtiesContextBeforeModesTestExecutionListener@39c0f4a, org.springframework.test.context.support.DependencyInjectionTestExecutionListener@1794d431, org.springframework.test.context.support.DirtiesContextTestExecutionListener@42e26948, org.springframework.test.context.transaction.TransactionalTestExecutionListener@57baeedf, org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener@343f4d3d]
INFO : org.springframework.context.support.GenericApplicationContext - Refreshing org.springframework.context.support.GenericApplicationContext@5f9d02cb: startup date [Sat Sep 28 23:37:45 JST 2019]; root of context hierarchy
INFO : org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor - JSR-330 'javax.inject.Inject' annotation found and supported for autowiring
INFO : com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
INFO : jdbc.connection - 1. Connection opened
INFO : jdbc.audit - 1. Connection.new Connection returned 
INFO : com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Driver does not support get/set network timeout for connections. (net.sf.log4jdbc.sql.jdbcapi.ConnectionSpy.getNetworkTimeout()I)
INFO : jdbc.audit - 1. Connection.setReadOnly(false) returned 
INFO : jdbc.audit - 1. Connection.setAutoCommit(true) returned 
INFO : jdbc.audit - 1. Connection.isValid(1) returned true
INFO : jdbc.audit - 1. Connection.getTransactionIsolation() returned 2
INFO : com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.
INFO : jdbc.connection - 2. Connection opened
INFO : jdbc.audit - 2. Connection.new Connection returned 
INFO : jdbc.audit - 2. Connection.setReadOnly(false) returned 
INFO : jdbc.audit - 2. Connection.setAutoCommit(true) returned 
INFO : com.spms.persistence.TimeMapperTests - getTime2
INFO : jdbc.audit - 1. Connection.getAutoCommit() returned true
INFO : jdbc.connection - 3. Connection opened
INFO : jdbc.audit - 3. Connection.new Connection returned 
INFO : jdbc.audit - 3. Connection.setReadOnly(false) returned 
INFO : jdbc.audit - 3. Connection.setAutoCommit(true) returned 
INFO : jdbc.connection - 4. Connection opened
INFO : jdbc.audit - 4. Connection.new Connection returned 
INFO : jdbc.audit - 4. Connection.setReadOnly(false) returned 
INFO : jdbc.audit - 4. Connection.setAutoCommit(true) returned 
INFO : jdbc.audit - 1. PreparedStatement.new PreparedStatement returned 
INFO : jdbc.audit - 1. Connection.prepareStatement(SELECT sysdate
		FROM dual) returned net.sf.log4jdbc.sql.jdbcapi.PreparedStatementSpy@3401a114
INFO : jdbc.sqlonly - SELECT sysdate FROM dual 

INFO : jdbc.sqltiming - SELECT sysdate FROM dual 
 {executed in 75 msec}
INFO : jdbc.audit - 1. PreparedStatement.execute() returned true
INFO : jdbc.resultset - 1. ResultSet.new ResultSet returned 
INFO : jdbc.audit - 1. PreparedStatement.getResultSet() returned net.sf.log4jdbc.sql.jdbcapi.ResultSetSpy@4cc6fa2a
INFO : jdbc.connection - 5. Connection opened
INFO : jdbc.audit - 5. Connection.new Connection returned 
INFO : jdbc.audit - 5. Connection.setReadOnly(false) returned 
INFO : jdbc.audit - 5. Connection.setAutoCommit(true) returned 
INFO : jdbc.resultset - 1. ResultSet.getMetaData() returned oracle.jdbc.driver.OracleResultSetMetaData@19fb8826
INFO : jdbc.resultset - 1. ResultSet.getType() returned 1003
INFO : jdbc.resultset - 1. ResultSet.next() returned true
INFO : jdbc.resultset - 1. ResultSet.getString(SYSDATE) returned 2019-09-28 23:37:46
INFO : jdbc.resultset - 1. ResultSet.wasNull() returned false
INFO : jdbc.resultsettable - 
|--------------------|
|sysdate             |
|--------------------|
|2019-09-28 23:37:46 |
|--------------------|

INFO : jdbc.resultset - 1. ResultSet.next() returned false
INFO : jdbc.resultset - 1. ResultSet.close() returned void
INFO : jdbc.audit - 1. Connection.getMetaData() returned oracle.jdbc.driver.OracleDatabaseMetaData@2c88b9fc
INFO : jdbc.audit - 1. PreparedStatement.isClosed() returned false
INFO : jdbc.audit - 1. PreparedStatement.close() returned 
INFO : jdbc.audit - 1. Connection.clearWarnings() returned 
INFO : com.spms.persistence.TimeMapperTests - 2019-09-28 23:37:46
INFO : org.springframework.context.support.GenericApplicationContext - Closing org.springframework.context.support.GenericApplicationContext@5f9d02cb: startup date [Sat Sep 28 23:37:45 JST 2019]; root of context hierarchy
INFO : com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Shutdown initiated...
INFO : jdbc.connection - 1. Connection closed
INFO : jdbc.audit - 1. Connection.close() returned 
INFO : jdbc.connection - 2. Connection closed
INFO : jdbc.audit - 2. Connection.close() returned 
INFO : jdbc.connection - 3. Connection closed
INFO : jdbc.audit - 3. Connection.close() returned 
INFO : jdbc.connection - 4. Connection closed
INFO : jdbc.audit - 4. Connection.close() returned 
INFO : jdbc.connection - 6. Connection opened
INFO : jdbc.audit - 6. Connection.new Connection returned 
INFO : jdbc.audit - 6. Connection.setReadOnly(false) returned 
INFO : jdbc.audit - 6. Connection.setAutoCommit(true) returned 
INFO : jdbc.connection - 5. Connection closed
INFO : jdbc.audit - 5. Connection.close() returned 
INFO : jdbc.connection - 6. Connection closed
INFO : jdbc.audit - 6. Connection.close() returned 
INFO : com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Shutdown completed.

 

로그에서 SQL 문을 로그에서 확인할 수 있으며, SQL 결과값을 확인할 수 있다.

---