• SpringBoot+Mybatis实现多数据源+分页

    1 主要依赖版本

    （1）SpringBoot 2.7.8

    （2）Mybatis 2.2.2

    （3）Pagehelper 1.3.0

    （4）MySQL 8.0.26

    （5）Oracle 11.2.0.3

    2 概述

    （1）这里使用两个数据源，分别是MySQL和Oracle，配置两个数据库；

    （2）针对以上数据源分别配置Mybatis和Pagehelper(实现分页)；

    （3）数据源、Mybatis、分页都自定义配置，在启动类关闭它们的自动配置。

    3 SpringBoot整合Mybatis实现多数据源动态切换

    3.1 整合与配置

    3.1.1 相关依赖pom.xml

    2. <dependency>
    3.     <groupId>org.mybatis.spring.bootgroupId>
    4.     <artifactId>mybatis-spring-boot-starterartifactId>
    5.     <version>2.2.2version>
    6. dependency>
    8. <dependency>
    9.     <groupId>com.github.pagehelpergroupId>
    10.     <artifactId>pagehelper-spring-boot-starterartifactId>
    11.     <version>1.3.0version>
    12. dependency>
    14. <dependency>
    15.     <groupId>mysqlgroupId>
    16.     <artifactId>mysql-connector-javaartifactId>
    17.     <version>8.0.26version>
    18.     <scope>runtimescope>
    19. dependency>
    21. <dependency>
    22.     <groupId>com.oraclegroupId>
    23.     <artifactId>ojdbc6artifactId>
    24.     <version>11.2.0.3version>
    25. dependency>
    27. <dependency>
    28.     <groupId>cn.hutoolgroupId>
    29.     <artifactId>hutool-allartifactId>
    30.     <version>5.7.22version>
    31. dependency>
    33. <dependency>
    34.     <groupId>org.springframework.bootgroupId>
    35.     <artifactId>spring-boot-configuration-processorartifactId>
    36.     <optional>trueoptional>
    37. dependency>

    3.1.2 application.yml配置

            yml配置中配置了db1和db2两个数据源以及分页，db1对应MySQL，db2对应Oracle，如果不需要分页这里可以省去spring.pagehelper下的配置。

    1. spring:
    2.   datasource:
    3.     db1:
    4.       driver-class-name: com.mysql.cj.jdbc.Driver
    5.       jdbc-url: jdbc:mysql://localhost:3306/studb?characterEncoding=UTF-8&useUnicode=true
    6.       username: root
    7.       password: root
    8.     db2:
    9.       driver-class-name: oracle.jdbc.driver.OracleDriver
    10.       jdbc-url: jdbc:oracle:thin:@//localhost:1521/XE
    11.       username: root
    12.       password: root
    13. #  分页配置
    14.   pagehelper:
    15.     db1:
    16.       dialect: mysql
    17.       offsetAsPageNum: true
    18.       rowBoundsWithCount: true
    19.       reasonable: true
    20.       supportMethodsArguments: true
    21.       params: count=countSql;pageNum=pageNum;pageSize=pageSize;
    22.     db2:
    23.       dialect: oracle
    24.       offsetAsPageNum: true
    25.       rowBoundsWithCount: true
    26.       reasonable: true
    27.       supportMethodsArguments: true
    28.       params: count=countSql;pageNum=pageNum;pageSize=pageSize;

    3.1.3 yml中分页配置设置到对应Bean

            这里使用@ConfigurationProperties注解将yml配置文件中指定前缀的数据自动填充到Bean。

    （1）db1数据源的分页属性Bean

    1. @Data
    2. @Component
    3. @ConfigurationProperties(prefix = "spring.pagehelper.db1")
    4. public class PrimaryMybatisProperties {
    5.     private String dialect;
    6.     private String offsetAsPageNum;
    7.     private String rowBoundsWithCount;
    8.     private String reasonable;
    9.     private String supportMethodsArguments;
    10.     private String params;
    11. }

    （2）db2数据源的分页属性Bean

    1. @Data
    2. @Component
    3. @ConfigurationProperties(prefix = "spring.pagehelper.db2")
    4. public class SecondMybatisProperties {
    5.     private String dialect;
    6.     private String offsetAsPageNum;
    7.     private String rowBoundsWithCount;
    8.     private String reasonable;
    9.     private String supportMethodsArguments;
    10.     private String params;
    11. }

    3.1.4 数据源配置

    （1）db1的数据源配置PrimaryDataSourceConfig.java

    注意：多数据源情况下这里使用@Primary注解指定默认使用的是db1。

    1. @Configuration
    2. @MapperScan(basePackages = "com.wen.mapper1", sqlSessionFactoryRef = "sqlSessionFactory1")
    3. public class PrimaryDataSourceConfig {
    4.     @Resource
    5.     private PrimaryMybatisProperties primaryMybatisProperties;
    6.  
    7.     @Bean(name = "dataSource1")
    8.     @Primary
    9.     @ConfigurationProperties(prefix = "spring.datasource.db1")
    10.     public DataSource dataSource1(){
    11.         return DataSourceBuilder.create().build();
    12.     }
    13.  
    14.     @Bean(name = "sqlSessionFactory1")
    15.     @Primary
    16.     public SqlSessionFactory sqlSessionFactory1(@Qualifier("dataSource1") DataSource dataSource1) throws Exception{
    17.         SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
    18.         sessionFactoryBean.setDataSource(dataSource1);
    19.         String locationPattern = "classpath*:mapper1/**/*.xml";
    20.         PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
    21.         sessionFactoryBean.setMapperLocations(resolver.getResources(locationPattern));
    22.         // mybatis
    23.         org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
    24.         configuration.setMapUnderscoreToCamelCase(true);
    25.         configuration.setLogImpl(StdOutImpl.class);
    26.         sessionFactoryBean.setConfiguration(configuration);
    27.         // 分页
    28.         Interceptor interceptor = new PageInterceptor();
    29.         Properties properties = new Properties();
    30.         properties.setProperty("helperDialect", primaryMybatisProperties.getDialect());
    31.         properties.setProperty("offsetAsPageNum", primaryMybatisProperties.getOffsetAsPageNum());
    32.         properties.setProperty("rowBoundsWithCount", primaryMybatisProperties.getRowBoundsWithCount());
    33.         properties.setProperty("reasonable", primaryMybatisProperties.getReasonable());
    34.         properties.setProperty("supportMethodsArguments",primaryMybatisProperties.getSupportMethodsArguments());
    35.         properties.setProperty("params",primaryMybatisProperties.getParams());
    36.         interceptor.setProperties(properties);
    37.         sessionFactoryBean.setPlugins(interceptor);
    38.         return sessionFactoryBean.getObject();
    39.     }
    40.     @Bean(name = "sqlSessionTemplate1")
    41.     @Primary
    42.     public SqlSessionTemplate sqlSessionTemplate1(@Qualifier("sqlSessionFactory1") SqlSessionFactory sqlSessionFactory1) {
    43.         return new SqlSessionTemplate(sqlSessionFactory1);
    44.     }
    45.  
    46.     @Bean(name = "dataSourceTransactionManager1")
    47.     @Primary
    48.     public DataSourceTransactionManager dataSourceTransactionManager1(@Qualifier("dataSource1") DataSource dataSource1){
    49.         return new DataSourceTransactionManager(dataSource1);
    50.  
    51.     }
    52. }

    （2）db2的数据源配置SecondDataSourceConfig.java

    1. @Configuration
    2. @MapperScan(basePackages = "com.wen.mapper2",sqlSessionFactoryRef = "sqlSessionFactory2")
    3. public class SecondDataSourceConfig {
    4.     @Resource
    5.     private SecondMybatisProperties secondMybatisProperties;
    6.  
    7.     @Bean(name = "dataSource2")
    8.     @Primary
    9.     @ConfigurationProperties(prefix = "spring.datasource.db2")
    10.     public DataSource dataSource2(){
    11.         return DataSourceBuilder.create().build();
    12.     }
    13.  
    14.     @Bean(name = "sqlSessionFactory2")
    15.     @Primary
    16.     public SqlSessionFactory sqlSessionFactory1(@Qualifier("dataSource2") DataSource dataSource2) throws Exception{
    17.         SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
    18.         sessionFactoryBean.setDataSource(dataSource2);
    19.         String locationPattern = "classpath*:mapper2/**/*.xml";
    20.         PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
    21.         sessionFactoryBean.setMapperLocations(resolver.getResources(locationPattern));
    22.         // mybatis下划线转驼峰
    23.         org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
    24.         configuration.setMapUnderscoreToCamelCase(true);
    25.         configuration.setLogImpl(StdOutImpl.class);
    26.         sessionFactoryBean.setConfiguration(configuration);
    27.         // 分页配置
    28.         Interceptor interceptor = new PageInterceptor();
    29.         Properties properties = new Properties();
    30.         properties.setProperty("helperDialect", secondMybatisProperties.getDialect());
    31.         properties.setProperty("offsetAsPageNum", secondMybatisProperties.getOffsetAsPageNum());
    32.         properties.setProperty("rowBoundsWithCount", secondMybatisProperties.getRowBoundsWithCount());
    33.         properties.setProperty("reasonable", secondMybatisProperties.getReasonable());
    34.         properties.setProperty("supportMethodsArguments",secondMybatisProperties.getSupportMethodsArguments());
    35.         properties.setProperty("params",secondMybatisProperties.getParams());
    36.         interceptor.setProperties(properties);
    37.         sessionFactoryBean.setPlugins(interceptor);
    38.         return sessionFactoryBean.getObject();
    39.     }
    40.     @Bean(name = "sqlSessionTemplate2")
    41.     @Primary
    42.     public SqlSessionTemplate sqlSessionTemplate1(@Qualifier("sqlSessionFactory2") SqlSessionFactory sqlSessionFactory2) {
    43.         return new SqlSessionTemplate(sqlSessionFactory2);
    44.     }
    45.  
    46.     @Bean(name = "dataSourceTransactionManager2")
    47.     @Primary
    48.     public DataSourceTransactionManager dataSourceTransactionManager1(@Qualifier("dataSource2") DataSource dataSource2){
    49.         return new DataSourceTransactionManager(dataSource2);
    50.  
    51.     }
    52. }

    注意：如果项目中有使用@Transactional事务注解，必须分别配置数据源的事务管理，以上代码中已经配置。

    3.1.5 启动类排除相关自动配置

            启动类排除数据源(DataSourceAutoConfiguration.class)、Mybatis(MybatisAutoConfiguration.class)、分页(PageHelperAutoConfiguration.class)的自动配置。

    1. @SpringBootApplication(exclude = {DataSourceAutoConfiguration.class, MybatisAutoConfiguration.class, PageHelperAutoConfiguration.class})
    2. public class Application {
    3.  
    4.     public static void main(String[] args) {
    5.         SpringApplication.run(Application.class, args);
    6.     }
    7.  
    8. }

    3.2 示例代码

    3.2.1 po

    src/main/java/com/wen/po

    1. @Data
    2. public class Student {
    3.     private Integer id;
    4.     private String name;
    5.     private Integer age;
    6.     private String gender;
    7.     private String address;
    8.     private Date birth;
    9. }
    1. @Data
    2. public class User {
    3.     private String id;
    4.     private String userName;
    5. }

    3.2.2 controller层

    src/main/java/com/wen/controller

    1. @RestController
    2. @RequestMapping("/student")
    3. public class StudentController {
    4.     @Resource
    5.     private StudentServiceI studentService;
    6.  
    7.     @GetMapping
    8.     public PageInfo queryByPage(int pageNum, int pageSize, Student student){
    9.         return studentService.queryByPage(pageNum,pageSize,student);
    10.     }
    11. }
    1. @RestController
    2. @RequestMapping("/user")
    3. public class UserController {
    4.     @Resource
    5.     private UserServiceI userService;
    6.  
    7.     @GetMapping
    8.     public PageInfo queryByPage(int pageNum, int pageSize, User user){
    9.         return userService.queryByPage(pageNum,pageSize,user);
    10.     }
    11. }

    3.2.3 service层

    src/main/java/com/wen/service

    1. public interface StudentServiceI {
    2.     PageInfo queryByPage(int pageNum, int pageSize, Student student);
    3.  
    4. }
    1. @Service
    2. public class StudentServiceImpl implements StudentServiceI {
    3.     @Resource
    4.     private StudentMapper studentMapper;
    5.     @Override
    6.     public PageInfo queryByPage(int pageNum, int pageSize, Student student) {
    7.         if (pageNum <= 0) {
    8.             pageNum = 1;
    9.         }
    10.         if (pageSize <= 0) {
    11.             pageSize = 10;
    12.         }
    13.         if(ObjectUtil.isEmpty(student)){
    14.             student = new Student();
    15.         }
    16.         PageMethod.startPage(pageNum,pageSize);
    17.         List students = studentMapper.selectByPage(student);
    18.         PageInfo pageInfo = new PageInfo<>(students);
    19.         return pageInfo;
    20.     }
    21. }
    1. public interface UserServiceI {
    2.     PageInfo queryByPage(int pageNum, int pageSize, User user);
    3. }
    1. @Service
    2. public class UserServiceImpl implements UserServiceI {
    3.     @Resource
    4.     private UserMapper userMapper;
    5.     @Override
    6.     public PageInfo queryByPage(int pageNum, int pageSize, User user) {
    7.         if (pageNum <= 0) {
    8.             pageNum = 1;
    9.         }
    10.         if (pageSize <= 0) {
    11.             pageSize = 10;
    12.         }
    13.         if(ObjectUtil.isEmpty(user)){
    14.             user = new User();
    15.         }
    16.         PageMethod.startPage(pageNum,pageSize);
    17.         List users = userMapper.selectByPage(user);
    18.         PageInfo pageInfo = new PageInfo<>(users);
    19.         return pageInfo;
    20.     }
    21. }

    3.2.4 mapper层

            注意不同数据源对应的mapper，数据源配置类中@MapperScan对应的属性basePackages = "com.wen.mapper2"指定数据源对应的mapper。

    （1）db1数据源对应的mapper层

    src/main/java/com/wen/mapper1/StudentMapper.java

    1. @Mapper
    2. public interface StudentMapper {
    3.     List selectByPage(@Param("student") Student student);
    4. }

    src/main/resources/mapper1/StudentMapper.xml

    1. "1.0" encoding="UTF-8"?>
    2. mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    3. <mapper namespace="com.wen.mapper1.StudentMapper">
    4.     <sql id="student">
    5.         id,name,age,gender,address,birth
    6.     sql>
    7.     <select id="selectByPage" resultType="com.wen.po.Student">
    8.         select
    9.         <include refid="student"/>
    10.         from student
    11.         <where>
    12.             <if test="student.id!=null">
    13.                 and id = #{student.id}
    14.             if>
    15.             <if test="student.name!=null and student.name!=''">
    16.                 <bind name="stuName" value="'%' + student.name + '%'"/>
    17.                 and name like #{stuName}
    18.             if>
    19.         where>
    20.     select>
    21. mapper>

    （2）db2数据源对应的mapper层

    src/main/java/com/wen/mapper2/UserMapper.java

    1. @Mapper
    2. public interface UserMapper {
    3.     List selectByPage(@Param("user") User user);
    4. }

    src/main/resources/mapper2/UserMapper.xml

    1. "1.0" encoding="UTF-8"?>
    2. mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    3. <mapper namespace="com.wen.mapper2.UserMapper">
    4.     <sql id="user">
    5.         id,user_name
    6.     sql>
    7.     <select id="selectByPage" resultType="com.wen.po.User">
    8.         select
    9.         <include refid="user"/>
    10.         from sys_user
    11.         <where>
    12.             <if test="user.id!=null">
    13.                 and id = #{student.id}
    14.             if>
    15.             <if test="user.userName!=null and user.userName!=''">
    16.                 <bind name="userName" value="'%' + user.userName + '%'"/>
    17.                 and user_name like #{userName}
    18.             if>
    19.         where>
    20.     select>
    21. mapper>

    3.3 测试

            使用Postman进行测试，如下：

    （1）使用db1数据源

    （2）使用db2数据源

     

  • 相关阅读:
    Obsidian+SyncTrayzor打造个人文档云同步平台
    纷享销客罗旭对话旷视唐文斌：数字化的AI革命之路
    数据结构预算法——刷题记录二
    《Linux驱动：使用音频设备驱动框架-OSS构建音频设备驱动》
    泊松随机变量的分解与求和
    高并发、多线程、分布式都不懂，你拿什么跳槽阿里、腾讯、京东？还不好好学习啊
    指针及其应用
    【第三章：Java开发岗：Redis篇】
    HDLbits: Dualedge
    Elasticsearch集群运维，重平衡、分片、宕节点、扩容
  • 原文地址：https://blog.csdn.net/weixin_48568302/article/details/133884651