日常开发中，我们很少会在一个应用程序中同时使用多个数据源。但是，如果涉及一些数据迁移等应用，可能会涉及将数据从一个库迁移到另外一个库，甚至是不同类型的数据库，比如MySQL到Oracle。
这篇博文，我们不介绍mybatis的基本使用，只介绍基于mybatis配置多数据源的方法。
数据源准备
我这里用了本地MySQL两个库，分别是mybatisone和mybatistwo,mybatisone库中有一张userone表，mybatistwo库中有一张usertwo表，建表sql如下：
userone
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 80021
Source Host : localhost:3306
Source Schema : mybatisone
Target Server Type : MySQL
Target Server Version : 80021
File Encoding : 65001
Date: 01/10/2020 00:34:29
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for userone
-- ----------------------------
DROP TABLE IF EXISTS `userone`;
CREATE TABLE `userone` (
`id` bigint(0) NOT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of userone
-- ----------------------------
INSERT INTO `userone` VALUES (1, '姓名1');
SET FOREIGN_KEY_CHECKS = 1;
usertwo
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 80021
Source Host : localhost:3306
Source Schema : mybatistwo
Target Server Type : MySQL
Target Server Version : 80021
File Encoding : 65001
Date: 01/10/2020 00:34:35
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for usertwo
-- ----------------------------
DROP TABLE IF EXISTS `usertwo`;
CREATE TABLE `usertwo` (
`id` bigint(0) NOT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of usertwo
-- ----------------------------
INSERT INTO `usertwo` VALUES (1, '姓名2');
SET FOREIGN_KEY_CHECKS = 1;
添加依赖
主要依赖如下
<!--mybatis依赖-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<!--MySQL驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!--druid-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.14</version>
</dependency>application.yaml配置文件
yaml文件配置数据源信息以及mybatis的配置信息
spring:
datasource:
mybatisone:
url: jdbc:mysql://localhost:3306/mybatisone?chartset=utf8mb4&serverTimezone=UTC&usessl=false
username: root
password: root
type: com.alibaba.druid.pool.DruidDataSource
mybatistwo:
url: jdbc:mysql://localhost:3306/mybatistwo?chartset=utf8mb4&serverTimezone=UTC&usessl=false
username: root
password: root
type: com.alibaba.druid.pool.DruidDataSource
mybatis:
mapper-locations: classpath*:mapper/*.xml
type-aliases-package: net.xiangcaowuyu.simplemybatis.entity配置druid
根据yaml文件提供的DataSource，分别配置对应的两个数据源。
DataSourceConfig.java
/**
* datasource配置文件
* @author laughing
* @date 2020/9/30
* @site https://www.xiangcaowuyu.net
*/
@Configuration
public class DataSourceConfig {
/**
*
* @return 第一个数据源
*/
@Bean
@ConfigurationProperties(prefix = "spring.datasource.mybatisone")
DataSource dataSourceOne(){
return DruidDataSourceBuilder.create().build();
}
/**
*
* @return 第二个数据源
*/
@Bean
@ConfigurationProperties(prefix = "spring.datasource.mybatistwo")
DataSource dataSourceTwo(){
return DruidDataSourceBuilder.create().build();
}
}配置mybatis数据源
MyBatisConfigOne.java
/**
* mybatis第一个配置文件
* @author laughing
* @date 2020/9/30
* @site https://www.xiangcaowuyu.net
*/
@Configuration
@MapperScan(basePackages = {"net.xiangcaowuyu.simplemybatis.mapper.one"},sqlSessionFactoryRef = "sqlSessionFactoryOne",sqlSessionTemplateRef = "sqlSessionTemplateOne")
public class MyBatisConfigOne {
private final Logger logger = LoggerFactory.getLogger(MyBatisConfigOne.class);
@Resource(name = "dataSourceOne")
DataSource dataSourceOne;
@Bean
SqlSessionFactory sqlSessionFactoryOne() {
SqlSessionFactory sqlSessionFactory = null;
try {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/one/*.xml"));
sqlSessionFactoryBean.setDataSource(dataSourceOne);
sqlSessionFactory = sqlSessionFactoryBean.getObject();
}catch (Exception ex){
logger.error(ex.getMessage());
}
return sqlSessionFactory;
}
@Bean
SqlSessionTemplate sqlSessionTemplateOne(){
return new SqlSessionTemplate(sqlSessionFactoryOne());
}
}MyBatisConfigTwo.java
/**
* mybatis第二个配置文件
* @author laughing
* @date 2020/9/30
* @site https://www.xiangcaowuyu.net
*/
@Configuration
@MapperScan(basePackages = {"net.xiangcaowuyu.simplemybatis.mapper.two"},sqlSessionFactoryRef = "sqlSessionFactoryTwo",sqlSessionTemplateRef = "sqlSessionTemplateTwo")
public class MyBatisConfigTwo {
private final Logger logger = LoggerFactory.getLogger(MyBatisConfigTwo.class);
@Resource(name = "dataSourceTwo")
DataSource dataSourceTwo;
@Bean
SqlSessionFactory sqlSessionFactoryTwo() {
SqlSessionFactory sqlSessionFactory = null;
try {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/two/*.xml"));
sqlSessionFactoryBean.setDataSource(dataSourceTwo);
sqlSessionFactory = sqlSessionFactoryBean.getObject();
}catch (Exception ex){
logger.error(ex.getMessage());
}
return sqlSessionFactory;
}
@Bean
SqlSessionTemplate sqlSessionTemplateTwo(){
return new SqlSessionTemplate(sqlSessionFactoryTwo());
}
}生成mybatis信息
mybatis不是我们要讲解的重点，我这里的信息都是通过Free MyBatis插件自动生成的。不做过多介绍。
增加实体
Userone.java
/**
* userone
* @author
*/
@Data
public class Userone implements Serializable {
private Long id;
private String name;
private static final long serialVersionUID = 1L;
}Usertwo.java
/**
* usertwo
* @author
*/
@Data
public class Usertwo implements Serializable {
private Long id;
private String name;
private static final long serialVersionUID = 1L;
}增加mapper
UseroneMapper.java
@Mapper
public interface UseroneMapper {
int deleteByPrimaryKey(Long id);
int insert(Userone record);
int insertSelective(Userone record);
Userone selectByPrimaryKey(Long id);
int updateByPrimaryKeySelective(Userone record);
int updateByPrimaryKey(Userone record);
}Usertwo.java
/**
* usertwo
* @author
*/
@Data
public class Usertwo implements Serializable {
private Long id;
private String name;
private static final long serialVersionUID = 1L;
}增加xml
UseroneMapper.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="net.xiangcaowuyu.simplemybatis.mapper.one.UseroneMapper">
<resultMap id="BaseResultMap" type="net.xiangcaowuyu.simplemybatis.entity.Userone">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="name" jdbcType="VARCHAR" property="name"/>
</resultMap>
<sql id="Base_Column_List">
id, `name`
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from userone
where id = #{id,jdbcType=BIGINT}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
delete from userone
where id = #{id,jdbcType=BIGINT}
</delete>
<insert id="insert" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.simplemybatis.entity.Userone"
useGeneratedKeys="true">
insert into userone (`name`)
values (#{name,jdbcType=VARCHAR})
</insert>
<insert id="insertSelective" keyColumn="id" keyProperty="id"
parameterType="net.xiangcaowuyu.simplemybatis.entity.Userone" useGeneratedKeys="true">
insert into userone
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="name != null">
`name`,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="name != null">
#{name,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="net.xiangcaowuyu.simplemybatis.entity.Userone">
update userone
<set>
<if test="name != null">
`name` = #{name,jdbcType=VARCHAR},
</if>
</set>
where id = #{id,jdbcType=BIGINT}
</update>
<update id="updateByPrimaryKey" parameterType="net.xiangcaowuyu.simplemybatis.entity.Userone">
update userone
set `name` = #{name,jdbcType=VARCHAR}
where id = #{id,jdbcType=BIGINT}
</update>
</mapper>UsertwoMapper.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="net.xiangcaowuyu.simplemybatis.mapper.two.UsertwoMapper">
<resultMap id="BaseResultMap" type="net.xiangcaowuyu.simplemybatis.entity.Usertwo">
<id column="id" jdbcType="BIGINT" property="id" />
<result column="name" jdbcType="VARCHAR" property="name" />
</resultMap>
<sql id="Base_Column_List">
id, `name`
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from usertwo
where id = #{id,jdbcType=BIGINT}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
delete from usertwo
where id = #{id,jdbcType=BIGINT}
</delete>
<insert id="insert" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.simplemybatis.entity.Usertwo" useGeneratedKeys="true">
insert into usertwo (`name`)
values (#{name,jdbcType=VARCHAR})
</insert>
<insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="net.xiangcaowuyu.simplemybatis.entity.Usertwo" useGeneratedKeys="true">
insert into usertwo
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="name != null">
`name`,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="name != null">
#{name,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="net.xiangcaowuyu.simplemybatis.entity.Usertwo">
update usertwo
<set>
<if test="name != null">
`name` = #{name,jdbcType=VARCHAR},
</if>
</set>
where id = #{id,jdbcType=BIGINT}
</update>
<update id="updateByPrimaryKey" parameterType="net.xiangcaowuyu.simplemybatis.entity.Usertwo">
update usertwo
set `name` = #{name,jdbcType=VARCHAR}
where id = #{id,jdbcType=BIGINT}
</update>
</mapper>配置测试Api
/**
* @author laughing
* @date 2020/9/30
* @site https://www.xiangcaowuyu.net
*/
@RestController
public class MyBatisController {
@Resource
UseroneMapper useroneMapper;
@Resource
UsertwoMapper usertwoMapper;
@RequestMapping("/one")
public Userone userone(){
return useroneMapper.selectByPrimaryKey(1L);
}
@RequestMapping("/two")
public Usertwo usertwo(){
return usertwoMapper.selectByPrimaryKey(1L);
}
}增加两个Api，分别用于获取数据源1和数据源2的数据。
整体代码结构
里面封装了全局异常，如果不了解的，可以参考 SpringBoot 之 @ControllerAdvice使用场景
代码测试
我们在分别打开两个请求，查看获取的数据
注意事项
在配置数据源时，注意设置mapper的位置，即如下代码：
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/one/*.xml"));如果没有设置，可能会提示
Invalid bound statement (not found)
感谢分享