Java 다중 디비 커넥션

java 다중 디비 커넥션

================================================================================================================================================

1. DB datasource bean2을 2개로 설정

<!-- mysql 접속 설정 -->

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">

<property name="driverClassName" value="#{config['db.driver']}"/>

<property name="url" value="#{config['db.url']}"/>

<property name="username" value="#{config['db.id']}"/>

<property name="password" value="#{config['db.passwd']}"/>

<property name="maxActive" value="#{config['db.maxactive']}"/>

<property name="maxIdle" value="#{config['db.maxidle']}"/>

<property name="maxWait" value="#{config['db.maxwait']}"/>

<property name="defaultAutoCommit" value="#{config['db.autocommit']}"/>

</bean>

<!--  set for mybatis Database Layer -->

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">

<property name="dataSource" ref="dataSource" />

<property name="mapperLocations" value="classpath*:mybatis/mapper/**/*.xml" />

<property name="configLocation" value="WEB-INF/mybatis-config.xml" />

</bean>

<bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">

<constructor-arg ref="sqlSessionFactory" />

</bean>

<!--  Transaction -->

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

<property name="dataSource" ref="dataSource"/>

</bean>

    

<!-- mysql2접속 설정 -->

<bean id="dataSource2" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">

<property name="driverClassName" value="#{config['db2.driver']}"/>

<property name="url" value="#{config['db2.url']}"/>

<property name="username" value="#{config['db2.id']}"/>

<property name="password" value="#{config['db2.passwd']}"/>

<property name="maxActive" value="#{config['db2.maxactive']}"/>

<property name="maxIdle" value="#{config['db2.maxidle']}"/>

<property name="maxWait" value="#{config['db2.maxwait']}"/>

<property name="defaultAutoCommit" value="#{config['db2.autocommit']}"/>

</bean>

<!--  set for mybatis Database Layer -->

<bean id="sqlSessionFactory2" class="org.mybatis.spring.SqlSessionFactoryBean">

<property name="dataSource" ref="dataSource2" />

<property name="mapperLocations" value="classpath*:mybatis/mapper2/**/*.xml" />

<property name="configLocation" value="WEB-INF/mybatis-config2.xml" />

</bean>

<bean id="sqlSessionTemplate2" class="org.mybatis.spring.SqlSessionTemplate">

<constructor-arg ref="sqlSessionFactory2" />

</bean>

<!--  Transaction -->

<bean id="transactionManager2" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

<property name="dataSource" ref="dataSource2" />

</bean>

================================================================================================================================================

2. sqlSessionTemplate, sqlSessionTemplate2 를 인젝션 하여 사용하는 클래스 롤 작성 

mybatis라 ibatis에서는 약간 다름. ibatis용으로 4.번을 참조.

egov에서는 EgovAbstractDAO 이 클래스가 아래의 클래스 역할을 하는 것으로 보임.

BaseDao.java

------------------------------------------------------------------------------------------------------------------------------------------------

package framework.db.dao;

import java.util.List;

import org.mybatis.spring.SqlSessionTemplate;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.beans.factory.annotation.Autowired;

public class BaseDao {

public SqlSessionTemplate sqlSessionTemplate;

@Autowired

public void setSqlSessionTemplate(SqlSessionTemplate sqlSessionTemplate) {

this.sqlSessionTemplate = sqlSessionTemplate;    

}

private Logger logger = LoggerFactory.getLogger(getClass());

/**

* 상세조회

* @param sqlId

* @param param

* @return

*/

public Object select(String sqlId, Object param) {

logger.debug("select {}", sqlId);

return sqlSessionTemplate.selectOne(sqlId, param);

}

public Object select(String sqlId) {

return select(sqlId, null);

}

/**

* 목록조회

* @param sqlId

* @param param

* @return

*/

public List<?> list(String sqlId, Object param) {

return sqlSessionTemplate.selectList(sqlId, param);

}

public List<?> list(String sqlId) {

return list(sqlId, null);

}

/**

* 등록

* @param sqlId

* @param param

* @return

*/

public Object insert(String sqlId, Object param) {

return sqlSessionTemplate.insert(sqlId, param);

}

public Object insert(String sqlId) {

return insert(sqlId, null);

}

/**

* 수정

* @param sqlId

* @param param

* @return

*/

public int update(String sqlId, Object param) {

return sqlSessionTemplate.update(sqlId, param);

}

public int update(String sqlId) {

return update(sqlId, null);

}

/**

* 삭제

* @param sqlId

* @param param

* @return

*/

public int delete(String sqlId, Object param) {

return sqlSessionTemplate.delete(sqlId, param);

}

public int delete(String sqlId) {

return delete(sqlId, null);

}

}

BaseDao2.java

------------------------------------------------------------------------------------------------------------------------------------------------

package framework.db.dao;

import java.util.List;

import org.mybatis.spring.SqlSessionTemplate;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.beans.factory.annotation.Autowired;

public class BaseDao2 {

public SqlSessionTemplate sqlSessionTemplate2;

    

@Autowired

public void setSqlSessionTemplate2(SqlSessionTemplate sqlSessionTemplate2) {

this.sqlSessionTemplate2 = sqlSessionTemplate2;    

}

private Logger logger = LoggerFactory.getLogger(getClass());

/**

* 상세조회

* @param sqlId

* @param param

* @return

*/

public Object select(String sqlId, Object param) {

logger.debug("select {}", sqlId);

return sqlSessionTemplate2.selectOne(sqlId, param);

}

public Object select(String sqlId) {

return select(sqlId, null);

}

메소드는 BaseDao.java와 동일

}

