2021-01-28多数据源的动态切换（2）

最新推荐文章于 2024-07-31 11:17:00 发布

smile__tiger

最新推荐文章于 2024-07-31 11:17:00 发布

阅读量237

点赞数

分类专栏：知识总结文章标签： spring

本文链接：https://blog.csdn.net/smile__tiger/article/details/113353221

版权

知识总结专栏收录该内容

2 篇文章 0 订阅

订阅专栏

springBoot jpa 多数据源的动态切换

参考地址：https://blog.csdn.net/sunshine920103/article/details/50363315

此方式适合对数据库进行业务拆分的情况，比如基础数据在一个数据库，重要的业务数据在一个库，日志又是存在另外一个库，只要是把原本在一个数据库的数据进行拆分到多个数据库就适合此方式

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>net.guides.springboot2</groupId>
	<artifactId>springbootjpa-crudexample</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>springbootjpa-crudexample</name>
	<description>Demo project for Spring Boot</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.4.RELEASE</version>
		<relativePath />
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-actuator</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
		</dependency>
		<!--<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>-->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>


		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<scope>runtime</scope>
		</dependency>

		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>fastjson</artifactId>
			<version>1.2.73</version>
		</dependency>


		<!-- 学习mybatis使用 -->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.1.0</version>
		</dependency>

		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.0</version>
		</dependency>

<!--		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-databind</artifactId>
			<version>2.11.2</version>
		</dependency>-->

		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.18.12</version>
			<scope>provided</scope>
		</dependency>

		<dependency>
			<groupId>commons-logging</groupId>
			<artifactId>commons-logging</artifactId>
			<version>1.2</version>
		</dependency>

		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.17</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-redis</artifactId>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>

	    <resources>

			<resource>
				<directory>src/main/java</directory>
				<includes>
					<include>**/*.xml</include>
				</includes>
			</resource>
		</resources>
	</build>


</project>

application.properties

## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
#spring.datasource.primary.jdbc-url = jdbc:postgresql://172.16.176.132:5432/users_database?useSSL=false
#spring.datasource.primary.username = root
#spring.datasource.primary.password = 123
#spring.datasource.primary.driver-class-name=org.postgresql.Driver


#spring.datasource.secondary.jdbc-url = jdbc:postgresql://172.16.176.132:5432/postgres?useSSL=false
#spring.datasource.secondary.username = root
#spring.datasource.secondary.password = 123
#spring.datasource.secondary.driver-class-name=org.postgresql.Driver
## Hibernate Properties
# The SQL dialect makes Hibernate generate better SQL for the chosen database
#spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQL95Dialect
spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults = false
# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = update

info.app.name=Spring Boot - RestTemplate CRUD Rest Client Example
info.app.description=Spring Boot - RestTemplate CRUD Rest Client Example
info.app.version=1.0.0

spring.jpa.show-sql=true
spring.main.allow-bean-definition-overriding=true
#server.servlet.context-path=/springboot-crud-rest

server.port=8080

spring.redis.database=0
#max total instance of jedis
spring.redis.jedis.pool.max-active=300
#max idel instance of jedis
spring.redis.jedis.pool.max-idle=20
#if wait too long ,throw JedisConnectionException
spring.redis.jedis.pool.max-wait=500
#if true,it will validate before borrow jedis instance,what you get instance is all usefull
#spring.redis.=true
spring.redis.host=172.16.176.132

启动类：

package com.exmaple;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * @author admin
 */
/*@Configuration
@Configurable(autowire = Autowire.BY_NAME)
@EnableAutoConfiguration
@ComponentScan
@ImportResource("classpath:application-data.xml")*/
@SpringBootApplication
public class SpringCrudApplication {
	
	public static void main(String[] args) {
		SpringApplication.run(SpringCrudApplication.class, args);
	}
}

主配置：主配置和其他配置需要把注入到spring容器的名称进行区分；还有需要把repository,model的路径单独配置，这两个路径就是区分不同的数据源操作的接口和实体，配置时，特别注意；比如下面的配置（com.exmaple.repositoryp，com.exmaple.model）

