Java知识点梳理——读写分离

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;
    }
}