springboot配置多种类型多数据源（mysql+postgresql+presto（数据仓库））yml方式集成分页

声明：

此处示例为mysql和postgresql数据库和presto 三种类型数据库源

mysql与postgresql都属于数据库所以这里简写只配置postgresql库

presto是数据仓库在配置数据源测试时会遇到很多坑，等下详细描述

首先

声明：我项目的springboot配置数据源的文件是yml方式的，对于properties格式的请童鞋自行转换写法

第一步：yml配置数据源代码。yml编写必须注意格式缩进！！！

第二步pom文件引入的驱动

<!-- Postgresql 数据驱动 -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.4.1212</version>
        </dependency>

<dependency>
            <groupId>com.facebook.presto</groupId>
            <artifactId>presto-jdbc</artifactId>
            <version>0.203</version>
        </dependency>

 <!-- MySQL 连接驱动依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql-connector}</version>
        </dependency>

 

数据库的驱动。presto的驱动必须是此groupId的否则无法对应请注意

 

第三步配置springboot加载数据源到spring中

1配置postgresql数据源 注意 @ConfigurationProperties(prefix = "postgresql.spring.datasource.pa")要与yml中配置一致

import com.github.pagehelper.PageHelper;
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.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
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 javax.sql.DataSource;
import java.util.Properties;
/**
 * @ClassName DataSourcePostgresqlConfig
 * @Description 配置postgresql数据源
 * @Author qixin
 * @Date 2018/7/6 10:14
 * Version 1.0
 **/
/*pa数据库*/
@Configuration
@MapperScan(basePackages = "com.renrenche.databus.dao.logdata", sqlSessionTemplateRef  = "paSqlSessionTemplate")
public class DataSourcePostgresqlConfig {
    private final static Logger logger = LoggerFactory.getLogger(DataSourcePostgresqlConfig.class);
    @Bean(name = "paDataSource")
    @ConfigurationProperties(prefix = "postgresql.spring.datasource.pa")
    @Primary
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "paSqlSessionFactory")
    @Primary /*此处必须在主数据库的数据源配置上加上@Primary*/
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("paDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        /*加载mybatis全局配置文件*/
        //bean.setConfigLocation(new PathMatchingResourcePatternResolver().getResource("classpath:mybatis/mybatis-config.xml"));
        /*加载所有的mapper.xml映射文件*/
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/logdata/*.xml"));
        //添加插件  （改为使用配置文件加载了）
        bean.setPlugins(new Interceptor[]{pageHelper()});
        logger.info("加载postgresql数据库连接......");
        return bean.getObject();
    }

    @Bean(name = "paTransactionManager")
    @Primary
    public DataSourceTransactionManager testTransactionManager(@Qualifier("paDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "paSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("paSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean
    public PageHelper pageHelper(){
       // logger.info("MyBatis分页插件PageHelper");
        //分页插件
        PageHelper pageHelper = new PageHelper();
        Properties properties = new Properties();
        properties.setProperty("offsetAsPageNum", "true");
        properties.setProperty("rowBoundsWithCount", "true");
        properties.setProperty("reasonable", "true");
        properties.setProperty("supportMethodsArguments", "true");
        properties.setProperty("returnPageInfo", "check");
        properties.setProperty("params", "count=countSql");
        pageHelper.setProperties(properties);
        return pageHelper;
    }

}

 

2配置另一个数据源presto。这里要重点说明一下因为我项目是springboot+mybatis，mybatis的集成presto数据仓库不支持预加载，所以配置之后如果用mybatis的xml写sql调用数据库查询数据必须配置一个参数

statementType="STATEMENT"  此处很重要！！！！！ 否则运行sql会报错抛异常

第二个数据源配置

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
/**
 * @ClassName DataSourcePrestoConfig
 * @Description 配置presto数据源
 * @Author qixin
 * @Date 2018/7/6 10:14
 * Version 1.0
 **/
/*marketing数据库*/
@Configuration
@MapperScan(basePackages = "com.renrenche.databus.dao.test", sqlSessionTemplateRef  = "marketingSqlSessionTemplate")
public class DataSourcePrestoConfig {
    private final static Logger logger = LoggerFactory.getLogger(DataSourcePrestoConfig.class);
    @Bean(name = "marketingDataSource")
    @ConfigurationProperties(prefix = "presto.spring.datasource.marketing")
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "marketingSqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("marketingDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        /*加载mybatis全局配置文件*/
        //bean.setConfigLocation(new PathMatchingResourcePatternResolver().getResource("classpath:mybatis/mybatis-config.xml"));
        /*加载所有的mapper.xml映射文件*/

        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/test/*.xml"));
        logger.info("加载presto数据库连接......");
        return bean.getObject();
    }

    @Bean(name = "marketingTransactionManager")
    public DataSourceTransactionManager testTransactionManager(@Qualifier("marketingDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "marketingSqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("marketingSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }


}

 

以上就是springboot配置两个数据源的yml+pom+config配置文件。只需将这三步配好，调试即可

注：配置config时包扫描路径一定要正确

 

结束

感谢观看！