[Spring] 다중 데이터베이스 사용(Multi DB)

[Spring] 다중 데이터베이스 사용(Multi DB)

# database.properties에 데이터베이스소스 두개 설정

# My SQL 연결 설정

db1.datasource.className=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
db1.datasource.url=jdbc:log4jdbc:mysql://127.0.0.1:3306/one
db1.datasource.username=root
db1.datasource.password=1234
db1.datasource.dbType=MySQL
#-----------------------------------------------------------------------
# My SQL2 연결 설정

db2.datasource.className=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
db2.datasource.url=jdbc:log4jdbc:mysql://127.0.0.1:3306/two
db2.datasource.username=root
db2.datasource.password=1234
db2.datasource.dbType=MySQL
#-----------------------------------------------------------------------

 

 

// one 데이터베이스 config 생성
// db1.datasource의 데이터베이스를 설정

@Configuration
@MapperScan(basePackages="com.test.mapper")
public class DatabaseConfiguration1 {
    
	/**
     * 환경 설정 프로퍼티 불러오기
     */
    @Autowired
    private Environment properties;
    
    /**
     * 벤더 ID 설정
     */
    @Primary
    private VendorDatabaseIdProvider databaseIdProvider() {
        VendorDatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
        Properties properties = new Properties();
        properties.put("MySQL", "MySQL");
        databaseIdProvider.setProperties(properties);
        return databaseIdProvider;
    }
    
    /**
     * 연결할 데이터베이스의 데이터 소스 설정
     */
    @Bean
    @Primary
    public DataSource dataSourceDB1() {
        BasicDataSource bds = new BasicDataSource();        
        bds.setDriverClassName(properties.getProperty("db1.datasource.className"));
        bds.setUrl(properties.getProperty("db1.datasource.url"));
        bds.setUsername(properties.getProperty("db1.datasource.username")); 
        bds.setPassword(properties.getProperty("db1.datasource.password"));
        return bds;
    }
    
    /**
     * 연결할 데이터 소스의 트랜잭션 설정
     */
    @Bean
    @Primary
    public PlatformTransactionManager TransactionManagerDB1() {
        return new DataSourceTransactionManager(dataSourceDB1());
    }
    
    /**
     * 연결할 데이터 소스의 Mybatis 연결 및 SQL Map 설정
     */
    @Bean
    @Primary
    public SqlSessionFactory sqlSessionFactoryDB1() throws Exception {
    	String dbType = properties.getProperty("db1.datasource.dbType");
        SqlSessionFactoryBean sfb = new SqlSessionFactoryBean();
        sfb.setDatabaseIdProvider(databaseIdProvider());
        sfb.setDataSource(dataSourceDB1());
        sfb.setTypeAliasesPackage("com.test");
        sfb.setConfigLocation(new ClassPathResource("/mybatis-config.xml"));
        sfb.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/sqlmap/mappers_db1/**/*_" + dbType +".xml"));
        return sfb.getObject();
    }
}

 

 

// two 데이터베이스 config 생성
// db2.datasource의 데이터베이스를 설정

@Configuration
@MapperScan(basePackages="com.test.mapper")
public class DatabaseConfiguration2 {
    
	/**
     * 환경 설정 프로퍼티 불러오기
     */
    @Autowired
    private Environment properties;
    
    /**
     * 벤더 ID 설정
     */
    private VendorDatabaseIdProvider databaseIdProvider() {
        VendorDatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
        Properties properties = new Properties();
        properties.put("MySQL", "MySQL");
        databaseIdProvider.setProperties(properties);
        return databaseIdProvider;
    }
    
    /**
     * 연결할 데이터베이스의 데이터 소스 설정
     */
    @Bean
    public DataSource dataSourceDB2() {
        BasicDataSource bds = new BasicDataSource();        
        bds.setDriverClassName(properties.getProperty("db2.datasource.className"));
        bds.setUrl(properties.getProperty("db2.datasource.url"));
        bds.setUsername(properties.getProperty("db2.datasource.username")); 
        bds.setPassword(properties.getProperty("db2.datasource.password"));
        return bds;
    }
    
    /**
     * 연결할 데이터 소스의 트랜잭션 설정
     */
    @Bean
    public PlatformTransactionManager TransactionManagerDB2() {
        return new DataSourceTransactionManager(dataSourceDB2());
    }
    
    /**
     * 연결할 데이터 소스의 Mybatis 연결 및 SQL Map 설정
     */
    @Bean
    public SqlSessionFactory sqlSessionFactoryDB2() throws Exception {
    	String dbType = properties.getProperty("db2.datasource.dbType");
        SqlSessionFactoryBean sfb = new SqlSessionFactoryBean();
        sfb.setDatabaseIdProvider(databaseIdProvider());
        sfb.setDataSource(dataSourceDB2());
        sfb.setTypeAliasesPackage("com.test");
        sfb.setConfigLocation(new ClassPathResource("/mybatis-config.xml"));
        sfb.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/sqlmap/mappers_db2/**/*_" + dbType +".xml"));
        return sfb.getObject();
    }
}

 

 

// MybatisWebDAO 클래스에 작성
// DatabaseConfiguration1의 sqlSessionFactoryDB1 메소드를 불러옴

@Component
@Repository("mybatisWebDAO")
public class MybatisWebDAO extends EgovAbstractMapper {

    @Autowired
    @Qualifier("sqlSessionFactoryDB1")
    public void setSqlSessionFactory(SqlSessionFactory sqlSessionFactory) {
        super.setSqlSessionFactory(sqlSessionFactory);
    }

    public void setCommit() throws SQLException {
        getSqlSession().commit();
    }

    public void setRollback() throws SQLException {
        getSqlSession().rollback();
    }

 

 

// MybatisWebDAO2 클래스 작성
// DatabaseConfiguration2의 sqlSessionFactoryDB2 메소드를 불러옴

@Component
@Repository("mybatisWebDAO2")
public class MybatisWebDAO2 extends EgovAbstractMapper {

    @Autowired
    @Qualifier("sqlSessionFactoryDB2")
    public void setSqlSessionFactory(SqlSessionFactory sqlSessionFactory) {
        super.setSqlSessionFactory(sqlSessionFactory);
    }

    public void setCommit() throws SQLException {
        getSqlSession().commit();
    }

    public void setRollback() throws SQLException {
        getSqlSession().rollback();
    }

 

 

// Service에서 mapper 두개와 MybatisWebDAO 두개 설정

@Service
@Transactional
public class testService extends GenericService {

	/**
	 * mapper 네임 스페이스
	 */
	private final String mapper = "test.";
	private final String mapper2 = "test2.";

	/**
	 * 마이 바티스 다오
	 */
	@Autowired
	@Resource(name = "mybatisWebDAO")
	private MybatisWebDAO mybatisWebDAO;
	
	@Autowired
	@Resource(name = "mybatisWebDAO2")
	private MybatisWebDAO2 mybatisWebDAO2;

 

 

// db1의 데이터베이스에 insert
public Map<String, Object> insertData(Map<String, Object> param) throws SQLException {
       Map<String, Object> result = new HashMap<String, Object>();

       int insertCnt = mybatisWebDAO.insert(mapper + "insertData", param);
       result.put("inserCnt", insertCnt);
       return result;
}

// db2의 데이터베이스에 insert
public Map<String, Object> insertData2(Map<String, Object> param) throws SQLException {
       Map<String, Object> result = new HashMap<String, Object>();

       int insertCnt = mybatisWebDAO2.insert(mapper2 + "insertData", param);
       result.put("inserCnt", insertCnt);
       return result;
}