Mybatis中使用循环遍历
Mybatis中传参数的方法
1.使用注解绑定,mapper.xml 对应方法 不需要指定 parameterType,(推荐使用注解绑定方式)
方法接口:
List<CalculateIdeacommissionsum> selectByExample(@Param("example") CalculateIdeacommissionsum example,@Param("roleNameList") List<String> roleNameList);
方法对应的Mapper.xml
<!-- 通过userStatus 排序查询 升序 -->
<select id="selectByExample" resultMap="BaseResultMap">
select
id, calculateYear, calculateMonth, userId, userCode, userName, userStatus, companyName,
companyId, curDeptId, curDeptName, roleName from Calculate_IdeaCommissionSum
where calculateYear=#{example.calculateYear,jdbcType=SMALLINT}
and calculateMonth=#{example.calculateMonth,jdbcType=SMALLINT}
and deleteFlag=#{example.deleteFlag,jdbcType=TINYINT}
<if test="roleNameList!=null and roleNameList.size()> 0">
<foreach collection="roleNameList" item="rolename" separator="," open="and rolename in(" close=")">
#{rolename,jdbcType=VARCHAR}
</foreach>
</if>
order by userStatus
</select>
2.如果接口参数没有使用注解绑定,mapper.xml 对应方法 需要指定对应的参数类型。
List<CalculateIdeacommissionsum> selectByExample(CalculateIdeacommissionsum example);
方法对应的Mapper.xml
<!-- 通过userStatus 排序查询 升序 -->
<select id="selectByExample" parameterType="实体类路径" resultMap="BaseResultMap">
select
id, calculateYear, calculateMonth, userId, userCode, userName, userStatus, companyName,
companyId, curDeptId, curDeptName, roleName from Calculate_IdeaCommissionSum
where calculateYear=#{calculateYear,jdbcType=SMALLINT}
and calculateMonth=#{calculateMonth,jdbcType=SMALLINT}
order by userStatus
</select>
3. parameterType 也可以使用Map存放参数进行查询
接口方法:
List<BaseEmpinfo> selectByParam(Map<String,String> map);
接口方法对应的Mapper.xml 文件方法:
<select id="selectByParam" parameterType="java.util.Map" resultType="com.pacific.rspBonus.model.po.twBonus.mbg.BaseEmpinfo">
select * from Base_EmpInfo
where deleteFlag=0
<if test="userName != null and userName!=''">
and userName=#{userName,jdbcType=VARCHAR}
</if>
</select>
4.mybatis 遍历循环
collection标识我们程序传值过来的集合
open表示我们遍历的集合以什么字符开始
close表示我们遍历的集合以什么字符结尾
item是给我们集合遍历取一个变量
separator 表示的是分隔符,将我们集合中遍历出来的数据用","分隔开。
<if test="roleNameList!=null and roleNameList.size()> 0">
<foreach collection="roleNameList" item="rolename" separator="," open="and rolename in(" close=")">
#{rolename,jdbcType=VARCHAR}
</foreach>
</if>
sql如下:
select * from Calculate_IdeaCommissionSum where calculateYear=2019 and calculateMonth=2 and roleName in ('副总经理','总监','经纪人');
将roleName的多个条件用关系转化为 roleName in (roleName1,roleName2,roleName3...) in中用foreach循环
参考博客:https://jingyan.baidu.com/album/00a07f3873520e82d028dcce.html?picindex=1