MyBatis +Spring+TDDL 问答实例

准备升级新问答系统DAO层(iBatis->MyBatis),写一个spring+mybatis+tddl的demo作为准备,特此记录

1:首先在pom.xml加入以下依赖：

<!-- mybatis --><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>${mybatis_version}</version></dependency> <!-- mysql-spring --><dependency><groupId>org.mybatis</groupId><artifactId>mybatis-spring</artifactId><version>${mybatis_spring_version}</version></dependency>

<!-- taobao tddl -->

<dependency><groupId>com.taobao.tddl</groupId><artifactId>tddl-client</artifactId><version>2.4.4.1</version></dependency><!-- taobao tddl sequence --><dependency><groupId>com.taobao.tddl</groupId><artifactId>tddl-sequence</artifactId><version>2.4.3</version></dependency><dependency><groupId>com.taobao.diamond</groupId><artifactId>diamond-client</artifactId><version>3.0.4</version></dependency>

关于 spring 插件:

   Spring插件的开发，Mybatis官方有一段说明，大意是Mybatis 3发布之前，Spring 3的开发已经结束了，Spring团队不愿意与没有正式发布的第三方集成（比较谨慎啊），因此MyBatis官方决定自己开发，详见http://mybatis.github.com/spring/。

提示:spring 和MyBatis结合注意版本要求, http://mybatis.github.io/spring/zh/

 

