MyBatis中foreach与resultMap使用
1、foreach标签
示例代码:
.xml文件
<select id="getArticle" resultType="com.my.Article">
select
ta.ID, ta.article
from t_article ta where 1=1
<if test="blacklist != null and blacklist.size()>0 ">
and ta.createrid not in
<foreach collection="blacklist" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
</select>
dao层接口
List<Article> getArticle(@Param("blacklist") List<Long> blackList);
说明
接口传入List类型,使用foreach遍历并查询
- collection 要遍历的集合名 在dao接口中使用@Param注解对应命名
- index 当前遍历元素下标
- item 遍历出的数据(集合中存放的数据)
- open 循环开头以什么字符。 为了符合not in 语法 所以开头使用“(”,结尾使用“)”
- separator 两次循环之间需要添加的字符,即not in(1,2,3)中的逗号
- close 遍历结束后要加入的字符 同open标签
- #{item} 取item的值
2、resultMap
示例代码
.xml文件映射配置
<resultMap id="GoodsSizeVo" type="com.my.GoodsSizeVo">
<result column="images" jdbcType="VARCHAR" property="images" />
<result column="price" jdbcType="BIGINT" property="price" />
<result column="goodsName" jdbcType="VARCHAR" property="goodsName" />
<result column="isCust" jdbcType="INTEGER" property="isCust" />
<result column="spuId" jdbcType="INTEGER" property="spuId" />
<collection property="groupAndParamVoList" ofType="com.my.GroupAndParamVo">
<result column="lable" property="lable"/>
<result column="sort" property="sort"/>
<collection property="params" ofType="java.lang.String" javaType="java.util.List">
<result column="param"/>
</collection>
</collection>
</resultMap>
.xml查询语句
<select id="selectSpuBaseInfo" parameterType="com.my.Spu" resultMap="GoodsSizeVo">
SELECT
ts.images,
ts.price,
ts.NAME goodsName,
ts.is_cust isCust,
ts.id spuId,
tsg.`name` lable,
tsg.sort,
tsp.`name` param
FROM
t_spu ts,
t_spec_group tsg,
t_spec_param tsp
WHERE
ts.id = #{id}
AND tsg.spuid = ts.id
AND tsp.spuid = ts.id
AND tsp.group_id = tsg.id
order by tsg.sort
</select>
dao接口
GoodsSizeVo selectSpuBaseInfo(Spu spu);
实体类数据格式
@Data
public class GoodsSizeVo extends Entity {
private String images;
private Integer price;
private String goodsName;
private Integer isCust;
private Long spuId;
private List<GroupAndParamVo> groupAndParamVoList;
}
@Data
public class GroupAndParamVo{
private String lable;
private List<String> params;
private Integer sort;
}