Springboot+Mybatis配置多数据源使用PageHelper分页_springboot mssql分页

前言

公司项目由老的.net项目，迁移为现在的springcloud。迁移是个比较漫长的过程，导致公司现有数据源SqlServer和Mybatis两种。相对于单数据源，PageHelper分页插件，几乎不用而配置即可使用，而双数据源切换会导致语法报错。

PageHelper分页插件需要设定一个默认的数据库源，而SqlServer、mysql的部分语法不同。例如先用mysql查询时，分页插件默认使用mysql语句，切换sqlserver后，却依然使用mysql语法，导致查询失败。

例如,查询前一百条狗狗的姓名：

mysql:       SELECT name FROM tbl_dog LIMIT100;

sqlserver:  SELECT TOP 100 name FROM tbl_dog; 

解决思路：

   先配置多数据源，再配置多个SqlSessionFactory指定不同包路径下的mapper使用不同的数据源，此时不同的dao层就可以访问不同数据源。

一.配置数据源

    所用到的技术：springboot、mybatis、druid、mysql、sqlserver。

确保自己两个不同的数据库，本文以mysql和sqlserver为例（其他数据源原理相同）

❶ pom.xml中导入相关依赖插件

       <!--分页插件-->
        <dependency>
             <groupId>com.github.pagehelper</groupId>
             <artifactId>pagehelper-spring-boot-starter</artifactId>
             <version>1.2.5</version>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!--druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.9</version>
        </dependency>
        <!--mysql连接驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.39</version>
            <scope>runtime</scope>
        </dependency>
        <!--sql-server连接驱动-->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.0</version>
        </dependency>

❷ 配置application.yml

server:
  port: 8079
  tomcat:
    uri-encoding: UTF-8
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    dmysql:
      type: com.alibaba.druid.pool.DruidDataSource
      driverClassName: com.mysql.jdbc.Driver
      jdbcUrl: jdbc:mysql://(你的ip地址):3306/vipdb?useUnicode=true&characterEncoding=UTF-8&useSSL=true
      username: root
      password: root
    dsqlserver:
      type: com.alibaba.druid.pool.DruidDataSource
      driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
      jdbcUrl: jdbc:sqlserver://(你的ip地址):49508;Databasename=VIPDB
      username: root
      password: root

❸配置mysql，sqlsever

MySqlDataSourceConfig.java 文件（mysql）

@Configuration
@MapperScan(basePackages={"com.example.demo.mapper.*"}, sqlSessionFactoryRef="mysqlSessionFactory")
public class MySqlDataSourceConfig {
    @Value("${spring.datasource.dmysql.type")
    private String type;
 
    @Value("${spring.datasource.dmysql.driverClassName}")
    private String driverClass;
 
    @Value("${spring.datasource.dmysql.jdbcUrl}")
    private String url;
 
    @Value("${spring.datasource.dmysql.username}")
    private String username;
 
    @Value("${spring.datasource.dmysql.password}")
    private String password;
    /**
     * 配置数据源基本信息
     */
    @Primary
    @Bean(value = "mysqlDataSource")
    public DataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setDbType(type);
        datasource.setDriverClassName(driverClass);
        datasource.setUrl(url);
        datasource.setUsername(username);
        datasource.setPassword(password);
        return datasource;
    }
 
    @Bean(name = "mysqlTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }
 
    @Primary
    @Bean(name = "mysqlSessionFactory")
    public SqlSessionFactory mysqlSessionFactory(@Qualifier("mysqlDataSource") DataSource dataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        //分页插件
        Interceptor interceptor = new PageInterceptor();
        Properties properties = new Properties();
        //数据库
        properties.setProperty("helperDialect", "mysql");
        //是否分页合理化
        properties.setProperty("reasonable", "false");
        interceptor.setProperties(properties);
 
        sessionFactory.setPlugins(new Interceptor[] {interceptor});
        sessionFactory.setDataSource(dataSource);
        return sessionFactory.getObject();
    }
 
    @Bean(name = "mysqlSessionTemplate")
    @Primary
    public SqlSessionTemplate mysqlSessionTemplate(@Qualifier("mysqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

SqlServerDataSourceeConfig.java 文件 (sqlserver)

@Configuration
@MapperScan(basePackages = {"com.example.demo.mssql.mapper.*"}, sqlSessionFactoryRef = "mssqlSessionFactory")
public class SqlServerDataSourceeConfig {
    @Value("${spring.datasource.dsqlserver.type}")
    private String type;
 
    @Value("${spring.datasource.dsqlserver.driverClassName}")
    private String driverClass;
 
    @Value("${spring.datasource.dsqlserver.jdbcUrl}")
    private String url;
 
    @Value("${spring.datasource.dsqlserver.username}")
    private String username;
 
    @Value("${spring.datasource.dsqlserver.password}")
    private String password;
 
    @Bean(value = "mssqlDataSource")
    public DataSource dataSource(){
        DruidDataSource datasource = new DruidDataSource();
        datasource.setDbType(type);
        datasource.setDriverClassName(driverClass);
        datasource.setUrl(url);
        datasource.setUsername(username);
        datasource.setPassword(password);
        return datasource;
    }
 
    @Bean(name = "mssqlTransactionManager")
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }
 
    @Bean(name = "mssqlSessionFactory")
    public SqlSessionFactory mssqlSessionFactory(@Qualifier("mssqlDataSource") DataSource dataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        //分页插件
        Interceptor interceptor = new PageInterceptor();
        Properties properties = new Properties();
        //数据库
        properties.setProperty("helperDialect", "sqlserver2012");
        //是否分页合理化
        properties.setProperty("reasonable", "false");
 
        interceptor.setProperties(properties);
 
        sessionFactory.setPlugins(new Interceptor[] {interceptor});
        sessionFactory.setDataSource(dataSource);
        return sessionFactory.getObject();
    }
 
    @Bean(name = "mssqlSessionTemplate")
    public SqlSessionTemplate mssqlSessionTemplate(@Qualifier("mssqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

请确保@MapperScan中的basePackages 指定的mapper路径和本地一致

❹ 分页查询（以mysql为例）

//controller
@RestController
public class VipController {
    @Autowired
    private VipService vipService;
 
    @GetMapping("/vip/name")
    public Object hello() {
        List<String> list = vipService.findVipNameAll();
        return list;
    }
}
 
//service层
@Service
public class VipService {
    @Autowired
    private VipMapper vipMapper;
    public List<String> findVipNameAll() {
        //查询第一页，每页3条数据，根据id升序
        PageInfo<String> pageInfo = PageHelper.startPage(1, 3,"id asc").doSelectPageInfo(()
                -> vipMapper.getVipNameAll());
        return pageInfo.getList();
    }
}
 
//dao层/mapper（我用注解简化此步骤）
@Mapper
public interface VipMapper {
 
    @Select("SELECT name from ap_vip")
    List<String> getVipNameAll();
}

["VIP1","VIP2", "VIP3"]

这样就可以实现pagehelper针对不同包路径下的sql，进行不同的sql处理。

❻ Application启动类

1. 首先要将springboot自带的DataSourceAutoConfiguration禁掉，因为它会读取application.properties文件的spring.datasource.* 属性并自动配置单数据源。在@SpringBootApplication注解中添加exclude属性排除。
2. 因为系统启动的时候PageHelperAutoConfiguration会自动注册，在@SpringBootApplication注解中添加exclude属性排除自动配置。

项目地址：https://github.com/PureLeaves/springboot_datasource