springboot干货——（十二【二】）springboot整合postgresql(maxcompute多数据源)

亦凉 2021-09-28 07:06 2699阅读 0赞

## 前言 ##

应最近公司项目需要，需要将部分数据量特别大的查询直接走阿里的maxcompute，小数据量的查询走mysql，故在原来的项目上集成了多数据源。

关于springboot多数据源的整合可以参考之前的博客[springboot干货——（十二）多数据源配置之mybatis][springboot_mybatis]，本篇博客主讲如何通过mybatis集成maxcompute

## 1.pom.xml中加入相关依赖 ##

<dependency>
    			<groupId>org.postgresql</groupId>
    			<artifactId>postgresql</artifactId>
    			<version>42.2.5</version>
    		</dependency>
    
    		<dependency>
    			<groupId>com.alibaba</groupId>
    			<artifactId>druid</artifactId>
    			<version>1.1.12</version>
    		</dependency>

## 2.properties配置文件 ##

配置文件加入如下内容

#mysql配置
    spring.datasource.primary.url=jdbc:mysql://XXXX:XXXX/XXX?rewriteBatchedStatements=true&autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false
    spring.datasource.primary.username=XXXX
    spring.datasource.primary.password=XXX
    spring.datasource.primary.driver-class-name=com.mysql.jdbc.Driver
    
    #maxcompute数据库
    spring.datasource.secondary.url=jdbc:postgresql://lightning.cn-shanghai.maxcompute.aliyun.com:443/datasourceName?prepareThreshold=0
    spring.datasource.secondary.username=XXXXXX
    spring.datasource.secondary.password=XXXXXX
    spring.datasource.secondary.driver-class-name=org.postgresql.Driver

注意：maxcompute上的url需要加入prepareThreshold=0，否则在多次请求的时候会报错！！！

## 3.相关代码位置 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d3ZDExNTQ5NzgzNTI_size_16_color_FFFFFF_t_70][]

## 4.DataSourceConfig ##

package com.XXXX.data.center.config;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import org.springframework.beans.factory.annotation.Qualifier;
    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 javax.sql.DataSource;
    
    /**
     * Description:
     * 作者：gu.weidong(Marco)
     * date:2019/3/11
     * ProjectName:data-center-server
     */
    
    @Configuration
    public class DataSourceConfig {
    
        @Bean(name = "primaryDataSource")
        @Qualifier("primaryDataSource")
        @ConfigurationProperties(prefix="spring.datasource.primary")
        public DataSource primaryDataSource() {
            DataSource dataSource = new DruidDataSource();
            return dataSource;
        }
    
        @Bean(name = "secondaryDataSource")
        @Qualifier("secondaryDataSource")
        @Primary
        @ConfigurationProperties(prefix="spring.datasource.secondary")
        public DataSource secondaryDataSource() {
            DataSource dataSource = new DruidDataSource();
            return dataSource;
        }
    }

## 5.PrimaryConfig ##

package com.XXX.data.center.config;
    
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    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.context.annotation.Primary;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import javax.sql.DataSource;
    
    /**
     * Description:
     * 作者：gu.weidong(Marco)
     * date:2019/3/11
     * ProjectName:data-center-server
     */
    
    @Configuration
    @MapperScan(basePackages = PrimaryConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
    public class PrimaryConfig {
        // 精确到 master 目录，以便跟其他数据源隔离
        static final String PACKAGE = "com.XXX.data.center.dao.mysql";
        static final String MAPPER_LOCATION = "classpath:mapper/mysql/**/*.xml";
    
        @Autowired
        @Qualifier("primaryDataSource")
        private DataSource primaryDataSource;
    
    
        @Bean(name = "masterTransactionManager")
        @Primary
        public DataSourceTransactionManager masterTransactionManager() {
            return new DataSourceTransactionManager(primaryDataSource);
        }
    
        @Bean(name = "masterSqlSessionFactory")
        @Primary
        public SqlSessionFactory masterSqlSessionFactory(@Qualifier("primaryDataSource") DataSource primaryDataSource)
                throws Exception {
            final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(primaryDataSource);
            sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources(PrimaryConfig.MAPPER_LOCATION));
            return sessionFactory.getObject();
        }
    }

## 6.SecondaryConfig ##

package com.XXX.data.center.config;
    
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    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.support.PathMatchingResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import javax.sql.DataSource;
    
    /**
     * Description:
     * 作者：gu.weidong(Marco)
     * date:2019/3/11
     * ProjectName:data-center-server
     */
    
    @Configuration
    @MapperScan(basePackages = SecondaryConfig.PACKAGE, sqlSessionFactoryRef = "clusterSqlSessionFactory")
    public class SecondaryConfig {
        // 精确到 cluster 目录，以便跟其他数据源隔离
        static final String PACKAGE = "com.XXX.data.center.dao.maxcompute";
        static final String MAPPER_LOCATION = "classpath:mapper/maxcompute/**/*.xml";
        @Autowired
        @Qualifier("secondaryDataSource")
        private DataSource secondaryDataSource;
    
        @Bean(name = "clusterTransactionManager")
        public DataSourceTransactionManager clusterTransactionManager() {
            return new DataSourceTransactionManager(secondaryDataSource);
        }
    
        @Bean(name = "clusterSqlSessionFactory")
        public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("secondaryDataSource") DataSource secondaryDataSource)
                throws Exception {
            final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(secondaryDataSource);
            sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources(SecondaryConfig.MAPPER_LOCATION));
            return sessionFactory.getObject();
        }
    }

## 7.FacadeApplication.java ##

package com.XXX.data.center.facade;
    
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.boot.builder.SpringApplicationBuilder;
    import org.springframework.boot.context.properties.EnableConfigurationProperties;
    import org.springframework.boot.web.support.SpringBootServletInitializer;
    import org.springframework.cloud.netflix.eureka.EnableEurekaClient;
    import org.springframework.cloud.netflix.feign.EnableFeignClients;
    import org.springframework.context.annotation.ComponentScan;
    import org.springframework.context.annotation.ComponentScans;
    import org.springframework.context.annotation.Configuration;
    
    
    /** 
     *  Description:
     *  作者：gu.weidong(Marco)
     *  date:2019年1月9日
     *  ProjectName:data-center-facade
     */
    @SpringBootApplication
    @ComponentScan("com.aikucun.data.center")
    @MapperScan(basePackages= {"com.XXX.data.center.dao"})
    @EnableEurekaClient
    @EnableFeignClients
    @Configuration
    @EnableConfigurationProperties
    public class FacadeApplication extends SpringBootServletInitializer {
    	public static void main(String[] args) {
    		SpringApplication.run(FacadeApplication.class, args);
    	}
    
    	@Override
    	protected SpringApplicationBuilder configure(SpringApplicationBuilder application) {
    		return application.sources(FacadeApplication.class);
    	}
    }

注意这边扫mapper的时候一定要扫到对应的包！！！

截止上面，相关的多数据源配置已经OK了，postgresql已经可以像mysql那样通过mybatis进行操作了！！！

这边是通过上面的PrimaryConfig和SecondConfig里面的包对应相应的数据源的，无需在额外处理，当然除了此种方式外还有jdbc的方式，这边暂时就不赘述了，网上资料应该有不少。

如有疑问或错误，可与我取得联系，谢谢！！！

[springboot_mybatis]: https://blog.csdn.net/gwd1154978352/article/details/79331176
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d3ZDExNTQ5NzgzNTI_size_16_color_FFFFFF_t_70]: /images/20210923/8753c688a83a41a6a66249dc38b0cdc3.png