一、第一种
springboot+druid+mybatisplus+jdk8使用注解整合
1.1 application.yml 配置文件如下
server:
port: 8080
spring:
datasource:
dynamic:
primary: database1 # 配置默认数据库
datasource:
database1: # 数据源1配置
url: jdbc:mysql://127.0.0.1:3306/database1?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
database2: # 数据源2配置
url: jdbc:mysql://127.0.0.1:3306/database2?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
durid:
initial-size: 1
max-active: 20
min-idle: 1
max-wait: 60000
autoconfigure:
# 去除druid配置
exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
1.2 配置启动类
@SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class)
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
1.3 给使用非默认数据源添加注解@DS
@DS("database2")
public interface DemoMapper extends BaseMapper<User> {
@Select("SELECT * FROM test")
@DS("database2")
List<User> selectAll();
}
//-------------------
@Service
@DS("database2")
public class DemoServiceImpl extends ServiceImpl<DemoMapper, DemoVo> implements IDemoService {}
//-------------------
二、第二种
springboot+mybatis使用分包方式整合
spring-boot-starter-web
mybatis-spring-boot-starter
mysql-connector-java
lombok
1.1 application.yml
server:
port: 8080 # 启动端口
spring:
datasource:
db1: # 数据源1
jdbc-url: jdbc:mysql://localhost:3306/db1?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
db2: # 数据源2
jdbc-url: jdbc:mysql://localhost:3306/db2?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
1.2 多数据源的配置类
SpringBoot2.0以前需要在方法上加Primary注解,来判断默认的数据库连接
@Configuration
@MapperScan(basePackages = "com.edu.multipledatasource.mapper.db1", sqlSessionFactoryRef = "db1SqlSessionFactory")
public class DataSourceConfig1 {
@Primary // 表示这个数据源是默认数据源, 这个注解必须要加,因为不加的话spring将分不清楚那个为主数据源(默认数据源)
@Bean("db1DataSource")
@ConfigurationProperties(prefix = "spring.datasource.db1") //读取application.yml中的配置参数映射成为一个对象
public DataSource getDb1DataSource(){
return DataSourceBuilder.create().build();
}
@Primary
@Bean("db1SqlSessionFactory")
public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
// mapper的xml形式文件位置必须要配置,不然将报错:no statement (这种错误也可能是mapper的xml中,namespace与项目的路径不一致导致)
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mybatis/db1/*.xml"));
return bean.getObject();
}
@Primary
@Bean("db1SqlSessionTemplate")
public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
return new SqlSessionTemplate(sqlSessionFactory);
}
}
1.3 配置文件2
@Configuration
@MapperScan(basePackages = "com.edu.multipledatasource.mapper.db2", sqlSessionFactoryRef = "db2SqlSessionFactory")
public class DataSourceConfig2 {
@Bean("db2DataSource")
@ConfigurationProperties(prefix = "spring.datasource.db2")
public DataSource getDb1DataSource(){
return DataSourceBuilder.create().build();
}
@Bean("db2SqlSessionFactory")
public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/db2/*.xml"));
return bean.getObject();
}
@Bean("db2SqlSessionTemplate")
public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
return new SqlSessionTemplate(sqlSessionFactory);
}
}
---------------------------------------------------------------------------------------------------------------------------------
多数据源事务处理(与上述的多数据源整合区分开)
1、需要使用jta-atomikos进行事务管理(pom.xml)
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.edu</groupId>
<artifactId>multiple-data-source</artifactId>
<version>1.0</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.3.RELEASE</version>
</parent>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- MySQL 连接驱动依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.39</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jta-atomikos</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
</project>
2、修改application.yml文件
server:
port: 8080
spring:
jta:
log-dir: /logs
datasource:
demo1:
jdbc-url: jdbc:mysql://localhost:3306/demo1?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: cwb138
driver-class-name: com.mysql.jdbc.Driver
borrowConnectionTimeout: 30
loginTimeout: 30
maintenanceInterval: 60
maxIdleTime: 60
maxLifetime: 20000
maxPoolSize: 25
minPoolSize: 3
uniqueResourceName: demo1DataSource
demo2:
jdbc-url: jdbc:mysql://localhost:3306/demo2?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: cwb138
driver-class-name: com.mysql.jdbc.Driver
borrowConnectionTimeout: 30
loginTimeout: 30
maintenanceInterval: 60
maxIdleTime: 60
maxLifetime: 20000
maxPoolSize: 25
minPoolSize: 3
uniqueResourceName: demo2DataSource
3、配置类1(Demo1Config、DataSourceConfig1)
package com.edu.config.vo;
import lombok.Data;
import lombok.ToString;
import org.springframework.boot.context.properties.ConfigurationProperties;
@Data
@ToString
@ConfigurationProperties(prefix = "spring.datasource.demo1")
public class Demo1Config {
private String jdbcUrl;
private String username;
private String password;
private int minPoolSize;
private int maxPoolSize;
private int maxLifetime;
private int borrowConnectionTimeout;
private int loginTimeout;
private int maintenanceInterval;
private int maxIdleTime;
private String testQuery;
private String uniqueResourceName;
}
package com.edu.config;
import com.edu.config.vo.Demo1Config;
import com.mysql.jdbc.jdbc2.optional.MysqlXADataSource;
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.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.sql.SQLException;
@Configuration
@MapperScan(value = "com.edu.mapper.demo1",sqlSessionTemplateRef = "demo1SqlSessionTemplate")
public class DataSourceConfig1 {
@Bean(name = "demo1DataSource")
public DataSource demo1DataSource (Demo1Config demo1Config) throws SQLException {
System.out.println(demo1Config);
MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();
mysqlXaDataSource.setUrl(demo1Config.getJdbcUrl());
mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
mysqlXaDataSource.setPassword(demo1Config.getPassword());
mysqlXaDataSource.setUser(demo1Config.getUsername());
mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
//注册到全局事务
AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
xaDataSource.setXaDataSource(mysqlXaDataSource);
xaDataSource.setUniqueResourceName(demo1Config.getUniqueResourceName());
xaDataSource.setMinPoolSize(demo1Config.getMinPoolSize());
xaDataSource.setMaxPoolSize(demo1Config.getMaxPoolSize());
xaDataSource.setMaxLifetime(demo1Config.getMaxLifetime());
xaDataSource.setBorrowConnectionTimeout(demo1Config.getBorrowConnectionTimeout());
xaDataSource.setLoginTimeout(demo1Config.getLoginTimeout());
xaDataSource.setMaintenanceInterval(demo1Config.getMaintenanceInterval());
xaDataSource.setMaxIdleTime(demo1Config.getMaxIdleTime());
xaDataSource.setTestQuery(demo1Config.getTestQuery());
return xaDataSource;
}
@Bean(name = "demo1SqlSessionFactory")
public SqlSessionFactory demo1SqlSessionFactory (@Qualifier("demo1DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
return bean.getObject();
}
@Bean(name = "demo1SqlSessionTemplate")
public SqlSessionTemplate demo1SqlSessionTemplate (@Qualifier("demo1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
3、配置类2(Demo2Config、DataSourceConfig2)
package com.edu.config.vo;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
@Data
@ConfigurationProperties(prefix = "spring.datasource.demo2")
public class Demo2Config {
private String jdbcUrl;
private String username;
private String password;
private int minPoolSize;
private int maxPoolSize;
private int maxLifetime;
private int borrowConnectionTimeout;
private int loginTimeout;
private int maintenanceInterval;
private int maxIdleTime;
private String testQuery;
private String uniqueResourceName;
}
package com.edu.config;
import com.edu.config.vo.Demo2Config;
import com.mysql.jdbc.jdbc2.optional.MysqlXADataSource;
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.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.sql.SQLException;
@Configuration
@MapperScan(value = "com.edu.mapper.demo2",sqlSessionTemplateRef = "demo2SqlSessionTemplate")
public class DataSourceConfig2 {
@Bean(name = "demo2DataSource")
public DataSource demo2DataSource (Demo2Config demo2Config) throws SQLException {
System.out.println(demo2Config);
MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();
mysqlXaDataSource.setUrl(demo2Config.getJdbcUrl());
mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
mysqlXaDataSource.setPassword(demo2Config.getPassword());
mysqlXaDataSource.setUser(demo2Config.getUsername());
mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
//注册到全局事务
AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
xaDataSource.setXaDataSource(mysqlXaDataSource);
xaDataSource.setUniqueResourceName(demo2Config.getUniqueResourceName());
xaDataSource.setMinPoolSize(demo2Config.getMinPoolSize());
xaDataSource.setMaxPoolSize(demo2Config.getMaxPoolSize());
xaDataSource.setMaxLifetime(demo2Config.getMaxLifetime());
xaDataSource.setBorrowConnectionTimeout(demo2Config.getBorrowConnectionTimeout());
xaDataSource.setLoginTimeout(demo2Config.getLoginTimeout());
xaDataSource.setMaintenanceInterval(demo2Config.getMaintenanceInterval());
xaDataSource.setMaxIdleTime(demo2Config.getMaxIdleTime());
xaDataSource.setTestQuery(demo2Config.getTestQuery());
return xaDataSource;
}
@Bean(name = "demo2SqlSessionFactory")
public SqlSessionFactory demo2SqlSessionFactory (@Qualifier("demo2DataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
return bean.getObject();
}
@Bean(name = "demo2SqlSessionTemplate")
public SqlSessionTemplate demo2SqlSessionTemplate (@Qualifier("demo2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
这就配置完多数据源,以及全都注册到了全局的事务,接下来是测试
1、创建两个数据库,并分别向两个库创建一个表
2、编写mapper
package com.edu.mapper.demo1;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
@Repository
public interface Demo1Mapper {
@Insert("insert into demo_user (username,age,sal) values (#{username},#{age},#{sal})")
int addDemo1(@Param("username") String username,@Param("age") int age,@Param("sal") Double sal);
}
package com.edu.mapper.demo2;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
@Repository
public interface Demo2Mapper {
@Insert("insert into demo2_city (city) values (#{city})")
int addDemo2(@Param("city") String city);
}
3、编写controller类
package com.edu.controller;
import com.edu.mapper.demo1.Demo1Mapper;
import com.edu.mapper.demo2.Demo2Mapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;
@RestController
public class DemoController {
@Autowired
private Demo1Mapper demo1Mapper;
@Autowired
private Demo2Mapper demo2Mapper;
@Transactional(rollbackFor = Exception.class)
@GetMapping("/test1")
public String test1 (@RequestParam String username, int age, Double sal) {
return demo1Mapper.addDemo1(username,age,sal)>0?"success":"fail";
}
@Transactional(rollbackFor = Exception.class)
@GetMapping("/test2/{city}")
public String test2 (@PathVariable("city") String city) {
String flag = demo2Mapper.addDemo2(city)>0?"success":"fail";
int a = Integer.valueOf(city);
return flag;
}
@Transactional(rollbackFor = Exception.class)
@GetMapping("/test3")
public String test3 (@RequestParam String username, int age, Double sal, String city) {
int i = demo1Mapper.addDemo1(username, age, sal);
int j = demo2Mapper.addDemo2(city);
int a = Integer.valueOf(city);
return j>0?"success":"fail";
}
}
经过测试test1、test2、test3报错之后,两个数据库中的两个表插入的数据都回滚了.
--------------------------------------
补充:
如果是整合了mybatis-plus,需要修改sqlSessionFactory为mybatis-plus的MybatisSqlSessionFactoryBean。
并且整合多数据源后,mybatis-plus的分页会出现问题,mybatis-plus的TableLogic逻辑删除注解也会出现问题,需要重新加载分页插件和逻辑删除插件,即DataSourceConfig1和DataSourceConfig2中的sqlSessionFactory应该修改为:(DataSourceConfig2中修改的方法同理)
/**
* 创建sqlSessionFactory
* @param dataSource
* @return
* @throws Exception
*/
@Bean(name = "demo1SqlSessionFactory")
public SqlSessionFactory demo1SqlSessionFactory(@Qualifier("demo1DataSource") DataSource dataSource) throws Exception {
//逻辑删除 如果值为1,表示已经删除,如果为0,表示未删除
GlobalConfiguration globalConfig = new GlobalConfiguration();
globalConfig.setLogicDeleteValue("1");
globalConfig.setLogicNotDeleteValue("0");
globalConfig.setSqlInjector(new LogicSqlInjector());
//分页插件
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.addInterceptor(new PaginationInterceptor());
MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setConfiguration(configuration);
sqlSessionFactoryBean.setGlobalConfig(globalConfig);
return sqlSessionFactoryBean.getObject();
}
如果任使用mybatis的SqlSessionFactoryBean,当调用BaseMapper的方法时,会报Invalid bound statement (not found)异常。
本文GIT源码: https://github.com/cuiwenbo1992/multiple-data-source