一、总结
二、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 >= CONCAT(#{startTime},' 00:00:00')
</if>
<if test = "endTime!=null">
and t.effective_date <= 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>
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);
}
}
五、