Mybatis 传参问题

问题一： 之前在mysql客户端上编写sql习惯导致使用mybatis 将字符串参数逗号拼接进行传值 in(1,2,3) 一直返回一条数据 ，控制台输出的sql也没问题反复找不出所以然，经过同事指导才解决…

//错误示例 原因：使用in 参数传值为1,2,3 mybaits底层不会解析 只能返回一条数据 List<String> list = usersMapper.getUserAccountByUsersId("1,2,3"); Assert.assertNotNull(list); <select id="getUserAccountByUsersId" resultType="java.lang.String"> select uas.userAccountId as userAccountIds from users u LEFT JOIN user_account uc ON u.id = uc.userId AND uc.`status` = 1 LEFT JOIN user_account_settlement uas on u.id = uas.userId AND uas.`status` = 1 WHERE u.deleted_at IS NULL AND u.id in #{usersids,jdbcType=VARCHAR} </select> //正确示例 将参数值存入list中 1 2 3 @Test public void select () { List<String> ids = new ArrayList<String>(); ids.add("1"); ids.add("2"); ids.add("3"); List<String> list = usersMapper.getUserAccountByUsersId(ids); Assert.assertNotNull(list); } //foreach 进行集合遍历 <select id="getUserAccountByUsersId" resultType="java.lang.String"> select uas.userAccountId as userAccountIds from users u LEFT JOIN user_account uc ON u.id = uc.userId AND uc.`status` = 1 LEFT JOIN user_account_settlement uas on u.id = uas.userId AND uas.`status` = 1 WHERE u.deleted_at IS NULL AND u.id in <foreach collection="list" item="uid" index="index" open="(" close=")" separator=","> #{uid} </foreach> </select>

问题二： mybatis 使用Map<String，Object>进行参数传递（map中有String,List类型）

//正确示例 @Test public void selectEntryExitCount() { List<String> arr = new ArrayList<String>(); arr.add("1"); arr.add("2"); arr.add("3"); HashMap<String, Object> params = new HashMap<>(); params.put("user_account_ids", arr); params.put("start_date", "2019-1-1"); params.put("end_date", "2019-1-1"); Assert.assertNotNull(entryExitCountMapper.selectEntryExitCount(params)); } <select id="selectMerchantsCountOrder" parameterType="java.util.HashMap" resultType="java.util.HashMap"> SELECT SUM(loadings.money+overs.money+fails.money) as acount_money FROM ( SELECT IFNULL(SUM(money),0) as money FROM entry_exit_account where 1=1 and status = 0 <if test="user_account_ids.size() > 0"> and userAccountId in <foreach collection="user_account_ids" open="(" close=")" separator="," item="id"> #{id} </foreach> </if> <if test="start_date != null and start_date != ''"> AND date <![CDATA[>=]]> #{start_date}</if> <if test="end_date != null and end_date != ''"> AND date <![CDATA[<=]]>#{end_date}</if> ) loadings, 注意：<if test="_parameter.containsKey('user_account_ids')"> 判空不推荐使用因为参数无值会传递null值， 注意括号中的单引号不能少！！！ map中集合遍历 foreach collection map中的key(集合) 注意不要使用#{params.userAccountId}报错 也不要params.userAccountId 都不行