mybatis问题。foreach循环遍历数组报错情况，及其解决方法失败1失败2失败3成功

根据条件查询数据列表，mybatis查询代码如下

如果只查询属于特定部门拥有的数据权限。这需要用 String[ ] codes保存当前部门及其子部门的部门编码。

所以需要在mybatis中遍历编码数组。

失败1

复制代码<select id="findList" resultType="xx.entity.Xxxx"> SELECT ${sqlMap.column.toSql()} FROM ${sqlMap.table.toSql()} <where> ${sqlMap.where.toSql()} <if test="codes != null and codes.length > 0"> AND u5.office_code in <foreach item="code" index="index" collection="codes" open="(" separator="," close=")"> ${code} </foreach> </if> </where> ORDER BY ${sqlMap.order.toSql()} </select>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<select id="findList" resultType="xx.entity.Xxxx">
SELECT ${sqlMap.column.toSql()}
FROM ${sqlMap.table.toSql()}
<where>
${sqlMap.where.toSql()}
<if test="codes != null and codes.length > 0">
AND u5.office_code in
<foreach item="code" index="index" collection="codes" open="(" separator="," close=")">
${code}
</foreach>
</if>
</where>
ORDER BY ${sqlMap.order.toSql()}
</select>

上面的代码会在仅查询部门范围的数据时报错。

Error querying database.  Cause: java.sql.SQLException: 在将 varchar 值 'SD' 转换成数据类型 int 时失败。

原因是 ${ } 符号获取数组值时，不会在值两边添加引号 ' '  查询数据库时，可能默认为这个in里面的是int数值，所以报类型转换异常。

还原真实sql如下 （数据库的部门编码是字符串值。）

复制代码select * from data a left join user u on u.user_code=a.createBy left join employee e on e.emp_code=u.ref_code left join office o on o.office_code=e.office_code where a.status='0' and o.office_code in (001016,003,004,005,006,008,007)

1
2
3
4
5
select * from data a
left join user u on u.user_code=a.createBy
left join employee e on e.emp_code=u.ref_code
left join office o on o.office_code=e.office_code
where a.status='0' and o.office_code in (001016,003,004,005,006,008,007)

失败2

就在想，既然是没加引号的问题，那我改用 #{ }这个符号来取值不就好的

复制代码　　　　　　　<if test="codes != null and codes.length > 0"> AND u5.office_code in <foreach item="code" index="index" collection="codes" open="(" separator="," close=")"> #{code} </foreach> </if>

1
2
3
4
5
6
　　　　　　　<if test="codes != null and codes.length > 0">
AND u5.office_code in
<foreach item="code" index="index" collection="codes" open="(" separator="," close=")">
#{code}
</foreach>
</if>

再次尝试还是不行。这次报差错不一样了

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.reflection.ReflectionException: There is no getter for property named '__frch_code_0' in 'class'

没有找到对应的get的属性方法。觉得默认以为我这个是一个实体类，然后以get方式获取值，但我这里明显是String[ ] 数组形式的数据。

 

失败3

再从网上搜索资料。还有一种用法

复制代码　　　　　　　<if test="codes != null and codes.length > 0"> AND u5.office_code in <foreach item="code" index="index" collection="codes" open="(" separator="," close=")"> #{codes[${index}]} </foreach> </if>

1
2
3
4
5
6
<if test="codes != null and codes.length > 0">
AND u5.office_code in
<foreach item="code" index="index" collection="codes" open="(" separator="," close=")">
#{codes[${index}]}
</foreach>
</if>

这种还是会报错。

nested exception is org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: java.lang.IllegalStateException: Type handler was null on parameter mapping for property 'codes[0]'. It was either not specified and/or could not be found for the javaType ([Ljava.lang.String;) : jdbcType (null) combination.
### Cause: java.lang.IllegalStateException: Type handler was null on parameter mapping for property 'codes[0]'. It was either not specified and/or could not be found for the javaType ([Ljava.lang.String;) : jdbcType (null) combination.

 

成功

最后，还是使用 '${ }'

用美元符号在左右两边加引号方式解决问题。

复制代码　　　　　　　<if test="codes != null and codes.length > 0"> AND u5.office_code in <foreach item="code" index="index" collection="codes" open="(" separator="," close=")"> '${code}' </foreach> </if>

1
2
3
4
5
6
<if test="codes != null and codes.length > 0">
AND u5.office_code in
<foreach item="code" index="index" collection="codes" open="(" separator="," close=")">
'${code}'
</foreach>
</if>