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

最新推荐文章于 2023-02-13 17:27:04 发布

smile__tiger

最新推荐文章于 2023-02-13 17:27:04 发布

阅读量203

点赞数

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

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

版权

知识总结专栏收录该内容

2 篇文章 0 订阅

订阅专栏

springBoot jpa 多数据源的动态切换

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

下面是自己的demo实现；此实现方式能做到根据用户的不同或者权限的不同（自己定义路由规则）而切换到不同的数据库（数据源）。

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

application-data.xml 位置在resources 下面

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd"
>
    <bean id="ds1" name="ds1"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource" primary="true">
        <property name="driverClassName" value="org.postgresql.Driver"/>
        <property name="url" value="jdbc:postgresql://172.16.176.132:5432/users_database?useSSL=false"/>
        <property name="username" value="root"/>
        <property name="password" value="123"/>
    </bean>

    <bean id="ds2" name="ds2"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource" >
        <property name="driverClassName" value="org.postgresql.Driver"/>
        <property name="url" value="jdbc:postgresql://172.16.176.132:5432/postgres?useSSL=false"/>
        <property name="username" value="root"/>
        <property name="password" value="123"/>
    </bean>

    <!--动态选择数据源-->
    <bean id="dataSource" class="com.exmaple.configuration.DynamicDataSource" >
        <property name="targetDataSources">
            <map key-type="java.lang.String">
                <entry key="1" value-ref="ds1"/>
                <entry key="2" value-ref="ds2"/>
            </map>
        </property>
        <property name="defaultTargetDataSource" ref="ds1"/>
    </bean>

    <bean id="entityManagerFactory"
          class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
          destroy-method="destroy" >
        <property name="dataSource" ref="dataSource" />
        <property name="jpaVendorAdapter">
            <bean
                    class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                <property name="databasePlatform" value="org.hibernate.dialect.SQLServer2008Dialect"/>
                <property name="showSql" value="true"/>
            </bean>
        </property>
        <property name="packagesToScan" value="com.exmaple.model"/>
        <property name="jpaPropertyMap">
            <map>
                <entry key="javax.persistence.schema-generation.database.action" value="none"/>
                <entry key="hibernate.temp.use_jdbc_metadata_defaults" value="false" />

            </map>
        </property>
    </bean>

    <bean id="transactionManager"
          class="org.springframework.orm.jpa.JpaTransactionManager">

        <property name="entityManagerFactory"
                  ref="entityManagerFactory"/>
    </bean>

    <tx:annotation-driven transaction-manager="transactionManager"/>

</beans>

主启动类的配置：

package com.exmaple;

import org.springframework.beans.factory.annotation.Autowire;
import org.springframework.beans.factory.annotation.Configurable;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.ImportResource;

/**
 * @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);
	}
}

测试数据查询的接口层

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 EmployeeService employeeService;


	//@Cacheable(value = "getAllEmployees")
	@GetMapping("/employees")
	public List<Employee> getAllEmployees() {
		return employeeService.findAll();
	}
}

service层（可不要）

package com.exmaple.service;

import com.exmaple.model.Employee;
import com.exmaple.repositoryp.EmployeeRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author xujiang
 * @date 2021/1/27 15:50
 * @Description: 服务类
 */
@Service
public class EmployeeService {

    @Autowired
    private EmployeeRepository employeeRepository;

    public List<Employee> findAll(){
        return employeeRepository.findAll();
    }

}

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>{
 

}

然后就是切换的路由配置：

DynamicDataSource 自定义类继承AbstractRoutingDataSource

package com.exmaple.configuration;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * @author xujiang
 * @date 2021/1/27 11:18
 * @Description: 获取数据源（依赖于spring）
 */
@Component
public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceHolder.getDataSource();
    }
}

定义AOP拦截；在调用方法前切换数据源；这里就是根据自己的业务逻辑来实现按照人的权限或者组织进行数据源切换

package com.exmaple.configuration;

import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.context.annotation.Configuration;

import java.util.concurrent.atomic.AtomicInteger;

/**
 * @author xujiang
 * @date 2021/1/27 11:23
 * @Description: 拦截controller,动态切换数据源
 */

@Aspect
@Configuration
@Slf4j
public class DataSourceAspect {

    public AtomicInteger atomicInteger=new AtomicInteger(0);
    /**定义一个切入点*/
    @Pointcut("execution(* com.exmaple.controller..*.*(..))")
    private void anyMethod(){}

    @Before("anyMethod()")
    public void dataSourceChange()
    {

        if(atomicInteger.get()% 2 ==0){
            log.info("---调用次数是偶数是切换为主数据源---");
            DataSourceHolder.setDataSource("1");
        }else {
            log.info("---调用次数是奇数是切换为备数据源---");
            DataSourceHolder.setDataSource("2");
        }

        int num = atomicInteger.incrementAndGet();
        log.info("---当前调用次数为:"+num+"---");

    }
}

定义线程变量保存每次请求的线程携带的数据源信息；

package com.exmaple.configuration;

/**
 * @author xujiang
 * @date 2021/1/27 11:19
 * @Description: 数据源操作
 */
public class DataSourceHolder {

    /**线程本地环境*/
    public static  ThreadLocal<String> dataSources = new ThreadLocal<>();
    /**设置数据源*/
    public static void setDataSource(String customerType) {
        dataSources.set(customerType);
    }
    /**获取数据源*/
    public static String getDataSource() {
        return dataSources.get();
    }
    /**清除数据源*/
    public static void clearDataSource() {
        dataSources.remove();
    }
}

最后加上实体类代码；特别注意实体所在的包，和application-data.xml里面的扫码包需要对应起来；

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

代码是自己本地已经跑通的代码，下一篇总结另外一种方式切换数据源。

smile__tiger

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
2021-01-28多数据源的动态切换（1）

springBoot jpa 多数据源的动态切换参考博客地址：https://blog.csdn.net/sunshine920103/article/details/50363315下面是自己的demo实现；此实现方式能做到根据用户的不同或者权限的不同（自己定义路由规则）而切换到不同的数据库（数据源）。pom.xml<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apach...
复制链接

扫一扫