本文示範多資料源(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());
}
}
Demo代碼