记录一次bug解决过程：resultType和手动开启事务

一、总结

二、BUG描述：MyBatis中resultType使用

　　MyBatis中的resultType类似于入参：parameterType。先看IDCM项目中的实际使用案例代码，如下：

// List<Integer> orderIds = assetBeanMapperExt.getOrderIdsByParentIds(SqlStringUtil.formatInStr(logisticsOrder.getParentIds()));

　　我们的目的是通过*Ext层直接返回一个list，list中有我们所需要操纵的数据。logisticsOrder.getParentIds()是前端传入的String类型的id字符串，用逗号或者封号分隔，这里，我们用工具类SqlStringUtil来处理它：

package com.alibaba.tboss.workorder.util;
public class SqlStringUtil {

    private final static String replaceChar_comma     = ",";
    private final static String replaceChar_semicolon = ";";

    // 格式化多个key值查询
    public static String formatInStr(String queryStr) {
        return queryInStr(sliptQueryStr(queryStr));
    }

    // 返回sql查询中的in集合
    public static String queryInStr(String[] queryStrs) {
        if (null == queryStrs || 0 == queryStrs.length) return null;
        StringBuffer buf = new StringBuffer();
        for (int i = 0; i < queryStrs.length; i++) {
            if (i != 0) buf.append(",");
            buf.append("'").append(queryStrs[i]).append("'");
        }
        return buf.toString();
    }

    // 查询字符串分割字符串数组
    public static String[] sliptQueryStr(String queryStr) {
        if (null == queryStr || "".equals(queryStr.trim())) return null;
        queryStr = queryStr.replaceAll(SqlStringUtil.replaceChar_comma, " ").replaceAll(replaceChar_semicolon, " ");
        return queryStr.split("\s+");
    }
}

上面的代码是将web端传入的字符串id，拼接成SQL中的字符串，采用${}替换符，将结果直接拼接到SQL语句中，完成SQL语句的查询。

// List<Integer> getOrderIdsByParentIds(String parentIds);

在*MapperExt.java中定义了上述方法后，我们在*MapperExt.xml中的代码如下：

    <select id="getOrderIdsByParentIds" parameterType="java.lang.String" resultType="java.lang.Integer">
    　　SELECT
        　　DISTINCT order_id
    　　FROM
        　　idc_asset_list
    　　WHERE
        　　is_deleted = 'n'
    　　AND parent_order_id IN (${_parameter})
    </select>

注意我们这里的传入参数是String，传出参数是Integer类型，由于是多个数据，所以用List来接收返回的数据。特别地，当有多个参数的时候，我们可以使用resultType="java.util.HashMap"。实例如下：

    /**
     * 
    * @Title: getAllPermissions
    * @Description: 获取所有的权限
    * @author： liupeng.lp@alibaba-inc.com
    * @Date: 2014年5月12日
     */
    @Override
    public DataResult<Map<String, Object>> getAllPermissions(
            PagePara pagePara,
            String permission_name) {
        Map<String, Object> _map_result = new HashMap<String, Object>();
        if(pagePara == null){
            pagePara = new PagePara();
            pagePara.setSkip(0);
            pagePara.setTake(10);
        }
        _map_result.put("begin", pagePara.getSkip());
        _map_result.put("length", pagePara.getTake());
        
        if(!StringUtil.isBlank(permission_name)){
            _map_result.put("permission_name", permission_name.trim());
        }
        DataResult<Map<String, Object>> dataResult = new DataResult<Map<String, Object>>();
        List<Map<String, Object>> dataRes = appPermissionMapperExt.getAllPermissions(_map_result);
        int count = appPermissionMapperExt.countAllPermissions(_map_result);
        dataResult.setCount(count);
        dataResult.setData(dataRes);
        return dataResult;
    }

其中关键代码：List<Map<String, Object>> dataRes = appPermissionMapperExt.getAllPermissions(_map_result);传入一个map结构，输出一个map结构的数据。其*MapperExt.xml层代码如下：

