Springboot2.0 + Druid 多数据源配置（Mysql）

最新推荐文章于 2024-05-04 00:45:13 发布

chenwen7800

最新推荐文章于 2024-05-04 00:45:13 发布

阅读量673

点赞数

文章标签： java 数据库

原文链接：https://my.oschina.net/u/3905676/blog/1844053

版权

#欢迎转载-原博客地址：https://my.oschina.net/u/3905676/blog/1844053

注释：druid强依赖与log4j，需要引入log4j、

由于项目的需要，使用的SpringData jpa进行数据库的,进行快速开发

依赖maven:

<dependency>
 <groupId>log4j</groupId>
 <artifactId>log4j</artifactId>
 <version>1.2.16</version>
 <scope>compile</scope>
</dependency>

配置Springboot的yml文件

spring:
  jackson:
    time-zone: GMT+8
    date-format: yyyy-MM-dd HH:mm:ss
    joda-date-time-format: yyyy-MM-dd HH:mm:ss
  jpa:
    show-sql: true
     #InnoDB才能支持事务
    database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
    hibernate:
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
  datasource:
#     主数据库
    primary:
      url: jdbc:mysql://xxx.xx.xx.xx:3306/dbName?useUnicode=true&characterEncoding=utf8&generateSimpleParameterMetadata=true&zeroDateTimeBehavior=convertToNull
      username: root
      password: root
      driver-class-name: com.mysql.jdbc.Driver    
#     从数据库
    secondary:
      url: jdbc:mysql://xxx.xx.xx.xx:3306/dbName?useUnicode=true&characterEncoding=utf8&generateSimpleParameterMetadata=true&zeroDateTimeBehavior=convertToNull
      username: root
      password: root
      driver-class-name: com.mysql.jdbc.Driver

DruidDBConfig：配置druid

import com.alibaba.druid.pool.DruidDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.ServletComponentScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
 
import javax.sql.DataSource;
import java.sql.SQLException;
 
/**
 * @author idemon wrote on 2018/7/11.
 * 数据库连接属性配置
 */
@ServletComponentScan
@Configuration
public class DruidDBConfig {
    private Logger logger = LoggerFactory.getLogger(DruidDBConfig.class);
 
    @Value("${spring.datasource.primary.url}")
    private String dbUrl1;
 
    @Value("${spring.datasource.primary.username}")
    private String username1;
 
    @Value("${spring.datasource.primary.password}")
    private String password1;
 
    @Value("${spring.datasource.secondary.username}")
    private String username2;
 
    @Value("${spring.datasource.secondary.password}")
    private String password2;
 
    @Value("${spring.datasource.secondary.url}")
    private String dbUrl2;
 
    @Value("com.mysql.jdbc.Driver")
    private String driverClassName;
 
    @Value("5")
    private int initialSize;
 
    @Value("5")
    private int minIdle;
 
    @Value("20")
    private int maxActive;
 
    @Value("60000")
    private int maxWait;
 
    /**
     * 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
     */
    @Value("60000")
    private int timeBetweenEvictionRunsMillis;
    /**
     * 配置一个连接在池中最小生存的时间，单位是毫秒
     */
    @Value("300000")
    private int minEvictableIdleTimeMillis;
 
    @Value("SELECT 1 FROM DUAL")
    private String validationQuery;
 
    @Value("true")
    private boolean testWhileIdle;
 
    @Value("false")
    private boolean testOnBorrow;
 
    @Value("false")
    private boolean testOnReturn;
 
    /**
     * 打开PSCache，并且指定每个连接上PSCache的大小
     */
    @Value("true")
    private boolean poolPreparedStatements;
 
    @Value("20")
    private int maxPoolPreparedStatementPerConnectionSize;
    /**
     * 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
     */
    @Value("stat,wall,log4j")
    private String filters;
    /**
     * 通过connectProperties属性来打开mergeSql功能；慢SQL记录
     */
    @Value("druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500")
    private String connectionProperties;
 
    @Bean(name = "primaryDataSource")
    @Qualifier("primaryDataSource")
    public DataSource dataSource() {
        return getDruidDataSource(username1, password1, dbUrl1);
    }
 
    @Bean(name = "secondaryDataSource")
    @Qualifier("secondaryDataSource")
    @Primary
    public DataSource secondaryDataSource() {
        return getDruidDataSource(username2, password2, dbUrl2);
    }
 