package com.exmaple.configuration;

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

/**
 * @author xujiang
 * @date 2021/1/26 10:03
 * @Description: Primary数据源的JPA配置
 */
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef="entityManagerFactoryPrimary",
        transactionManagerRef="transactionManagerPrimary",
        basePackages= {"com.exmaple.repositoryp"})
public class PrimaryConfig {

    @Autowired
    @Qualifier(value = "primaryDataSource")
    private DataSource primaryDataSource;

    @Autowired
    private JpaProperties jpaProperties;

    @Autowired
    private HibernateProperties hibernateProperties;

    private Map<String, Object> getVendorProperties() {

        return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
    }

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

    @Primary
    @Bean(name = "entityManagerFactoryPrimary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary (EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(primaryDataSource)
                .packages("com.exmaple.model")
                .persistenceUnit("primaryPersistenceUnit")
                .properties(getVendorProperties())
                .build();
    }

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

第二个配置：实体类的包可在一个包路径下，也可分开在不同的包，根据数据源不同而分包。

package com.exmaple.configuration;

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

/**
 * @author xujiang
 * @date 2021/1/26 10:25
 * @Description: Secondary数据源的JPA配置
 */
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef="entityManagerFactorySecondary",
        transactionManagerRef="transactionManagerSecondary",
        basePackages= {"com.exmaple.repositoryu"})
public class SecondaryConfig {

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

    @Autowired
    private JpaProperties jpaProperties;
    @Autowired
    private HibernateProperties hibernateProperties;

    private Map<String, Object> getVendorProperties() {
        return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
    }

    @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)
                .packages("com.exmaple.model")
                .persistenceUnit("secondaryPersistenceUnit")
                .properties(getVendorProperties())
                .build();
    }

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

主配置的Repository

package com.exmaple.repositoryp;

import com.exmaple.model.Employee;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @author admin
 */
@Repository(value = "employeeRepositoryp")
@Transactional(rollbackFor = Exception.class)
public interface EmployeeRepository extends JpaRepository<Employee, String>{
    /**
     * 自定义原生的sql进行逻辑删除操作
     * @param id  employees表Id
     *
     * 结合博客和实际代码验证，执行修改和删除的sql语句时，需要加上  @Modifying 注解，如果不加，事务可提交，但是会提示报错--->
     * 说明无事务控制   综合：需要使用  @Modifying修饰此sql语句执行的时修改或者删除操作，并且需要事务控制（是新增方法在新的类，无法受到父类和接口注解的事务控制，
     *  就算是控制，父类和接口都设置为readOnly类型，单独的方法需要事务注解再次覆盖）
     */
    @Query(value = "update employees set status = '1' where id = ?1 and status='0' ",nativeQuery = true)
    @Modifying
    void deleteEmpoyeeById(String id);

    /**
     * 命名参数的自自定义sql 进行模糊查询
     * @param name  名称首字母
     */
    @Query(value = "select t from Employee t where t.firstName like %?1%")
    List<Employee> findByNameLike(String name);


    List<Employee> findByIdContaining(String id, Pageable pageable);

    List<Employee> findByIdContainingAndStatusContaining(String id,int status,Pageable pageable);

   //此类定义的方法，对此博客功能点的验证不需要的哈；可删除

}

第二个数据源的接口：

package com.exmaple.repositoryu;

import com.exmaple.model.Users;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

/**
 * @author xujiang
 * @date 2021/1/28 16:08
 * @Description:
 */
@Repository
public interface UsersRepository extends JpaRepository<Users, String> {
}

主配置的实体：

package com.exmaple.model;

import org.hibernate.annotations.SQLDelete;

import javax.persistence.*;
import javax.validation.constraints.NotEmpty;
import java.io.Serializable;

/**
 * @author admin
 * 下面的注解是实现数据的逻辑删除
 *  还有SQLDeleteAll注解 @SQLUpdate @SQLInsert
 *
 *  @NotNull  NotBlank  NotEmpty 区别：
 *  @NotNull 验证是否为null,如果是“”可通过校验 ；可验证非String 类型
 *  NotBlank 只能验证String,null和“”都会被校验，需要字符串长度大于0
 *  NotEmpty 和NotBlank一致，
 */
