[DB & Mybatis] DB 연동하기 (정리)

1.  pom.xml

• jar 추가

<dependency>
	<groupId>org.postgresql</groupId>
	<artifactId>postgresql</artifactId>
	<version>42.2.6</version>
</dependency>

2. jdbc.properties

• DB정보 암호화하여 적용

##### testdb #####
jdbc.testdb.driver=net.sf.log4jdbc.DriverSpy
jdbc.testdb.url=ENC(2Qu3E~)
jdbc.testdb.username=ENC(zts~)
jdbc.testdb.password=ENC(eBt~)

3. context - datasource.xml

• id, value 확인

<!-- testDb -->
<bean id="dataSourceTestDb" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="${jdbc.testDb.driver}" /> <!-- jdbc.properties 변수명 참고-->
    <property name="url" value="${jdbc.testDb.url}" />
    <property name="username" value="${jdbc.testDb.username}" />
    <property name="password" value="${jdbc.testDb.password}" />

    <property name="initialSize" value="6"/>
    <property name="maxActive" value="200"/>
    <property name="minIdle" value="6"/> 
    <property name="maxIdle" value="200"/>
    <property name="maxWait" value="5000"/>

    <property name="validationQuery" value="SELECT 1 "/>
    <!-- Evictor 가 동작하는 간격 (default = -1, 비활성화) -->
    <property name="timeBetweenEvictionRunsMillis" value="60000"/>
    <!-- Evictor 동작 시 한 번에 검사할 커넥션의 개수 (default = 3) --> 
    <property name="numTestsPerEvictionRun" value="10"/> 
    <!--  Evictor 동작 시 커넥션의 유휴 시간을 확인해 설정 값 이상일 경우 커넥션을 제거 (ms단위, default = 30분) -1 로 설정할 경우 사용하지 않음. (권장) -->
    <property name="minEvictableIdleTimeMillis" value="60000"/>

    <property name="testWhileIdle" value="true"/>
    <property name="testOnBorrow" value="false" />
    <property name="testOnReturn" value="false" />
    <property name="logAbandoned" value="true"/>

    <property name="removeAbandoned" value="true"/> 
    <property name="removeAbandonedTimeout" value="60"/>

    <!-- 
    <property name="initialSize" value="10"/>
    <property name="maxActive" value="40"/>
    <property name="minIdle" value="10"/>
    <property name="maxIdle" value="-1"/>
    <property name="maxWait" value="10000"/>

    <property name="validationQuery" value="SELECT 1 "/>
    <property name="timeBetweenEvictionRunsMillis" value="300000"/>
    <property name="testWhileIdle" value="true"/>
    <property name="testOnBorrow" value="false" />
    <property name="testOnReturn" value="false" />
    <property name="logAbandoned" value="true"/>

    <property name="removeAbandoned" value="true"/> 
    <property name="removeAbandonedTimeout" value="60"/>
         -->
</bean>

4. context - mapper.xml

• id, ref,value 확인

<?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 http://www.springframework.org/schema/beans/spring-beans-3.2.xsd">

	<!-- lob Handler -->
    <bean id="lobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler" lazy-init="true" />

    <!-- testdb-->
    <!-- define the SqlSessionFactory -->
    <bean id="sqlSessionFactoryTestDb" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSourceTestDb" />
        <property name="configLocation" value="classpath:/mapper/config/sql-mapper-config.xml" /> 
		<property name="mapperLocations" value="classpath:/mapper/TestDb/*.xml" />
    </bean>

    <!-- scan for mappers and let them be autowired -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="mapper.TestDb" />
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactoryTestDb" />
    </bean>
</beans>

5. SampleMapper.java 

• src > ... > 서비스명 > mapper > testdb > sampleMapper.java 생성
• @Repository, @Resource, 경로 확인

@Repository("sampleMapper2") //다른 빈이름과 겹치면 안됨
public class SampleMapper extends RuruAbstractMapper {

	@Override
	@Resource(name = "sqlSessionFactoryTestdb") // context-mapper.xml(sqlSessionFactoryBeanName)참고
	public void setSqlSessionFactory(SqlSessionFactory sqlSession) {
		super.setSqlSessionFactory(sqlSession);
	}

	public HashMap<String, Object> selectTest(HashMap<String, Object> param) {
		return selectOne("mapper.testdb.sampleMapper.selectTest", param);
        // sampleMapper.xml(namespace) 참고
	}
}

6. SampleMapper.xml

• resources > mapper > testdb > sampleMapper.xml 생성
• namespace 확인

<?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="mapper.testdb.sampleMapper"> //해당경로 sampleMapper.java에서 사용
	<select id="selectTest" parameterType="java.util.HashMap" resultType="java.util.HashMap">
		SELECT
			*
		FROM
			charge_info
		WHERE 
			charge_no = '189'
	</select>
	
</mapper>