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

清疚 2024-03-02 10:57 49阅读 0赞

## 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 ####

<dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.2</version>
    </dependency>
    
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper-spring-boot-starter</artifactId>
        <version>1.3.0</version>
    </dependency>
    
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.26</version>
        <scope>runtime</scope>
    </dependency>
    
    <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ojdbc6</artifactId>
        <version>11.2.0.3</version>
    </dependency>
    
    <dependency>
        <groupId>cn.hutool</groupId>
        <artifactId>hutool-all</artifactId>
        <version>5.7.22</version>
    </dependency>
    
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-configuration-processor</artifactId>
        <optional>true</optional>
    </dependency>

#### 3.1.2 application.yml配置 ####

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

spring:
      datasource:
        db1:
          driver-class-name: com.mysql.cj.jdbc.Driver
          jdbc-url: jdbc:mysql://localhost:3306/studb?characterEncoding=UTF-8&useUnicode=true
          username: root
          password: root
        db2:
          driver-class-name: oracle.jdbc.driver.OracleDriver
          jdbc-url: jdbc:oracle:thin:@//localhost:1521/XE
          username: root
          password: root
    #  分页配置
      pagehelper:
        db1:
          dialect: mysql
          offsetAsPageNum: true
          rowBoundsWithCount: true
          reasonable: true
          supportMethodsArguments: true
          params: count=countSql;pageNum=pageNum;pageSize=pageSize;
        db2:
          dialect: oracle
          offsetAsPageNum: true
          rowBoundsWithCount: true
          reasonable: true
          supportMethodsArguments: true
          params: count=countSql;pageNum=pageNum;pageSize=pageSize;

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

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

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

@Data
    @Component
    @ConfigurationProperties(prefix = "spring.pagehelper.db1")
    public class PrimaryMybatisProperties {
        private String dialect;
        private String offsetAsPageNum;
        private String rowBoundsWithCount;
        private String reasonable;
        private String supportMethodsArguments;
        private String params;
    }

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

@Data
    @Component
    @ConfigurationProperties(prefix = "spring.pagehelper.db2")
    public class SecondMybatisProperties {
        private String dialect;
        private String offsetAsPageNum;
        private String rowBoundsWithCount;
        private String reasonable;
        private String supportMethodsArguments;
        private String params;
    }

#### 3.1.4 数据源配置 ####

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

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

@Configuration
    @MapperScan(basePackages = "com.wen.mapper1", sqlSessionFactoryRef = "sqlSessionFactory1")
    public class PrimaryDataSourceConfig {
        @Resource
        private PrimaryMybatisProperties primaryMybatisProperties;
    
        @Bean(name = "dataSource1")
        @Primary
        @ConfigurationProperties(prefix = "spring.datasource.db1")
        public DataSource dataSource1(){
            return DataSourceBuilder.create().build();
        }
    
        @Bean(name = "sqlSessionFactory1")
        @Primary
        public SqlSessionFactory sqlSessionFactory1(@Qualifier("dataSource1") DataSource dataSource1) throws Exception{
            SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
            sessionFactoryBean.setDataSource(dataSource1);
            String locationPattern = "classpath*:mapper1/**/*.xml";
            PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            sessionFactoryBean.setMapperLocations(resolver.getResources(locationPattern));
            // mybatis
            org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
            configuration.setMapUnderscoreToCamelCase(true);
            configuration.setLogImpl(StdOutImpl.class);
            sessionFactoryBean.setConfiguration(configuration);
            // 分页
            Interceptor interceptor = new PageInterceptor();
            Properties properties = new Properties();
            properties.setProperty("helperDialect", primaryMybatisProperties.getDialect());
            properties.setProperty("offsetAsPageNum", primaryMybatisProperties.getOffsetAsPageNum());
            properties.setProperty("rowBoundsWithCount", primaryMybatisProperties.getRowBoundsWithCount());
            properties.setProperty("reasonable", primaryMybatisProperties.getReasonable());
            properties.setProperty("supportMethodsArguments",primaryMybatisProperties.getSupportMethodsArguments());
            properties.setProperty("params",primaryMybatisProperties.getParams());
            interceptor.setProperties(properties);
            sessionFactoryBean.setPlugins(interceptor);
            return sessionFactoryBean.getObject();
        }
        @Bean(name = "sqlSessionTemplate1")
        @Primary
        public SqlSessionTemplate sqlSessionTemplate1(@Qualifier("sqlSessionFactory1") SqlSessionFactory sqlSessionFactory1) {
            return new SqlSessionTemplate(sqlSessionFactory1);
        }
    
        @Bean(name = "dataSourceTransactionManager1")
        @Primary
        public DataSourceTransactionManager dataSourceTransactionManager1(@Qualifier("dataSource1") DataSource dataSource1){
            return new DataSourceTransactionManager(dataSource1);
    
        }
    }

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

