mybatis xml sql语法

批量删除

int batchDelete(@Param("ids") List<Integer> ids);

  <delete id="batchDelete" parameterType="list">
    DELETE FROM cs
    WHERE id IN
    <foreach item="id" index="index" collection="ids" open="(" separator="," close=")">
      #{id}
    </foreach>
  </delete>

模糊查询

like CONCAT(#{keyword}, '%')
like CONCAT('%',#{keyword})
like CONCAT(CONCAT('%',#{keyword}),'%')

if语句

判断非空

<select id="findUsers" parameterType="com.example.User" resultType="com.example.User">
  SELECT * FROM users
  <where>
    <if test="name != null and name != ''">
      name = #{name}
    </if>
    <if test="email != null and email != ''">
      AND email = #{email}
    </if>
  </where>
</select>

choose、when语句

类似java中的switch,会进入第一个条件为true的语句

<select id="findUsers" resultType="com.example.User">
  SELECT * FROM users
  <where>
    <choose>
      <when test="name != null">
        name = #{name}
      </when>
      <when test="email != null">
        email = #{email}
      </when>
      <otherwise>
        id = #{id}
      </otherwise>
    </choose>
  </where>
</select>

update语句

<update id="updateUser" parameterType="com.example.User">
  UPDATE users
  <set>
    <if test="name != null">
      name = #{name},
    </if>
    <if test="email != null">
      email = #{email},
    </if>
  </set>
  WHERE id = #{id}
</update>

集合遍历

public interface UserMapper {
    List<User> selectUsersByIds(@Param("ids") List<Integer> ids);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.UserMapper">
    <select id="selectUsersByIds" parameterType="list" resultType="com.example.User">
        SELECT *
        FROM users
        WHERE id IN 
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
</mapper>

如果你将一个 ID [1,2,3] 的列表传递给 selectUsersByIds() 方法，MyBatis 执行处理后得到的 SQL 语句将是：

SELECT *
FROM users
WHERE id IN (1,2,3)

Map参数

public interface UserMapper {
    List<User> findUsers(@Param("map") Map<String, Object> map);
}

<select id="findUsers" resultType="com.example.User">
    SELECT * FROM users
    <where>
        <if test="map.name != null and map.name != ''">
            AND name = #{params.name}
        </if>
        <if test="map.email != null and map.email != ''">
            AND email = #{map.email}
        </if>
    </where>
</select>