mybatis 中IN 查询写法

最新推荐文章于 2025-03-12 20:43:37 发布

iteye_3232

最新推荐文章于 2025-03-12 20:43:37 发布

阅读量1.3k

点赞数

分类专栏： mybatis 文章标签： PostgreSQL xml java sql

本文链接：https://blog.csdn.net/iteye_3232/article/details/82474622

版权

mybatis 专栏收录该内容

5 篇文章

订阅专栏

这个问题昨天困扰了我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
     * @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.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   member_info.membercode = sale_info.membercode
						AND member_info.licensecode = sale_info.licensecode
						INNER JOIN dimension_goods
						ON   sale_info.goodsbarcode = dimension_goods.goodsbarcode
						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  sale_info.goodsbarcode =  dimension_goods.goodsbarcode
				INNER JOIN dimension_brand 
				ON  dimension_goods.brandid = dimension_brand.dimbrandid
			WHERE checkflag = '0'
				AND membercode != '-1'
				AND to_char(saletime,'yyyyMM') = #{date}
				AND sale_info.goodsbarcode 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>