Mybatis动态SQL

常规注释编写SQL语句

package qzer.mbs1.dao;

import org.apache.ibatis.annotations.*;
import qzer.mbs1.pojo.User;

import java.util.List;

@Mapper
public interface UserMapper {
    @Select("select * from user")
    public List<User> show();//查询所有

    @Select("select  * from user where id = #{id}")
    public User getById(int id);//获取单个

    @Delete("delete from user where id = #{id}")
    public int deleteById(int id);//删除单个

    @Select("select * from user where id = #{id} and phone = #{phone}")
    public int login(int id, String phone);//条件匹配

    @Insert("insert into user(name,age,gender,phone)" +
            "values(#{name},#{age},#{gender},#{phone})")
    public int insert(User user);//增加单个

    @Update("update user set name = #{name}, age = #{age}, gender = #{gender}, phone = #{phone} where id = #{id}")
    public int update(User user);//修改
    @Select("select * from user where name like concat('%',#{name},'%') ")
    public List<User> getByName(String name);//模糊查询 注意concat函数的使用
}

使用xml映射编写动态SQL语句

xml映射文件与Mapper接口名称一致，并且将xml文件放置在Mapper接口相同包下(同包同名)
xml映射文件的namespace属性值与Mapper接口全限定名一致
xml映射文件中sql语句的id与接口方法名一致，返回数据类型一致
xml映射文件配置(Mapper.xml)

动态SQL

where:用来动态拼接条件

if:用来进行条件判断并动态拼接

    public List<User> getByName(String name);//模糊查询 注意concat函数的使用

   <select id="getByName" resultType="qzer.mbs1.pojo.User">
   		select *
        from user
        <where>
            <if test="name != null">name like concat('%', #{name}, '%')</if>
        </where>
    </select>

set : 动态地在行首插入SET关键字，会删除额外的逗号。（在update语句中）

    public int update(User user);//修改

 <!--    动态更新-->
    <update id="update">
        update user
        <set>
            <if test="name != null">
            name   = #{name},
            </if>
            <if test="age != null">
            age    = #{age},
            </if>
            <if test="gender != null">
            gender = #{gender},
            </if>
            <if test="phone != null">
            phone  = #{phone}
            </if>
        </set>
        where id = #{id}
    </update>

批量删除forearch

collection:遍历的集合
iteam:遍历出来的元素
separator:分隔符
open:遍历开始前拼接的SQL片段
close:遍历结束后拼接的SQL片段

  public void deleteByIds(List<Integer> list);

  <delete id="deleteByIds">
        delete from user where id in
        <foreach collection="list" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

UserMapper.java

package qzer.mbs1.dao;

import org.apache.ibatis.annotations.*;
import qzer.mbs1.pojo.User;

import java.util.List;

@Mapper

public interface UserMapper {
    public List<User> show();//查询所有

    public User getById(int id);//获取单个

    public int deleteById(int id);//删除单个

    public int login(int id, String phone);//条件匹配

    public int insert(User user);//增加单个


    public int update(User user);//修改

    public List<User> getByName(String name);//模糊查询 注意concat函数的使用

    public void deleteByIds(List<Integer> list);
}

UserMapper.xml

<?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="qzer.mbs1.dao.UserMapper">

    <!--    resultType:单条结果的类型-->
    <!--    id:Mapper对应方法的方法名-->
    <select id="show" resultType="qzer.mbs1.pojo.User">
        select *
        from user
    </select>
    <select id="getById" resultType="qzer.mbs1.pojo.User">
        select *
        from user
        where id = #{id}
    </select>
    <select id="login" resultType="java.lang.Integer">
        select *
        from user
        where id = #{id}
          and phone = #{phone}
    </select>
    <!--    动态查询-->
    <select id="getByName" resultType="qzer.mbs1.pojo.User">
        select *
        from user
        <where>
            <if test="name != null">name like concat('%', #{name}, '%')</if>
        </where>
    </select>
    <delete id="deleteById">delete
                            from user
                            where id = #{id}
    </delete>
    <delete id="deleteByIds">
        delete from user where id in
        <foreach collection="list" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>
    <insert id="insert">
        insert into user(name, age, gender, phone)
        values (#{name}, #{age}, #{gender}, #{phone})
    </insert>
    <!--    动态更新-->
    <update id="update">
        update user
        <set>
            <if test="name != null">
                name = #{name},
            </if>
            <if test="age != null">
                age = #{age},
            </if>
            <if test="gender != null">
                gender = #{gender},
            </if>
            <if test="phone != null">
                phone = #{phone}
            </if>
        </set>
        where id = #{id}
    </update>

</mapper>

总结

if
用于判断该条件是否成立，如果条件为true，则拼接SQL形式

where
where元素只在子元素有内容的情况下才插入where子句，而且会自动去除子句的开头AND或OR

set
动态地在行首插入SET关键字，并且会删除额外的逗号，在update语句中使用

forearch
依赖操作集合

sql
通过id定义SQL片段

include
通过refid引用SQL片段