SpringBoot整合mysql、postgres、sqlserver实现多数据源配置案例

• 数据库

        分享一下近期处理的一个小demo，关于配置多数据源实现不同服务之间的数据推送和数据治理。第一次接触到pg库和sqlserver一头雾水，选择了JDBC+mybatis-plus的方式去链接。

1、首先要引入以下依赖

	
		
			org.springframework.boot
			spring-boot-starter-test
			test
		
		
			org.springframework.boot
			spring-boot-starter-web
		
		
			org.projectlombok
			lombok
			1.18.12
			true
		
		
			org.apache.httpcomponents
			httpclient
			4.5.10
		
		
			com.alibaba
			fastjson
			1.2.62
		
		
		
			mysql
			mysql-connector-java
			8.0.22
		
		
		
			org.postgresql
			postgresql
			runtime
		
		
		
			com.microsoft.sqlserver
			mssql-jdbc
			9.4.0.jre8
		
		
		
			org.springframework.boot
			spring-boot-starter-jdbc
		
		
			com.alibaba
			druid
			1.1.13
		
		
			com.baomidou
			mybatis-plus-boot-starter
			3.4.0
		
		
			cn.afterturn
			easypoi-base
			4.4.0
		
	

	
		
			
				org.springframework.boot
				spring-boot-maven-plugin
				2.7.12
				
					
					com.zkgl.ZsjDemoApplication
				
				
					
						
							
							repackage

2、demo的项目结构如下

SpringBoot整合mysql、postgres、sqlserver实现多数据源配置案例SpringBoot整合mysql、postgres、sqlserver实现多数据源配置案例

3、yml配置文件

server:
  port:3666
spring:
  application:
    name: multiple-data
  datasource:
    show-sql: false
    db1:
      jdbc-url: jdbc:mysql://ip地址:端口/数据库名?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true
      username: 数据库账号
      password: 数据库密码
      driver-class-name: com.mysql.cj.jdbc.Driver
    db2:
      jdbc-url: jdbc:postgresql://ip地址:端口/数据库名?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true
      username: 数据库账号
      password: 数据库密码
      driver-class-name: org.postgresql.Driver
    db3:
      jdbc-url: jdbc:sqlserver://ip地址:端口;databaseName=数据库名
      username: 数据库账号
      password: 数据库密码
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver

4、配置类

package com.zkgl.config;

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.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;
/**
 * @Author: 袁振豪
 * @Description:
 * @Date: Created in  2023-07-23 11:04 PM
 * @Modified By:
 */
@Configuration
@MapperScan(basePackages = "com.zkgl.dao.db1",sqlSessionFactoryRef = "db1SqlSessionFactory")
public class MysqlDataSourceConfig {
    static final String MAPPER_LOCATION = "classpath:/mapper/db1/*.xml";
    @Bean("db1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSource getDb1DataSource(){
        return DataSourceBuilder.create().build();
    }
    @Bean("db1SqlSessionFactory")
    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        return bean.getObject();
    }
    @Bean("db1SqlSessionTemplate")
    public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

package com.zkgl.config;

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.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;
/**
 * @Author: 袁振豪
 * @Description:
 * @Date: Created in  2023-07-23 11:04 PM
 * @Modified By:
 */
@Configuration
@MapperScan(basePackages = "com.zkgl.dao.db2",sqlSessionFactoryRef = "db2SqlSessionFactory")
public class PgDataSourceConfig {

    static final String MAPPER_LOCATION = "classpath:/mapper/db2/*.xml";
    @Bean("db2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource getDb2DataSource(){
        return DataSourceBuilder.create().build();
    }
    @Bean("db2SqlSessionFactory")
    public SqlSessionFactory db2SqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        return bean.getObject();
    }
    @Bean("db2SqlSessionTemplate")
    public SqlSessionTemplate db2SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

package com.zkgl.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Configuration;

/**
 * @Author: 袁振豪
 * @Description:
 * @Date: Created in  2023-07-23 11:04 PM
 * @Modified By:
 */
@Configuration
@MapperScan(basePackages = "com.zkgl.dao.db3",sqlSessionFactoryRef = "db3SqlSessionFactory")
public class SqlServerDataSourceConfig {
    static final String MAPPER_LOCATION = "classpath:/mapper/db3/*.xml";
    @Bean("db3DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db3")
    public DataSource getDb1DataSource(){
        return DataSourceBuilder.create().build();
    }
    @Bean("db3SqlSessionFactory")
    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db3DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
        return bean.getObject();
    }
    @Bean("db3SqlSessionTemplate")
    public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db3SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

5、controller、dao、service以及对应的.xml文件可以根据自身业务情况进行开发，再次不做过多赘述。

总结：要进行多数据源的配置，以此案例为例，最重要的是对各个库的适配，也就是各自的驱动。众所周知，要使用JDBC连接数据库，主要有以下步骤：

  1. 注册驱动
  2. 建立数据库连接
  3. 创建数据库操作对象
  4. 执行SQL语句
  5. 处理查询结果集
  6. 关闭资源 

而在本案例中，pom中引入了相关依赖，在yml配置了驱动，之后在config中以Bean的形式分别命名和初始化相关配置，这样在Springboot项目中，通过@SpringBootApplication注解中的@EnableAutoConfigtion注解就可以扫描到这些配置好的Bean，从而正常使用了。


                                                        
本文来自网络，不代表协通编程立场，如若转载，请注明出处：https://net2asp.com/95694f6aeb.html
                        

                        

                            

                            

                                
 赞 (0)


                                                            


                            

                                

                                                                        

                                        

                                                                                                                                                                            

                                        
                                        

                                            
                                        

                                    

                                

                            


                            

                                                                    

                                        
                                        

                                            « 上一篇
                                            1天前
                                        

                                    

                                                                                                    

                                        
                                        

                                            下一篇  »
                                            1天前
                                        

                                    

                                                            

                                                        
相关推荐