首页 > 代码库 > 记录一次bug解决过程：resultType和手动开启事务

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

2024-08-11 08:38:16   219人阅读

一、总结

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

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

技术分享
 1 <?xml version="1.0" encoding="UTF-8"?> 2 <beans xmlns="http://www.springframework.org/schema/beans" 3     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  4     xmlns:context="http://www.springframework.org/schema/context" 5     xmlns:aop="http://www.springframework.org/schema/aop" 6     xmlns:tx="http://www.springframework.org/schema/tx" 7     xsi:schemaLocation=" 8     http://www.springframework.org/schema/beans 9     http://www.springframework.org/schema/beans/spring-beans-3.0.xsd10     http://www.springframework.org/schema/context11     http://www.springframework.org/schema/context/spring-context-3.0.xsd12     http://www.springframework.org/schema/aop 13     http://www.springframework.org/schema/aop/spring-aop-3.0.xsd14     http://www.springframework.org/schema/tx 15     http://www.springframework.org/schema/tx/spring-tx-3.0.xsd"16     default-autowire="byName">17 18     <bean id="dataSource" class="com.taobao.tddl.jdbc.group.TGroupDataSource" init-method="init">19         <property name="appName" value="IDCM_APP"/>20         <property name="dbGroupKey" value="IDCM_GROUP"/>21     </bean>22 23 24     <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">25                 <property name="mapperLocations" value="classpath*:com/**/dal/**/*Mapper*.xml" /> 26        <property name="dataSource" ref="dataSource" />27         <property name="typeAliasesPackage" value="com.alibaba.***.dal" />28     </bean>29 30     <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">31             <property name="annotationClass" value="javax.annotation.Resource"></property>32         <property name="basePackage" value="com.alibaba.***.dal.***.mapper,com.alibaba.***.dal.***.***.mapper" />33     </bean>34 35     <bean id="transactionManager"36         class="org.springframework.jdbc.datasource.DataSourceTransactionManager">37     </bean>38     39     <tx:annotation-driven transaction-manager="transactionManager" />40 41     <context:annotation-config />    42         <tx:advice id="defaultTxAdvice">43         <tx:attributes>44             <tx:method name="*" rollback-for="Exception" />45         </tx:attributes>46     </tx:advice>47 48     <aop:config>49         <aop:pointcut id="ao_bo"50             expression="(execution(* *..*BoImpl.*(..))) or ( execution(* *..*AoImpl.*(..)) and ( not execution(* *..*AoImpl.mtx_*(..)) ) )" />51         <aop:advisor pointcut-ref="ao_bo" advice-ref="defaultTxAdvice" />52     </aop:config>53     54     <context:component-scan base-package="com.alibaba.tboss.biz" />55     56 </beans>
datasource.xml配置文件

　　再需要使用手动事务的地方，使用方式如下：（事务的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);    }}

五、

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


联系
我们