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

简介

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();
    }


}

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());
    }
}

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());
    }
}

配置文件

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

同时连接固定数量的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);
    }






}

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());
    }

}

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());
    }

}

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());
    }
}

mapper例子

package com.test.bidatatask.mapperGZ;

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



@Mapper
@Repository
public interface GZV2Mapper {


    
    void insertSysApplySummary(ParamModel paramModel);
}

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

读取数据库配置
初始化数据库连接池 DruidUtil.initDataSource
根据数据库标识获取对应数据库连接池，执行sql DruidUtil.getConnection
关闭数据库连接池 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;


@Slf4j
public class DruidUtil {

    
    private static Map map = new HashMap<>();
    private final static String connectionProperties = "?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&nullCatalogMeansCurrent=true";

    
    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);
        }
    }


    
    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();
        }
    }
}

配置信息

package com.test.sync.out.dto;

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

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


@Data
public class SyncTaskConfig implements Serializable {

    
    private Long hId;

    
    private String hName;

    
    private Long taskInitialDelay;


    
    private Long taskPeriod;

    
    private String cloudUsername;


    
    private String cloudPassword;


    
    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 + '"'
                + "}";
    }
}

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

其他

		
            com.alibaba
            druid-spring-boot-starter
            1.1.10
        
        
            mysql
            mysql-connector-java
            runtime

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

Java相关栏目本月热门文章