@Entity
@Table(name = "employees")
//@Where(clause = "status = '0' ")
@SQLDelete(sql = "update employees set status = '1' where id = ?")
public class Employee implements Serializable {
	@NotEmpty(message = "id不能为空")
	private String id;
	private String firstName;
	private String lastName;
	private String emailId;
	private String status;
	
	public Employee() {
		
	}
	
	public Employee(String firstName, String lastName, String emailId) {
		this.firstName = firstName;
		this.lastName = lastName;
		this.emailId = emailId;
	}
	
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	
	@Column(name = "first_name", nullable = false)
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	
	@Column(name = "last_name", nullable = false)
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	
	@Column(name = "email_address", nullable = false)
	public String getEmailId() {
		return emailId;
	}

	public void setEmailId(String emailId) {
		this.emailId = emailId;
	}

	public void setStatus(String status) {
		this.status = status;
	}

	public String getStatus() {
		return status;
	}

	@Override
	public String toString() {
		return "Employee [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + ", emailId=" + emailId
				+ "]";
	}
	
}

第二个配置的实体：

package com.exmaple.model;

import lombok.Data;

import javax.persistence.*;

/**
 * @author xujiang
 * @date 2021/1/28 16:00
 * @Description:
 */
@Entity
@Data
@Table(name = "t_users")
public class Users {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private String id;
    @Column(name = "name", nullable = false)
    private String name;
}

配置多数据源：

package com.exmaple.configuration;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import javax.sql.DataSource;

/**
 * @author xujiang
 * @date 2021/1/25 17:45
 * @Description: 多数据源配置
 */
@Configuration
public class DataSourceConfig {

    @Primary
    @Bean
    //@ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource primaryDataSource() {
        DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
        driverManagerDataSource.setDriverClassName("org.postgresql.Driver");
        driverManagerDataSource.setUsername("root");
        driverManagerDataSource.setPassword("123");
        driverManagerDataSource.setUrl("jdbc:postgresql://172.16.176.132:5432/users_database?useSSL=false");
        return driverManagerDataSource;
    }

    @Bean
    //@ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource secondaryDataSource() {
        DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
        driverManagerDataSource.setDriverClassName("org.postgresql.Driver");
        driverManagerDataSource.setUsername("root");
        driverManagerDataSource.setPassword("123");
        driverManagerDataSource.setUrl("jdbc:postgresql://172.16.176.132:5432/nacos?useSSL=false");
        return driverManagerDataSource;
    }
}

访问的接口：

主配置：

package com.exmaple.controller;

import com.exmaple.exception.ResourceNotFoundException;
import com.exmaple.model.Employee;
import com.exmaple.repositoryp.EmployeeRepository;
import com.exmaple.service.EmployeeService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.http.ResponseEntity;
import org.springframework.validation.BindingResult;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
import javax.validation.Valid;
import javax.validation.constraints.NotBlank;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author admin
 */
@RestController
public class EmployeeController {

	public Logger logger=LoggerFactory.getLogger(this.getClass());

	@Autowired
	private EmployeeRepository employeeRepository;

	@GetMapping("/employees")
	public List<Employee> getAllEmployees() {
		return employeeRepository.findAll();
	}
}

第二配置：

package com.exmaple.controller;

import com.exmaple.model.Users;
import com.exmaple.repositoryu.UsersRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;

/**
 * @author xujiang
 * @date 2021/1/28 16:12
 * @Description:
 */
@RestController
public class UserController {

    @Autowired
    private UsersRepository usersRepository;


    @GetMapping("/users")
    public List<Users> getAllEmployees() {
        return usersRepository.findAll();
    }
}

访问http://localhost:8080/employees http://localhost:8080/users 会切换到不同的数据库进行查询，进而增删改查就会在对应的数据库进行，把数据库进行拆分；