Spring-jdbc实现数据库读写分离

Spring实现数据库读写分离

目录

数据库读写分离

1.AbstractRoutingDataSource的关键属性

2.protected abstract Object determineCurrentLookupKey()

3.Demo如下

0）pom文件配置

1）自定义注解

2）实现AbstractRoutingDataSource中的determineCurrentLookupKey方法

3）配置数据源

4）配置文件如下

5)添加dao

6）运行demo

---

 Spring-jdbc jar包下提供的org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource可以用于切换数据源，通过实现该类可以实现读写分离；

---

1.AbstractRoutingDataSource的关键属性

1. Map<Object, Object> targetDataSources：
数据库数据源配置map,用于配置多个数据源：

targetDataSources.put("数据源key1",Datasource1)；
targetDataSources.put("数据源key2",Datasource2)；

其中数据源key1和key2为字符串，作为为通过key 来查找数据源

2.protected abstract Object determineCurrentLookupKey()

用来切换数据源，可通过自定义方法，来返回 targetDataSources 中的key

通过配置以上步骤，即可实现读写分离；

3.Demo如下

0）pom文件配置

<?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">
    <parent>
        <artifactId>shj-learn</artifactId>
        <groupId>shj-learn-total</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>
 
    <artifactId>shj-data-learn</artifactId>
    <properties>
        <spring_version>5.3.3</spring_version>
        <mysql-connector-java.version>8.0.23</mysql-connector-java.version>
    </properties>
 
    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring_version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring_version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql-connector-java.version}</version>
        </dependency>
    </dependencies>
 
</project>

1）自定义注解

package masterslave.config;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DataSource {

    String dataSource() default "master";
}

2）实现AbstractRoutingDataSource中的determineCurrentLookupKey方法

 

package masterslave.config;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import java.lang.reflect.Method;

public class DatabaseRouting extends AbstractRoutingDataSource {
    public final static String MASTER = "master";
    public final static String SLAVE = "slave";

    private String PRE_SERVICE_PACKAGE;

    public DatabaseRouting(String  servicePackage){
        this.PRE_SERVICE_PACKAGE = servicePackage;

    }

    @Override
    protected Object determineCurrentLookupKey() {
        StackTraceElement[] stackTraceElements = Thread.currentThread().getStackTrace();
        String dataSourceKey = MASTER;
        for (StackTraceElement e : stackTraceElements){
            if (e.getClassName().startsWith(PRE_SERVICE_PACKAGE)){
                String currentMethodName = e.getMethodName();
                try {
                    Method[] declaredMethods = Class.forName(e.getClassName()).getMethods();
                    for (Method declaredMethod: declaredMethods){
                        if (currentMethodName.equals(declaredMethod.getName())){
                            if (declaredMethod.isAnnotationPresent(DataSource.class)){
                                dataSourceKey = declaredMethod.getAnnotation(DataSource.class).dataSource();
                            }
                        }
                    }


                } catch (ClassNotFoundException e1) {
                    e1.printStackTrace();
                }



            }
        }
        return dataSourceKey;
    }

}

上述的determineCurrentLookupKey可以根据自己的项目实现，这边是通过注解来判断是否读取从库还是主库

3）配置数据源

package masterslave.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;

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

/**
 * 配置数据源
 */
@Configuration
@PropertySource(value = {"classpath:data-master-slave-source.properties"})
public class DatabaseConfig {


    @Value("${spring-datasource-driveClassName}")
    private String driverClassName;


    @Value("${spring-datasource-masterUrl}")
    private String masterUrl;


    @Value("${spring-datasource-masterUserName}")
    private String masterUserName;

    @Value("${spring-datasource-masterPwd}")
    private String masterPwd;


    @Value("${spring-datasource-slaveUrl}")
    private String slaveUrl;


    @Value("${spring-datasource-salveUserName}")
    private String salveUserName;


    @Value("${spring-datasource-slavePwd}")
    private String slavePwd;

    @Value("${service-package-dao-pre}")
    private String servicePackage;