================================================================================================================================================

3. Dao 클래스 (원하는 디비와 연결하는 클래스(BaseDao, BaseDao2)를 상속하여 Dao 클래스를 작성.

------------------------------------------------------------------------------------------------------------------------------------------------

package admin.authpackage.dao;

import java.util.List;

import org.springframework.dao.DataAccessException;

import org.springframework.stereotype.Repository;

import admin.authpackage.domain.AuthPackage;

import admin.authpackage.domain.AuthPackageSearchParam;

import framework.db.dao.BaseDao2;

@Repository

public class AuthPackageDao extends BaseDao2 {

public Integer checkAuthPackage(AuthPackage authPackage) throws DataAccessException {

return (Integer) select("checkAuthPackage", authPackage);

}

..............................

}

================================================================================================================================================

4. ibatis 용 BaseDao 예제

------------------------------------------------------------------------------------------------------------------------------------------------

package framework.db.dao;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import org.springframework.orm.ibatis.SqlMapClientCallback;

import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;

import org.springframework.transaction.TransactionDefinition;

import org.springframework.transaction.TransactionStatus;

import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.ibatis.sqlmap.client.SqlMapExecutor;

public class BaseDao extends SqlMapClientDaoSupport {

private Logger logger = LoggerFactory.getLogger(getClass()); 

protected DataSourceTransactionManager txManager;

public TransactionStatus start() {

DefaultTransactionDefinition def = new DefaultTransactionDefinition();

   def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);

   TransactionStatus txStatus= txManager.getTransaction(def);

return txStatus;

}

public void commit(TransactionStatus txStatus) {

txManager.commit(txStatus);

}

public void end(TransactionStatus txStatus) {

txManager.rollback(txStatus);

}

/**

* 상세조회

* @param sqlId

* @param param

* @return

*/

public Object select(String sqlId, Object param) {

return this.getSqlMapClientTemplate().queryForObject(sqlId, param);

}

public Object select(String sqlId) {

return select(sqlId, null);

}

/**

* 목록조회

* @param sqlId

* @param param

* @return

*/

public List<?> list(String sqlId, Object param) {

return this.getSqlMapClientTemplate().queryForList(sqlId, param);

}

public List<?> list(String sqlId) {

return list(sqlId, null);

}

/**

* 등록

* @param sqlId

* @param param

* @return

*/

public Object insert(String sqlId, Object param) {

return this.getSqlMapClientTemplate().insert(sqlId, param);

}

public Object insert(String sqlId) {

return insert(sqlId, null);

}

/**

* 수정

* @param sqlId

* @param param

* @return

*/

public int update(String sqlId, Object param) {

return this.getSqlMapClientTemplate().update(sqlId, param);

}

public int update(String sqlId) {

return update(sqlId, null);

}

/**

* 삭제

* @param sqlId

* @param param

* @return

*/

public int delete(String sqlId, Object param) {

return this.getSqlMapClientTemplate().delete(sqlId, param);

}

public int delete(String sqlId) {

return delete(sqlId, null);

}

/**

* 다중 INSERT

* @param sqlId

* @param paramList

* @return

*/

public List<?> batchCreate(final String sqlId, final List<?> paramList) {

List<?> resultList = (List<?>) getSqlMapClientTemplate().execute(new SqlMapClientCallback<Object>() {

@Override

public Object doInSqlMapClient(SqlMapExecutor executor) throws SQLException {

List<Object> resultList = new ArrayList<Object>();

int processedCount = 0;

executor.startBatch();

for (Object param : paramList) {

resultList.add(executor.insert(sqlId, param));

processedCount++;

}

executor.executeBatch();

if (logger.isDebugEnabled()) {

logger.debug("Created count = " + processedCount);

}

return resultList;

}

});

return resultList;

}

/**

* 다중 UPDATE

* @param sqlId

* @param paramList

* @return

*/

public int batchUpdate(final String sqlId, final List<?> paramList) {

Integer batchCount = (Integer) getSqlMapClientTemplate().execute(new SqlMapClientCallback<Object>() {

@Override

public Object doInSqlMapClient(SqlMapExecutor executor) throws SQLException {

int processedCount = 0;

executor.startBatch();

for (Object param : paramList) {

executor.update(sqlId, param);

processedCount++;

}

executor.executeBatch();

if (logger.isDebugEnabled()) {

logger.debug("Updated count = {}", processedCount);

}

return processedCount;

}

});

return batchCount.intValue();

}

/**

* 다중 DELETE

* @param sqlId

* @param paramList

* @return

*/

public int batchDelete(final String sqlId, final List<?> paramList) {

Integer batchCount = (Integer) getSqlMapClientTemplate().execute(new SqlMapClientCallback<Object>() {

@Override

public Object doInSqlMapClient(SqlMapExecutor executor) throws SQLException {

int processedCount = 0;

executor.startBatch();

for (Object param : paramList) {

executor.delete(sqlId, param);

processedCount++;

}

executor.executeBatch();

if (logger.isDebugEnabled()) {

logger.debug("Deleted count = " + processedCount);

}

return processedCount;

}

});

return batchCount.intValue();

}

}