Spring Boot 应用系列 3 -- Spring Boot 2 整合MyBatis和Druid，多数据源
本文演示多数据源（MySQL+SQL Server）的配置，并且我引入了分页插件pagehelper。
1. 项目结构
（1）db.properties存储数据源和连接池配置。
（2）两个数据源的mapper配置分别在src/main/resources下面的datasource1和datasource2里面。
2. pom.xml
需要在dependencies节点中添加：
<!-- MySQL --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.12</version> </dependency> <!-- MySQL end --> <!-- SQL Server --> <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>mssql-jdbc</artifactId> </dependency> <!-- SQL Server end --> <!-- Connection Pool --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.10</version> </dependency> <!-- Connection Pool end --> <!-- MyBatis --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency> <!-- MyBatis end --> <!-- Page Helper --> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>5.1.5</version> </dependency> <!-- Page Helper end -->
3. properties配置文件
我们把主程序配置文件application.properties和数据库配置文件分开，这样可使application.properties不至于臃肿。
(1) application.properties
server.port=9008 spring.application.name=devutility-test-database-mybatis-springboot #Configuration for druid spring.datasource.druid.stat-view-servlet.enabled=true spring.datasource.druid.stat-view-servlet.url-pattern=/druid/* spring.datasource.druid.stat-view-servlet.login-username=admin spring.datasource.druid.stat-view-servlet.login-password=admin
Druid ui的配置也放在里面，可通过http://localhost:9008/druid来访问。
(2) db.properties
1 #Data source 1 2 db1.sqlserver.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver 3 db1.sqlserver.url=${DB1_URL:jdbc:sqlserver://127.0.0.1:1433;DatabaseName=MyTestDb1} 4 db1.sqlserver.username=${DB1_UID:tester} 5 db1.sqlserver.password=${DB1_PWD:tester} 6 db1.sqlserver.initial-size=1 7 db1.sqlserver.min-idle=1 8 db1.sqlserver.max-active=20 9 db1.sqlserver.max-wait=60000 10 db1.sqlserver.time-between-eviction-runs-millis=60000 11 db1.sqlserver.min-evictable-idle-time-millis=300000 12 db1.sqlserver.validation-query=select 1 13 db1.sqlserver.test-on-borrow=true 14 db1.sqlserver.test-While-Idle=true 15 db1.sqlserver.test-on-return=false 16 db1.sqlserver.pool-prepared-statements=false 17 db1.sqlserver.max-pool-prepared-statement-per-connection-size=20 18 19 db1.sqlserver.mybatis.config-location=classpath:datasource1/mybatis-config.xml 20 21 db1.sqlserver.filter.stat.enabled=true 22 db1.sqlserver.filter.stat.db-type=mssql 23 db1.sqlserver.filter.stat.log-slow-sql=true 24 db1.sqlserver.filter.stat.slow-sql-millis=200 25 26 #Data source 2 27 db2.mysql.driver-class-name=com.mysql.cj.jdbc.Driver 28 db2.mysql.url=${DB2_URL:jdbc:mysql://127.0.0.1:3306/Test}?useUnicode=true&useSSL=false 29 db2.mysql.username=${DB2_UID:tester} 30 db2.mysql.password=${DB2_PWD:tester} 31 db2.mysql.initial-size=1 32 db2.mysql.min-idle=1 33 db2.mysql.max-active=20 34 db2.mysql.max-wait=60000 35 db2.mysql.time-between-eviction-runs-millis=60000 36 db2.mysql.min-evictable-idle-time-millis=300000 37 db2.mysql.validation-query=select 1 38 db2.mysql.test-on-borrow=true 39 db2.mysql.test-While-Idle=true 40 db2.mysql.test-on-return=false 41 db2.mysql.pool-prepared-statements=false 42 db2.mysql.max-pool-prepared-statement-per-connection-size=20 43 44 db2.mysql.mybatis.config-location=classpath:datasource2/mybatis-config.xml 45 46 db2.mysql.filter.stat.enabled=true 47 db2.mysql.filter.stat.db-type=mysql 48 db2.mysql.filter.stat.log-slow-sql=true 49 db2.mysql.filter.stat.slow-sql-millis=1000
注意19和44行，我们为两个数据源分别使用mybatis-config.xml来管理它们所属的mapper xml和其他一些配置。
4. JavaConfig
(1) DataSource1
package devutility.test.database.mybatis.springboot.multi.source.config; import java.util.Properties; import javax.sql.DataSource; import org.apache.ibatis.plugin.Interceptor; 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.boot.context.properties.ConfigurationProperties; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.PropertySource; import org.springframework.core.io.Resource; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder; import com.github.pagehelper.PageInterceptor; @Configuration @PropertySource("classpath:db.properties") @MapperScan(basePackages = { "devutility.test.database.mybatis.springboot.multi.source.ds1" }, sqlSessionFactoryRef = "sqlSessionFactory1", sqlSessionTemplateRef = "sqlSessionTemplate1") public class DataSource1Configuration { @Bean @ConfigurationProperties("db1.sqlserver") public DataSource dataSource1() { return DruidDataSourceBuilder.create().build(); } @Bean @ConfigurationProperties("db1.sqlserver") public Properties ormProperties1() { return new Properties(); } @Bean public Interceptor pageHelperInterceptor1() { Properties properties = new Properties(); properties.setProperty("helperDialect", "sqlserver2012"); Interceptor interceptor = new PageInterceptor(); interceptor.setProperties(properties); return interceptor; } @Bean public SqlSessionFactory sqlSessionFactory1(DataSource dataSource1, Properties ormProperties1) throws Exception { String configLocation = ormProperties1.getProperty("mybatis.config-location"); Resource[] resources = new PathMatchingResourcePatternResolver().getResources(configLocation); SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(dataSource1); sqlSessionFactoryBean.setConfigLocation(resources[0]); sqlSessionFactoryBean.setPlugins(new Interceptor[] { pageHelperInterceptor1() }); return sqlSessionFactoryBean.getObject(); } @Bean public SqlSessionTemplate sqlSessionTemplate1(SqlSessionFactory sqlSessionFactory1) throws Exception { return new SqlSessionTemplate(sqlSessionFactory1); } @Bean public DataSourceTransactionManager dataSourceTransactionManager1(DataSource dataSource1) { return new DataSourceTransactionManager(dataSource1); } }
(2) DataSource2
package devutility.test.database.mybatis.springboot.multi.source.config; import java.util.Properties; import javax.sql.DataSource; import org.apache.ibatis.plugin.Interceptor; 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.boot.context.properties.ConfigurationProperties; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.PropertySource; import org.springframework.core.io.Resource; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder; import com.github.pagehelper.PageInterceptor; @Configuration @PropertySource("classpath:db.properties") @MapperScan(basePackages = { "devutility.test.database.mybatis.springboot.multi.source.ds2" }, sqlSessionFactoryRef = "sqlSessionFactory2", sqlSessionTemplateRef = "sqlSessionTemplate2") public class DataSource2Configuration { @Bean @ConfigurationProperties("db2.mysql") public DataSource dataSource2() { return DruidDataSourceBuilder.create().build(); } @Bean @ConfigurationProperties("db2.mysql") public Properties ormProperties2() { return new Properties(); } @Bean public Interceptor pageHelperInterceptor2() { Properties properties = new Properties(); properties.setProperty("helperDialect", "mysql"); Interceptor interceptor = new PageInterceptor(); interceptor.setProperties(properties); return interceptor; } @Bean public SqlSessionFactory sqlSessionFactory2(DataSource dataSource2, Properties ormProperties2) throws Exception { String configLocation = ormProperties2.getProperty("mybatis.config-location"); Resource[] resources = new PathMatchingResourcePatternResolver().getResources(configLocation); SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(dataSource2); sqlSessionFactoryBean.setConfigLocation(resources[0]); sqlSessionFactoryBean.setPlugins(new Interceptor[] { pageHelperInterceptor2() }); return sqlSessionFactoryBean.getObject(); } @Bean public SqlSessionTemplate sqlSessionTemplate2(SqlSessionFactory sqlSessionFactory2) throws Exception { return new SqlSessionTemplate(sqlSessionFactory2); } @Bean public DataSourceTransactionManager dataSourceTransactionManager2(DataSource dataSource2) { return new DataSourceTransactionManager(dataSource2); } }
注意，两个配置文件均有一个Properties类型的bean，该bean存储着db.properties中的所有配置，但是真正用到的配置只有一个"mybatis.config-location"，它的值就是mybatis-config.xml的存储地址。当然，你也可以删掉ormProperties 这个bean，删掉db.properties的"mybatis.config-location"属性，然后以hardcode的方式在Configuration中配ConfigLocation.
5. MyBatis相关配置
MyBatis的配置主要包括mybatis-config.xml，mapper的xml文件，mapper的接口文件，和实体类，比单数据源多了一个mybatis-config.xml文件的配置。
(1) mybatis-config.xml
在本文中，该文件用来配置数据源相关的xml映射文件和实体类的包，其他配置项可参考MyBatis官网。
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <settings> <setting name="logImpl" value="STDOUT_LOGGING" /> </settings> <typeAliases> <package name="devutility.test.database.mybatis.springboot.multi.source.ds2.entities" /> </typeAliases> <mappers> <mapper resource="datasource2/mappers/CustomerMapper.xml" /> </mappers> </configuration>
datasource1的配置类似，不再赘述。
(2) mapper的xml文件
<?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="devutility.test.database.mybatis.springboot.multi.source.ds2.mappers.CustomerMapper"> <resultMap id="CustomerMapping" type="Customer"> <id column="Name1" property="name" /> <id column="Address1" property="address" /> </resultMap> <select id="get" resultMap="CustomerMapping"> select * from Customer where id = #{id}; </select> <select id="list" resultMap="CustomerMapping"> select * from Customer where Name1 is not null order by Created desc </select> </mapper>
(3) mapper的接口文件
package devutility.test.database.mybatis.springboot.multi.source.ds2.mappers; import java.util.List; import org.apache.ibatis.annotations.Mapper; import devutility.test.database.mybatis.springboot.multi.source.ds2.entities.Customer; @Mapper public interface CustomerMapper { Customer get(long id); List<Customer> list(); }
(4) 实体类
package devutility.test.database.mybatis.springboot.multi.source.ds2.entities; public class Customer extends BaseEntity { private long id; private String name; private String address; private String city; private String state; private int zip; private String phone; private String email;
6. 应用
package devutility.test.database.mybatis.springboot.multi.source.controller; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import com.github.pagehelper.PageHelper; import devutility.test.database.mybatis.springboot.multi.source.ds2.entities.Customer; import devutility.test.database.mybatis.springboot.multi.source.ds2.mappers.CustomerMapper; @RestController @RequestMapping("/ds2") public class Ds2Controller { @Autowired private CustomerMapper customerMapper; @RequestMapping("/customer") public Customer customer(long id) { return customerMapper.get(id); } @RequestMapping("customers-page") public List<Customer> customersPage(int page) { return PageHelper.startPage(page, 10).doSelectPage(() -> customerMapper.list()); } }