@Configuration
    @MapperScan(basePackages = "com.wen.mapper2",sqlSessionFactoryRef = "sqlSessionFactory2")
    public class SecondDataSourceConfig {
        @Resource
        private SecondMybatisProperties secondMybatisProperties;
    
        @Bean(name = "dataSource2")
        @Primary
        @ConfigurationProperties(prefix = "spring.datasource.db2")
        public DataSource dataSource2(){
            return DataSourceBuilder.create().build();
        }
    
        @Bean(name = "sqlSessionFactory2")
        @Primary
        public SqlSessionFactory sqlSessionFactory1(@Qualifier("dataSource2") DataSource dataSource2) throws Exception{
            SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
            sessionFactoryBean.setDataSource(dataSource2);
            String locationPattern = "classpath*:mapper2/**/*.xml";
            PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            sessionFactoryBean.setMapperLocations(resolver.getResources(locationPattern));
            // mybatis下划线转驼峰
            org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
            configuration.setMapUnderscoreToCamelCase(true);
            configuration.setLogImpl(StdOutImpl.class);
            sessionFactoryBean.setConfiguration(configuration);
            // 分页配置
            Interceptor interceptor = new PageInterceptor();
            Properties properties = new Properties();
            properties.setProperty("helperDialect", secondMybatisProperties.getDialect());
            properties.setProperty("offsetAsPageNum", secondMybatisProperties.getOffsetAsPageNum());
            properties.setProperty("rowBoundsWithCount", secondMybatisProperties.getRowBoundsWithCount());
            properties.setProperty("reasonable", secondMybatisProperties.getReasonable());
            properties.setProperty("supportMethodsArguments",secondMybatisProperties.getSupportMethodsArguments());
            properties.setProperty("params",secondMybatisProperties.getParams());
            interceptor.setProperties(properties);
            sessionFactoryBean.setPlugins(interceptor);
            return sessionFactoryBean.getObject();
        }
        @Bean(name = "sqlSessionTemplate2")
        @Primary
        public SqlSessionTemplate sqlSessionTemplate1(@Qualifier("sqlSessionFactory2") SqlSessionFactory sqlSessionFactory2) {
            return new SqlSessionTemplate(sqlSessionFactory2);
        }
    
        @Bean(name = "dataSourceTransactionManager2")
        @Primary
        public DataSourceTransactionManager dataSourceTransactionManager1(@Qualifier("dataSource2") DataSource dataSource2){
            return new DataSourceTransactionManager(dataSource2);
    
        }
    }

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

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

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

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class, MybatisAutoConfiguration.class, PageHelperAutoConfiguration.class})
    public class Application {
    
        public static void main(String[] args) {
            SpringApplication.run(Application.class, args);
        }
    
    }

### 3.2 示例代码 ###

#### 3.2.1 po ####

src/main/java/com/wen/po

@Data
    public class Student {
        private Integer id;
        private String name;
        private Integer age;
        private String gender;
        private String address;
        private Date birth;
    }

@Data
    public class User {
        private String id;
        private String userName;
    }

#### 3.2.2 controller层 ####

src/main/java/com/wen/controller

