mybatis动态sql代码（增删改查）

动态查询

方法一

if：如果条件成立则自动拼接内部关键字
where：

1. 自动根据内部条件添加where关键字，如果内部条件为空则不添加where关键字，反之添加
2. 可以自动的去除前面的的多余的AND/OR关键字

        SELECT
        *
        FROM
        student
        <where>
            <if test="id != null">
                id = #{id}
            </if>

            <if test="name != null">
                and name = #{name}
            </if>
        </where>

方法二

prefix：如果内容不为空则拼接prefix中的内容
prefixOverrides：前缀覆盖，如果最前面是以AND/OR等关键字开头，则将该关键字替换为空格
suffix：后缀suffix给拼串后的整个字符串加一个后缀
suffOverrides：后缀覆盖，如果最后面是以AND/OR等关键字结尾，则将该关键字替换为空格

        <trim prefix="where" prefixOverrides="and" >
            <if test="id != null">
                and id = #{id}
            </if>

            <if test="name != null">
                and name = #{name}
            </if>
        </trim>

方法三

choose：相当于于java中的switch
when：相当于switch中的case
otherwise：相当于switch中的default
优先级：如果有多个则优先级为，从上到下，只执行一条

SELECT
        *
        FROM
        student
      <where>
            <choose>
                <when test="id!=null">
                    id = #{id}
                </when>
                <when test="name!=null">
                    `name`=#{name}
                </when>
            </choose>

        </where>

动态插入

StudentMapper接口

package com.software.mapper;

import com.software.pojo.Student;

import java.util.List;

public interface StudentMapper {

    boolean insertStudent(Student student);
}

StudentMapper.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="com.software.mapper.StudentMapper">
    <insert id="insertStudent">
        INSERT INTO
        student(
        <trim suffixOverrides=",">
            <if test="id!=null">
                id,
            </if>
            <if test="id!=null">
                `name`,
            </if>
            <if test="id!=null">
                teacher_id,
            </if>
        </trim>
        )
        VALUES(
        <trim suffixOverrides=",">
            <if test="id!=null">
                #{id},
            </if>
            <if test="id!=null">
                #{name},
            </if>
            <if test="id!=null">
                #{teacherId},
            </if>
        </trim>

        )

    </insert>

</mapper>

动态修改

StudentMapper接口

package com.software.mapper;

import com.software.pojo.Student;

import java.util.List;

public interface StudentMapper {

    boolean updateStudent(Student student);
}

StudentMapper.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="com.software.mapper.StudentMapper">
    <update id="updateStudent">
        update
            student
        set
            <trim suffixOverrides=",">

                <if test="name!=null">
                    `name`=#{name},
                </if>
                <if test="teacherId!=null">
                    teacher_id=#{teacherId},
                </if>

            </trim>
            <where>
                <if test="id!=null">
                    id=#{id}
                </if>
            </where>

    </update>

</mapper>

StudentMapper.xml方法二（set标签）

    <update id="updateStudent">
        update
            student
        <set>
                <if test="name!=null">
                    `name`=#{name},
                </if>
                <if test="teacherId!=null">
                    teacher_id=#{teacherId},
                </if>
        </set>

            <where>
                <if test="id!=null">
                    id=#{id}
                </if>
            </where>

    </update>

IN 查询

StudentMapper接口

package com.software.mapper;

import com.software.pojo.Student;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface StudentMapper {
    List<Student> selectInId(List<Integer> ids);
}

StudentMapper.xml
– collection:必须要传的集合
– item：遍历的每个名称
– open:前缀
– close：后缀
– separator:以什么分割

    <select id="selectInId" resultType="Student">
        select * from student
        <where>
            <if test="list!=null">
                <foreach collection="list" item="id" open="id in(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

如果需要动态插入多条则需要在数据库连接后面加上&allowMultiQueries=true

参考地址
https://www.cnblogs.com/guxiao/articles/12570393.html