1.引入依赖
<!-- mysql连接的jar包 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- 数据库连接池 -->
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>${c3p0.version}</version>
</dependency>
<!-- Mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>${mybatis-spring-boot-starter.version}</version>
</dependency>
2.application.properties件中添加数据源配置
#C3P0数据库连接池配置
c3p0.master.jdbcUrl=jdbc:mysql:///***?useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false
c3p0.master.user=root
c3p0.master.password=***
c3p0.master.driverClass=com.mysql.jdbc.Driver
c3p0.master.minPoolSize=0
c3p0.master.maxPoolSize=13
c3p0.master.maxIdleTime=1800000
c3p0.master.acquireIncrement=5
c3p0.master.maxStatements=1000
c3p0.master.initialPoolSize=3
c3p0.master.idleConnectionTestPeriod=60
c3p0.master.acquireRetryAttempts=30
c3p0.master.acquireRetryDelay=1000
c3p0.master.breakAfterAcquireFailure=false
c3p0.master.testConnectionOnCheckout=false
# 从数据源
c3p0.slave.jdbcUrl=jdbc:mysql://***.***.***.***:3306/***?useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false
c3p0.slave.user=cat
c3p0.slave.password=123456
c3p0.slave.driverClass=com.mysql.jdbc.Driver
c3p0.slave.minPoolSize=0
c3p0.slave.maxPoolSize=13
c3p0.slave.maxIdleTime=1800000
c3p0.slave.acquireIncrement=5
c3p0.slave.maxStatements=1000
c3p0.slave.initialPoolSize=3
c3p0.slave.idleConnectionTestPeriod=60
c3p0.slave.acquireRetryAttempts=30
c3p0.slave.acquireRetryDelay=1000
c3p0.slave.breakAfterAcquireFailure=false
c3p0.slave.testConnectionOnCheckout=false
3.路由
package com.template.db;
import com.template.constant.TemplateConstantPool;
/**
* 线程私有路由配置,用于ReadWriteSplitRoutingDataSource动态读取配置
*
* @Title: DbContextHolder.java
* @Package com.template.db
* @Description: TODO
* @author Autumn、
* @date 2018年8月5日
*/
public class DbContextHolder {
/**
* 当前数据库类型
*/
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
/**
* 设置数据库类型
*
* @param dbType
*/
public static void setDbType(String dbType) {
if (dbType == null)
throw new NullPointerException();
contextHolder.set(dbType);
}
/**
* 获取数据库类型
*
* @return
*/
public static String getDbType() {
return contextHolder.get() == null ? TemplateConstantPool.DATASOURCE_MASTER : contextHolder.get();
}
/**
* 清除数据库类型
*/
public static void clearDbType() {
contextHolder.remove();
}
}
4.动态数据源
package com.template.db;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* 实现可动态路由的数据源,在每次数据库查询操作前执行
* @Title: ReadWriteSplitRoutingDataSource.java
* @Package com.template.db
* @Description: TODO
* @author Autumn、
* @date 2018年8月5日
*/
public class ReadWriteSplitRoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DbContextHolder.getDbType();
}
}
5.声明只读注解
package com.template.db;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 只读注解,用于标注方法的数据库操作只走从库
*
* @Title: ReadOnlyConnection.java
* @Package com.template.db
* @Description: TODO
* @author Autumn、
* @date 2018年8月5日
*/
@Target({ ElementType.METHOD })
@Retention(RetentionPolicy.RUNTIME)
public @interface ReadOnlyConnection {
}
6.设置AOP切面,动态切换数据源
package com.template.db;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import com.template.constant.TemplateConstantPool;
/**
* 动态数据源绑定,指定只读数据源(从库)
* @Title: ReadOnlyConnectionAspect.java
* @Package com.template.db
* @Description: TODO
* @author Autumn、
* @date 2018年8月5日
*/
@Aspect
@Component
@Order(5)
public class ReadOnlyConnectionAspect{
/**
* 切换到从库
*
* @param proceedingJoinPoint
* @param readOnlyConnection
* @return
* @throws Throwable
*/
@Around("@annotation(readOnlyConnection)")
public Object proceed(ProceedingJoinPoint proceedingJoinPoint, ReadOnlyConnection readOnlyConnection)
throws Throwable {
try {
DbContextHolder.setDbType(TemplateConstantPool.DATASOURCER_SLAVE);
Object result = proceedingJoinPoint.proceed();
return result;
} finally {
DbContextHolder.clearDbType();
}
}
}
7.动态数据源配置及MyBatis的sqlSessionFactor配置
package com.template.config;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.PropertySource;
import org.springframework.context.support.PropertySourcesPlaceholderConfigurer;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter;
import org.springframework.web.servlet.DispatcherServlet;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.template.constant.TemplateConstantPool;
import com.template.db.ReadWriteSplitRoutingDataSource;
public class TemplateBeans {
/**
* 动态数据源
*
* @return 动态数据库连接池 DataSource
*/
@Bean(name = "dataSource")
@Qualifier(value = "dataSource")
@Primary
//这里声明动态数据源在主从数据源加载完毕后加载,不加可能会出现依赖循环
@DependsOn({ "dataSourceMaster", "dataSourceSlave" })
public DataSource dataSource(@Qualifier("dataSourceMaster") DataSource dataSourceMaster,
@Qualifier("dataSourceSlave") DataSource dataSourceSlave) {
System.out.println(dataSourceMaster.toString());
System.out.println(dataSourceSlave.toString());
ReadWriteSplitRoutingDataSource writeSplitRoutingDataSource = new ReadWriteSplitRoutingDataSource();
Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
targetDataSources.put(TemplateConstantPool.DATASOURCE_MASTER, dataSourceMaster);
targetDataSources.put(TemplateConstantPool.DATASOURCER_SLAVE, dataSourceSlave);
//TemplateConstantPool.DATASOURCE_MASTER和TemplateConstantPool.DATASOURCER_SLAVE是字符串常量,
//可直接使用字符串代替或者自己声明,内容任意无重复即可
writeSplitRoutingDataSource.setTargetDataSources(targetDataSources);
writeSplitRoutingDataSource.setDefaultTargetDataSource(dataSourceMaster);
return writeSplitRoutingDataSource;
}
/**
* 主数据库连接池配置C3P0
*
* @return 数据库连接池 DataSource
*/
@Bean(name = "dataSourceMaster")
@Qualifier(value = "dataSourceMaster")
@ConfigurationProperties(prefix = "c3p0.master")
public DataSource dataSourceMaster() {
ComboPooledDataSource dataSource = (ComboPooledDataSource) DataSourceBuilder.create()
.type(com.mchange.v2.c3p0.ComboPooledDataSource.class).build();
// 解决c3p0连接数据库8小时失效问题
// 每次获取connection测试其有效性
dataSource.setTestConnectionOnCheckin(true);
// 测试表
dataSource.setAutomaticTestTable("C3P0TestTable");
// 每18000秒检查一次空闲连接
dataSource.setIdleConnectionTestPeriod(18000);
// 25000秒未使用的空闲连接将被丢弃
dataSource.setMaxIdleTime(25000);
return dataSource;
}
/**
* 从数据库连接池配置C3P0
*
* @return 数据库连接池 DataSource
*/
@Bean(name = "dataSourceSlave")
@Qualifier(value = "dataSourceSlave")
@ConfigurationProperties(prefix = "c3p0.slave")
public DataSource dataSourceSlave() {
ComboPooledDataSource dataSource = (ComboPooledDataSource) DataSourceBuilder.create()
.type(com.mchange.v2.c3p0.ComboPooledDataSource.class).build();
// 解决c3p0连接数据库8小时失效问题
// 每次获取connection测试其有效性
dataSource.setTestConnectionOnCheckin(true);
// 测试表
dataSource.setAutomaticTestTable("C3P0TestTable");
// 每18000秒检查一次空闲连接
dataSource.setIdleConnectionTestPeriod(18000);
// 25000秒未使用的空闲连接将被丢弃
dataSource.setMaxIdleTime(25000);
return dataSource;
}
/**
* 返回sqlSessionFactory
*/
@Bean
public SqlSessionFactoryBean sqlSessionFactoryBean(@Qualifier("dataSource") DataSource dataSource) {
SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
sqlSessionFactory.setDataSource(dataSource);
// 设置mybatis的主配置文件
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
Resource mybatisConfigXml = resolver.getResource("classpath:config/mybatis/SqlMapConfig.xml");
sqlSessionFactory.setConfigLocation(mybatisConfigXml);
// 设置别名包
sqlSessionFactory.setTypeAliasesPackage("com.template.po");
return sqlSessionFactory;
}
}
8测试(这里使用的是Spring Data Jpa进行测试,MyBatis同理)
package com.test.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.backstage.domain.User;
import com.backstage.repository.UserRepository;
import com.template.db.ReadOnlyConnection;
/**
* @Title: RWTest1.java
* @Package com.test.service
* @Description: TODO
* @author Autumn、
* @date 2018年8月5日
*/
@Service
public class RWTest1 {
@Autowired
private UserRepository userRepository;
public List<User> findUser1() {
return userRepository.findAll();
}
@ReadOnlyConnection
public List<User> findUser2() {
return userRepository.findAll();
}
/**
* @return the userRepository
*/
public UserRepository getUserRepository() {
return userRepository;
}
/**
* @param userRepository
* the userRepository to set
*/
public void setUserRepository(UserRepository userRepository) {
this.userRepository = userRepository;
}
}