jpa 自定义sql if_SpringBoot整合JPA实现多数据源及读写分离

SpringBoot整合JPA实现多数据源及读写分离

项目地址：https://github.com/baojingyu/spring-boot-jpa-dynamic-datasource

本项目使用 SpringBoot 和 SpringData JPA 实现多数据源，动态数据源的切换。

前段时间写 MySQL 主从复制的技术分享，我就在想，既然主从复制都搭建好了，不写个 Demo 玩玩读写分离，好像差点意思哼～ 于是就有了这么个 Demo Project。

一、什么是读写分离？

读写分离其实就是将数据库分为主从库，简单点说是主库处理事务性增、改、删操作，多个从库处理查询操作。主库的事务性操作导致的变更通过BinLog 日志同步到集群中的从库。

二、为什么要读写分离？

其实最主要是减轻数据库的压力。数据库的写操作比较耗时，如果没有进行读写分离，写操作将会影响到读操作的效率。

三、什么时候要读写分离？

如果程序读多写少，那么可以考虑读写分离，利用数据库主从同步，能减轻数据库压力，提高性能。

四、读写分离方案

数据库读远大于写，查询多的情况，那就得考虑主库负责写操作，多个从库负责读操作，另外结合 Redis 等缓存来配合分担数据库的读操作。

五、代码层面进行读写分离

代码环境是SpringBoot + SpringData JPA + Druib连接池。想要读写分离就需要配置多个数据源，在进行写操作时选择写的数据源，读操作时选择读的数据源。其中有两个关键点：

1. 如何切换数据源
2. 如何根据不同的方法选择正确的数据源

1)、如何切换数据源

通常用 SpringBoot 时都是使用它的默认配置，只需要在配置文件中定义好连接属性就行了，但是现在我们需要自己来配置了，Spring 是支持多数据源的，多个datasource放在一个HashMapTargetDataSource中，通过dertermineCurrentLookupKey获取key来决定要使用哪个数据源。因此我们的目标就很明确了，建立多个datasource放到TargetDataSource中，同时重写dertermineCurrentLookupKey方法来决定使用哪个key。

用户自定义设置数据库路由

SpringBoot 提供了 AbstractRoutingDataSource 根据用户定义的规则选择当前的数据库，这样我们可以在执行查询之前，设置读取从库，在执行完成后，恢复到主库。AbstractRoutingDataSource 就是DataSource 的抽象，基于 lookupKey 的方式在多个数据库中进行切换。重点关注setTargetDataSources，setDefaultTargetDataSource，determineCurrentLookupKey三个方法。那么AbstractRoutingDataSource就是Spring读写分离的关键了。

实现可动态路由的数据源，在每次数据库查询操作前执行。

2)、如何选择数据源

事务一般是注解在Service层的，因此在开始这个service方法调用时要确定数据源，有什么通用方法能够在开始执行一个方法前做操作呢？相信你已经想到了那就是切面 。怎么切有两种办法：

注解式，定义一个只读注解，被该数据标注的方法使用读库 方法名，根据方法名写切点，比如getXXX用读库，setXXX用写库

六、部分代码

DataSourcesConfig