2 :添加Spring的配置文件如下:

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">        <property name="dataSource" ref="tddl_ds"/>        <!-- mybatis配置文件的位置 -->        <property name="configLocation" value="classpath:mybatis-config-spring.xml"/>        <!-- domain的包路径，类似MyBatis的typeAliases配置 -->        <property name="typeAliasesPackage" value="org.xiuyuan.mybatis.demo.domain"/>        <!-- mapper配置文件的路径，类似MyBatis的mappers配置 -->        <property name="mapperLocations" value="classpath*:org/xiuyuan/mybatis/demo/dao/*.xml"/>    </bean>    <!-- 扫描接口类的包路径 -->    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">        <property name="basePackage" value="org.xiuyuan.mybatis.demo.dao"/>    </bean>

提示:

    1:domain的包路径和mapper路径 的配置可以自动扫描，避免手动维护.

    2:扫描接口类MapperScannerConfigurer 可是动态实现DAO接口并加入spring容器，大大节省开发成本.

3:加入问答TDDL配置,用于MyBatis.SqlSessionFactory的数据源(dataSource)

<!-- TDDL Datasource --><bean id="tddl_ds" class="com.taobao.tddl.client.jdbc.TDataSource" init-method="init"><property name="useLocalConfig" value="true" /><!--<property name="appRuleFile" value="classpath:springbeans-tddl.xml" />--><property name="appRuleFile" value="classpath:springbeans-tddl.xml" /><property name="appName" value="DEV_ETAO_WENDA_APP" /></bean><!-- sequence dao --><bean id="sequenceDao" class="com.taobao.tddl.client.sequence.impl.GroupSequenceDao" init-method="init"><property name="appName" value="DEV_ETAO_WENDA_APP" /><property name="dbGroupKeys"><list><value>${matrix.tba.tddl.groupname}</value></list></property><!-- 重试次数，在多个gourpDataSource的场景下，建议设置成1-2次。默认为2次 --><property name="retryTimes" value="1" /><!-- 内步长 ,默认为1000，取值在1-100000之间 --><property name="innerStep" value="100" /><property name="dscount" value="1" /><!-- 使用的表的表名 ，默认为sequence --><property name="tableName" value="tba_ids_new" /><!-- id生成器的字段名,默认为name --><property name="nameColumnName" value="type" /><!-- 存值的列的字段名,默认为value --><property name="valueColumnName" value="id" /><!-- 存修改时间的字段名 ,默认为gmt_modified --><property name="gmtModifiedColumnName" value="gmt_modified" /><property name="adjust" value="true" /></bean><!-- sequence map --><bean id="sequenceMap" class="org.xiuyuan.mybatis.demo.SequenceFactoryBean"><property name="sequenceDao" ref="sequenceDao" /><!-- 数据库中sequence的key，以逗号分隔  --><property name="sequenceKeys"><value>activity,userId,bar,bar_activity,blackId,blackIp,centerWord,operationId,overallWord,timeLimitId,userRole,userRoleApply,replyId,threadId,operationLogId,messageId,guiderStatisticId,userCollectionId</value></property></bean><bean class="org.xiuyuan.mybatis.demo.util.BaseDaoUtil"><property name="sequenceMap" ref="sequenceMap"></property></bean><bean id="root" class="com.taobao.tddl.common.config.beans.AppRule" init-method="init"><property name="readwriteRule" ref="readwriteRule" /></bean><bean id="readwriteRule" class="com.taobao.tddl.common.config.beans.ShardRule"><!-- 数据库类型 --><property name="dbtype" value="MYSQL" /><!-- 库默认路由规则: sql中的表名在tableRules里没有则会自动使用defaultDbIndex来进行查询  --><property name="defaultDbIndex" value="${matrix.tba.tddl.default.groupname}" /><!-- 表路由规则:key是逻辑表名, value是逻辑表的规则对象 --><property name="tableRules"><map><entry key="tba_buy_thread" value-ref="tba_buy_thread" /><entry key="tba_buy_thread_body" value-ref="tba_buy_thread_body" /><entry key="tba_buy_thread_reply" value-ref="tba_buy_thread_reply" /></map></property></bean><!-- 基本规则 --><bean id="tba_base_rule" class="com.taobao.tddl.common.config.beans.TableRule" abstract="true"><!-- 禁止查询所有物理表 --><property name="disableFullTableScan" value="false" /><!-- 逻辑表被分散在多少个库里,以逗号分隔,顺序敏感,顺序从0开始 --><property name="dbIndexes" value="${matrix.tba.tddl.groupname}" /><!-- 分库规则,目前不分库可以不配置 --><!--<property name="dbRules" value="(#userId#.longValue() % 32).intdiv(32)" />--></bean><!-- 求购帖子表 --><bean id="tba_buy_thread" class="com.taobao.tddl.common.config.beans.TableRule" parent="tba_base_rule"><property name="tbRules" value="${matrix.tba.tddl.tbrules.id}" /><property name="tbSuffix" value="${matrix.tba.tddl.tbsuffix.id}" /></bean><!-- 求购帖子内容表 --><bean id="tba_buy_thread_body" class="com.taobao.tddl.common.config.beans.TableRule" parent="tba_base_rule"><property name="tbRules" value="${matrix.tba.tddl.tbrules.thread_id}" /><property name="tbSuffix" value="${matrix.tba.tddl.tbsuffix.thread_id}" /></bean><!-- 求购帖子回复表 --><bean id="tba_buy_thread_reply" class="com.taobao.tddl.common.config.beans.TableRule" parent="tba_base_rule"><property name="tbRules" value="${matrix.tba.tddl.tbrules.thread_id}" /><property name="tbSuffix" value="${matrix.tba.tddl.tbsuffix.thread_id}" /></bean>

 

  4：编写DAO接口

package org.xiuyuan.mybatis.demo.dao;import org.apache.ibatis.annotations.Param;import org.xiuyuan.mybatis.demo.domain.BuyThreadDO;/** * Created with IntelliJ IDEA. * User: yijun.zyj * Date: 13-6-21 */public interface BuyThreadDao {  public void createBuyThread(BuyThreadDO thread) ;  public BuyThreadDO querySingleBuyThread(@Param("threadId") long threadId, @Param("includeDeleted") boolean includeDeleted);  public int deleteThreadById(@Param("threadId") long threadId) ;  public int deleteThreadInDarkRoom(@Param("threadId") long threadId, @Param("operatorId") long operatorId) ;  public int resumeThreadById(@Param("threadId") long threadId) ;  public int updateBuyThreadTCPAuditState(BuyThreadDO buyThread) ;}

提示:当接口方法有多个参数时，可以使用@Param("XXX")注解，便于sql配置文件提取参数，不必像iBatis那样必须封装成map再传入.

