Java知识点梳理——读写分离
程序员文章站
2023-04-07 08:21:02
1、读写分离:可以通过Spring提供的AbstractRoutingDataSource类,重写determineCurrentLookupKey方法,实现动态切换数据源的功能;读写分离可以有效减轻写库的压力,又可以把查询数据的请求分发到不同读库; 2、写数据库:当调用insert、update、 ......
1、读写分离:可以通过spring提供的abstractroutingdatasource类,重写determinecurrentlookupkey方法,实现动态切换数据源的功能;读写分离可以有效减轻写库的压力,又可以把查询数据的请求分发到不同读库;
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-8 w.datasource.password=write123 #读库 r.datasource.url=jdbc\:mysql\://ip\:3306/ddt?characterencoding\=utf-8 r.datasource.password=read123 #连接池配置 c3p0.acquireincrement=3 c3p0.acquireretryattempts=10 c3p0.acquireretrydelay=1000 c3p0.initialpoolsize=20 c3p0.idleconnectiontestperiod=3600 c3p0.testconnectiononcheckout=true c3p0.minpoolsize=10 c3p0.maxpoolsize=80 c3p0.maxstatements=100 c3p0.numhelperthreads=10 c3p0.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; public class dynamicdatasource extends abstractroutingdatasource { /** * 重写determinecurrentlookupkey方法 */ @override protected object determinecurrentlookupkey() { object obj = dbhelper.getdbtype(); return obj; } }
d、dbhelper工具类
package com.eb3.ddt; import org.apache.commons.lang.stringutils; public class dbhelper { private static threadlocal<string> dbcontext = new threadlocal<string>(); // 写数据源标识 public final static string db_write = "datasourcewrite"; // 读数据源标识 public final static string db_read = "datasourceread"; /** * 获取数据源类型,即是写数据源,还是读数据源 * * @return */ public static string getdbtype() { string db_type = dbcontext.get(); if (stringutils.isempty(db_type)) { // 默认是写数据源 db_type = db_write; } return db_type; } /** * 设置该线程的数据源类型 * * @param str */ public static void setdbtype(string str) { dbcontext.set(str); } }
e、服务层调用
/*@aspect 此注解会影响数据源切换,运行代码得知不加的话会先执行dynamicdatasource里的determinecurrentlookupkey方法,后执行service层里dbhelper.setdbtype()方法,导致数据源切换失败! */ @aspect @component("userservice") public class userserviceimpl extends baseserviceimpl<user, user, integer> implements userservice { @resource private userdao userdao; @override protected basedao<user, integer> getdao() { return this.userdao; } @override public void save(user user) { dbhelper.setdbtype(dbhelper.db_write); // 写库 this.userdao.save(user); } @override public user findbyusername(string username) { dbhelper.setdbtype(dbhelper.db_read); // 读库 list<user> userlist = this.userdao.findby("username", username); return collectionutils.isnotempty(userlist) ? userlist.get(0) : null; } }