/** * @author jingyu.bao * @version 1.0 * @className DataSourceConfig * @description * @date 7/5/2020 20:09 **/@EnableTransactionManagement@Configurationpublic class DataSourceConfig {    @Value("${spring.datasource.druid.master.name}")    private String masterName;    @Value("${spring.datasource.druid.master.url}")    private String masterUrl;    @Value("${spring.datasource.druid.master.username}")    private String masterUsername;    @Value("${spring.datasource.druid.master.password}")    private String masterPassword;    @Value("${spring.datasource.druid.master.driver-class-name}")    private String masterDriverClassName;    @Value("${spring.datasource.druid.slave.name}")    private String slaveName;    @Value("${spring.datasource.druid.slave.url}")    private String slaveUrl;    @Value("${spring.datasource.druid.slave.username}")    private String slaveUsername;    @Value("${spring.datasource.druid.slave.password}")    private String slavePassword;    @Value("${spring.datasource.druid.slave.driver-class-name}")    private String slaveDriverClassName;    @Value("${spring.datasource.druid.initial-size}")    private String initialSize;    @Value("${spring.datasource.druid.min-idle}")    private String minIdle;    @Value("${spring.datasource.druid.max-active}")    private String maxActive;    @Value("${spring.datasource.druid.max-wait}")    private String maxWait;    @Value("${spring.datasource.druid.time-between-eviction-runs-millis}")    private String timeBetweenEvictionRunsMillis;    @Value("${spring.datasource.druid.min-evictable-idle-time-millis}")    private String minEvictableIdleTimeMillis;    @Value("${spring.datasource.druid.validation-query}")    private String validationQuery;    @Value("${spring.datasource.druid.test-while-idle}")    private String testWhileIdle;    @Value("${spring.datasource.druid.test-on-borrow}")    private String testOnBorrow;    @Value("${spring.datasource.druid.test-on-return}")    private String testOnReturn;    @Value("${spring.datasource.druid.filters}")    private String filters;    @Value("{spring.datasource.druid.filter.stat.log-slow-sql}")    private String logSlowSql;    @Value("{spring.datasource.druid.filter.stat.slow-sql-millis}")    private String slowSqlMillis;    @Value("${spring.datasource.druid.type}")    private String type;    @Value("${spring.datasource.druid.stat-view-servlet.login-username}")    private String loginUserName;    @Value("${spring.datasource.druid.stat-view-servlet.login-password}")    private String password;    @Bean(name = "masterDataSource")    public DataSource masterDataSource() {        DruidDataSource datasource = new DruidDataSource();        datasource.setUrl(masterUrl);        datasource.setUsername(masterUsername);        datasource.setPassword(masterPassword);        datasource.setDriverClassName(masterDriverClassName);        //configuration        if (!StringUtils.isEmpty(initialSize)) {            datasource.setInitialSize(Integer.parseInt(initialSize));        }        if (!StringUtils.isEmpty(minIdle)) {            datasource.setMinIdle(Integer.parseInt(minIdle));        }        if (!StringUtils.isEmpty(maxActive)) {            datasource.setMaxActive(Integer.parseInt(maxActive));        }        if (!StringUtils.isEmpty(maxWait)) {            datasource.setMaxWait(Integer.parseInt(maxWait));        }        if (!StringUtils.isEmpty(timeBetweenEvictionRunsMillis)) {            datasource.setTimeBetweenEvictionRunsMillis(Integer.parseInt(timeBetweenEvictionRunsMillis));        }        if (!StringUtils.isEmpty(minEvictableIdleTimeMillis)) {            datasource.setMinEvictableIdleTimeMillis(Integer.parseInt(minEvictableIdleTimeMillis));        }        if (!StringUtils.isEmpty(validationQuery)) {            datasource.setValidationQuery(validationQuery);        }        if (!StringUtils.isEmpty(testWhileIdle)) {            datasource.setTestWhileIdle(Boolean.parseBoolean(testWhileIdle));        }        if (!StringUtils.isEmpty(testOnBorrow)) {            datasource.setTestOnBorrow(Boolean.parseBoolean(testOnBorrow));        }        if (!StringUtils.isEmpty(testOnReturn)) {            datasource.setTestOnReturn(Boolean.parseBoolean(testOnReturn));        }        try {            datasource.setFilters(filters);        } catch (SQLException e) {            e.printStackTrace();        }        return datasource;    }    @Bean(name = "slaveDataSource")    public DataSource slaveDataSource() {        DruidDataSource datasource = new DruidDataSource();        datasource.setUrl(masterUrl);        datasource.setUsername(masterUsername);        datasource.setPassword(masterPassword);        datasource.setDriverClassName(masterDriverClassName);        //configuration        if (!StringUtils.isEmpty(initialSize)) {            datasource.setInitialSize(Integer.parseInt(initialSize));        }        if (!StringUtils.isEmpty(minIdle)) {            datasource.setMinIdle(Integer.parseInt(minIdle));        }        if (!StringUtils.isEmpty(maxActive)) {            datasource.setMaxActive(Integer.parseInt(maxActive));        }        if (!StringUtils.isEmpty(maxWait)) {            datasource.setMaxWait(Integer.parseInt(maxWait));        }        if (!StringUtils.isEmpty(timeBetweenEvictionRunsMillis)) {            datasource.setTimeBetweenEvictionRunsMillis(Integer.parseInt(timeBetweenEvictionRunsMillis));        }        if (!StringUtils.isEmpty(minEvictableIdleTimeMillis)) {            datasource.setMinEvictableIdleTimeMillis(Integer.parseInt(minEvictableIdleTimeMillis));        }        if (!StringUtils.isEmpty(validationQuery)) {            datasource.setValidationQuery(validationQuery);        }        if (!StringUtils.isEmpty(testWhileIdle)) {            datasource.setTestWhileIdle(Boolean.parseBoolean(testWhileIdle));        }        if (!StringUtils.isEmpty(testOnBorrow)) {            datasource.setTestOnBorrow(Boolean.parseBoolean(testOnBorrow));        }        if (!StringUtils.isEmpty(testOnReturn)) {            datasource.setTestOnReturn(Boolean.parseBoolean(testOnReturn));        }        try {            datasource.setFilters(filters);        } catch (SQLException e) {            e.printStackTrace();        }        return datasource;    }    @Primary    @Bean    public DynamicRoutingDataSource dynamicDataSource(@Qualifier(value = "masterDataSource") DataSource masterDataSource, @Qualifier(value = "slaveDataSource") DataSource slaveDataSource) {        Map targetDataSources = new HashMap<>(2);        targetDataSources.put(DynamicRoutingDataSourceContext.MASTER, masterDataSource);        targetDataSources.put(DynamicRoutingDataSourceContext.SLAVE, slaveDataSource);        DynamicRoutingDataSource dynamicRoutingDataSource = new DynamicRoutingDataSource();        //设置数据源        dynamicRoutingDataSource.setTargetDataSources(targetDataSources);        //设置默认选择的数据源        dynamicRoutingDataSource.setDefaultTargetDataSource(masterDataSource);        dynamicRoutingDataSource.afterPropertiesSet();        return dynamicRoutingDataSource;    }    @Bean    public ServletRegistrationBean statViewServlet() {        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");        //设置ip白名单        servletRegistrationBean.addInitParameter("allow", "");        //设置ip黑名单，优先级高于白名单        servletRegistrationBean.addInitParameter("deny", "");        //设置控制台管理用户        servletRegistrationBean.addInitParameter("loginUsername", loginUserName);        servletRegistrationBean.addInitParameter("loginPassword", password);        //是否可以重置数据        servletRegistrationBean.addInitParameter("resetEnable", "false");        return servletRegistrationBean;    }    @Bean    public FilterRegistrationBean statFilter() {        //创建过滤器        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());        //设置过滤器过滤路径        filterRegistrationBean.addUrlPatterns("/*");        //忽略过滤的形式        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");        return filterRegistrationBean;    }

DynamicRoutingDataSource

/** * @author jingyu.bao * @version 1.0 * @className DynamicRoutingDataSource * @description * @date 7/5/2020 21:22 **/public class DynamicRoutingDataSource extends AbstractRoutingDataSource {    @Override    protected Object determineCurrentLookupKey() {        Object lookupKey = DynamicRoutingDataSourceContext.getRoutingDataSource();        System.err.println(Thread.currentThread().getName() + " determineCurrentLookupKey : " + lookupKey);        return lookupKey;    }}

DynamicRoutingDataSourceContext

/** * @author jingyu.bao * @version 1.0 * @className DynamicRoutingDataSourceContext * @description * @date 7/5/2020 20:16 **/public class DynamicRoutingDataSourceContext {    public static final String MASTER = "master";    public static final String SLAVE = "slave";    private static final ThreadLocal threadLocalDataSource = new ThreadLocal<>();    public static void setRoutingDataSource(Object dataSource) {        if (dataSource == null) {            throw new NullPointerException();        }        threadLocalDataSource.set(dataSource);        // System.err.println(Thread.currentThread().getName()+" set RoutingDataSource : " + dataSource);    }    public static Object getRoutingDataSource() {        Object dataSourceType = threadLocalDataSource.get();        if (dataSourceType == null) {            threadLocalDataSource.set(DynamicRoutingDataSourceContext.MASTER);            return getRoutingDataSource();        }        // System.err.println(Thread.currentThread().getName()+" get RoutingDataSource : " + dataSourceType);        return dataSourceType;    }    public static void removeRoutingDataSource() {        threadLocalDataSource.remove();        // System.err.println(Thread.currentThread().getName()+" remove RoutingDataSource");    }}

RoutingAopAspect

/** * @author jingyu.bao * @version 1.0 * @className RoutingAopAspect * @description * @date 7/5/2020 20:21 **/@Order(0)@Aspect@Componentpublic class RoutingAopAspect {    @Around("@annotation(targetDataSource)")    public Object routingWithDataSource(ProceedingJoinPoint joinPoint, TargetDataSource targetDataSource) throws Throwable {        try {            DynamicRoutingDataSourceContext.setRoutingDataSource(targetDataSource.value());            return joinPoint.proceed();        } finally {            DynamicRoutingDataSourceContext.removeRoutingDataSource();        }    }}

TargetDataSource

/** * @author jingyu.bao * @version 1.0 * @className TargetDataSource * @description * @date 7/5/2020 20:40 **/@Target({ElementType.METHOD, ElementType.TYPE})@Retention(value = RetentionPolicy.RUNTIME)@Documentedpublic @interface TargetDataSource {    String value();}

UserInfoServiceImpl

/** * @author jingyu.bao * @version 1.0 * @className UserServiceImpl * @description * @date 7/5/2020 21:39 **/@Servicepublic class UserInfoServiceImpl implements IUserInfoService {    @Autowired    private IUserInfoRepository userInfoRepository;    @TargetDataSource(value = "slave")    @Override    public List findAll() {        return userInfoRepository.findAll();    }    @Transactional    @Override    public UserInfoEntity save(UserInfoEntity userInfoEntity) {        return userInfoRepository.save(userInfoEntity);    }    @TargetDataSource(value = "slave")    @Override    public UserInfoEntity findById(Long id) {        Optional userInfoEntity = userInfoRepository.findById(id);        return userInfoEntity.isPresent() ? userInfoEntity.get() : null;    }    @Override    public List findAllMaster() {        return userInfoRepository.findAll();    }    @Transactional    @Override    public void saveAll(ArrayList list) {        userInfoRepository.saveAll(list);    }}

application.properties

server.port=8080server.tomcat.max-threads=3000server.tomcat.max-connections=20000server.tomcat.uri-encoding=UTF-8server.tomcat.accept-count=800# 自定义线程池参数fxea.threadPool.coreThreadNum=5fxea.threadPool.maxThreadNum=25# 这个参数是在建表的时候，将默认的存储引擎切换为 InnoDB 用的spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialectspring.jpa.database=mysql# 配置在日志中打印出执行的 SQL 语句信息。spring.jpa.show-sql=true# 第一次建表create,后面用update,要不然每次重启都会新建表spring.jpa.hibernate.ddl-auto=create# Druidspring.datasource.druid.type=com.alibaba.druid.pool.DruidDataSource#初始化连接大小spring.datasource.druid.initial-size=10#最小连接池数量spring.datasource.druid.min-idle=10#最大连接池数量spring.datasource.druid.max-active=100#配置获取连接等待超时的时间spring.datasource.druid.max-wait=60000#配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒spring.datasource.druid.time-between-eviction-runs-millis=60000#配置一个连接在池中最小生存的时间，单位是毫秒spring.datasource.druid.min-evictable-idle-time-millis=300000#测试连接spring.datasource.druid.validation-query=SELECT 'x'#申请连接的时候检测，建议配置为true，不影响性能，并且保证安全性spring.datasource.druid.test-while-idle=true#获取连接时执行检测，建议关闭，影响性能spring.datasource.druid.test-on-borrow=false#归还连接时执行检测，建议关闭，影响性能spring.datasource.druid.test-on-return=false#druid 用户spring.datasource.druid.stat-view-servlet.login-username=admin#druid 密码spring.datasource.druid.stat-view-servlet.login-password=admin#配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙spring.datasource.druid.filters=stat,wallspring.datasource.druid.filter.stat.log-slow-sql=truespring.datasource.druid.filter.stat.slow-sql-millis=1# Db Masterspring.datasource.druid.master.name=masterspring.datasource.druid.master.driver-class-name=com.mysql.cj.jdbc.Driverspring.datasource.druid.master.url=jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=truespring.datasource.druid.master.username=rootspring.datasource.druid.master.password=123456# Db Slavesspring.datasource.druid.slave.name=slavespring.datasource.druid.slave.driver-class-name=com.mysql.cj.jdbc.Driverspring.datasource.druid.slave.url=jdbc:mysql://localhost:3307/test?useSSL=false&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=truespring.datasource.druid.slave.username=rootspring.datasource.druid.slave.password=123456

点击左下角阅读原文可跳转至项目下载地址：https://github.com/baojingyu/spring-boot-jpa-dynamic-datasource

点个关注，和我一起共同进步吧！

微信公众号：鲸鱼手记