java同时连多个数据库方式【JPA、Mybatis、JDBC】_java连接多个数据库

简介

java同时连多个数据库方式

同时连接固定数量的mysql【JPA】

config包

package com.test.cloud.config;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
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;

@Configuration
public class DataSourceConfig {


    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.cloud")
    DataSource dsCloud(){
        return DruidDataSourceBuilder.create().build();
    }


    @Bean
    @ConfigurationProperties("spring.datasource.udi")
    DataSource dsUDI(){
        return DruidDataSourceBuilder.create().build();
    }


}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

package com.test.cloud.config;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.annotation.Resource;
import javax.sql.DataSource;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "com.test.cloud.module.cloud.dao",
        entityManagerFactoryRef = "entityManagerFactoryBeanCloud",
        transactionManagerRef = "platformTransactionManagerCloud")
public class DataSourceCloud {

    @Resource(name="dsCloud")
    DataSource dsCloud;
    @Autowired
    JpaProperties jpaProperties;

    @Bean
    @Primary
        //创建entityManageFactoryBeanCloud 工厂实例,用来加载 EntityManageerFactoryBuilder对象
    LocalContainerEntityManagerFactoryBean entityManagerFactoryBeanCloud (EntityManagerFactoryBuilder builder){
        return builder.dataSource(dsCloud)
                .properties(jpaProperties.getProperties())
                .packages("com.test.cloud.module.cloud.bo")
                .persistenceUnit("puCloud")
                .build();
    }
    @Bean
    @Primary
    PlatformTransactionManager platformTransactionManagerCloud(EntityManagerFactoryBuilder builder){
        LocalContainerEntityManagerFactoryBean factoryBean =
                entityManagerFactoryBeanCloud(builder);//上一个@Bean注解的方法创造的实体管理工厂
        return new JpaTransactionManager(factoryBean.getObject());
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

package com.test.cloud.config;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.annotation.Resource;
import javax.sql.DataSource;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "com.test.cloud.module.udi.dao",
        entityManagerFactoryRef = "entityManagerFactoryBeanUDI",
        transactionManagerRef = "platformTransactionManagerUDI")
public class DataSourceUDI {

    @Resource(name="dsUDI")
    DataSource dsUDI;
    @Autowired
    JpaProperties jpaProperties;

