SpringBoot配置多数据源
1. SpringBoot配置多个数据库
在开发中，经常会碰到需要从两个不同的数据库查询数据。
今天，本项目将讲解通过 SpringBoot 配置多个数据源。
2. 项目配置
2.1 创建SpringBoot项目
项目结构
2.2 pom文件导入需要的依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.2</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
</dependency>
</dependencies>2.4 配置yml文件
Springboot工程中修改配置文件 aplication.yml （注意：如果是单数据源使用 url ，多数据源要使用 jdbc-url ）
server:
# 服务器的HTTP端口
port: 8097
spring:
# 配置数据源
datasource:
master:
jdbc-url: jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
test:
jdbc-url: jdbc:mysql://localhost:3306/test2?useSSL=false&serverTimezone=UTC
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
# MyBatis配置
mybatis:
# 搜索指定包别名
# (如果 typeAliasesPackage不进行配置，resultType就得写全名resultType="com.liyh.entity.User"，
# 但是如果配置了别名,就可以不用写全路径直接写resultType="User"即可)
type-aliases-package: com.liyh.entity
# 配置mapper的扫描，找到所有的mapper.xml映射文件
mapper-locations: classpath*:mapper/**/*Mapper.xml
# 使用驼峰命名
# 数据库表列：user_name
# 实体类属性：userName
configuration:
map-underscore-to-camel-case: true2.5 创建数据库和数据库表
2.5.1 在 test 数据库创建表并加一条数据
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户账号',
`nick_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户昵称',
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户信息表' ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of sys_user
-- ----------------------------
INSERT INTO `sys_user` VALUES (1, 'admin', '超级管理员');
SET FOREIGN_KEY_CHECKS = 1;2.5.2 在 test2 数据库创建表并加一条数据
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户账号',
`nick_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户昵称',
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户信息表' ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Records of sys_user
-- ----------------------------
INSERT INTO `sys_user` VALUES (1, '测试', '测试员');
SET FOREIGN_KEY_CHECKS = 1;3. 业务代码
3.1 创建读取第一个数据库配置文件配置类
package com.liyh.config;
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.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = {"com.liyh.mapper"}, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MybatisMasterConfig {
@Bean(name = "masterDataSource")
@ConfigurationProperties(prefix = "spring.datasource.master")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
/**
* 配置事务管理器，不然事务不起作用
*
* @return
*/
@Bean
public PlatformTransactionManager transactionManager() {
return new DataSourceTransactionManager(this.dataSource());
}
@Primary
@Bean(name = "masterSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources("classpath*:mapper/*.xml"));
sqlSessionFactoryBean.setTypeAliasesPackage("com.liyh.entity");
sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
return sqlSessionFactoryBean.getObject();
}
}3.2 创建读取第二个数据库配置文件配置类
package com.liyh.config;
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.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.liyh.test.mapper", sqlSessionFactoryRef = "testSqlSessionFactory")
public class MybatisTestConfig {
@Bean(name = "testDataSource")
@ConfigurationProperties(prefix = "spring.datasource.test")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Bean("testSqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("testDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean ();
sqlSessionFactoryBean.setDataSource(dataSource);
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources("classpath*:mapper/test/*.xml"));
sqlSessionFactoryBean.setTypeAliasesPackage("com.liyh.entity");
sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
return sqlSessionFactoryBean.getObject();
}
}3.3 创建 Entity 接口数据对象
package com.liyh.entity;
import lombok.Data;
/**
* 用户对象 sys_user
*
* @author liyh
*/
@Data
public class SysUser {
/**
* 用户ID
*/
private Long userId;
/**
* 用户账号
*/
private String userName;
/**
* 用户昵称
*/
private String nickName;
}3.4 创建 UserController 接口
package com.liyh.controller;
import com.liyh.entity.SysUser;
import com.liyh.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
/**
* @Author: liyh
*/
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
UserService userService;
/**
* 根据用户id查询用户信息
*
* @param userId
* @return
*/
@RequestMapping("/getMasterInfo")
public SysUser getMasterInfo(String userId) {
SysUser user = userService.getMasterInfo(userId);
return user;
}
/**
* 根据用户id查询用户信息
*
* @param userId
* @return
*/
@RequestMapping("/getTestInfo")
public SysUser getTestInfo(String userId) {
SysUser user = userService.getTestInfo(userId);
return user;
}
}3.5 创建 UserService 接口
package com.liyh.service;
import com.liyh.entity.SysUser;
/**
* 用户业务接口
*
* @Author: liyh
*/
public interface UserService {
SysUser getMasterInfo(String userId);
SysUser getTestInfo(String userId);
}3.6 创建 UserService 实现类
package com.liyh.service.impl;
import com.liyh.entity.SysUser;
import com.liyh.mapper.MasterMapper;
import com.liyh.service.UserService;
import com.liyh.test.mapper.TestMapper;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
/**
* 用户业务实现类
*
* @Author: liyh
*/
@Service
public class UserServiceImpl implements UserService {
@Resource
private MasterMapper masterMapper;
@Resource
private TestMapper testMapper;
@Override
public SysUser getMasterInfo(String userId) {
return masterMapper.getMasterInfo(userId);
}
@Override
public SysUser getTestInfo(String userId) {
return testMapper.getTestInfo(userId);
}
}3.7 在 mapper 包下创建 mapper和xml
package com.liyh.mapper;
import com.liyh.entity.SysUser;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
/**
* mapper接口
*
* @Author: liyh
*/
@Mapper
public interface MasterMapper {
SysUser getMasterInfo(@Param("userId") String userId);
}<?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.liyh.mapper.MasterMapper">
<resultMap type="com.liyh.entity.SysUser" id="UserResult">
<id property="userId" column="user_id"/>
<id property="userName" column="user_name"/>
<id property="nickName" column="nick_name"/>
</resultMap>
<select id="getMasterInfo" resultType="SysUser">
select * from sys_user where user_id = #{userId}
</select>
</mapper>3.8 在 test/mapper 包下创建 mapper和xml
package com.liyh.test.mapper;
import com.liyh.entity.SysUser;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
/**
* test接口
*
* @Author: liyh
*/
@Mapper
public interface TestMapper {
SysUser getTestInfo(@Param("userId") String userId);
}<?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.liyh.test.mapper.TestMapper">
<resultMap type="com.liyh.entity.SysUser" id="UserResult">
<id property="userId" column="user_id"/>
<id property="userName" column="user_name"/>
<id property="nickName" column="nick_name"/>
</resultMap>
<select id="getTestInfo" resultType="SysUser">
select * from sys_user where user_id = #{userId}
</select>
</mapper>4. 测试接口
4.1 测试第一个数据库查询接口
http://localhost:8097/user/getMasterInfo?userId=1
4.2 测试第二个数据库查询接口
http://localhost:8097/user/getTestInfo?userId=1
