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

妖狐艹你老母 2022-05-20 00:49 801阅读 0赞

## 声明： ##

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

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

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

首先

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

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

![70][]

## 第二步pom文件引入的驱动 ##

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

<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会报错抛异常

![70 1][]

第二个数据源配置

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时包扫描路径一定要正确

结束

感谢观看！

[70]: /images/20220520/9f58f0a50a5149cea53eaf32b13e3555.png
[70 1]: /images/20220520/e926b6098cd34bc682618a77784ebf59.png