Myabits添加修改返回主键-when动态查询-set动态修改-trim动态-foreach遍历参数(数组,map,list)批量添加

添加修改返回主键

//xml中写法
useGeneratedKeys=“true” 默认false
keyProperty=“id” 设置为目标属性

<insert id="insert01" useGeneratedKeys="true" keyProperty="id">
        insert into user
        values
        .....
</insert>

例如：
实体类

@AllArgsConstructor
@NoArgsConstructor
@Data
public class User implements Serializable {
    private Integer id;
    private String name;
    private String password;
}

sql语句为

<insert id="insert01" useGeneratedKeys="true" keyProperty="id">
        insert into user
        values
        (null,#{name},#{password})
</insert>

调用

 User user = new User(null, "小兰", "123");
 userMapper.insert01(user);
 System.out.println(user);

这个时候输出user对象内的属性为
id: 当前主键
name: 小兰
password : 123

when动态查询

<!--SQL语句中字段的提取   公共信息-->
    <sql id="student_column">
        student_id,student_name,student_sex,age,birthday
    </sql>

<select id="where01" parameterType="student" resultMap="studentMapper">
        SELECT <include refid="student_column"/> FROM student
        <where>
            <if test="studentName != null and studentName.trim().length() >0 ">
                AND student_name LIKE CONCAT('%',#{studentName},'%')
            </if>
            <if test="studentSex != null &amp;&amp; studentSex.trim().length()>0">
                AND student_sex=#{studentSex}
            </if>
        </where>
    </select>

set动态修改

 <!--SET标签和IF标签实现动态的SQL语句-->
    <update id="update01" parameterType="student">
        UPDATE student
        <set>
            <if test="studentName != null and studentName != ''">
                student_name=#{studentName},
            </if>
            <if test="studentSex != null and studentSex != ''">
                student_sex=#{studentSex},
            </if>
            <if test="age != null">
                age=#{age},
            </if>
            <if test="birthday != null">
                birthday=#{birthday},
            </if>
        </set>
        WHERE student_id=#{studentId}
    </update>

trim动态

实现when 同版动态查询

<select id="trim01" parameterType="student" resultMap="studentMapper">
        SELECT <include refid="student_column"/> FROM student
        <trim prefix="where" prefixOverrides="AND |OR ">
            <if test="studentName != null and studentName.trim().length() >0 ">
                AND student_name LIKE CONCAT('%',#{studentName},'%')
            </if>
            <if test="studentSex != null &amp;&amp; studentSex.trim().length()>0">
                AND student_sex=#{studentSex}
            </if>
        </trim>
    </select>

实现set同版动态修改

<update id="trim02" parameterType="student">
        UPDATE student
        <trim prefix="set" suffixOverrides=",">
            <if test="studentName != null and studentName != ''">
                student_name=#{studentName},
            </if>
            <if test="studentSex != null and studentSex != ''">
                student_sex=#{studentSex},
            </if>
            <if test="age != null">
                age=#{age},
            </if>
            <if test="birthday != null">
                birthday=#{birthday},
            </if>
        </trim>
        WHERE student_id=#{studentId}
    </update>

实现动态添加

<insert id="add" parameterType="student" useGeneratedKeys="true" keyProperty="studentId">

        INSERT INTO student     <!-- (student_name,student_sex,age,birthday) -->
        /*动态添加的字段和数据顺序必须要保持一致*/
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="studentName != null and studentName != ''">
                student_name,
            </if>
            <if test="studentSex != null and studentSex != ''">
                student_sex,
            </if>
            <if test="age != null">
                age,
            </if>
            <if test="birthday != null">
                birthday,
            </if>
        </trim>
        <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
            <if test="studentName != null and studentName != ''">
               #{studentName},
            </if>
            <if test="studentSex != null and studentSex != ''">
                #{studentSex},
            </if>
            <if test="age != null">
                #{age},
            </if>
            <if test="birthday != null">
                #{birthday},
            </if>
        </trim>
    </insert>

foreach遍历参数，批量添加

参数是数组

<!--foreach标签  数组   数组时不用写parameterType-->
    <delete id="delete01" >
        DELETE FROM student WHERE student_id  <!--IN(12,3,4)-->
        <!-- 数组没有指定KEY  MyBatis默认传递一个形参数组的时候默认KEY是 array-->
        <foreach collection="array" item="val" open="IN (" close=")" separator=",">
            #{val}
        </foreach>
    </delete>

参数是List

<!--foreach标签  List-->
    <delete id="delete02" parameterType="list">
        DELETE FROM student WHERE student_id  <!--IN(12,3,4)-->
        <!-- 集合没有指定KEY  MyBatis默认传递一个形参集合的时候默认KEY是 list-->
        <foreach collection="list" item="val" open="IN (" close=")" separator=",">
            #{val}
        </foreach>
    </delete>

参数是Map

<delete id="delete03" parameterType="map">
    DELETE FROM student WHERE student_id  <!--IN(12,3,4)-->
    <!-- 使用Map集合中自己指定的KEY-->
    <foreach collection="myMap" item="val" open="IN (" close=")" separator=",">
        #{val}
    </foreach>
</delete>

参数是对象内的数组

实体类
public class Model {

private String[] datas;

@Override
public String toString() {
    return "Model{" +
            "datas=" + Arrays.toString(datas) +
            '}';
}

public String[] getDatas() {
    return datas;
}

public void setDatas(String[] datas) {
    this.datas = datas;
}

}

sql

<delete id="delete05" parameterType="com.crx.model.Model">
    DELETE FROM student WHERE student_id  <!--IN(12,3,4)-->
    <!-- 使用自定义KEY获取自定义类中的数组数据-->
    <foreach collection="model.datas" item="val" open="IN (" close=")" separator=",">
        #{val}
    </foreach>
</delete>

批量添加 相同数量的字段和数据

<insert id="addBatch" parameterType="list">
    INSERT INTO student (student_name,student_sex,age) VALUES
    <foreach collection="list" item="st" separator=",">
        (#{st.studentName},#{st.studentSex},#{st.age})
    </foreach>
</insert>

选择标签

<!--选择标签  涉及的问题是:小于号以及中文的比较问题-->
<select id="query01" parameterType="string" resultMap="studentMapper">
    SELECT * FROM student
    <where>
    <choose>
        <when test='sex == "男"'>
            student_id > 16
        </when>
        <when test='sex == "女"'>
            student_id &lt; 10   <!--不支持小于号的描述-->
        </when>
        <otherwise>
            <!--CDATA表达方式-->
            <!--CDATA中不会进行转译 可以使用小于号 并且不支持标签-->
            <![CDATA[
                    student_id < 6
                ]]>
        </otherwise>
    </choose>
</where>
</select>