mybatis中IN查询写法
这个问题昨天困扰了我2个⼩时，终于在同事的开导下，这么写出来了，其实这种⽅法以前也写过，但是忘记了，现在发表到博客上⾯备忘。
/**
* 获取性别分析指标
*
* @author shangql
* @date 2013-1-17
* @param date
* @return
* @return List<Map<String,Object>>
*/
@Override
public List<Map<String, Object>> getGender(String countValue, String date, Integer[] brandIds) throws Exception {
Map<String, Object> inParamMap = new HashMap<String, Object>();
inParamMap.put("countValue", countValue);
inParamMap.put("date", date);
inParamMap.put("brandIdsList", parseToParam(brandIds));
inParamMap.put("size", EmptyUtil.isEmpty(brandIds)?0:brandIds.length);
List<Map<String, Object>> resultList = dao.queryForList("ConsumerInfoServiceImpl/getGender", inParamMap);
return nonEmpty(resultList);
}
/**
* 判断⾮空数据
*
* @author shangql
* @date 2013-1-23
* @param srcList
join和in哪个查询更快
* @return
* @return List<Map<String,Object>>
*/
private List<Map<String, Object>> nonEmpty(List<Map<String, Object>> srcList) {
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
Map<String, Object> itemMap = null;
if (EmptyUtil.isEmpty(srcList)) {
itemMap = new HashMap<String, Object>();
itemMap.put("name", "暂⽆数据");
itemMap.put("data1", 0);
resultList.add(itemMap);
return resultList;
} else {
return srcList;
}
}
/
**
* 装配条件参数
*
* @author shangql
* @date 2013-1-24
* @param brandNames
* @return
* @return String
*/
private List<Map<String, Object>> parseToParam(Integer[] brandIds) throws Exception{
List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
Map<String, Object> itemMap = null;
if(!EmptyUtil.isEmpty(brandIds)){
for (Integer i : brandIds) {
itemMap = new HashMap<String, Object>();
itemMap = new HashMap<String, Object>();
itemMap.put("brandId", i);
resultList.add(itemMap);
}
}
return resultList;
}
mybaits SQl xml：
<!-- author: shangql date: 2013.01.17 desc：获取性别分析指标  -->
<select id="ConsumerInfoServiceImpl/getGender" parameterType="java.util.HashMap" resultType="java.util.HashMap">  SELECT  dimension_brand.dimbrandid AS brandid,
dimension_brand.brandname AS name,
COALESCE(
(SELECT
COUNT(membersex) AS sexcount
FROM member_info
INNER JOIN sale_info
ON  bercode = bercode
AND member_info.licensecode = sale_info.licensecode
INNER JOIN dimension_goods
ON  dsbarcode = dsbarcode
WHERE membersex = #{countValue}
AND dimension_brand.dimbrandid = brandId
GROUP BY dimension_goods.brandid
ORDER BY dimension_goods.brandid ASC)
,0) AS data1
FROM sale_info
INNER JOIN dimension_goods
ON  dsbarcode =  dsbarcode
INNER JOIN dimension_brand
ON  dimension_goods.brandid = dimension_brand.dimbrandid
WHERE checkflag = '0'
AND membercode != '-1'
AND to_char(saletime,'yyyyMM') = #{date}
AND dsbarcode IN(SELECT  goodsbarcode FROM goods_info)
<if test="size != 0">
AND dimension_brand.dimbrandid IN
<foreach item="item" collection="brandIdsList" open="(" separator="," close=")">
#{item.brandId}
</foreach>
</if>
GROUP BY  dimension_brand.dimbrandid,dimension_brand.brandname
ORDER BY dimension_brand.dimbrandid ASC;
</select>

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。