5:编写 sql映射xml文件

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"        "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="org.xiuyuan.mybatis.demo.dao.BuyThreadDao"><sql id="buyThread-full-columns">t.id, t.type_id, t.second_type_id, t.subject, t.summary, t.status, t.deleted, t.view_count, t.reply_count, t.options, t.author_id, t.author_nick, t.author_ip, t.last_reply_author_id, t.last_reply_author_nick, t.type_path, t.last_reply_id, t.last_reply_time, t.last_modified, t.security_status, t.security_operator_id, t.security_operation_time, t.notification_type, t.claim_time, t.claim_status, t.best_reply_id, t.best_user_id, t.best_user_nick, t.best_finish_date, t.best_reply_abstract, t.best_status, t.best_srp_content, t.image_url, t.tag, t.audit_state, t.audit_time, t.audit_user_id, t.audit_user_nick,t.tcp_audit_state, t.tcp_audit_time, t.tcp_auditor,t.gmt_create, t.gmt_modified, t.recommend_state,t.anonymous_state</sql>    <insert id="createBuyThread" parameterType="BuyThreadDO">    <![CDATA[insert into tba_buy_thread   (id, type_id, second_type_id, subject, summary, status, deleted, view_count, reply_count, options, author_id, author_nick, author_ip,type_path, last_reply_time, last_modified, security_status, notification_type, claim_status, best_status, image_url, tag, gmt_create, gmt_modified, recommend_state,anonymous_state)values   (#{id}, #{typeId}, #{secondTypeId}, #{subject}, #{summary}, 0, 0, 0, 0, 0, #{authorId}, #{authorNick}, #{authorIp},    #{typePath}, now(), now(), #{securityStatus}, #{notificationType}, 0, 0, #{imageUrl}, #{tag}, now(), now(), 0,#{anonymousState})]]></insert>    <select id="querySingleBuyThread" resultType = "BuyThreadDO">

       <!-- 无需编写resultMap,通过mapUnderscoreToCamelCase

只是用列名就映射到属性 -->        select <include refid="buyThread-full-columns"/>        from tba_buy_thread t        where t.id = #{threadId}        <if test="includeDeleted = false">            and t.deleted = 0 and t.tcp_audit_state = 0        </if>    </select>    <update id="deleteThreadById">update tba_buy_thread tsett.deleted = 1,t.gmt_modified = CURRENT_TIMESTAMPwhere t.id = #{threadId}</update>    <update id="deleteThreadInDarkRoom">update tba_buy_thread tsett.deleted = 1,t.security_operator_id = #{securityOperatorId},t.security_operation_time = CURRENT_TIMESTAMP,t.gmt_modified = CURRENT_TIMESTAMPwhere t.id = #{threadId}</update>    <update id="resumeThreadById">update tba_buy_thread tsett.deleted = 0,t.tcp_audit_state = 0,t.gmt_modified = CURRENT_TIMESTAMPwhere t.id = #{threadId}</update>    <update id="updateBuyThreadTCPAuditState"><![CDATA[UPDATE tba_buy_thread tSETt.tcp_audit_state = #{tcpAuditState},t.tcp_auditor = #{tcpAuditor},t.tcp_audit_time = CURRENT_TIMESTAMP,t.gmt_modified = CURRENT_TIMESTAMPWHEREt.id = #{id}]]></update></mapper>

提示:

  1: parameterType可以通过之前的别名(typeAliasesPackage)配置自动识别.

  2: 打开mapUnderscoreToCamelCase参数,Mybatis自动将下划线的命名格式转换为驼峰命名，如表字段名create_time，在domain类中定义为createTime，执行sql语句后，mybatis会自动把create_time转换为createTime，省去了写sql时指定别名的麻烦。

 

6：编写单元测试

package org.xiuyuan.mybatis.demo.dao;//ingore import/** * Created with IntelliJ IDEA. * User: yijun.zyj * Date: 13-6-21 * To change this template use File | Settings | File Templates. */@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration(locations = { "/springbeans-test.xml" })public class BuyThreadDaoTest {    protected final Logger log = LoggerFactory.getLogger(this.getClass());        @Resource    private BuyThreadDao buyThreadDao;        @Test    public void querySingleBuyThreadTest(){    long threadId = 1801;boolean includeDeleted = Boolean.TRUE;BuyThreadDO buyThreadDO = buyThreadDao.querySingleBuyThread(threadId, includeDeleted);log.info("result = "+buyThreadDO);    }        @Test    public void testNextId(){    long nextId = BaseDaoUtil.nextBuyThreadId();    log.info("id = " + nextId);    }        @Test    public void createBuyThreadTest(){    long threadId = 1801;boolean includeDeleted = Boolean.TRUE;BuyThreadDO buyThreadDO = buyThreadDao.querySingleBuyThread(threadId, includeDeleted);if(buyThreadDO == null){log.error("error");return;}long nextId = BaseDaoUtil.nextBuyThreadId();buyThreadDO.setId(nextId);buyThreadDO.setSubject(buyThreadDO.getSubject() + "-222");buyThreadDao.createBuyThread(buyThreadDO);    }     }