[JAVA] I want to issue a connection when a database is created using Spring and MyBatis

What you want to do

--Each server has MySQL and you can access each from the app

• static_db（10.0.0.101:3306） --Pool connections when launching the app
• dynamic_db（10.0.0.111:3306 ～ 10.0.0.115:3306） --dynamic_db has not been created yet when the app is launched -** Decide which database to connect to when created **, and want to issue a connection

Realization method

--static_db normally uses XML configuration file (spring-servlet.xml) --dynamic_db dynamically generates SqlSessionFactory without ** using XML config file **

• How do I build SqlSessionFactory without XML?

Implementation

Various versions

• Java 1.8
• Spring 4.2.4.RELEASE
• MyBatis 3.2.2
• MyBatis-Spring 1.2.1
• MySQL 5.6.39

Setting static_db connection information

spring-servlet.xml

  <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://10.0.0.101:3306/static_db" />
    <property name="username" value="root" />
    <property name="password" value="password" />
  </bean>

  <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="sample.da.mapper" />
    <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
  </bean>

  <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean" name="sqlSessionFactory">
    <property name="dataSource" ref="dataSource"></property>
    <property name="mapperLocations" value="classpath*:sample/da/mapper/*.xml"/>
  </bean>

  <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource"/>
  </bean>
  <tx:annotation-driven transaction-manager="transactionManager"/>

Access to static_db

Use DI Mapper instance

@Service
public class StaticDb01AccessService {

	@Autowired
	private UsersMapper usersMapper;
	
	public void insert() {
		Users record = new Users();
		record.setNum("001");
		record.setName("Ichiro Tanaka");
		record.setCreated(new Date());
		record.setUpdated(new Date());
		usersMapper.insert(record);
	}
}

Access to dynamic_db

Manually generate SqlSessionFactory and Mapper

@Service
public class DynamicDbAccessService {

	//For example, 10.0.0.115 dynamic_Called when db was created
	public void insert(String endpoint) throws Exception {
		DataSource dataSource = getDataSource(endpoint); // endpoint「10.0.0.115」
		
		TransactionFactory trxFactory = new JdbcTransactionFactory();
		
		Environment env = new Environment("dynamic_db", trxFactory, dataSource);
		
		Configuration config = new Configuration(env);
		TypeAliasRegistry aliases = config.getTypeAliasRegistry();
		aliases.registerAlias("user", Users.class);
		
		config.addMapper(UsersMapper.class);
		
		SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(config);
		SqlSession session = factory.openSession();
		
		try {
			UsersMapper usersMapper = session.getMapper(UsersMapper.class);
			
			Users record = new Users();
			record.setNum("001");
			record.setName("Ichiro Tanaka");
			record.setCreated(new Date());
			record.setUpdated(new Date());
			
			usersMapper.insert(record);
			
			session.commit();
		} catch (Exception e) {
			session.rollback();
		} finally {
			session.close();
		}
	}

	private DataSource getDataSource(String endpoint) {
		BasicDataSource dataSource = new BasicDataSource();
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUrl(String.format("jdbc:mysql://%s/dynamic_db", endpoint));
		dataSource.setUsername("root");
		dataSource.setPassword("password");
		return dataSource;
	}
}