日常系统开发中,数据库往往是大多数系统最终性能瓶颈,最终业务操作都在数据库中完成,除了一些热点数据我们往往会把它放入缓存数据库中,提高系统查询效率,但是当数据量大,Redis往往不能解决所有的问题,所以我们会对数据库做一个双机读写操作,同时也提供了数据的一个备份操作。同时我们的业务往往都是读多写少,这样我们更加有必要做一个数据库的读写分离。好了,话不多说,直接上代码:
代码工程是基于Spring Boot 2.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">
<modelVersion>4.0.0</modelVersion>
<groupId>com.chown.pine</groupId>
<artifactId>master-slave-database</artifactId>
<version>1.0</version>
<packaging>jar</packaging>
<name>master-slave-database</name>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
<mybatis.spring.version>1.3.2</mybatis.spring.version>
<ali.durid.version>1.1.9</ali.durid.version>
<mybatis.version>3.4.6</mybatis.version>
<guava.version>19.0</guava.version>
</properties>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.1.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<exclusions>
<exclusion>
<artifactId>spring-boot-starter-logging</artifactId>
<groupId>org.springframework.boot</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>${mybatis.spring.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${ali.durid.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>${guava.version}</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.18</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
</dependencies>
</project>
数据库配置文件(mybatis.properties)
# 主数据源,默认的
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://192.168.0.11:21000/tland?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=utf-8
spring.datasource.username=tland
spring.datasource.password=quanteng@2018
# 初始化大小,最小,最大
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.maxWait=60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
spring.datasource.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
# 打开PSCache,并且指定每个连接上PSCache的大小
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
# 从数据源
spring.slave.type=com.alibaba.druid.pool.DruidDataSource
spring.slave.driver-class-name=com.mysql.jdbc.Driver
spring.slave.url=jdbc:mysql://192.168.0.11:21000/tland_dev?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=utf-8
spring.slave.username=tland
spring.slave.password=quanteng@2018
spring.slave.initialSize=5
spring.slave.minIdle=5
spring.slave.maxActive=20
# 配置获取连接等待超时的时间
spring.slave.maxWait=60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
spring.slave.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
spring.slave.minEvictableIdleTimeMillis=300000
spring.slave.validationQuery=SELECT 1 FROM DUAL
spring.slave.testWhileIdle=true
spring.slave.testOnBorrow=false
spring.slave.testOnReturn=false
# 打开PSCache,并且指定每个连接上PSCache的大小
spring.slave.poolPreparedStatements=true
spring.slave.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
spring.datasource.useGlobalDataSourceStat=true
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
spring.slave.filters=stat,wall,logback
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
spring.slave.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
spring.slave.useGlobalDataSourceStat=true
项目属性配置文件
server.port=18023
readatasource.readSize=1
数据源配置
package com.chown.pine.config;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.PropertySource;
import javax.sql.DataSource;
/**
* <数据源配置>
*
* @author zping
* @version 2019/3/1 0001
* @see [相关类/方法]
* @since [产品/模块版本]
*/
@Configuration
@PropertySource ("classpath:mybatis.properties")
public class DataSourceConfiguration
{
/**
* 数据源类型:Druid、Jdbc、JNI
*/
@Value ("${spring.datasource.type}")
private Class<? extends DataSource> dataSourceType;
/**
* 主数据源 : 负责数据写操作
*
* @return
*/
@Bean (name = "writeDataSource", destroyMethod = "close", initMethod = "init")
@Primary
@ConfigurationProperties (prefix = "spring.datasource")
public DataSource writeDataSource ()
{
System.out.println ("-------------------- writeDataSource init ---------------------");
return DataSourceBuilder.create ().type (dataSourceType).build ();
}
/**
* 备数据库 :负责数据读操作
*
* @return
*/
@Bean (name = "readDataSource")
@ConfigurationProperties (prefix = "spring.slave")
public DataSource readDataSourceOne ()
{
System.out.println ("-------------------- readDataSourceOne init ---------------------");
return DataSourceBuilder.create ().type (dataSourceType).build ();
}
}
动态数据源配置
package com.chown.pine.config;
import com.chown.pine.cons.DataSourceType;
import com.chown.pine.context.DataSourceContextHolder;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* <多数据源切换>
*
* @author zping
* @version 2019/3/1 0001
* @see [相关类/方法]
* @since [产品/模块版本]
*/
public class MyAbstractRoutingDataSource extends AbstractRoutingDataSource
{
/**
* 数据源
*/
private final int dataSourceNumber;
public MyAbstractRoutingDataSource (int dataSourceNumber)
{
this.dataSourceNumber = dataSourceNumber;
}
@Override
protected Object determineCurrentLookupKey ()
{
String typeKey = DataSourceContextHolder.getJdbcType ();
if (typeKey.equals (DataSourceType.write.getType ()))
{
return DataSourceType.write.getType ();
}
else
{
return DataSourceType.read.getType ();
}
}
}
MyBatis配置初始化
package com.chown.pine.config;
import com.chown.pine.cons.DataSourceType;
import com.google.common.collect.Maps;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;
/**
* <数据库配置>
*
* @author zping
* @version 2019/3/4 0004
* @see [相关类/方法]
* @since [产品/模块版本]
*/
@Configuration
@ConditionalOnClass ({ EnableTransactionManagement.class })
@Import ({ DataSourceConfiguration.class })
@MapperScan (basePackages = { "com.chown.pine.mapper" })
public class MybatisConfiguration
{
@Value ("${spring.datasource.type}")
private Class<? extends DataSource> dataSourceType;
@Value ("${readatasource.readSize}")
private String dataSourceSize;
@Resource (name = "writeDataSource")
private DataSource dataSource;
@Resource (name = "readDataSource")
private DataSource readDataSources;
/**
* mybatis配置文件路径
*/
private static final String MYBATIS_PATH = "classpath:/mybatis-config.xml";
/**
* mybatis映射文件路径
*/
private static final String MAPPER_PATH = "classpath:/mapper/*.xml";
@Bean
@ConditionalOnMissingBean
public SqlSessionFactory sqlSessionFactory () throws Exception
{
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean ();
sqlSessionFactoryBean.setDataSource (roundRobinDataSouceProxy ());
sqlSessionFactoryBean.setTypeAliasesPackage ("com.chown.pine.entity");
//sqlSessionFactoryBean.getObject ().getConfiguration ().setMapUnderscoreToCamelCase (true);
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver ();
try
{
/*扫描mybatis配置文件*/
sqlSessionFactoryBean.setConfigLocation (resolver.getResource (MYBATIS_PATH));
sqlSessionFactoryBean.setMapperLocations (resolver.getResources (MAPPER_PATH));
return sqlSessionFactoryBean.getObject ();
}
catch (Exception e)
{
System.out.println ("init database error." + e.getMessage ());
}
return sqlSessionFactoryBean.getObject ();
}
/**
* 有多少个数据源就要配置多少个bean
*
* @return
*/
@Bean
public AbstractRoutingDataSource roundRobinDataSouceProxy ()
{
int size = Integer.parseInt (dataSourceSize);
MyAbstractRoutingDataSource proxy = new MyAbstractRoutingDataSource (size);
Map<Object, Object> targetDataSources = Maps.newHashMap ();
// DataSource writeDataSource = SpringContextHolder.getBean("writeDataSource");
targetDataSources.put (DataSourceType.write.getType (), dataSource);
targetDataSources.put (DataSourceType.read.getType (), readDataSources);
//多个读数据库时
/*for (int i = 0; i < size; i++)
{
targetDataSources.put (i, readDataSources.get (i));
}*/
proxy.setDefaultTargetDataSource (dataSource);
proxy.setTargetDataSources (targetDataSources);
return proxy;
}
}
主数据源配置事务
package com.chown.pine.config;
import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import javax.annotation.Resource;
import javax.sql.DataSource;
/**
* <主数据源配置事务>
*
* @author zping
* @version 2019/3/4 0004
* @see [相关类/方法]
* @since [产品/模块版本]
*/
@Configuration
@EnableTransactionManagement
public class DataSourceTransactionManager extends DataSourceTransactionManagerAutoConfiguration
{
/**
* 自定义事务
* MyBatis自动参与到spring事务管理中,无需额外配置,
* 只要org.mybatis.spring.SqlSessionFactoryBean引用的数据源与DataSourceTransactionManager引用的数据源一致即可,
* 否则事务管理会不起作用。
*
* @return
*/
@Resource (name = "writeDataSource")
private DataSource dataSource;
@Bean (name = "transactionManager")
public org.springframework.jdbc.datasource.DataSourceTransactionManager transactionManagers ()
{
System.out.println ("-------------------- transactionManager init ---------------------");
return new org.springframework.jdbc.datasource.DataSourceTransactionManager (dataSource);
}
}
数据源枚举定义
package com.chown.pine.cons;
import lombok.Getter;
/**
* <数据源类型枚举定义>
*
* @author zping
* @version 2019/3/1 0001
* @see [相关类/方法]
* @since [产品/模块版本]
*/
public enum DataSourceType
{
read ("read", "从数据源"),
write ("write", "主数据源");
@Getter
private String type;
@Getter
private String name;
DataSourceType (String type, String name)
{
this.type = type;
this.name = name;
}
}
本地线程变量
package com.chown.pine.context;
import com.chown.pine.cons.DataSourceType;
/**
* <本地线程全局变量>
*
* @author zping
* @version 2019/3/1 0001
* @see [相关类/方法]
* @since [产品/模块版本]
*/
public class DataSourceContextHolder
{
/**
* 本地现场变量
*/
private static final ThreadLocal<String> local = new ThreadLocal<String> ();
/**
* 获取本地变量
*
* @return
*/
public static ThreadLocal<String> getLocal ()
{
return local;
}
/**
* 读可能是多个库
*/
public static void read ()
{
System.out.println ("reader reader reader ...... ");
local.set (DataSourceType.read.getType ());
}
/**
* 写只有一个库
*/
public static void write ()
{
System.out.println ("write write write ...... ");
local.set (DataSourceType.write.getType ());
}
/**
* 获取JDBC类型
*
* @return
*/
public static String getJdbcType ()
{
return local.get ();
}
}
数据库AOP切面定义
package com.chown.pine.aspect;
import com.chown.pine.context.DataSourceContextHolder;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.stereotype.Component;
/**
* <数据库数据切面操作>
*
* @author zping
* @version 2019/3/5 0005
* @see [相关类/方法]
* @since [产品/模块版本]
*/
@Aspect
@Component
public class DataOperateAop
{
/**
* 数据查询切面
*/
@Before ("execution(* com.chown.pine.mapper..*.get*(..)) || execution(* com.chown.pine.mapper..*.query*(..))")
public void setReadDataSourceType ()
{
DataSourceContextHolder.read ();
System.out.println ("dataSource切换到:Read");
}
/**
* 数据写切面
*/
@Before ("execution(* com.chown.pine.mapper..*.add*(..)) || execution(* com.chown.pine.mapper..*.update*(..))"
+ "|| execution(* com.chown.pine.mapper..*.del*(..))")
public void setWriteDataSourceType ()
{
DataSourceContextHolder.write ();
System.out.println ("dataSource切换到:write");
}
}
至此,主要代码都在上面,只有Mybatis中数据操作Mapper和XML文件配置需要自己写!!!