    @Bean
        //创建entityManageFactoryBeanUDI 工厂实例,用来加载 EntityManageerFactoryBuilder对象
    LocalContainerEntityManagerFactoryBean entityManagerFactoryBeanUDI (EntityManagerFactoryBuilder builder){
        return builder.dataSource(dsUDI)
                .properties(jpaProperties.getProperties())
                .packages("com.test.cloud.module.udi.bo")
                .persistenceUnit("puUDI")
                .build();
    }
    @Bean
    PlatformTransactionManager platformTransactionManagerUDI(EntityManagerFactoryBuilder builder){
        LocalContainerEntityManagerFactoryBean factoryBean =
                entityManagerFactoryBeanUDI(builder);//上一个@Bean注解的方法创造的实体管理工厂
        return new JpaTransactionManager(factoryBean.getObject());
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

配置文件

spring:
  application:
    name: udi-sync
  main:
    web-application-type: none
  datasource:
    udi:
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:mysql://localhost:3306/udi?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true
      username: root
      password: root
    cloud:
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:mysql://localhost:3306/cloud2x_test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true
      username: root
      password: root
  jpa:
    show-sql: false
    properties:
      javax:
        persistence:
          sharedCache:
            mode: DISABLE_SELECTIVE
      hibernate:
        globally_quoted_identifiers: true
        connection:
          release_mode: on_close
        cache:
          use_second_level_cache: true
          use_query_cache: true
          region:
            factory_class: org.hibernate.cache.ehcache.EhCacheRegionFactory
          default_cache_concurrency_strategy: read-write
          use_structured_entries: true
          use_minimal_puts: true
        jdbc:
          batch_size: 100
        order_inserts: true
    servlet:
      multipart:
        max-file-size: 20MB
        max-request-size: 100MB
  activiti:
    check-process-definitions: false
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

同时连接固定数量的mysql【MyBatis】

config包

package com.test.bidatatask.config;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
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 org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
@Configuration
@EnableTransactionManagement //启用多事务
public class DataSourceConfig {
    @Bean
    @ConfigurationProperties("spring.datasource.dz")
    DataSource dsDZ(){
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.gz")
    DataSource dsGZ(){
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.bifx")
    DataSource dsBIFX(){
        return DruidDataSourceBuilder.create().build();
    }


    @Bean("platformTransactionManager")
    public PlatformTransactionManager platformTransactionManager(@Qualifier("dsBIFX") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Primary
    @Bean("dzPlatformTransactionManager")
    public PlatformTransactionManager dzPlatformTransactionManager(@Qualifier("dsDZ") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }


    @Primary
    @Bean("gzPlatformTransactionManager")
    public PlatformTransactionManager gzPlatformTransactionManager(@Qualifier("dsGZ") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }






}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

package com.test.bidatatask.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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
@MapperScan(value="com.test.bidatatask.mapperDZ",sqlSessionFactoryRef = "sqlSessionFactoryBeanDZ")//配置扫描器扫描
public class DataSourceDZ {

    @Autowired
    @Qualifier("dsDZ")
    DataSource dsDZ;
    @Bean
        //配置sqlSession实例
    SqlSessionFactory sqlSessionFactoryBeanDZ () throws Exception {
        SqlSessionFactoryBean  factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dsDZ);
        return factoryBean.getObject();
    }
    @Bean   //配置SqlSessionTemplate，传进去 sqlSessionFactoryBean1实例
    SqlSessionTemplate  sqlSessionTemplate() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryBeanDZ());
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

package com.test.bidatatask.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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
@MapperScan(value="com.test.bidatatask.mapperGZ",sqlSessionFactoryRef = "sqlSessionFactoryBeanGZ")//配置扫描器扫描
public class DataSourceGZ {

    @Autowired
    @Qualifier("dsGZ")
    DataSource dsGZ;
    @Bean
        //配置sqlSession实例
    SqlSessionFactory sqlSessionFactoryBeanGZ () throws Exception {
        SqlSessionFactoryBean  factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dsGZ);
        return factoryBean.getObject();
    }
    @Bean   //配置SqlSessionTemplate，传进去 sqlSessionFactoryBean1实例
    SqlSessionTemplate  sqlSessionTemplate() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryBeanGZ());
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

package com.test.bidatatask.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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
@MapperScan(value="com.test.bidatatask.mapperBIFX",sqlSessionFactoryRef = "sqlSessionFactoryBeanBIFX")//配置扫描器扫描
public class DataSourceBIFX {

    @Autowired
    @Qualifier("dsBIFX")
    DataSource dsBIFX;
    @Bean
        //配置sqlSession实例
    SqlSessionFactory sqlSessionFactoryBeanBIFX () throws Exception {
        SqlSessionFactoryBean  factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dsBIFX);
        return factoryBean.getObject();
    }
    @Bean   //配置SqlSessionTemplate，传进去 sqlSessionFactoryBean1实例
    SqlSessionTemplate  sqlSessionTemplate() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryBeanBIFX());
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

mapper例子

package com.test.bidatatask.mapperGZ;

import com.test.bidatatask.model.ParamModel;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;


/**
 * @description: 高值2.0数据访问
 * @author August
 * @date 2021/1/6 10:52
 * @version 1.0
 */
@Mapper
@Repository
public interface GZV2Mapper {


    /**
     *同步xx汇总表
     * @param paramModel
     */
    void insertSysApplySummary(ParamModel paramModel);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.test.bidatatask.mapperGZ.GZV2Mapper">

    <insert id="insertSysApplySummary" parameterType="com.test.bidatatask.model.ParamModel">
 </insert> 
</mapper>
1
2
3
4
5
6
7

同时连接多个可变数量的Mysql【JDBC】

1. 读取数据库配置
2. 初始化数据库连接池 DruidUtil.initDataSource
3. 根据数据库标识获取对应数据库连接池，执行sql DruidUtil.getConnection
4. 关闭数据库连接池 DruidUtil.close

数据库连接池工具类

package com.test.sync.out.util;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.test.sync.out.dto.SyncTaskConfig;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * @author August
 * @version 1.0
 * @description: 数据库连接池工具类
 * @date 2021/6/22 14:23
 */
@Slf4j
public class DruidUtil {

    /**
     * 多数据源的Map的存储对象
     */
    private static Map<Long, DruidDataSource> map = new HashMap<>();
    private final static String connectionProperties = "?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&nullCatalogMeansCurrent=true";

    /**
     * 功能描述： 初始化数据库连接池
     * @param config 待初始化的数据库配置信息
     */
    public static void initDataSource(SyncTaskConfig config) {
        Properties prop = new Properties();
        prop.setProperty("driver", "com.mysql.cj.jdbc.Driver");
        prop.setProperty("url", config.getDbUrl() + connectionProperties);
        //prop.setProperty("connectionProperties", "useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&nullCatalogMeansCurrent=true");
        prop.setProperty("username", config.getDbUsername());
        prop.setProperty("password", config.getDbPassword());
        // 设置数据连接池初始化连接池数量
        prop.setProperty("initialSize", "3");
        // 最大连接数
        prop.setProperty("maxActive", "50");
        prop.setProperty("minIdle", "3");
        // 连接最大等待时间60秒
        prop.setProperty("maxWait", "60000");
        prop.setProperty("filters", "stat");
        prop.setProperty("timeBetweenEvictionRunsMillis", "35000");
        prop.setProperty("minEvictableIdleTimeMillis", "30000");
        prop.setProperty("testWhileIdle", "true");
        prop.setProperty("testOnBorrow", "false");
        prop.setProperty("testOnReturn", "false");
        prop.setProperty("poolPreparedStatements", "false");
        prop.setProperty("maxPoolPreparedStatementPerConnectionSize", "200");
        prop.setProperty("removeAbandoned", "true");
        try {
            DruidDataSource dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(prop);
            map.put(config.getHId(), dataSource);
        } catch (Exception e) {
            log.error("初始化创建连接池失败！");
            log.error(e.getMessage(), e);
        }
    }


    /**
     * 获取数据库连接
     * @param id 即数据库ID hId
     * @return
     * @throws SQLException
     */
    public static DruidPooledConnection getConnection(Long id) throws SQLException {
        DruidDataSource source = map.get(id);
        //System.out.println("当前数据库连接池的量为：" + source.getActiveCount() + "---" + source.getActivePeak());
        return (DruidPooledConnection) source.getPooledConnection();
    }

    public static void close(Connection conn, Statement ps, ResultSet resultSet) throws SQLException {
        if(conn != null && !conn.isClosed()) {
            conn.close(); 
        }
        if(ps != null && !ps.isClosed()) {
            ps.close();
        }
        if(resultSet != null && !resultSet.isClosed()) {
            resultSet.close();
        }
    }

    public static void close(Connection conn, PreparedStatement ps) throws SQLException {
        if(conn != null && !conn.isClosed()) {
            conn.close(); 
        }
        if(ps != null && !ps.isClosed()) {
            ps.close();
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

配置信息

package com.test.sync.out.dto;

import com.alibaba.fastjson.JSON;
import lombok.Data;

import java.io.Serializable;
import java.util.List;

/**
 * @author August
 * @version 1.0
 * @description: 同步任务配置
 * @date 2021/6/22 9:49
 */
@Data
public class SyncTaskConfig implements Serializable {

    /**
     * 数据库ID
     */
    private Long hId;

    /**
     * 数据库名称
     */
    private String hName;

    /**
     * 定时任务启动延时 单位：毫秒
     */
    private Long taskInitialDelay;


    /**
     * 定时任务执行周期  单位：毫秒
     */
    private Long taskPeriod;

    /**
     * 请求X平台用户名
     */
    private String cloudUsername;


    /**
     * 请求X平台密码
     */
    private String cloudPassword;


    /**
     * 数据库 url
     */
    private String dbUrl;

    /**
     * 数据库 用户名
     */
    private String dbUsername;

    /**
     * 数据库 密码
     */
    private String dbPassword;




    public static void main(String[] args) {
        SyncTaskConfig syncTaskConfig = new SyncTaskConfig();
        System.out.println(syncTaskConfig.toString());
    }

    @Override
    public String toString() {
        return "{"
                + "\"hId\":"
                + hId
                + ",\"hName\":\""
                + hName + '\"'
                + ",\"taskInitialDelay\":"
                + taskInitialDelay
                + ",\"taskPeriod\":"
                + taskPeriod
                + ",\"cloudUsername\":\""
                + cloudUsername + '\"'
                + ",\"cloudPassword\":\""
                + cloudPassword + '\"'
                + ",\"dbUrl\":\""
                + dbUrl + '\"'
                + ",\"dbUsername\":\""
                + dbUsername + '\"'
                + ",\"dbPassword\":\""
                + dbPassword + '\"'
                + "}";
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

spring.application.name=sync-out
spring.main.web-application-type=none

#sync task config    time unit:millisecond
sys.task.sync.config=[{ "hId": 1, "hName": "测试数据库", "taskInitialDelay": 1000, "taskPeriod": 30000, "cloudUsername": "test", "cloudPassword": "111111", "dbUrl": "jdbc:mysql://localhost:3306/test_spd_v3", "dbUsername": "root", "dbPassword": "root" }]
#sync task thread pool size
sys.task.sync.corePoolSize=5
1
2
3
4
5
6
7

其他

		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
1
2
3
4
5
6
7
8
9
10