springBpoot配置双数据源以及分页问题

一、application.properties配置

#解决双数据源分页问题
pagehelper.reasonable=false
pagehelper.supportMethodsArguments=true
pagehelper.params.count=countSql
pagehelper.auto-dialect=true
#这个一定要加上，不然mysql和oracle分页两个只能用一个，另一个会报错，加上后，两中数据库分页都可以用了
pagehelper.auto-runtime-dialect=true

spring.datasource.view.driverClassName=com.mysql.cj.jdbc.Driver
#spring.datasource.view.url=jdbc:mysql://10.20.16.xxx:3306/ibrain?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=CTT
#spring.datasource.view.username=root
#spring.datasource.view.password=abc123456
spring.datasource.view.test-while-idle=true
spring.datasource.view.test-on-borrow=false
spring.datasource.view.test-on-return=false
spring.datasource.view.validation-query=SELECT 1 FROM DUAL
spring.datasource.view.time-between-eviction-runs-millis=300000
spring.datasource.view.min-evictable-idle-time-millis=1800000
spring.datasource.view.initial-size=5
spring.datasource.view.max-active=50
spring.datasource.view.max-wait=120000
spring.datasource.view.min-idle=5


#配置oracle数据源
spring.datasource.oracle.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.oracle.jdbc-url=jdbc:oracle:thin:@132.151.19.xxx:1521/iob
spring.datasource.oracle.userName=iob_data
spring.datasource.oracle.password=Data_2021#
spring.datasource.oracle.postgres.time-between-eviction-runs-millis=300000
spring.datasource.oracle.min-evictable-idle-time-millis=1800000
spring.datasource.oracle.initial-size=5
spring.datasource.oracle.max-active=50
spring.datasource.oracle.max-wait=60000
spring.datasource.oracle.min-idle=5

二、config文件配置

@Configuration
@Component("dataSourceConfig")
public class DataSourceConfig {
    @Bean(name = "dbView")
    @ConfigurationProperties(prefix = "spring.datasource.view") // application.properteis中对应属性的前缀
    public DataSource dataSourcePortal() {
        return DataSourceBuilder.create().type(BasicDataSource.class).build();
    }


    @Bean(name = "oracle")
    @ConfigurationProperties(prefix = "spring.datasource.oracle") // application.properteis中对应属性的前缀
    public DataSource dataSourcePostgresPortal() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }
}

三、配置mysql文件

package com.asiainfo.view.config;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInterceptor;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.util.Properties;

@Configuration
@MapperScan(basePackages = {"com.asiainfo.view.dao.dbview"}, sqlSessionFactoryRef = "sqlSessionFactory")

public class MybatisDbViewConfig {
    @Autowired
    @Qualifier("dbView")
    private DataSource dbView;

    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory() throws Exception {
//        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
//        factoryBean.setDataSource(dbView);
//        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mybatis/mapper/dbview/*.xml"));
//        factoryBean.setConfigLocation(new DefaultResourceLoader().getResource("classpath:mybatis-config-mysql.xml"));

        MybatisSqlSessionFactoryBean sessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dbView);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mybatis/mapper/dbview/*.xml"));
        sessionFactoryBean.setConfigLocation(new DefaultResourceLoader().getResource("classpath:mybatis-config-mysql.xml"));

//        //分页插件
//        Interceptor interceptor = new PageInterceptor();
//        Properties properties = new Properties();
//        //数据库
//        properties.setProperty("helperDialect", "mysql");
//        //是否将参数offset作为PageNum使用
//        properties.setProperty("offsetAsPageNum", "true");
//        //是否进行count查询
//        properties.setProperty("rowBoundsWithCount", "true");
//        //是否分页合理化
//        // 注意的是reasonable参数，表示分页合理化，默认值为false。
//        //如果该参数设置为 true 时，pageNum<=0 时会查询第一页，pageNum>pages（超过总数时），会查询最后一页。默认false 时，直接根据参数进行查询。
//        properties.setProperty("reasonable", "false");
//        interceptor.setProperties(properties);

        sessionFactoryBean.setPlugins(new MapperInterceptor());
        return sessionFactoryBean.getObject();

//        return factoryBean.getObject();

    }

    @Bean(name = "sqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate() throws Exception {
        SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory());
        return template;
    }

    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dbView);
    }

//    /**
//     * 分页插件
//     * @return
//     */
//    @Bean
//    public PaginationInterceptor paginationInterceptor() {
//        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
//        // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false
//        // paginationInterceptor.setOverflow(false);
//        // 设置最大单页限制数量，默认 500 条，-1 不受限制
//        paginationInterceptor.setLimit(-1);
//        // 开启 count 的 join 优化,只针对部分 left join
//        // paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
//        return paginationInterceptor;
//    }
}

四、配置oracle文件

package com.asiainfo.view.config;

import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.github.pagehelper.PageInterceptor;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;
import java.util.Properties;


/**
 * @description:
 * @author mosong
 * @date: 2021/12/13 17:05
 */
@Configuration
@MapperScan(basePackages = {"com.asiainfo.view.dao.oracle"}, sqlSessionFactoryRef = "oraclesqlSessionFactory")
public class MybatisOracleConfig {
    @Autowired
    @Qualifier("oracle")
    private DataSource oracle;

    @Bean(name = "oraclesqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory() throws Exception {

        MybatisSqlSessionFactoryBean sessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(oracle);
        sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mybatis/mapper/oracle/*Mapper*.xml"));
        sessionFactoryBean.setConfigLocation(new DefaultResourceLoader().getResource("classpath:mybatis-config-mysql.xml"));

//        //分页插件
//        Interceptor interceptor = new PageInterceptor();
//        Properties properties = new Properties();
//        //数据库
//        properties.setProperty("helperDialect", "oracle");
//        //是否将参数offset作为PageNum使用
//        properties.setProperty("offsetAsPageNum", "true");
//        //是否进行count查询
//        properties.setProperty("rowBoundsWithCount", "true");
//        //是否分页合理化
//        // 注意的是reasonable参数，表示分页合理化，默认值为false。
//        //如果该参数设置为 true 时，pageNum<=0 时会查询第一页，pageNum>pages（超过总数时），会查询最后一页。默认false 时，直接根据参数进行查询。
//        properties.setProperty("reasonable", "false");
//        interceptor.setProperties(properties);

        sessionFactoryBean.setPlugins(new MapperInterceptor());
        return sessionFactoryBean.getObject();

    }

    @Bean(name = "oraclesqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate() throws Exception {
        SqlSessionTemplate template = new SqlSessionTemplate(sqlSessionFactory());
        return template;
    }

    @Bean(name = "oraclesTransactionManager")
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(oracle);
    }
}