有的时候我们的项目可能需要从不同的数据源获取或者操作数据,那么这个时候就需要配置多数据源,接下来我们整合下Mybatis+Druid实现多数据源,本实例代码08-MultipleDataSource,废话不多说,开搞...
<?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.perfree</groupId>
<artifactId>multipledatasource</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>multipledatasource</name>
<description>SpringBoot-MultipleDataSource-Demo</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.5.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.39</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.10</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
关键依赖:
<!-- Mybatis依赖 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!-- Mysql驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.39</version>
</dependency>
<!-- Druid依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.10</version>
</dependency>
其中datasource和mapper(包括映射文件)包下分别都配置了两个数据源,其他地方基本没啥变化,接着往下看
因为就是用来进行测试的,就随便准备了两个数据库,test1数据库有一张user1表,test2有一张user2表
下边是sql语句,数据库自己建吧~
user1:
CREATE TABLE `user1` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(50) DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user1
-- ----------------------------
INSERT INTO `user1` VALUES ('1', '张三', '22');
user2:
CREATE TABLE `user2` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(50) DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user2
-- ----------------------------
INSERT INTO `user2` VALUES ('1', 'lucy', '21');
User:
package com.perfree.multipledatasource.pojo;
/**
* @ClassName User
* @Description TODO(user实体类)
* @Author Perfree
* @Date 2018/10/3 21:19
* @Version 1.0
*/
public class User {
private Integer id;
private String name;
private Integer age;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
}
这里要注意了,我在这里进行了分包,结构如下:
User1Mapper代码:
package com.perfree.multipledatasource.mapper.test1;
import com.perfree.multipledatasource.pojo.User;
import org.apache.ibatis.annotations.Mapper;
/**
* User1 Mapper接口
*/
@Mapper
public interface User1Mapper {
//根据用户id查询用户信息
User getUserById(int id);
}
User2Mapper代码:
package com.perfree.multipledatasource.mapper.test2;
import com.perfree.multipledatasource.pojo.User;
import org.apache.ibatis.annotations.Mapper;
/**
* User2 Mapper接口
*/
@Mapper
public interface User2Mapper {
//根据用户id查询用户信息
User getUserById(int id);
}
注意结构:
User1Mapper代码:
<?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="com.perfree.multipledatasource.mapper.test1.User1Mapper">
<!-- 根据用户id查询用户信息 -->
<select id="getUserById" parameterType="int" resultType="com.perfree.multipledatasource.pojo.User">
SELECT
id,
NAME,
age
FROM
USER1
WHERE
id = #{id}
</select>
</mapper>
User2Mapper代码:
<?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="com.perfree.multipledatasource.mapper.test2.User2Mapper">
<!-- 根据用户id查询用户信息 -->
<select id="getUserById" parameterType="int" resultType="com.perfree.multipledatasource.pojo.User">
SELECT
id,
NAME,
age
FROM
USER2
WHERE
id = #{id}
</select>
</mapper>
UserService代码:
package com.perfree.multipledatasource.service;
import com.perfree.multipledatasource.pojo.User;
/**
* UserService接口
*/
public interface UserService {
//从test1数据库中根据id查找用户信息
User getUserByIdAndTest1(int id);
//从test2数据库中根据id查找用户信息
User getUserByIdAndTest2(int id);
}
UserServiceImpl代码:
package com.perfree.multipledatasource.service.impl;
import com.perfree.multipledatasource.mapper.test1.User1Mapper;
import com.perfree.multipledatasource.mapper.test2.User2Mapper;
import com.perfree.multipledatasource.pojo.User;
import com.perfree.multipledatasource.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
/**
* UserService实现类
*/
@Service
public class UserServiceImpl implements UserService {
//注入User1Mapper
@Autowired
private User1Mapper user1Mapper;
//注入User2Mapper
@Autowired
private User2Mapper user2Mapper;
@Override
public User getUserByIdAndTest1(int id) {
User user = user1Mapper.getUserById(id);
return user;
}
@Override
public User getUserByIdAndTest2(int id) {
User user = user2Mapper.getUserById(id);
return user;
}
}
MultipleDataSourceController代码:
package com.perfree.multipledatasource.controller;
import com.perfree.multipledatasource.pojo.User;
import com.perfree.multipledatasource.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* MultipleDataSourceController
*/
@RestController
public class MultipleDataSourceController {
@Autowired
private UserService userService;
//从数据库test1根据用户id查询用户信息
@RequestMapping("/test1/getUser")
public String getUserByIdAndTest1(){
User user = userService.getUserByIdAndTest1(1);
return user.getName();
}
//从数据库test2根据用户id查询用户信息
@RequestMapping("/test2/getUser")
public String getUserByIdAndTest2(){
User user = userService.getUserByIdAndTest2(1);
return user.getName();
}
}
配置数据源,前缀无特定格式,随意写
## 数据源1对应test1数据库
test1.datasource.url=jdbc:mysql://localhost:3306/test1?characterEncoding=utf8
test1.datasource.username=root
test1.datasource.password=215521
test1.datasource.driver-class-name=com.mysql.jdbc.Driver
## 数据源2对应test2数据库
test2.datasource.url=jdbc:mysql://localhost:3306/test2?characterEncoding=utf8
test2.datasource.username=root
test2.datasource.password=215521
test2.datasource.driver-class-name=com.mysql.jdbc.Driver
结构:
这一步也是最关键的一步,对两条数据源进行了配置,解释基本都写到注释里了,所以直接上代码
DataSource1Config代码:
package com.perfree.multipledatasource.datasource;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
/**
* 配置test1数据源
*/
@Configuration// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = DataSource1Config.PACKAGE, sqlSessionFactoryRef = "test1SqlSessionFactory")
public class DataSource1Config {
// 配置范围,精确到 test1 目录,以便跟其他数据源隔离
static final String PACKAGE = "com.perfree.multipledatasource.mapper.test1";
static final String MAPPER_LOCATION = "classpath:mapper/test1/*.xml";
//下面这些@Value都是从application.properties读取设置好的数据源
@Value("${test1.datasource.url}")
private String url;
@Value("${test1.datasource.username}")
private String user;
@Value("${test1.datasource.password}")
private String password;
@Value("${test1.datasource.driver-class-name}")
private String driverClass;
//Druid连接池
@Bean(name = "test1DataSource")
@Primary
public DataSource test1DataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
//TransactionManager
@Bean(name = "test1TransactionManager")
@Primary
public DataSourceTransactionManager test1TransactionManager() {
return new DataSourceTransactionManager(test1DataSource());
}
//SqlSessionFactory
@Bean(name = "test1SqlSessionFactory")
@Primary
public SqlSessionFactory test1SqlSessionFactory(@Qualifier("test1DataSource") DataSource test1DataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(test1DataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(DataSource1Config.MAPPER_LOCATION));
return sessionFactory.getObject();
}
}
DataSource2Config代码:
package com.perfree.multipledatasource.datasource;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
/**
* Test2数据源配置
*/
@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = DataSource2Config.PACKAGE, sqlSessionFactoryRef = "test2SqlSessionFactory")
public class DataSource2Config {
// 配置范围,精确到 test2 目录,以便跟其他数据源隔离
static final String PACKAGE = "com.perfree.multipledatasource.mapper.test2";
static final String MAPPER_LOCATION = "classpath:mapper/test2/*.xml";
//下面这些@Value都是从application.properties读取设置好的数据源
@Value("${test2.datasource.url}")
private String url;
@Value("${test2.datasource.username}")
private String user;
@Value("${test2.datasource.password}")
private String password;
@Value("${test2.datasource.driver-class-name}")
private String driverClass;
//Druid连接池
@Bean(name = "test2DataSource")
public DataSource test2DataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
//TransactionManager
@Bean(name = "test2TransactionManager")
public DataSourceTransactionManager test2TransactionManager() {
return new DataSourceTransactionManager(test2DataSource());
}
//SqlSessionFactory
@Bean(name = "test2SqlSessionFactory")
public SqlSessionFactory test2SqlSessionFactory(@Qualifier("test2DataSource") DataSource test2DataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(test2DataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(DataSource2Config.MAPPER_LOCATION));
return sessionFactory.getObject();
}
}
其实mybatis原生xml写多了,这些一看就明白了,其中特别注意DataSource1Config加入了@Primary而DataSource2Config没有,这是因为必须要有一个默认配置,否则运行会报错!
先看数据库数据
Test1.user1:
Test2.user2:
接下来运行项目,启动成功后我们先测试取test1数据库中的值:
ok,没问题,接着测试取test2数据库中的值:
大功告成~