• Java实现数据库的读写分离


    引言

    1、读写分离:可以通过Spring提供的AbstractRoutingDataSource类,重写determineCurrentLookupKey方法,实现动态切换数据源的功能;读写分离可以有效减轻写库的压力,又可以把查询数据的请求分发到不同读库;

    2、写数据库:当调用insert、update、delete及一些实时数据用到的库;

    3、读数据库:当调用select查询数据用到的库;

    4、JaveWeb工程通过AbstractRoutingDataSource类实现读写分离;

     一、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=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

     二、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>

    三、继承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;

        }

    }

    四、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);

        }

    }

    五、服务层调用

    /*@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;

        }

    }

  • 相关阅读:
    java 微信公众服务平台 下发 模板消息
    java web实现 忘记密码(找回密码)功能及代码
    DES加密后get获取url参数无法解密问题
    java实现url转码、解码
    java 实现 DES加密 解密算法
    oracle sqlplus 导出csv文件
    oracle导出多CSV文件的靠谱的
    Oracle 释放过度使用的Undo表空间
    Archiving not possible: No primary destinations errors
    Linux 之 shell 比较运算符
  • 原文地址:https://www.cnblogs.com/liujinhua306/p/9808420.html
Copyright © 2020-2023  润新知