gpt4 book ai didi

Spring 多数据源配置

转载 作者:知者 更新时间:2024-03-13 17:38:03 27 4
gpt4 key购买 nike

一 配置文件

1 修改前

# Specify datasource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://XXXX:3306/zzzzz?useUnicode\=true&characterEncoding\=UTF-8
spring.datasource.username=XXXX
spring.datasource.password=XXXX

spring.datasource.initial-size=5
spring.datasource.min-idle=5
spring.datasource.max-idle=10
spring.datasource.max-active=50
spring.datasource.max-wait=10000
spring.datasource.validation-query=SELECT 1
spring.datasource.test-while-idle=true
spring.datasource.time-between-eviction-runs-millis=300000
spring.datasource.test-on-borrow=false
spring.datasource.test-on-return=false
spring.datasource.min-evictable-idle-time-millis=1800000

# Specify jpa
spring.jpa.database=MYSQL
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.hibernate.dialect=org.hibernate.dialect.MySQLDialect

2 修改后

# Specify datasource
# primary datasource
spring.datasource.primary.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.primary.url=jdbc:mysql://XXXX:3306/zzzzz?useUnicode\=true&characterEncoding\=UTF-8
spring.datasource.primary.username=XXXX
spring.datasource.primary.password=XXXX

# secondary datasource
spring.datasource.secondary.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.secondary.url=jdbc:mysql://XXXX:3306/iiiii?useUnicode\=true&characterEncoding\=UTF-8
spring.datasource.secondary.username=XXXX
spring.datasource.secondary.password=XXXX

# primary datasource
spring.datasource.primary.initial-size=5
spring.datasource.primary.min-idle=5
spring.datasource.primary.max-idle=10
spring.datasource.primary.max-active=50
spring.datasource.primary.max-wait=10000
spring.datasource.primary.validation-query=SELECT 1
spring.datasource.primary.test-while-idle=true
spring.datasource.primary.time-between-eviction-runs-millis=18800
spring.datasource.primary.test-on-borrow=false
spring.datasource.primary.test-on-return=false
spring.datasource.primary.min-evictable-idle-time-millis=1800000

# primary datasource
spring.datasource.secondary.initial-size=5
spring.datasource.secondary.min-idle=5
spring.datasource.secondary.max-idle=10
spring.datasource.secondary.max-active=50
spring.datasource.secondary.max-wait=10000
spring.datasource.secondary.validation-query=SELECT 1
spring.datasource.secondary.test-while-idle=true
spring.datasource.secondary.time-between-eviction-runs-millis=18800
spring.datasource.secondary.test-on-borrow=false
spring.datasource.secondary.test-on-return=false
spring.datasource.secondary.min-evictable-idle-time-millis=1800000

# Specify the DBMS
spring.jpa.database=MYSQL
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.hibernate.dialect=org.hibernate.dialect.MySQLDialect

二 配置文件

1 增加数据源配置文件

package com.project.common.config.dbSourceConfig;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 {
    // primaryDataSource
    @Bean(name = "primaryDataSource")
    @Qualifier("primaryDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        System.out.println("=================》数据源[1st]《===============");
        System.out.println("数据源 ----[zzzzz]---- 开始建立连接");
        return DataSourceBuilder.create().build();
    }

    // secondaryDataSource
    @Bean(name = "secondaryDataSource")
    @Qualifier("secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        System.out.println("=================》数据源[2nd]《===============");
        System.out.println("数据源 ----[iiiii]---- 开始建立连接");
        return DataSourceBuilder.create().build();
    }
}

2 增加 primaryDataSource 数据源配置文件

package com.project.common.config.dbSourceConfig;

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 javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;

@Configuration
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactoryPrimary",
        transactionManagerRef = "transactionManagerPrimary",
        basePackages = {"com.project.dao"}) //设置 dao 所在位置
public class PrimaryConfig {
    @Autowired
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;

    @Primary
    @Bean(name = "entityManagerPrimary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return (EntityManager) entityManagerFactoryPrimary(builder).getObject().createEntityManager();
    }

