MyBatis增删改查总结

一、 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>