本文演示多数据源(MySQL+SQL Server)的配置,并且我引入了分页插件pagehelper。
1. 项目结构
(1)db.properties存储数据源和连接池配置。
(2)两个数据源的mapper配置分别在src/main/resources下面的datasource1和datasource2里面。
2. pom.xml
需要在dependencies节点中添加:
mysql mysql-connector-java 8.0.12 com.microsoft.sqlserver mssql-jdbc com.alibaba druid-spring-boot-starter 1.1.10 org.mybatis.spring.boot mybatis-spring-boot-starter 1.3.2 com.github.pagehelper pagehelper 5.1.5
3. properties配置文件
我们把主程序配置文件application.properties和数据库配置文件分开,这样可使application.properties不至于臃肿。
(1) application.properties
server.port=9008spring.application.name=devutility-test-database-mybatis-springboot#Configuration for druidspring.datasource.druid.stat-view-servlet.enabled=truespring.datasource.druid.stat-view-servlet.url-pattern=/druid/*spring.datasource.druid.stat-view-servlet.login-username=adminspring.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=6000010 db1.sqlserver.time-between-eviction-runs-millis=6000011 db1.sqlserver.min-evictable-idle-time-millis=30000012 db1.sqlserver.validation-query=select 113 db1.sqlserver.test-on-borrow=true14 db1.sqlserver.test-While-Idle=true15 db1.sqlserver.test-on-return=false16 db1.sqlserver.pool-prepared-statements=false17 db1.sqlserver.max-pool-prepared-statement-per-connection-size=2018 19 db1.sqlserver.mybatis.config-location=classpath:datasource1/mybatis-config.xml20 21 db1.sqlserver.filter.stat.enabled=true22 db1.sqlserver.filter.stat.db-type=mssql23 db1.sqlserver.filter.stat.log-slow-sql=true24 db1.sqlserver.filter.stat.slow-sql-millis=20025 26 #Data source 227 db2.mysql.driver-class-name=com.mysql.cj.jdbc.Driver28 db2.mysql.url=${DB2_URL:jdbc:mysql://127.0.0.1:3306/Test}?useUnicode=true&useSSL=false29 db2.mysql.username=${DB2_UID:tester}30 db2.mysql.password=${DB2_PWD:tester}31 db2.mysql.initial-size=132 db2.mysql.min-idle=133 db2.mysql.max-active=2034 db2.mysql.max-wait=6000035 db2.mysql.time-between-eviction-runs-millis=6000036 db2.mysql.min-evictable-idle-time-millis=30000037 db2.mysql.validation-query=select 138 db2.mysql.test-on-borrow=true39 db2.mysql.test-While-Idle=true40 db2.mysql.test-on-return=false41 db2.mysql.pool-prepared-statements=false42 db2.mysql.max-pool-prepared-statement-per-connection-size=2043 44 db2.mysql.mybatis.config-location=classpath:datasource2/mybatis-config.xml45 46 db2.mysql.filter.stat.enabled=true47 db2.mysql.filter.stat.db-type=mysql48 db2.mysql.filter.stat.log-slow-sql=true49 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映射文件和实体类的包,其他配置项可参考。
datasource1的配置类似,不再赘述。
(2) mapper的xml文件
(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;@Mapperpublic interface CustomerMapper { Customer get(long id); Listlist();}
(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 ListcustomersPage(int page) { return PageHelper.startPage(page, 10).doSelectPage(() -> customerMapper.list()); }}