@RestController
    @RequestMapping("/student")
    public class StudentController {
        @Resource
        private StudentServiceI studentService;
    
        @GetMapping
        public PageInfo<Student> queryByPage(int pageNum, int pageSize, Student student){
            return studentService.queryByPage(pageNum,pageSize,student);
        }
    }

@RestController
    @RequestMapping("/user")
    public class UserController {
        @Resource
        private UserServiceI userService;
    
        @GetMapping
        public PageInfo<User> queryByPage(int pageNum, int pageSize, User user){
            return userService.queryByPage(pageNum,pageSize,user);
        }
    }

#### 3.2.3 service层 ####

src/main/java/com/wen/service

public interface StudentServiceI {
        PageInfo<Student> queryByPage(int pageNum, int pageSize, Student student);
    
    }

@Service
    public class StudentServiceImpl implements StudentServiceI {
        @Resource
        private StudentMapper studentMapper;
        @Override
        public PageInfo<Student> queryByPage(int pageNum, int pageSize, Student student) {
            if (pageNum <= 0) {
                pageNum = 1;
            }
            if (pageSize <= 0) {
                pageSize = 10;
            }
            if(ObjectUtil.isEmpty(student)){
                student = new Student();
            }
            PageMethod.startPage(pageNum,pageSize);
            List<Student> students = studentMapper.selectByPage(student);
            PageInfo<Student> pageInfo = new PageInfo<>(students);
            return pageInfo;
        }
    }

public interface UserServiceI {
        PageInfo<User> queryByPage(int pageNum, int pageSize, User user);
    }

@Service
    public class UserServiceImpl implements UserServiceI {
        @Resource
        private UserMapper userMapper;
        @Override
        public PageInfo<User> queryByPage(int pageNum, int pageSize, User user) {
            if (pageNum <= 0) {
                pageNum = 1;
            }
            if (pageSize <= 0) {
                pageSize = 10;
            }
            if(ObjectUtil.isEmpty(user)){
                user = new User();
            }
            PageMethod.startPage(pageNum,pageSize);
            List<User> users = userMapper.selectByPage(user);
            PageInfo<User> pageInfo = new PageInfo<>(users);
            return pageInfo;
        }
    }

#### 3.2.4 mapper层 ####

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

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

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

@Mapper
    public interface StudentMapper {
        List<Student> selectByPage(@Param("student") Student student);
    }

src/main/resources/mapper1/StudentMapper.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="com.wen.mapper1.StudentMapper">
        <sql id="student">
            id,name,age,gender,address,birth
        </sql>
        <select id="selectByPage" resultType="com.wen.po.Student">
            select
            <include refid="student"/>
            from student
            <where>
                <if test="student.id!=null">
                    and id = #{student.id}
                </if>
                <if test="student.name!=null and student.name!=''">
                    <bind name="stuName" value="'%' + student.name + '%'"/>
                    and name like #{stuName}
                </if>
            </where>
        </select>
    </mapper>

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

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

@Mapper
    public interface UserMapper {
        List<User> selectByPage(@Param("user") User user);
    }

src/main/resources/mapper2/UserMapper.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="com.wen.mapper2.UserMapper">
        <sql id="user">
            id,user_name
        </sql>
        <select id="selectByPage" resultType="com.wen.po.User">
            select
            <include refid="user"/>
            from sys_user
            <where>
                <if test="user.id!=null">
                    and id = #{student.id}
                </if>
                <if test="user.userName!=null and user.userName!=''">
                    <bind name="userName" value="'%' + user.userName + '%'"/>
                    and user_name like #{userName}
                </if>
            </where>
        </select>
    </mapper>

### 3.3 测试 ###

使用Postman进行测试，如下：

（1）使用db1数据源

![326fb4c28eca4d308c212a69b0393ae4.png][]

（2）使用db2数据源

![89b3a736ffe94224a9fd77c9ea1fe159.png][]

[326fb4c28eca4d308c212a69b0393ae4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/02/7ab6475e48f0433cb3826c19c052ce54.png
[89b3a736ffe94224a9fd77c9ea1fe159.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/02/f14f678ab6b14d79ab791bc881dfdd2f.png