    @Bean(name = "master")
    public DataSource getMasterDatasource() throws ClassNotFoundException {
        SimpleDriverDataSource masterDatasource = new SimpleDriverDataSource();
        masterDatasource.setUsername(masterUserName);
        masterDatasource.setUrl(masterUrl);
        masterDatasource.setPassword(masterPwd);
        masterDatasource.setDriverClass((Class<? extends Driver>) Class.forName(driverClassName));
        return masterDatasource;
    }

    @Bean(name = "slave")
    public DataSource getSlaveDatasource() throws ClassNotFoundException {
        SimpleDriverDataSource masterDatasource = new SimpleDriverDataSource();
        masterDatasource.setUsername(salveUserName);
        masterDatasource.setUrl(slaveUrl);
        masterDatasource.setPassword(slavePwd);
        masterDatasource.setDriverClass((Class<? extends Driver>) Class.forName(driverClassName));
        return masterDatasource;
    }



    @Bean(name = "databaseRoutingDatabase")
    public DataSource getDatabaseRoutingDatabase(@Qualifier("master")DataSource masterDatasource, @Qualifier("slave")DataSource slaveDatasource){
        DatabaseRouting databaseRouting = new DatabaseRouting(servicePackage);
        Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
        targetDataSources.put(DatabaseRouting.MASTER,masterDatasource);
        targetDataSources.put(DatabaseRouting.SLAVE,slaveDatasource);
        databaseRouting.setTargetDataSources(targetDataSources);
        return databaseRouting;
    }

    /**
     * 创建jdbcTemplate
     * @param dataSource
     * @return
     */
    @Bean(name = "jdbcTemplate")
    public JdbcTemplate getSqlSessionFactory(@Qualifier(value = "databaseRoutingDatabase") DataSource dataSource) throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        return jdbcTemplate;
    }


}

4）配置文件如下

spring-datasource-driveClassName=com.mysql.cj.jdbc.Driver

spring-datasource-masterUrl=jdbc:mysql://ip:3306/springlearning
spring-datasource-masterUserName=root
spring-datasource-masterPwd=!QAZ2wsx

spring-datasource-slaveUrl=jdbc:mysql://ip:3306/springlearnslave
spring-datasource-salveUserName=root
spring-datasource-slavePwd=!QAZ2wsx


service-package-dao-pre=masterslave.dao

5)添加dao

package masterslave.dao;

import masterslave.config.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

@Service(value = "masterDao")
public class MasterDao {


    @Autowired
    JdbcTemplate jdbcTemplate;

    @DataSource(dataSource = "master")
    public void qryInfo(){
        List<Map<String, Object>> maps = jdbcTemplate.queryForList("SELECT  * from ps_sys_dic  where  id = 2 ");
        Map<String, Object> stringObjectMap = maps.get(0);
        System.out.println(stringObjectMap.entrySet());
    }
}

package masterslave.dao;

import masterslave.config.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

@Service(value = "slaveDao")
public class SlaveDAO {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @DataSource(dataSource = "slave")
    public void qryInfo(){
        List<Map<String, Object>> maps = jdbcTemplate.queryForList("SELECT  * from ps_sys_dic  where  id = 2 ");
        Map<String, Object> stringObjectMap = maps.get(0);
        System.out.println(stringObjectMap.entrySet());
    }
}

6）运行demo

package masterslave;

import masterslave.config.DatabaseConfig;
import masterslave.dao.MasterDao;
import masterslave.dao.SlaveDAO;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.annotation.ComponentScan;

@ComponentScan(value = "masterslave.*")
public class BootDemo {

    public static void main(String[] args) {
        AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext();
        applicationContext.register(DatabaseConfig.class,MasterDao.class,SlaveDAO.class);
        applicationContext.refresh();
        MasterDao masterDao = (MasterDao)applicationContext.getBean("masterDao");
        SlaveDAO slaveDao = (SlaveDAO)applicationContext.getBean("slaveDao");
        masterDao.qryInfo();
        slaveDao.qryInfo();
    }
}