    @Primary
    @Bean(name = "entityManagerFactoryPrimary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(primaryDataSource)
                .properties(getVendorProperties(primaryDataSource))
                .packages("com.project.domain") // 设置实体类所在位置
                .persistenceUnit("primaryPersistenceUnit")
                .build();
    }

    @Autowired(required = false)
    private JpaProperties jpaProperties;

    private Map<String, Object> getVendorProperties(DataSource dataSource) {
        return jpaProperties.getHibernateProperties(new HibernateSettings());
    }

    @Primary
    @Bean(name = "transactionManagerPrimary")
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }
}

3 增加 secondaryDataSource 数据源配置文件

package com.project.common.config.dbSourceConfig;

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 javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;

@Configuration
@EnableJpaRepositories(
        entityManagerFactoryRef = "entityManagerFactorySecondary",
        transactionManagerRef = "transactionManagerSecondary",
        basePackages = {"com.project.dao.imms"}) //设置 dao 所在位置
public class SecondaryConfig {
    @Autowired(required = false)
    private JpaProperties jpaProperties;

    @Autowired
    @Qualifier("secondaryDataSource")
    private DataSource secondaryDataSource;

    @Bean(name = "entityManagerSecondary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactorySecondary(builder).getObject().createEntityManager();
    }

    @Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactorySecondary(EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(secondaryDataSource)
                .properties(getVendorProperties(secondaryDataSource))
                .packages("com.project.domain.imms") // 设置实体类所在位置
                .persistenceUnit("secondaryPersistenceUnit")
                .build();
    }

    private Map<String, Object> getVendorProperties(DataSource dataSource) {
        return jpaProperties.getHibernateProperties(new HibernateSettings());
    }

    @Bean(name = "transactionManagerSecondary")
    PlatformTransactionManager transactionManagerSecondary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactorySecondary(builder).getObject());
    }
}

三 修改 BaseServiceImpl

1 修改前

package com.project.common.service;

import com.project.common.exception.RuntimeException;
import com.project.common.util.RedisCacheUtil;
import com.project.common.util.SpecificationUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import static org.springframework.data.jpa.repository.query.QueryUtils.toOrders;

/*******************************************
* @class:BaseServiceImpl
* @desc:基础业务实现类
*******************************************/
public class BaseServiceImpl<T> {

    @Autowired
    public RedisCacheUtil cacheUtil;

    @Autowired
    private EntityManager em;

    /**
     * 原生 sql 查询方法
     *
     * @param sql
     * @return List<Map < String, Object>>
     */
    @SuppressWarnings("unchecked")
    public List<Map<String, Object>> nativeQuery(String sql) {
        Query query = em.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return query.getResultList();
    }

    /**
     * 原生 sql 查询方法
     *
     * @param sql
     * @param target
     * @return List<T>
     */
    @SuppressWarnings("unchecked")
    public List<T> nativeQueryT(String sql, Class<T> target) {
        Query query = em.createNativeQuery(sql, target);
        return query.getResultList();
    }

    /**
     * 原生 sql 修改删除
     *
     * @param sql
     * @return
     */
    public int nativeUpdate(String sql) {
        return em.createNativeQuery(sql).executeUpdate();
    }

    /**
     * @param searchMap
     * @param sort
     * @param target
     * @return
     */
    public List<T> customQueryT(Map<String, Object> searchMap, Sort sort, Class<T> target) {
        Specification<T> spec = new SpecificationUtils<T>().getSpecification(target, searchMap);
        CriteriaBuilder builder = em.getCriteriaBuilder();
        CriteriaQuery<T> query = builder.createQuery(target);
        Root<T> root = query.from(target);
        Predicate predicate = spec.toPredicate(root, query, builder);
        if (predicate != null) {
            query.where(predicate);
        }
        if (sort != null) {
            query.orderBy(toOrders(sort, root, builder));
        }
        return em.createQuery(query).getResultList();
    }
}

2 修改后

package com.project.common.service;

import com.project.common.exception.RuntimeException;
import com.project.common.util.RedisCacheUtil;
import com.project.common.util.SpecificationUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import static org.springframework.data.jpa.repository.query.QueryUtils.toOrders;

