为了减轻数据库的压力，一般会使用数据库主从(master/slave)的方式，但是这种方式会给应用程序带来一定的麻烦，比如说，应用程序如何做到把数据写到master库，而读取数据的时候，从slave库读取。如果应用程序判断失误，把数据写入到slave库，会给系统造成致命的打击。
解决读写分离的方案很多，常用的有SQL解析、动态设置数据源。SQL解析主要是通过分析sql语句是insert/select/update/delete中的哪一种，从而对应选择主从。而动态设置数据源，则是通过拦截方法名称的方式来决定主从的，例如：save*(),insert*() 形式的方法使用master库，select()开头的，使用slave库。蛮多公司会使用在方法上标上自定义的@Master、@Slave之类的标签来选择主从，也有公司直接就调用setxxMaster,setxxSlave之类的代码进行主从选择。
本文基于spring AOP编写了一个简单的读写分离的demo
前提环境：eclipse 、maven、 mysql、mybatis
数据库信息：两个库 masterdb和slaverdb 偷懒放在同一台mysql了，可以用虚拟机模拟多台实例，这里简单弄一下
两个库都有一个user表：
CREATE TABLE `user` ( `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'id', `name` varchar(20) NOT NULL COMMENT '姓名', `age` int(3) NOT NULL COMMENT '年龄', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
初始数据都为：
代码：
工程结构如下：
代码如下：
package com.light.domain; import java.io.Serializable; public class User implements Serializable{ private static final long serialVersionUID = 6102526449700267066L; private int id; private String name; private int age; public User() { super(); } public User(int id, String name, int age) { super(); this.id = id; this.name = name; this.age = age; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } @Override public String toString() { return "User [id=" + id + ", name=" + name + ", age=" + age + "]"; } }
package com.light.dynamicdatasource; import org.apache.commons.lang3.StringUtils; import org.aspectj.lang.JoinPoint; /** * 定义数据源的AOP切面，通过该Service的方法名判断是应该走读库还是写库 * */ public class DataSourceAspect { /** * 在进入Service方法之前执行 * * @param point 切面对象 */ public void before(JoinPoint point) { // 获取到当前执行的方法名 String methodName = point.getSignature().getName(); if (isSlave(methodName)) { // 标记为读库 DynamicDataSourceHolder.markSlave(); } else { // 标记为写库 DynamicDataSourceHolder.markMaster(); } } /** * 判断是否为读库 * @param methodName * @return */ private Boolean isSlave(String methodName) { // 方法名以query、find、get开头的方法名走从库 return StringUtils.startsWithAny(methodName, "query", "find", "get"); } }
package com.light.dynamicdatasource; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; /** * 定义动态数据源，实现通过集成Spring提供的AbstractRoutingDataSource， * 只需要实现determineCurrentLookupKey方法即可 * * 由于DynamicDataSource是单例的，线程不安全的，所以采用ThreadLocal保证线程安全， * 由DynamicDataSourceHolder完成。 * */ public class DynamicDataSource extends AbstractRoutingDataSource{ @Override protected Object determineCurrentLookupKey() { // 使用DynamicDataSourceHolder保证线程安全，并且得到当前线程中的数据源key String dataSourceKey = DynamicDataSourceHolder.getDataSourceKey(); System.out.println("dataSourceKey ======> "+dataSourceKey); return dataSourceKey; } }
package com.light.dynamicdatasource; /** * * 使用ThreadLocal技术来记录当前线程中的数据源的key * */ public class DynamicDataSourceHolder { //写库对应的数据源key private static final String MASTER = "master"; //读库对应的数据源key private static final String SLAVE = "slave"; //使用ThreadLocal记录当前线程的数据源key private static final ThreadLocal<String> holder = new ThreadLocal<String>(); /** * 设置数据源key * @param key */ public static void putDataSourceKey(String key) { holder.set(key); } /** * 获取数据源key * @return */ public static String getDataSourceKey() { return holder.get(); } /** * 标记写库 */ public static void markMaster(){ putDataSourceKey(MASTER); } /** * 标记读库 */ public static void markSlave(){ putDataSourceKey(SLAVE); } }
package com.light.mapper; import com.light.domain.User; public interface UserMapper { public User getById(int id); public void deleteById(int id); public void insertUserCacheId(User user); }
package com.light.service; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import com.light.domain.User; import com.light.mapper.UserMapper; @Service public class UserService { @Autowired private UserMapper userMapper; public User getUser(int id) { return this.userMapper.getById(id); } public void delete(int id) { this.userMapper.deleteById(id); } public void insert(User user) { this.userMapper.insertUserCacheId(user); } }
package com.light.web; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import com.light.domain.User; import com.light.service.UserService; @RestController @RequestMapping("user") public class UserController { @Autowired private UserService userService; @RequestMapping("get/{id}") public User get(@PathVariable("id") int id) { System.out.println("查询的id是: "+ id); System.out.println("id: " + this.userService.getUser(id)); return this.userService.getUser(id); } @RequestMapping("delete/{id}") public String delete(@PathVariable("id") int id) { System.out.println("删除用户id: " + id); this.userService.delete(id); return "success"; } @RequestMapping("insert") public void insert() { System.out.println("增加用户信息记录"); User user = new User(4, "小黑", 19); this.userService.insert(user); System.out.println("增加用户成功：" + user); } }
配置文件：
UserMapper.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.light.mapper.UserMapper"> <select id="getById" parameterType="java.lang.Integer" resultType="com.light.domain.User"> select id, name, age from user where id = #{id} </select> <delete id="deleteById" parameterType="java.lang.Integer"> delete from user where id = #{id} </delete> <insert id="insertUserCacheId" parameterType="com.light.domain.User"> insert into user(id,name,age) values(#{id},#{name},#{age}) </insert> </mapper>
SqlMapConfig.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <settings> <!-- 获取数据库自增主键值 --> <setting name="useGeneratedKeys" value="true"/> <!-- 使用列别名替换列名，默认为 true --> <setting name="useColumnLabel" value="true"/> <!-- 开启驼峰命名转换：Table(create_time) => Entity(createTime) --> <setting name="mapUnderscoreToCamelCase" value="true"/> <!-- 开启懒加载 --> <setting name="lazyLoadingEnabled" value="true"/> <setting name="aggressiveLazyLoading" value="false"/> </settings> </configuration>
applicationContext-mybatis.xml
<?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:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd"> <context:component-scan base-package="com.light.*"> <context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/> </context:component-scan> <context:property-placeholder location="classpath:*.properties"/> <!-- 数据源 --> <bean id="dataSource" class="com.light.dynamicdatasource.DynamicDataSource"> <property name="targetDataSources"> <map key-type="java.lang.String"> <entry key="master" value-ref="masterDataSource"></entry> <entry key="slave" value-ref="slave01DataSource"></entry> </map> </property> <!-- 默认数据源 --> <property name="defaultTargetDataSource" ref="masterDataSource"/> </bean> <!-- 主库数据源 --> <bean id="masterDataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close"> <property name="url" value="${jdbc.master.url}"/> <property name="username" value="${jdbc.master.username}"/> <property name="password" value="${jdbc.master.password}"/> <property name="driverClassName" value="${jdbc.driver}"/> <property name="initialSize" value="5"/> <property name="minIdle" value="5"/> <property name="maxActive" value="50"/> </bean> <!-- 从库数据源 --> <bean id="slave01DataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close"> <property name="url" value="${jdbc.slave01.url}"/> <property name="username" value="${jdbc.slave01.username}"/> <property name="password" value="${jdbc.slave01.password}"/> <property name="driverClassName" value="${jdbc.driver}"/> <property name="initialSize" value="5"/> <property name="minIdle" value="5"/> <property name="maxActive" value="50"/> </bean> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource"></property> <!-- 引入 mybatis 配置文件 --> <property name="configLocation" value="classpath:mybatis/SqlMapConfig.xml"></property> <property name="typeAliasesPackage" value="com.light.domain"></property> <!-- sql配置文件 --> <property name="mapperLocations" value="classpath:mybatis/mapper/*.xml"></property> </bean> <!-- 扫描Mapper --> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="com.light.mapper"></property> <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property> </bean> <!-- 事务管理器 --> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource"/> </bean> <!-- 通知 --> <tx:advice id="txAdvice" transaction-manager="transactionManager"> <tx:attributes> <!-- 传播行为 --> <tx:method name="save*" propagation="REQUIRED"/> <tx:method name="insert*" propagation="REQUIRED"/> <tx:method name="delete*" propagation="REQUIRED"/> <tx:method name="update*" propagation="REQUIRED"/> <tx:method name="find*" propagation="SUPPORTS" read-only="true"/> <tx:method name="get*" propagation="SUPPORTS" read-only="true"/> <tx:method name="query*" propagation="SUPPORTS" read-only="true"/> </tx:attributes> </tx:advice> <!-- 切面 --> <bean id="dataSourceAspect" class="com.light.dynamicdatasource.DataSourceAspect"></bean> <aop:config proxy-target-class="true"> <aop:pointcut id="myPointcut" expression="execution(* com.light.service.*.*(..))" /> <!-- 事务切面 --> <aop:advisor advice-ref="txAdvice" pointcut-ref="myPointcut"/> <!-- 自定义切面 --> <aop:aspect ref="dataSourceAspect" order="-9999"> <aop:before method="before" pointcut-ref="myPointcut" /> </aop:aspect> </aop:config> <tx:annotation-driven transaction-manager="transactionManager"/> </beans>
jdbc.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.master.url=jdbc:mysql://localhost:3306/masterDB
jdbc.master.username=root
jdbc.master.password=123456
jdbc.slave01.url=jdbc:mysql://localhost:3306/slaverDB
jdbc.slave01.username=root
jdbc.slave01.password=123456
log4j.properties
# Global logging configuration
#u5728u5f00u53d1u73afu5883u4e0bu65e5u5fd7u7ea7u522bu8981u8bbeu7f6eu6210DEBUGuff0cu751fu4ea7u73afu5883u8bbeu7f6eu6210infou6216error
log4j.rootLogger=debug, stdout
# Console
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
springmvc.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd"> <!-- 只扫描含有@Controller注解 --> <context:component-scan base-package="com.light.web"> <context:include-filter type="annotation" expression="org.springframework.stereotype.Controller"/> </context:component-scan> <!-- 加载解析 @rquestMapping等注解的解析器 --> <mvc:annotation-driven> <!-- 处理responseBody 里面日期类型 --> <mvc:message-converters> <bean class="org.springframework.http.converter.json.MappingJackson2HttpMessageConverter"> <property name="objectMapper"> <bean class="com.fasterxml.jackson.databind.ObjectMapper"> <property name="dateFormat"> <bean class="java.text.SimpleDateFormat"> <constructor-arg type="java.lang.String" value="yyyy-MM-dd HH:mm:ss" /> </bean> </property> </bean> </property> </bean> </mvc:message-converters> </mvc:annotation-driven> </beans>
pom.xml
<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/maven-v4_0_0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.light</groupId> <artifactId>dynamicdatasource</artifactId> <packaging>war</packaging> <version>0.0.1-SNAPSHOT</version> <name>dynamicdatasource Maven Webapp</name> <url>http://maven.apache.org</url> <properties> <spring.version>4.3.7.RELEASE</spring.version> <mybatis.version>3.4.2</mybatis.version> <mybatis.spring.version>1.3.0</mybatis.spring.version> <slf4j.version>1.7.12</slf4j.version> <druid.version>1.0.28</druid.version> <commons-lang3.version>3.4</commons-lang3.version> <commons-io.version>2.4</commons-io.version> <commons-codec.version>1.10</commons-codec.version> <jackson.version>2.8.8</jackson.version> <fastjson.version>1.2.32</fastjson.version> <spring.redis.version>1.7.2.RELEASE</spring.redis.version> <mapper.version>3.4.5</mapper.version> <pagehelper.version>4.1.6</pagehelper.version> </properties> <dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>${commons-lang3.version}</version> </dependency> <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>${commons-io.version}</version> </dependency> <dependency> <groupId>commons-codec</groupId> <artifactId>commons-codec</artifactId> <version>${commons-codec.version}</version> </dependency> <!-- 日志处理 --> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> <version>${slf4j.version}</version> </dependency> <!-- Mybatis --> <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> <!-- MySql --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.8</version> </dependency> <!-- 连接池 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>${druid.version}</version> </dependency> <!-- Spring --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-beans</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aspects</artifactId> <version>${spring.version}</version> </dependency> <!-- Jackson Json处理工具包 --> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-databind</artifactId> <version>${jackson.version}</version> </dependency> <!-- fastjson --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>${fastjson.version}</version> </dependency> <!-- servlet API --> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>4.0.0</version> <scope>provided</scope> </dependency> <dependency> <groupId>javax.servlet.jsp</groupId> <artifactId>javax.servlet.jsp-api</artifactId> <version>2.3.1</version> <scope>provided</scope> </dependency> </dependencies> <build> <finalName>dynamicdatasource</finalName> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <version>3.7.0</version> <configuration> <source>1.7</source> <target>1.7</target> </configuration> </plugin> <plugin> <groupId>org.apache.tomcat.maven</groupId> <artifactId>tomcat7-maven-plugin</artifactId> <version>2.2</version> <configuration> <port>8080</port> <path>/</path> </configuration> </plugin> </plugins> </build> </project>
测试结果：
查询：
增加：http://localhost:8080/dynamicdatasource/user/insert
masterdb库的user表数据增加了，slaverdb库的user表没有增加记录
删除：
结果：masterdb库的user表的id=4的记录删除了。