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: true

2.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

4.3 需要的小伙伴可以根据需求修改，项目Gitee地址：SpringBoot-Druid

posted @ 2022-04-27 12:23 helloliyh 阅读(9954) 评论(2) 收藏举报

刷新页面返回顶部

helloliyh

SpringBoot配置多数据源

1. SpringBoot配置多个数据库

2. 项目配置

2.1 创建SpringBoot项目

2.2 pom文件导入需要的依赖

2.4 配置yml文件

2.5 创建数据库和数据库表

2.5.1 在 test 数据库创建表并加一条数据

2.5.2 在 test2 数据库创建表并加一条数据

3. 业务代码

3.1 创建读取第一个数据库配置文件配置类

3.2 创建读取第二个数据库配置文件配置类

3.3 创建 Entity 接口数据对象

3.4 创建 UserController 接口

3.5 创建 UserService 接口

3.6 创建 UserService 实现类

3.7 在 mapper 包下创建 mapper和xml

3.8 在 test/mapper 包下创建 mapper和xml

4. 测试接口

4.1 测试第一个数据库查询接口

4.2 测试第二个数据库查询接口

4.3 需要的小伙伴可以根据需求修改，项目Gitee地址：SpringBoot-Druid

公告