1、读写分离：可以通过Spring提供的AbstractRoutingDataSource类，重写determineCurrentLookupKey方法，实现动态切换数据源的功能；读写分离可以有效减轻写库的压力，又可以把查询数据的请求分发到不同读库；MySql主从同步传送门
2、写数据库：当调用insert、update、delete及一些实时数据用到的库；
3、读数据库：当调用select查询数据用到的库；
4、JaveWeb工程通过AbstractRoutingDataSource类实现读写分离；
a、jdbc.properties文件配置读写数据源
datasource.type=mysql
datasource.driverClassName=com.mysql.jdbc.Driver
datasource.username=root#写库w.datasource.url=jdbc\:mysql\://127.0.0.1\:3306/ddt?characterEncoding\=utf-8w.datasource.password=write123#读库r.datasource.url=jdbc\:mysql\://IP\:3306/ddt?characterEncoding\=utf-8r.datasource.password=read123
#连接池配置
c3p0.acquireIncrement=3c3p0.acquireRetryAttempts=10c3p0.acquireRetryDelay=1000c3p0.initialPoolSize=20c3p0.idleConnectionTestPeriod=3600c3p0.testConnectionOnCheckout=truec3p0.minPoolSize=10c3p0.maxPoolSize=80c3p0.maxStatements=100c3p0.numHelperThreads=10c3p0.maxIdleTime=10800
b、application.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:jee="http://www.springframework.org/schema/jee" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:task="http://www.springframework.org/schema/task" xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
http://www.springframework.org/schema/jee
http://www.springframework.org/schema/jee/spring-jee-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
http://www.springframework.org/schema/task
http://www.springframework.org/schema/task/spring-task-3.0.xsd"> <!-- 使用annotation 自动注册bean,并保证@Required,@Autowired的属性被注入 --><context:component-scan base-package="com.eb3"><context:include-filter type="annotation" expression="org.springframework.stereotype.Service"/><context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/></context:component-scan><bean class="org.springframework.beans.factory.config.PreferencesPlaceholderConfigurer"><property name="ignoreResourceNotFound" value="true"/><property name="properties" ref="configProperties"/></bean><bean id="configProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean"><property name="locations"><list><value>classpath*:jdbc.properties</value></list></property></bean><context:property-placeholder location="classpath:jdbc.properties"/><!-- 定义Hibernate读数据源 --><bean id="dataSourceRead" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"><property name="driverClass"><value>${datasource.driverClassName}</value></property><property name="jdbcUrl"><value>${r.datasource.url}</value></property><property name="user"><value>${datasource.username}</value></property><property name="password"><value>${r.datasource.password}</value></property><!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。--><property name="acquireIncrement"><value>${c3p0.acquireIncrement}</value></property><!--定义在从数据库获取新连接失败后重复尝试的次数。--><property name="acquireRetryAttempts"><value>${c3p0.acquireRetryAttempts}</value></property><!--两次连接中间隔时间，单位毫秒。--><property name="acquireRetryDelay"><value>${c3p0.acquireRetryDelay}</value></property><property name="initialPoolSize"><value>${c3p0.initialPoolSize}</value></property><property name="testConnectionOnCheckout"><value>${c3p0.testConnectionOnCheckout}</value></property><property name="minPoolSize"><value>${c3p0.minPoolSize}</value></property><property name="maxPoolSize"><value>${c3p0.maxPoolSize}</value></property><property name="maxIdleTime"><value>${c3p0.maxIdleTime}</value></property><property name="idleConnectionTestPeriod"><value>${c3p0.idleConnectionTestPeriod}</value></property><property name="maxStatements"><value>${c3p0.maxStatements}</value></property><property name="numHelperThreads"><value>${c3p0.numHelperThreads}</value></property></bean><!-- 定义Hibernate写数据源 --><bean id="dataSourceWrite" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"><property name="driverClass"><value>${datasource.driverClassName}</value></property><property name="jdbcUrl"><value>${w.datasource.url}</value></property><property name="user"><value>${datasource.username}</value></property><property name="password"><value>${w.datasource.password}</value></property><!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。--><property name="acquireIncrement"><value>${c3p0.acquireIncrement}</value></property><!--定义在从数据库获取新连接失败后重复尝试的次数。--><property name="acquireRetryAttempts"><value>${c3p0.acquireRetryAttempts}</value></property><!--两次连接中间隔时间，单位毫秒。--><property name="acquireRetryDelay"><value>${c3p0.acquireRetryDelay}</value></property><property name="initialPoolSize"><value>${c3p0.initialPoolSize}</value></property><property name="testConnectionOnCheckout"><value>${c3p0.testConnectionOnCheckout}</value></property><property name="minPoolSize"><value>${c3p0.minPoolSize}</value></property><property name="maxPoolSize"><value>${c3p0.maxPoolSize}</value></property><property name="maxIdleTime"><value>${c3p0.maxIdleTime}</value></property><property name="idleConnectionTestPeriod"><value>${c3p0.idleConnectionTestPeriod}</value></property><property name="maxStatements"><value>${c3p0.maxStatements}</value></property><property name="numHelperThreads"><value>${c3p0.numHelperThreads}</value></property></bean><!-- 动态数据源 --><bean id="dynamicDataSource" class="com.eb3.ddt.DynamicDataSource"><!-- 通过key-value关联数据源 --><property name="targetDataSources"><map><entry value-ref="dataSourceWrite" key="dataSourceWrite"></entry><entry value-ref="dataSourceRead" key="dataSourceRead"></entry></map></property><property name="defaultTargetDataSource" ref="dataSourceWrite"/></bean><!-- 设置sessionFactory --><bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"><!-- 依赖注入数据源，注入正是上文定义的dataSource --><property name="dataSource" ref="dynamicDataSource"/><property name="packagesToScan" value="com.eb3.ddt.pojo,com.eb3.loan.pojo"/><!--定义Hibernate的SessionFactory的属性 --><property name="hibernateProperties"><props><!-- 指定Hibernate的连接方言--><prop key="hibernate.dialect"> ${hibernate.dialect}
</prop><prop key="hibernate.connection.autocommit">${hibernate.connection.autocommit}</prop><!-- 制定Hibernate是否打印SQL语句 --><prop key="hibernate.show_sql">${hibernate.show_sql}</prop><prop key="hibernate.format_sql">${hibernate.format_sql}</prop><!-- 设create(启动创建),create-drop(启动创建,退出删除),update(启动更新),validate(启动验证) --><prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop><prop key="connection.characterEncoding">utf-8</prop><!-- 设置二级缓存 --><prop key="hibernate.cache.user_query_cache">${hibernate.cache.user_query_cache}</prop><prop key="hibernate.user_second_level_cache">${hibernate.user_second_level_cache}</prop><prop key="hibernate.cache.provider_class">${hibernate.cache.class}</prop><prop key="hibernate.cache.provider_configuration_file_resource_path">${hibernate.ehcache_config_file}</prop></props></property></bean><!-- 事务管理器配置,单数据源事务 --><bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager"><property name="sessionFactory" ref="sessionFactory"/></bean>
<tx:advice id="txAdvice" transaction-manager="transactionManager"><tx:attributes><tx:method name="save*" propagation="REQUIRED" rollback-for="Exception"/><tx:method name="add*" propagation="REQUIRED" rollback-for="Exception"/><tx:method name="delete*" propagation="REQUIRED" rollback-for="Exception"/><tx:method name="update*" propagation="REQUIRED" rollback-for="Exception"/><tx:method name="merge*" isolation="READ_COMMITTED" propagation="REQUIRED" rollback-for="Exception"/><tx:method name="get*" read-only="true"/><tx:method name="find*" read-only="true"/><tx:method name="list*" read-only="true"/><tx:method name="select*" read-only="true"/><tx:method name="*" propagation="REQUIRED"/></tx:attributes></tx:advice><aop:config><aop:pointcut id="interceptorPointCuts" expression="execution(* com.eb3.*.service.*.*(..))"/><aop:advisor advice-ref="txAdvice" pointcut-ref="interceptorPointCuts"/></aop:config><aop:aspectj-autoproxy proxy-target-class="true"/><!-- 定时器配置 task:scheduler@pool-size调度线程池的大小，调度线程在被调度任务完成前不会空闲 task:executor/@pool-size：可以指定执行线程池的初始大小、最大大小
task:executor/@queue-capacity：等待执行的任务队列的容量 task:executor/@rejection-policy：当等待队已满时的策略，分为丢弃、由任务执行器直接运行等方式
@Async 异步任务时 task任务执行线程数 task:scheduler 和 task:executor 两个线程池同样起作用 没有异步注解时
task任务执行线程数只受task:scheduler的线程池大小影响 --><!-- 声明一个具有10个线程的池，每一个对象将获取同样的运行机会 --><task:scheduler id="scheduler" pool-size="10"/><task:executor id="executor" keep-alive="3600" pool-size="100-300" queue-capacity="500" rejection-policy="CALLER_RUNS"/><task:annotation-driven executor="executor" scheduler="scheduler"/></beans>
c、继承AbstractRoutingDataSource类的动态数据源类DynamicDataSource
package com.eb3.ddt;import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;publicclassDynamicDataSourceextends AbstractRoutingDataSource {
/** * 重写determineCurrentLookupKey方法
*/ @Override
protected Object determineCurrentLookupKey() {
Object obj = DBHelper.getDbType();
return obj;
}
}
d、DBHelper工具类
package com.eb3.ddt;import org.apache.commons.lang.StringUtils;publicclass DBHelper {
privatestaticThreadLocal dbContext =newThreadLocal();
// 写数据源标识publicfinalstaticString DB_WRITE = "dataSourceWrite";
// 读数据源标识publicfinalstaticString DB_READ = "dataSourceRead";
/** * 获取数据源类型，即是写数据源，还是读数据源
*
* @return*/publicstatic String getDbType() {
String db_type = dbContext.get();
if (StringUtils.isEmpty(db_type)) {
// 默认是写数据源db_type = DB_WRITE;
}
return db_type;
}
/** * 设置该线程的数据源类型
*
* @param str
*/publicstaticvoid setDbType(String str) {
dbContext.set(str);
}
}
e、服务层调用
/*@Aspect 此注解会影响数据源切换，运行代码得知不加的话会先执行DynamicDataSource里的determineCurrentLookupKey方法，后执行Service层里DBHelper.setDbType()方法，导致数据源切换失败！*/@Aspect
@Component("userService")publicclassUserServiceImplextendsBaseServiceImplimplements UserService {
@Resource
private UserDao userDao;
@Override
protectedBaseDao getDao() {
returnthis.userDao;
}
@Override
publicvoidsave(User user) {DBHelper.setDbType(DBHelper.DB_WRITE); // 写库this.userDao.save(user);
}
@Override
publicUser findByUserName(String username) {DBHelper.setDbType(DBHelper.DB_READ); // 读库List userList =this.userDao.findBy("username", username);
returnCollectionUtils.isNotEmpty(userList) ? userList.get(0) :null;
} 欢迎工作一到五年的Java工程师朋友们加入Java群： 891219277
群内提供免费的Java架构学习资料（里面有高可用、高并发、高性能及分布式、Jvm性能调优、Spring源码，MyBatis，Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多个知识点的架构资料）合理利用自己每一分每一秒的时间来学习提升自己，不要再用"没有时间“来掩饰自己思想上的懒惰！趁年轻，使劲拼，给未来的自己一个交代！
网友评论