Spring boot + mybatis + multiple data sources
Necessary dependencies
<!--Connect mysql data base--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!--Link pool configuration, connection driver --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.13</version> </dependency> <!--Introduce mybatis--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.0</version> </dependency>
application.properties configures multiple data source connections and connection pools
#Configure multiple data sources spring.datasource.primary.jdbc-url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8 &serverTimezone=Asia/Shanghai spring.datasource.primary.username=root spring.datasource.primary.password=root spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver ########################### cbh data base ########################## #Configure multiple data sources spring.datasource.secondary.jdbc-url=jdbc:mysql://127.0.0.1:3306/cbh?useUnicode=true&characterEncoding=utf8 &serverTimezone=Asia/Shanghai spring.datasource.secondary.username=root spring.datasource.secondary.password=root spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver #Do not use the default configuration source spring.datasource.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.initialSize=2 spring.datasource.minIdle=1 spring.datasource.maxActive=20 spring.datasource.maxWait=60000
Configuring the Core Configuration of Multiple Data Sources
Configuration of Connecting test Master Database
@ Primary // is used to mark the master data source, which is not added to any injected file except the master data source.
@ MapperScan(basePackages = com.example.demo.dao.primary), sqlSession Template Ref = Primary Session Template) where basePackages represent the scope of the data source when connecting mybatis to a database
import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.SqlSessionTemplate; import org.mybatis.spring.annotation.MapperScan; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.jdbc.DataSourceBuilder; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import javax.sql.DataSource; @Configuration @MapperScan(basePackages = "com.example.demo.dao.primary", sqlSessionTemplateRef = "PrimarySessionTemplate") public class PrimaryDataSourceConfig { @Bean(name = "PrimaryDataSource") //As a bean object and named @ConfigurationProperties(prefix = "spring.datasource.primary") //In the configuration file, the prefix of the data source @Primary //For marking up master data sources, this annotation is not added to any injected files other than master data sources public DataSource PrimaryDataSource() { return DataSourceBuilder.create().build(); } @Bean(name = "PrimarySessionFactory") @Primary public SqlSessionFactory PrimarySessionFactory(@Qualifier("PrimaryDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); return bean.getObject(); } @Bean(name = "PrimaryTransactionManager") @Primary public DataSourceTransactionManager PrimaryTransactionManager(@Qualifier("PrimaryDataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } @Bean(name = "PrimarySessionTemplate") @Primary public SqlSessionTemplate PrimarySessionTemplate(@Qualifier("PrimarySessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { return new SqlSessionTemplate(sqlSessionFactory); } }
Configuration of connecting cbh database (from database)
@Configuration @MapperScan(basePackages = "com.example.demo.dao.secondary", sqlSessionTemplateRef = "SecondarySessionTemplate") public class SecondaryDataSourceConfig { @Bean(name = "SecondaryDataSource") @ConfigurationProperties(prefix = "spring.datasource.secondary") // @Primary public DataSource SecondaryDataSource() { return DataSourceBuilder.create().build(); } @Bean(name = "SecondarySessionFactory") // @Primary public SqlSessionFactory SecondarySessionFactory(@Qualifier("SecondaryDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); return bean.getObject(); } @Bean(name = "SecondaryTransactionManager") // @Primary public DataSourceTransactionManager SecondaryTransactionManager(@Qualifier("SecondaryDataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } @Bean(name = "SecondarySessionTemplate") // @Primary public SqlSessionTemplate SecondarySessionTemplate(@Qualifier("SecondarySessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { return new SqlSessionTemplate(sqlSessionFactory); } }
Project structure and project code
Operation of mybatis connecting database
Operation of Connecting Main Database
I put it in the com.example.demo.dao.primary directory and connected to the test database.
@Repository public interface TuserDao { //View all Tuser information @Select("select * from t_user") List<Tuser> getListOfTuser(); }
Operation of Connection Number Database
I put it in the com.example.demo.dao.secondary directory and connected to the cbh database.
@Repository public interface CBHDao { @Select("select id,name,phone,uuid,login_pwd from t_user") @Results({ @Result(column = "login_pwd", property = "pwd") }) List<Tuser> getListOfTuserByCBH(); }
Control layer
Operating test database
@Autowired private TuserService tuserServiceimpl; @GetMapping("/test/getListOfTuser") public Object getListOfTuser(){ List<Tuser> list=tuserServiceimpl.getListOfTuser(); return list; }
Operating cbh database
@Autowired private CBHService cbhServiceimpl; @GetMapping("/cbh/getListOfTuserByCBH") public Object getListOfTuserByCBH(){ List<Tuser> list=cbhServiceimpl.getListOfTuserByCBH(); return "list Number of records"+list.size(); }
Results from multiple data sources
Connecting to test database: http://localhost:8080/test/getListOfTuser
Operation of connecting CBH database: http://localhost:8080/cbh/getListOfTuserByCBH