MyBatis 动态 SQL

public class User {
    private Integer id;
    private String username;
    private String userEmail;
    private String userCity;
    private Integer age;
}Copy

CREATE TABLE user (
  id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(255) DEFAULT NULL,
    user_email varchar(255) DEFAULT NULL,
    user_city varchar(255) DEFAULT NULL,
    age int(11) DEFAULT NULL,
  PRIMARY KEY (id)
)Copy

public List<User> findByUser(User user);Copy

<select id="findByUser" resultType="com.example.mybatis.entity.User">
    select
    id, username, user_email userEmail, user_city userCity, age
    from user
    where
    <if test="username != null and username != ''">
        username = #{username}
    </if>
    <if test="userEmail != null and userEmail != ''">
        and user_email = #{userEmail}
    </if>
    <if test="userCity != null and userCity != ''">
        and user_city = #{userCity}
    </if>
</select>Copy

select id, username, user_email userEmail, user_city userCity, age 
from user 
where username = ? and user_email = ? and user_city = ? Copy

select id, username, user_email userEmail, user_city userCity, age 
from user 
where username = ? Copy

select id, username, user_email userEmail, user_city userCity, age 
from user 
where and user_email = ? and user_city = ? Copy

    <select id="findByUser" resultType="com.example.mybatis.entity.User">
        select
        id, username, user_email userEmail, user_city userCity, age
        from user
        <where>
            <if test="username != null and username != ''">
                username = #{username}
            </if>
            <if test="userEmail != null and userEmail != ''">
                and user_email = #{userEmail}
            </if>
            <if test="userCity != null and userCity != ''">
                and user_city = #{userCity}
            </if>
        </where>
    </select>Copy

<select id="findByUser" resultType="com.example.mybatis.entity.User">
        select
        id, username, user_email userEmail, user_city userCity, age
        from user
        <trim prefix="where" prefixOverrides="and">
            <if test="username != null and username != ''">
                username = #{username}
            </if>
            <if test="userEmail != null and userEmail != ''">
                and user_email = #{userEmail}
            </if>
            <if test="userCity != null and userCity != ''">
                and user_city = #{userCity}
            </if>
        </trim>
    </select>Copy

and user_email = #{userEmail} and user_city = #{userCity}Copy

where user_email = #{userEmail} and user_city = #{userCity}Copy

<select id="findByUser" resultType="com.example.mybatis.entity.User">
        select
        id, username, user_email userEmail, user_city userCity, age
        from user
        <where>
            <choose>
                <when test="username != null and username != ''">
                    username = #{username}
                </when>
                <when test="userEmail != null and userEmail != ''">
                    and user_email = #{userEmail}
                </when>
                <when test="userCity != null and userCity != ''">
                    and user_city = #{userCity}
                </when>
            </choose>
        </where>
    </select>Copy

public int updateUser(User user);Copy

<update id="updateUser" parameterType="com.example.mybatis.entity.User">
       update user
       <set>
           <if test="username != null and username != ''">
               username=#{username},
           </if>
           <if test="userEmail != null and userEmail != ''">
               user_email=#{userEmail},
           </if>
           <if test="userCity != null and userCity != ''">
               user_city=#{userCity},
           </if>
           <if test="age != null">
              age=#{age}
           </if>
       </set>
       where id=#{id}
    </update>Copy

public List<User> getUsersByIds(List<Integer> ids);Copy

<!--
        collection: 指定要遍历的集合
            默认情况下
                如果为Collection类型的，key为collection；
                如果为List类型的，key为list
                如果是数组类型，key为array
            可以通过@Param("ids")来指定key
        item: 将当前遍历的元素赋值给指定的变量
        open: 给遍历的结果添加一个开始字符
        close: 给遍历的结果添加一个结束字符
        separator: 每个元素之间的分隔符
    -->
<select id="getUsersByIds"
        resultType="com.example.mybatis.entity.User">
    select * from user
    where id in
    <foreach collection="list" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</select>Copy

public int addUserList(List<User> users);Copy

<insert id="addUserList"
        parameterType="com.example.mybatis.entity.User">
    insert into user
    (username, user_email, user_city, age)
    values
    <foreach item="user"  collection="list" separator=",">
        (#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age})
    </foreach>
</insert>

<!--返回自增主键-->
<insert id="addUserList"
        parameterType="com.example.mybatis.entity.User"
        useGeneratedKeys="true"
        keyProperty="id">
    insert into user
    (username, user_email, user_city, age)
    values
    <foreach item="user"  collection="list" separator=",">
        (#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age})
    </foreach>
</insert>

<!--还可以这样写-->
<!--
    这种方式需要数据库连接属性设置allowMultiQueries=true
    这种分号分隔多个SQL还可以用于其他的批量操作，如修改、删除
-->
<insert id="addUserList"
        parameterType="com.example.mybatis.entity.User">
    <foreach item="user"  collection="list" separator=";">
        insert into user
        (username, user_email, user_city, age)
        values
        (#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age})
    </foreach>
</insert>


<!--如果是Oracle数据库，则需要这样写-->
<insert id="addUserList"
        parameterType="com.example.mybatis.entity.User">
    <foreach item="user" open="begin" close="end;"  collection="list">
        insert into user
        (username, user_email, user_city, age)
        values
        (#{user.username}, #{user.userEmail}, #{user.userCity}, #{user.age});
    </foreach>
</insert>Copy