/*******************************************
* @class:BaseServiceImpl
* @desc:基础业务实现类
*******************************************/
public class BaseServiceImpl<T> {
    @Autowired
    public RedisCacheUtil cacheUtil;

    // 关键是这里修改了
    @PersistenceContext(unitName="entityManagerFactoryPrimary")
    EntityManager em;

    /**
     * 原生 sql 查询方法
     *
     * @param sql
     * @return List<Map < String, Object>>
     */
    @SuppressWarnings("unchecked")
    public List<Map<String, Object>> nativeQuery(String sql) {
        Query query = em.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return query.getResultList();
    }

    /**
     * 原生 sql 查询方法
     *
     * @param sql
     * @param target
     * @return List<T>
     */
    @SuppressWarnings("unchecked")
    public List<T> nativeQueryT(String sql, Class<T> target) {
        Query query = em.createNativeQuery(sql, target);
        return query.getResultList();
    }

    /**
     * 原生 sql 修改删除
     *
     * @param sql
     * @return
     */
    public int nativeUpdate(String sql) {
        return em.createNativeQuery(sql).executeUpdate();
    }

    /**
     * @param searchMap
     * @param sort
     * @param target
     * @return
     */
    public List<T> customQueryT(Map<String, Object> searchMap, Sort sort, Class<T> target) {
        Specification<T> spec = new SpecificationUtils<T>().getSpecification(target, searchMap);
        CriteriaBuilder builder = em.getCriteriaBuilder();
        CriteriaQuery<T> query = builder.createQuery(target);
        Root<T> root = query.from(target);
        Predicate predicate = spec.toPredicate(root, query, builder);
        if (predicate != null) {
            query.where(predicate);
        }
        if (sort != null) {
            query.orderBy(toOrders(sort, root, builder));
        }
        return em.createQuery(query).getResultList();
    }
}

四 增加 secondaryDataSource 对应的实体类

@Entity
@Table(name ="visit_customer")
public class IVisitCustomer implements java.io.Serializable {

    private static final long serialVersionUID = 5454155825314635342L;

    // columns START
    // 主键ID
    @Id
    @Column(length = 32)
    @ApiModelProperty(hidden = true)
    private java.lang.String id;

    // 预计拜访日期
    @Column(nullable = false)
    @ApiModelProperty(value = "预计拜访时间")
    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    private java.util.Date planVisitTime;
    ......
}

五 增加 secondaryDataSource 对应的dao

@Repository
public interface IVisitCustomerDao extends JpaRepository<IVisitCustomer, String>, JpaSpecificationExecutor<IVisitCustomer> {
    /**
     * 根据 Id 查询信息
     *
     * @param id
     * @return VisitCustomer
     */
    IVisitCustomer getById(java.lang.String id);

    /**
     * 根据id删除
     *
     * @param id
     */
    void deleteById(java.lang.String id);
}

六 修改涉及 secondaryDataSource 的接口

package com.project.service;

...

public interface VisitCustomerService {
    ...

    /**
     * 该接口涉及 secondaryDataSource
     */
    void addVisitCustomerImms(List<IVisitCustomer> ivclist);
}

七 修改涉及 secondaryDataSource 的实现类

package com.project.service.impl;

...
@Service
@CacheConfig(cacheNames = "VisitCustomer", keyGenerator = "smpkeyGenerator")
public class VisitCustomerServiceImpl extends BaseServiceImpl<VisitCustomer> implements VisitCustomerService {

    @Override
    @Transactional(value = "transactionManagerPrimary", rollbackFor = Exception.class) // Primary 数据源事务的注解
    public void addVisitCustomerCtrans(List<VisitCustomer> vclist) throws Exception {
        JSONObject obj = new JSONObject();
    }

    @Autowired
    IVisitCustomerDao ivisitCustomerDao;

    @Override
    @Transactional(value = "transactionManagerSecondary", rollbackFor = Exception.class) //  Secondary 数据源事务的注解
    public void addVisitCustomerImms(List<IVisitCustomer> list) {
        ivisitCustomerDao.saveAll(list);
    }
}

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com