<SELECT id = "getAllPermissions" parameterType = "java.util.HashMap" resultType = "java.util.HashMap" > 
　　SELECT
        p.id,
        p.res_type AS permission_type,
        p.res_name AS permission_name
    FROM
        app_permission AS p
    WHERE
        p.is_deleted = 'n' 
    <IF test = "permission_name!=null" >
        AND p.res_name LIKE CONCAT('%', #{permission_name}, '%')
    </IF >
    ORDER BY
        CONVERT (p.res_name USING gbk)
    LIMIT #{begin},#{length}
</SELECT>

值得注意的是：resultType为HashMap类型，使用了List<Map<String,Object>>来接收。此外，对于排序：数据库中存储res_name的字段采用的是utf8字符集，需要在排序的时候对字段进行转码，转码方式CONVERT (p.res_name USING gbk)。

三、BUG描述：标准的列表页查询代码如何写

　　业务逻辑层的实现如下：

    /**
     * 分页获取工单列表
     * 
     * @return
     */
    @Override
    public DataResult<WorkOrderMain> queryOrderPagination(WorkOrderMain orderMain, PagePara pagePara) {
        DataResult<WorkOrderMain> dr = new DataResult<WorkOrderMain>();
        try {
            Map<String, Object> queryPara = getQueryPara(orderMain, pagePara);

            int count = assetUpDownMapperExt.selectAssetUpDownCnt(queryPara);
            List<WorkOrderMain> list = null;
            if (count > 0) {
                list = assetUpDownMapperExt.selectAssetUpDownOrder(queryPara);
                if (null != list) {
                    AssetUpDownOrderUtil.transValueForPage(list);
                    AssetUpDownOrderUtil.setWorkOrderSla(list);
                    AssetUpDownOrderUtil.setShowValue(list);
                }

            } else {
                list = new ArrayList<WorkOrderMain>();
            }
            dr.setData(list);
            dr.setCount(count);
        } catch (Exception e) {
            logger.error(" WorkOrderUpDownBoImpl_queryOrderPagination_error [orderMain={}]:",
                         JSON.toJSON(orderMain).toString(), e);
            throw new ServiceException(ErrorCode.Query_Error, e);
        }
        return dr;
    }

　　先count，然后再select，接着对查询出来的数据转义。对于*MapperExt中的查询最好共用一个where来查询，如下：

    <select id="selectAssetUpDownCnt" parameterType="java.util.HashMap" resultType="java.lang.Integer">
        select count(t.id) 
        <include refid="page_WhereSql"/>
    </select>

    <select id="selectAssetUpDownOrder" parameterType="java.util.HashMap" resultMap="assetUpDownMap">
        select t.id, t.title, t.category,t.order_device_type, t.subject, t.demander, t.is_atomic, t.atomic_id, t.operationer, 
               t.operation_role, t.state, t.sub_state, t.expect_time, t.sla, t.evaluation, t.creator, t.create_source, 
               t.source_key, t.gmt_create, t.modifier, t.gmt_modified, t.is_deleted, t.remark, t.parent_id,
               t.asset_total,t.sla_standard,t.sla_unit,t.effective_date,t.is_timeout,t.statement_date,t.finish_asset_total,t.identify_type,
               a.updown_id, a.asset_type, a.opt_type, a.take_over_time,a.site,a.site_name 
            <include refid="page_WhereSql"/>
            order by t.gmt_create desc
            limit #{begin},#{length}
    </select>

    <sql id="page_WhereSql">
        from idc_work_order_main t , idc_asset_updown a 
        where t.atomic_id =a.atomic_id and
            (
                <if test="showAll!=null">
                    ( t.creator = #{appUserId} and t.state = 'created') or (t.creator != #{appUserId} and t.state != 'created')
                </if>
                <if test="showAll==null">
                    t.creator = #{appUserId}  or t.operationer = #{workNo} 
                    <if test="sites!=null">
                        or( t.site in (${sites}) and t.state != 'created') 
                    </if>
                </if>
            )
        <if test = "title!=null">
            and t.title like CONCAT('%', #{title}, '%')
        </if>
        <if test = "startTime!=null">
            and t.effective_date &gt;= CONCAT(#{startTime},' 00:00:00')
        </if>
        <if test = "endTime!=null">
            and t.effective_date &lt;= CONCAT(#{endTime},' 23:59:59')
        </if>
        <if test = "queryIds!=null" >
            and t.id in (${queryIds})
        </if>
        <if test = "state!=null">
            and (t.state =#{state} or t.sub_state =#{state}) 
        </if>
        <if test ="isTimeout!=null">
            and t.is_timeout =#{isTimeout}
        </if>
        <if test ="evaluation!=null">
            and t.evaluation =#{evaluation}
        </if>
        <if test="siteName!=null">
            and a.site_name = #{siteName}
        </if>
        <if test ="assetType!=null">
            and a.asset_type =#{assetType}
        </if>
        <if test ="optType!=null">
            and a.opt_type =#{optType}
        </if>
        <if test ="orderDeviceType!=null">
            and t.order_device_type=#{orderDeviceType}
        </if>
        <if test ="parentId!=null">
            and t.parent_id=#{parentId}
        </if>
        <if test = "sourceKey!=null">
            and t.source_key in (${sourceKey})
        </if>
        <if test = "identifyType !=null and identifyType !=''">
            and t.identify_type =#{identifyType}
        </if>
        and t.is_deleted ='n'
        and t.subject='assetUpdown'
    </sql>

四、Spring手动开启事务

　　手动控制事务在项目IDCM中的使用如下，首先在配置文件datasource.xml中做出如下配置：

    <bean id="transactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    </bean>
    
    <tx:annotation-driven transaction-manager="transactionManager" />

　　此外，该配置文件还配置了其他信息，如下：

<?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:context="http://www.springframework.org/schema/context"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="
    http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-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/tx 
    http://www.springframework.org/schema/tx/spring-tx-3.0.xsd"
    default-autowire="byName">

    <bean id="dataSource" class="com.taobao.tddl.jdbc.group.TGroupDataSource" init-method="init">
        <property name="appName" value="IDCM_APP"/>
        <property name="dbGroupKey" value="IDCM_GROUP"/>
    </bean>


    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
                <property name="mapperLocations" value="classpath*:com/**/dal/**/*Mapper*.xml" /> 
       <property name="dataSource" ref="dataSource" />
        <property name="typeAliasesPackage" value="com.alibaba.***.dal" />
    </bean>

    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="annotationClass" value="javax.annotation.Resource"></property>
        <property name="basePackage" value="com.alibaba.***.dal.***.mapper,com.alibaba.***.dal.***.***.mapper" />
    </bean>

    <bean id="transactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    </bean>
    
    <tx:annotation-driven transaction-manager="transactionManager" />

    <context:annotation-config />    
        <tx:advice id="defaultTxAdvice">
        <tx:attributes>
            <tx:method name="*" rollback-for="Exception" />
        </tx:attributes>
    </tx:advice>

    <aop:config>
        <aop:pointcut id="ao_bo"
            expression="(execution(* *..*BoImpl.*(..))) or ( execution(* *..*AoImpl.*(..)) and ( not execution(* *..*AoImpl.mtx_*(..)) ) )" />
        <aop:advisor pointcut-ref="ao_bo" advice-ref="defaultTxAdvice" />
    </aop:config>
    
    <context:component-scan base-package="com.alibaba.tboss.biz" />
    
</beans>

　　再需要使用手动事务的地方，使用方式如下：（事务的name不需要配置，前后约定一致就好）

import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

public class TransactionManagerTest{
    @Autowired
    DataSourceTransactionManager                      transactionManager;

    private static final DefaultTransactionDefinition assetUpdateTx         = new DefaultTransactionDefinition();
    static {
        assetUpdateTx.setName("assetUpdateTx");
        assetUpdateTx.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
    }

    //测试方法
    public void test() {

        // 单独开启事务, 提交数据库
        TransactionStatus status = transactionManager.getTransaction(assetUpdateTx);

        try{
            //TODO 业务逻辑
        } catch (Exception e) {
            logger.error("transactionManager-updateAssetList :", e.getMessage(), e);
            transactionManager.rollback(status);
            throw new ServiceException("更新数据失败");
        }
        transactionManager.commit(status);
    }
}

五、