    private DruidDataSource getDruidDataSource(String username, String password, String url) {
        DruidDataSource datasource = new DruidDataSource();
 
        datasource.setUrl(url);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
 
        //configuration
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        datasource.setPoolPreparedStatements(poolPreparedStatements);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
            logger.error("druid configuration initialization filter : {0}", e);
        }
        datasource.setConnectionProperties(connectionProperties);
 
        return datasource;
    }
}

DataSourceConfig: 数据源引入两个数据源

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.context.annotation.Primary;

import javax.sql.DataSource;


@Configuration
public class DataSourceConfig {
    @Bean(name = "primaryDataSource")
    @Qualifier("primaryDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDatasource() {
        return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();
    }
    
    @Bean(name = "secondaryDataSource")
    @Qualifier("secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().type(com.alibaba.druid.pool.DruidDataSource.class).build();
    }

}

PrimaryConfig：主数据源

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
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.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef="entityManagerFactoryPrimary",
        transactionManagerRef="transactionManagerPrimary",
        basePackages= { "com.xxx.tender.dao.xxxx" }) //设置Repository所在位置
public class PrimaryConfig {
    
    @Resource
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;
    
    @Primary
    @Bean(name = "entityManagerPrimary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
    }
    
    @Resource
    private JpaProperties jpaProperties;
    
    private Map<String, Object> getVendorProperties() {
        return jpaProperties.getHibernateProperties(new HibernateSettings());
    }
    
    /**
     * 设置实体类所在位置
     */
    @Primary
    @Bean(name = "entityManagerFactoryPrimary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(primaryDataSource)
                .packages("com.xxxx.tender.dao.xxxxx.dataobject")
                .persistenceUnit("primaryPersistenceUnit")
                .properties(getVendorProperties())
                .build();
    }
    
    @Primary
    @Bean(name = "transactionManagerPrimary")
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }
    
}

SecondaryConfig：第二个数据源

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
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.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef="entityManagerFactorySecondary",
        transactionManagerRef="transactionManagerSecondary",
        basePackages= { "com.xxxx.tender.xxxx.log" }) //设置Repository所在位置
public class SecondaryConfig {
    
    @Resource
    @Qualifier("secondaryDataSource")
    private DataSource secondaryDataSource;
    
    @Bean(name = "entityManagerSecondary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactorySecondary(builder).getObject().createEntityManager();
    }
    
    @Resource
    private JpaProperties jpaProperties;
    
    private Map<String, Object> getVendorProperties() {
        return jpaProperties.getHibernateProperties(new HibernateSettings());
    }
    
    @Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(secondaryDataSource)
                .packages("com.xxxx.tender.dao.xxxx.dataobject")
                .persistenceUnit("secondaryPersistenceUnit")
                .properties(getVendorProperties())
                .build();
    }
    
    @Bean(name = "transactionManagerSecondary")
    PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
    }
    
}

配置文件就到这了，接下来怎们就可以正常使用两个库了。

注意在不同的库配置不同的Repository时候，需要在配置里面添加，或者区分package,是主库文件还是从库文件。

以下是dao层融入jpa的写法，修改的时候需要添加@Modifying注解

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Transactional
@Repository
public interface TestMapperDAO extends JpaRepository<TestMapperDO, Integer>, JpaSpecificationExecutor<TestMapperDO> {

	void deleteByNum(String num);

	TestMapperDO findByNum(String num);

	@Modifying
    @Query(value="update TestMapperDO tm set tm.state=:state where tm.num=:num")
    int updateState(@Param(value="num") String num,@Param(value="state") String state);
    
}

在实体类中需要制定表名和字段，

在Springboot2.0中id自增需要使用@GeneratedValue(strategy= GenerationType.IDENTITY)

import javax.persistence.*;
import java.util.Date;
import lombok.Getter;
import lombok.Setter;

@Entity
@Table(name = "test_mapper")
@Getter
@Setter
public class TestMapperDO {
    /**
     * 自增主键
     */
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private Integer id;
    @Column(name = "num")
    private String num;
    
    @Column(name = "task_name")
    private String taskName;
    @Column(name = "token")
    private String token;
    @Column(name = "state")
    private String state;
    @Column(name = "count_now")
    private Integer countNow;
    @Column(name = "percentage")
    private String percentage;
    @Column(name = "gmt_create_time")
    private Date gmtCreateTime;

}

转载于:https://my.oschina.net/u/3905676/blog/1844053