2020-07-12 01:46:47 登录注册 RSS

当前位置: 公理网 >> 正义之家 >> spring+mybatis利用interceptor(plugin)兑现数据库读写分离

spring+mybatis利用interceptor(plugin)兑现数据库读写分离
发布时间:06-30| 来源:公理网 | 点击发表评论
84456132","ab":"new"}'target="_blank"href="https://blog.csdn.net/san_yun/article/details/84456132">深入JVM锁机制1-synchro...
trb0811:ContentionList中那些有资格成为候选人的线程被移到EntryList,有资格成为候选人的线程如何判断有资格????


public?class?PrototypeLoadBalanceDataSource?extends?AbstractRoutingDataSource??{??
????private?Lock?lock?=?new?ReentrantLock();??
????private?int?counter?=?0;??
????private?int?dataSourceNumber?=?3;??
[email protected]??
????protected?Object?determineCurrentLookupKey()?{??
????????lock.lock();??
????????try{??
????????????counter++;??
????????????int?lookupKey?=?counter?%?getDataSourceNumber();??
????????????return?new?Integer(lookupKey);??
????????}finally{??
????????????lock.unlock();??
????????}??
????}??
????//?...??

我们在介绍AbstractRoutingDataSource的时候说过,要继承该类,通常只需要给出determineCurrentLookupKey()方法的逻辑即可。下面是针对PrototypeLoadBalanceDataSource的配置:


bean?id="dataSourc1"?destroy-method="close"??
????property?name="url"?value=".."/??
????property?name="driverClassName"?value=".."/??
????property?name="username"?value=".."/??
????property?name="password"?value=".."/??
????!--?other?property?settings?--??
/bean??
bean?id="dataSource2"?destroy-method="close"??
????property?name="url"?value=".."/??
????property?name="driverClassName"?value=".."/??
????property?name="username"?value=".."/??
????property?name="password"?value=".."/??
????!--?other?property?settings?--??
/bean??
bean?id="dataSource3"?destroy-method="close"??
????property?name="url"?value=".."/??
????property?name="driverClassName"?value=".."/??
????property?name="username"?value=".."/??
????property?name="password"?value=".."/??
????!--?other?property?settings?--??
/bean??
util:map?id="dataSources"??
????entry?key="0"?value-ref="dataSource1"/??
????entry?key="1"?value-ref="dataSource2"/??
????entry?key="2"?value-ref="dataSource3"/??
/util:map??
bean?id="dataSourceLookup"???
????constructor-arg??
????????ref?bean="dataSources"/??
????/constructor-arg??
/bean??
bean?id="dataSource"???
????property?name="defaultTargetDataSource"?ref="dataSourc1"/??
????property?name="targetDataSources"?ref="dataSources"/??
????property?name="dataSourceLookup"?ref=""/??
/bean??
bean?id="jdbcTemplate"???
????property?name="dataSource"?ref="dataSource"/??
/bean??
bean?id="someDao"???
????property?name=""jdbcTemplate""?ref=""jdbcTemplate""/??
????!--?other?property?settings?--??
/bean??



使用spring的动态路由实现数据库读写分离

Spring2.0.1以后的版本已经支持配置多数据源,并且可以在运行的时候动态加载不同的数据源。通过继承AbstractRoutingDataSource就可以实现多数据源的动态转换。目前做的项目就是需要访问2个数据源,每个数据源的表结构都是相同的,所以要求数据源的变动对于编码人员来说是透明,也就是说同样SQL语句在不同的环境下操作的数据库是不一样的。具体的流程如下:



1.建立一个获得和设置上下文的类


public?class?JdbcContextHolder?{??
????private?static?final?ThreadLocalString?contextHolder?=?new?ThreadLocalString????
????public?static?void?setJdbcType(String?jdbcType)?{????
????????contextHolder.set(jdbcType);??
????}????
????public?static?void?setSlave(){??
????????setJdbcType("slave");??
????}??
????public?static?void?setMaster(){??
????????clearJdbcType();??
????}??
????public?static?String?getJdbcType(){????
????????return?(String)?contextHolder.get();???
????}????
????public?static?void?clearJdbcType()?{????
????????contextHolder.remove();????
????}????

?


2.建立动态数据源类,这个类必须继承AbstractRoutingDataSource


package?com.lvye.base.dao.impl.jdbc;??
import?org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;????
public?class?DynamicDataSource?extends?AbstractRoutingDataSource{??
????/*(non-Javadoc)?
?????*@see?org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource#determineCurrentLookupKey()?
?????*@author?wenc?
?????*/??
[email protected]??
?????protected?Object?determineCurrentLookupKey()?{??
????????return?JdbcContextHolder.getJdbcType();??
?????}??

这个类实现了determineCurrentLookupKey方法,该方法返回一个Object,一般是返回字符串。该方法中直接使用了JdbcContextHolder.getJdbcType();方法获得上下文环境并直接返回。


?


3.编写spring的配置文件配置数据源


????bean?id="master"?destroy-method="close"??
????????property?name="driverClass"??
????????????valuecom.mysql.jdbc.Driver/value??
????????/property??
????????property?name="jdbcUrl"??
????????????valuejdbc:mysql://192.168.18.143:3306/wenhq?useUnicode=truecharacterEncoding=utf-8/value??
????????/property??
????????property?name="user"??
????????????valueroot/value??
????????/property??
????????property?name="password"??
????????????value/value??
????????/property??
????/bean??
????bean?id="slave"?destroy-method="close"??
????????property?name="driverClass"??
????????????valuecom.mysql.jdbc.Driver/value??
????????/property??
????????property?name="jdbcUrl"??
????????????valuejdbc:mysql://192.168.18.144:3306/?wenhq?useUnicode=truecharacterEncoding=utf-8/value??
????????/property??
????????property?name="user"??
????????????valueroot/value??
????????/property??
????????property?name="password"??
????????????value/value??
????????/property??
????/bean??
????bean?id="mySqlDataSource"????
????????property?name="targetDataSources"???
????????????map???
????????????????entry?key="slave"?value-ref="slave"/???
????????????/map???
????????/property???
????????property?name="defaultTargetDataSource"?ref="master"/???
????/bean???
????bean?id="jdbcTemplate"???
????????property?name="dataSource"?ref="mySqlDataSource"?/??
????/bean??
????bean?id="transactionManager"???
????????property?name="dataSource"?ref="mySqlDataSource"?/??
????/bean??
/beans??

在这个配置中可以看到首先配置两个真实的数据库连接,使用的msyql数据库;master和slave是按照mysql配置的主从关系的数据库,数据会自动实时同步mySqlDataSource会根据上下文选择不同的数据源。在这个配置中第一个property属性配置目标数据源,entrykey="slave"value-ref="slave"/中key的值必须要和JdbcContextHolder类中设置的参数值相同,如果有多个值,可以配置多个entry标签。第二个property属性配置默认的数据源,我们一般默认为主数据库。有些朋友喜欢使用hibernate,只需要把上面的jdbcTemplate替换为hibernate的就可以了。

4.多数据库连接配置完毕,简单测试


public?void?testSave()?throws?Exception{????
????jdbcContextHolder.setSlave();//设置从数据源????
????Test?test?=?new?Test();????
????test.setTest("www.wenhq.com.cn");???????????????
????mydao.save(test);//使用dao保存实体?????????????
?????jdbcContextHolder.setMaster();//设置主数据源??
????mydao.save(test);//使用dao保存实体到另一个库中?????????????
}????

?


5.实现读写分离,上面的测试通过了,现在就简单了我的程序是使用jdbc实现的保存数据,只是使用了c3p0的数据库连接池而已。把所有访问数据库的方法包装一下,统一调用。把执行更新的sql发送到主数据库了



把查询的发送到从数据库,需要注意的是像LAST_INSERT_ID这类的查询需要特殊处理,必须发送到主数据库,建议增加专门的方法,用于获取自增长的主键。


public?List?findObject(String?queryString,?Class?clazz)?{??
????JdbcContextHolder.setSlave();??
????log.debug("findObject-sql:"?+?queryString);??
????List?list?=?jdbcTemplate.queryForList(queryString);??
????try?{??
????????list?=?StringBase.convertList(list,?clazz);//?将List转化为Listclazz??
????}?catch?(Exception?e)?{??
????????log.error("List?convert?ListObject?error:"?+?e);??
????}??
????AbstractRoutingDataSourcereturn?list;??


1.前提


???好长时间不写博客了,应该吐槽,写点什么东西了!最近在研究数据库读写分离,分表分库的一些东西。其实这个问题好早之前就想好,只是以前使用hibernate,难点是不好判断什么样的sql走读库,什么样的sql走主库?用正则匹配开头或许可以,/^select没想出什么好的解决方法,mybatis就不一样了,mappedstatement有commandtype属性,象select,update,delete等类型,为实现读写分离打下来良好的基矗


2.解决方法


???LazyConnectionProxy+RoutingDataSource?+??Plugin


在SqlSessionTemplate,创建DefaultSqlSession的时候,使用connectionproxy的代理,这时并没有真正的获取connection,因为我们不知道是要取读还是写的数据源。待到StatementHandler的prepare()使用connection创建PreparedStatement的时候再根据mappedstatement的commandType去路由获取真实的connection。


??RoutingDataSource支持一主一从,或者一主多从并采用roundrobin的方式简单负载均衡,预留接口路由和负载均衡策略可自定义。


??不支持事务,适合autocommit为true的场景。表述能力


?


applicationContext-common.xml



?


?xml?version="1.0"?encoding="UTF-8"???
beans?xmlns="http://www.springframework.org/schema/beans"??
????????xmlns:aop="http://www.springframework.org/schema/aop"?xmlns:tx="http://www.springframework.org/schema/tx"??
????????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-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/tx????
????????http://www.springframework.org/schema/tx/spring-tx-3.0.xsd??
????????http://www.springframework.org/schema/context??
????????http://www.springframework.org/schema/context/spring-context-3.0.xsd"??
????????!--?导入属性配置文件?--??
????????context:property-placeholder?location="classpath*:*.properties"?/??
????bean?id="abstractDataSource"?abstract="true"??
?????????????????
????????????????destroy-method="close"??
????????????????property?name="driverClass"?value="com.mysql.jdbc.Driver"?/??
????????????????property?name="user"?value="root"?/??
????????????????property?name="password"?value=""?/??
????????/bean??
????????bean?id="readDS"?parent="abstractDataSource"??
????????????????property?name="jdbcUrl"?value="jdbc:mysql://localhost:3306/test"?/??
????????/bean??
??????????
????????bean?id="writeDS"?parent="abstractDataSource"??
????????????????property?name="jdbcUrl"?value="jdbc:mysql://localhost:3306/test"?/??
????????/bean??
??????????
????????!--简单的一个master和一个slaver?读写分离的数据源?--??
????????bean?id="routingDS"???
????????????property?name="targetDataSources"??
?????????????????map?key-type="java.lang.String"??
?????????????????????entry?key="read"?value-ref="readDS"/entry??
?????????????????????entry?key="write"?value-ref="writeDS"/entry??
?????????????????/map??
????????????/property??
????????????property?name="defaultTargetDataSource"?ref="writeDS"/property??
????????/bean??
??????????
????????!--?适用于一个master和多个slaver的场景,并用roundrobin做负载均衡?--??
????????bean?id="roundRobinDs"????
??????????????property?name="writeDataSource"??ref="writeDS"/property??
??????????????property?name="readDataSoures"??
??????????????????list??
??????????????????????ref?bean="readDS"/??
??????????????????????ref?bean="readDS"/??
??????????????????????ref?bean="readDS"/??
??????????????????/list??
??????????????/property??
??????????????property?name="readKey"?value="READ"/property??
??????????????property?name="writeKey"?value="WRITE"/property??
????????/bean??
??????????
????????bean?id="sqlSessionFactory"???
????????????????property?name="dataSource"?ref="routingDS"?/??
????????????????property?name="configLocation"?value="classpath:mybatis-config.xml"?/??
????????????????!--?mapper和resultmap配置路径?--??
????????????????property?name="mapperLocations"??
????????????????????????list??
????????????????????????????????valueclasspath:com/test/rwmybatis/mapper/**/*-Mapper.xml??
????????????????????????????????/value??
????????????????????????/list??
????????????????/property??
????????/bean??
??????????
????????bean?id="sqlSessionTemplate"????
??????constructor-arg?ref="sqlSessionFactory"?/??
????/bean??
????????!--?通过扫描的模式,扫描目录下所有的mapper,?根据对应的mapper.xml为其生成代理类--??
????????bean?id="mapper"???
????????????????property?name="basePackage"?value="com.test.rwmybatis.mapper"?/??
????????????????property?name="sqlSessionTemplate"?ref="sqlSessionTemplate"/property??
????????/bean??
!--????bean?id="monitor"?/bean?--??
!--????aop:config?--??
!--???????aop:pointcut?expression="execution(*?com.taofang.smc.persistence..*.*(..))"??id="my_pc"/?--??
!--???????aop:advisor?advice-ref="monitor"?pointcut-ref="my_pc"/?--??
!--????/aop:config?--??
/beans

最新新闻

手机浏览

公理网 版权所有

公理网 Total 0.036816(s) query 6, 报料QQ:点击这里

给我发消息