Spring Boot 2.X(五)：MyBatis 多数据源配置

时间：2023-03-09 23:44:33
Spring Boot 2.X(五)：MyBatis 多数据源配置

前言

MyBatis 多数据源配置，最近在项目建设中，需要在原有系统上扩展一个新的业务模块，特意将数据库分库，以便减少复杂度。本文直接以简单的代码示例，如何对 MyBatis 多数据源配置。

准备

创建数据库

db_test

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for t_user

-- ----------------------------

DROP TABLE IF EXISTS `t_user`;

CREATE TABLE `t_user` (

  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT 'ID',

  `user_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户姓名',

  `user_sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户性别',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of t_user

-- ----------------------------

BEGIN;

INSERT INTO `t_user` VALUES (1, '刘备', '男');

INSERT INTO `t_user` VALUES (2, '孙尚香', '女');

INSERT INTO `t_user` VALUES (3, '周瑜', '男');

INSERT INTO `t_user` VALUES (4, '小乔', '女');

INSERT INTO `t_user` VALUES (5, '诸葛亮', '男');

INSERT INTO `t_user` VALUES (6, '黄月英', '女');

INSERT INTO `t_user` VALUES (7, '关羽', '男');

INSERT INTO `t_user` VALUES (8, '张飞', '男');

INSERT INTO `t_user` VALUES (9, '赵云', '男');

INSERT INTO `t_user` VALUES (10, '黄总', '男');

INSERT INTO `t_user` VALUES (11, '曹操', '男');

INSERT INTO `t_user` VALUES (12, '司马懿', '男');

INSERT INTO `t_user` VALUES (13, '貂蝉', '女');

INSERT INTO `t_user` VALUES (14, '吕布', '男');

INSERT INTO `t_user` VALUES (15, '马超', '男');

INSERT INTO `t_user` VALUES (16, '魏延', '男');

INSERT INTO `t_user` VALUES (17, '孟获', '男');

INSERT INTO `t_user` VALUES (18, '大乔', '女');

INSERT INTO `t_user` VALUES (19, '刘婵', '男');

INSERT INTO `t_user` VALUES (20, '姜维', '男');

INSERT INTO `t_user` VALUES (21, '廖化', '男');

INSERT INTO `t_user` VALUES (22, '关平', '男');

COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

dbb_test2



SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for t_hero

-- ----------------------------

DROP TABLE IF EXISTS `t_hero`;

CREATE TABLE `t_hero` (

  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',

  `hero_code` varchar(32) DEFAULT NULL COMMENT '英雄编码',

  `hero_name` varchar(20) DEFAULT NULL COMMENT '英雄名称',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of t_hero

-- ----------------------------

BEGIN;

INSERT INTO `t_hero` VALUES (1, '001', '德玛西亚');

COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

构建项目，项目目录结构

Spring Boot 2.X(五)：MyBatis 多数据源配置

pom 文件

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">

	<modelVersion>4.0.0</modelVersion>

	<parent>

		<groupId>org.springframework.boot</groupId>

		<artifactId>spring-boot-starter-parent</artifactId>

		<version>2.1.9.RELEASE</version>

		<relativePath/> <!-- lookup parent from repository -->

	</parent>

	<groupId>cn.zwqh</groupId>

	<artifactId>spring-boot-mybatis-mulidatasource</artifactId>

	<version>0.0.1-SNAPSHOT</version>

	<name>spring-boot-mybatis-mulidatasource</name>

	<description>spring-boot-mybatis-mulidatasource</description>

	<properties>

		<java.version>1.8</java.version>

	</properties>

	<dependencies>

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter-web</artifactId>

		</dependency>

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter-test</artifactId>

			<scope>test</scope>

		</dependency>

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-starter-jdbc</artifactId>

		</dependency>

		<!-- 热部署模块 -->

		<dependency>

			<groupId>org.springframework.boot</groupId>

			<artifactId>spring-boot-devtools</artifactId>

			<optional>true</optional> <!-- 这个需要为 true 热部署才有效 -->

		</dependency>

		<!-- mysql 数据库驱动. -->

		<dependency>

			<groupId>mysql</groupId>

			<artifactId>mysql-connector-java</artifactId>

			<scope>runtime</scope>

		</dependency>

		<!-- mybaits -->

		<dependency>

			<groupId>org.mybatis.spring.boot</groupId>

			<artifactId>mybatis-spring-boot-starter</artifactId>

			<version>2.1.0</version>

		</dependency>

		<!-- alibaba的druid数据库连接池 -->

		<dependency>

			<groupId>com.alibaba</groupId>

			<artifactId>druid-spring-boot-starter</artifactId>

			<version>1.1.20</version>

		</dependency>

		<!-- pagehelper -->

		<dependency>

			<groupId>com.github.pagehelper</groupId>

			<artifactId>pagehelper-spring-boot-starter</artifactId>

			<version>1.2.12</version>

		</dependency>

	</dependencies>

	<build>

		<plugins>

			<plugin>

				<groupId>org.springframework.boot</groupId>

				<artifactId>spring-boot-maven-plugin</artifactId>

			</plugin>

		</plugins>

	</build>

</project>

这里使用了alibaba的druid数据库连接池，Druid 能够提供强大的监控和扩展功能。这里我们暂时只做简单的应用。

配置文件

#master 数据源配置

master.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

master.datasource.url=jdbc:mysql://127.0.0.1:3306/db_test?useUnicode=true&characterEncoding=UTF-8&useSSL=true

master.datasource.username=root

master.datasource.password=zwqh@0258

#slave 数据源配置

slave.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

slave.datasource.url=jdbc:mysql://127.0.0.1:3306/db_test2?useUnicode=true&characterEncoding=UTF-8&useSSL=true

slave.datasource.username=root

slave.datasource.password=zwqh@0258

#mybatis

mybatis.mapper-locations=classpath:/mapper/**/*Mapper.xml

数据源配置

MasterDataSourceConfig 对应数据库 db_test

@Configuration

@MapperScan(basePackages = "cn.zwqh.springboot.dao.master", sqlSessionFactoryRef = "masterSqlSessionFactory")

public class MasterDataSourceConfig {

	@Value("${master.datasource.driver-class-name}")

	private String driverClassName;

	@Value("${master.datasource.url}")

	private String url;

	@Value("${master.datasource.username}")

	private String username;

	@Value("${master.datasource.password}")

	private String password;

	@Bean(name = "masterDataSource")

	@Primary

	public DataSource dataSource() {

		DruidDataSource dataSource = new DruidDataSource();

		dataSource.setDriverClassName(this.driverClassName);

		dataSource.setUrl(this.url);

		dataSource.setUsername(this.username);

		dataSource.setPassword(this.password);

		return dataSource;

	}

	@Bean(name = "masterSqlSessionFactory")

	@Primary

	public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {

		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

		bean.setDataSource(dataSource);

		bean.setMapperLocations(

				new PathMatchingResourcePatternResolver().getResources("classpath*:/mapper/master/*Mapper.xml"));

		return bean.getObject();

	}

	@Bean(name = "masterTransactionManager")

	@Primary

	public DataSourceTransactionManager transactionManager(@Qualifier("masterDataSource") DataSource dataSource) {

		return new DataSourceTransactionManager(dataSource);

	}

	@Bean(name = "masterSqlSessionTemplate")

	@Primary

	public SqlSessionTemplate testSqlSessionTemplate(

			@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {

		return new SqlSessionTemplate(sqlSessionFactory);

	}

}

SlaveDataSourceConfig 对应数据库 db_test2

@Configuration

@MapperScan(basePackages = "cn.zwqh.springboot.dao.slave", sqlSessionFactoryRef = "slaveSqlSessionFactory")

public class SlaveDataSourceConfig {

	@Value("${slave.datasource.driver-class-name}")

	private String driverClassName;

	@Value("${slave.datasource.url}")

	private String url;

	@Value("${slave.datasource.username}")

	private String username;

	@Value("${slave.datasource.password}")

	private String password;

	@Bean(name = "slaveDataSource")

	public DataSource dataSource() {

		DruidDataSource dataSource = new DruidDataSource();

		dataSource.setDriverClassName(this.driverClassName);

		dataSource.setUrl(this.url);

		dataSource.setUsername(this.username);

		dataSource.setPassword(this.password);

		return dataSource;

	}

	@Bean(name = "slaveSqlSessionFactory")

	public SqlSessionFactory sqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {

		SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

		bean.setDataSource(dataSource);

		bean.setMapperLocations(

				new PathMatchingResourcePatternResolver().getResources("classpath*:/mapper/slave/*Mapper.xml"));

		return bean.getObject();

	}

	@Bean(name = "slaveTransactionManager")

	public DataSourceTransactionManager transactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {

		return new DataSourceTransactionManager(dataSource);

	}

	@Bean(name = "slaveSqlSessionTemplate")

	public SqlSessionTemplate testSqlSessionTemplate(

			@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {

		return new SqlSessionTemplate(sqlSessionFactory);

	}

}

多个数据源在使用的过程中必须指定主库，不然会报错。

@MapperScan(basePackages = "cn.zwqh.springboot.dao.slave") 指定对应 Dao 层的扫描路径。

dao 层和 xml 层

db_test 数据库的 dao 层在 cn.zwqh.springboot.dao.master 包下，db_test2 数据库的 dao 层在 cn.zwqh.springboot.dao.slave 包下。

UserDao

public interface UserDao {

	List<UserEntity> getAll();

}

HeroDao

public interface HeroDao {

	List<Hero> getAllHero();

}

db_test 数据库的 xml 层在 /mapper/master/ 文件路径下，db_test2 数据库的 xml 层在 /mapper/slave/ 文件路径下。

UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.4//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="cn.zwqh.springboot.dao.master.UserDao">

	<resultMap type="cn.zwqh.springboot.model.UserEntity" id="user">

		<id property="id" column="id"/>

		<result property="userName" column="user_name"/>

		<result property="userSex" column="user_sex"/>

	</resultMap>

	<!-- 获取所有用户 -->

	<select id="getAll" resultMap="user">

		select * from t_user

	</select>

</mapper>

HeroMapper.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.4//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="cn.zwqh.springboot.dao.slave.HeroDao">

	<resultMap type="cn.zwqh.springboot.model.Hero" id="hero">

		<id property="id" column="id"/>

		<result property="heroCode" column="hero_code"/>

		<result property="heroName" column="hero_name"/>

	</resultMap>

	<!-- 获取所有用户 -->

	<select id="getAllHero" resultMap="hero">

		select * from t_hero

	</select>

</mapper>

测试

测试可以使用 SpringBootTest,也可以放到 Controller中，个人习惯用 Controller。

@RestController

@RequestMapping("/test")

public class TestController {

	@Autowired

	private UserDao userDao;

	@Autowired

	private HeroDao heroDao;

	/**

	 *  查找所有用户

	 * @return

	 */

	@RequestMapping("/getAllUser")

	public List<UserEntity> getAllUser(){

		return userDao.getAll();

	}

	/**

	 *  查找所有英雄

	 * @return

	 */

	@RequestMapping("/getAllHero")

	public List<Hero> getAllHero(){

		return heroDao.getAllHero();

	}

}

浏览器直接访问：http://127.0.0.1:8080/test/ 加上相关测试路径即可。

总结

多数据源一般用于主从模式或者按业务分库。

示例代码

github

码云

非特殊说明，本文版权归 朝雾轻寒 所有，转载请注明出处.

原文标题：Spring Boot 2.X(五)：MyBatis 多数据源配置

原文地址：https://www.zwqh.top/article/info/12

如果文章对您有帮助，请扫码关注下我的公众号，文章持续更新中...

Spring Boot 2.X(五)：MyBatis 多数据源配置

相关文章