一、 sql in 查询
1. 当查询的参数只有一个时
findByIds(List<Long>ids)
1.1 如果参数的类型是List,则在使用时,collection属性要必须指定为 list
Xml代码
<selectid="findByIdsMap" resultMap="BaseResultMap">
Select
<include refid="Base_Column_List"/>
from jria where ID in
<foreach item="item"index="index" collection="list" open="("separator="," close=")">
#{item}
</foreach>
</select>
findByIds(Long[] ids)
1.2如果参数的类型是Array,则在使用时,collection属性要必须指定为 array
Xml代码
<select id="findByIdsMap"resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from tabs where ID in
<foreach item="item"index="index" collection="array" open="("separator="," close=")">
#{item}
</foreach>
</select>
2. 当查询的参数有多个时,例如 findByIds(Stringname, Long[] ids)
这种情况需要特别注意,在传参数时,一定要改用Map方式,这样在collection属性可以指定名称
下面是一个示例
Map<String, Object> params = new HashMap<String, Object>(2);
params.put("name", name);
params.put("ids", ids);
mapper.findByIdsMap(params);
Xml代码
<selectid="findByIdsMap" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from tabs where ID in
<foreach item="item"index="index" collection="ids" open="("separator="," close=")">
#{item}
</foreach>
</select>
//模糊查询时
1. sql中字符串拼接
SELECT * FROM tableName WHERE nameLIKE CONCAT(CONCAT('%', #{text}), '%');
2. 使用 ${...} 代替 #{...}
SELECT * FROM tableName WHERE nameLIKE '%${text}%';
3. 程序中拼接
Java
// String searchText = "%"+ text + "%";
String searchText = newStringBuilder("%").append(text).append("%").toString();
parameterMap.put("text",searchText);
SqlMap.xml
SELECT * FROM tableName WHERE nameLIKE #{text};
4. 大小写匹配查询
SELECT * FROMTABLENAME WHERE UPPER(SUBSYSTEM) LIKE '%' || UPPER('jz') || '%'
或者
SELECT * FROMTABLENAME WHERE LOWER(SUBSYSTEM) LIKE '%' || LOWER('jz') || '%'
5.List集合插入
<insertid="addUserApp" parameterType="java.util.List">
insertinto manage_userapp (userAppId,userId,appId,juname,createTime,createUser)values
<foreachcollection="list" item="userapp" index="index"separator=",">
(#{userapp.userAppId},#{userapp.userId},#{userapp.appId},#{userapp.juname},NOW(),#{userapp.createUser})
</foreach>
</insert>