mybatis操作mysql的奇淫技巧总结（代码库）

2023-08-03 16:32:58

1、添加公共sql代码段

使用<sql> 和 <include> 标签

<sql id="userSubassemblyRecordParam">

    id, user_id, lesson_id, subassembly_id, record_id, course_id, teacher_id, status, create_time, update_time

</sql>

<select id="listUserSubassemblyRecordByParam" resultMap="userSubassemblyRecordMap">

    SELECT

      <include refid="userSubassemblyRecordParam"/>

    FROM

      rel_user_subassembly_record

</select>

2、动态更新

选择更新，为空则不更新，在<set> 标签里添加 <if> 标签，不需要考虑<if> 标签里的逗号

<update id="updateSchoolLevel" parameterType="com.ajz.course.entity.School">

    UPDATE

      school

    <set>

        <if test="gaokao != null">

            gaokao = #{gaokao},

        </if>

        <if test="contest != null">

            contest = #{contest},

        </if>

        <if test="independent != null &amp;&amp; independent != 0">

            independent = #{independent},

        </if>

    </set>

    WHERE

      id = #{id}

</update>

3、批量动态更新

在2 的基础上添加<trim> 和<foreach>标签

<update id="updateCourseRateStateByCourseList">

    UPDATE

        course

    <trim prefix="set" suffixOverrides=",">

        <trim prefix="rate_state =case" suffix="end,">

            <foreach collection="list" item="item" index="index">

                <if test="item.rateState!=null">

                    when id=#{item.id} then #{item.rateState}

                </if>

            </foreach>

        </trim>

        <trim prefix="name =case" suffix="end,">

            <foreach collection="list" item="item" index="index">

                <if test="item.name!=null">

                    when id=#{item.id} then #{item.name}

                </if>

            </foreach>

        </trim>

    </trim>

    WHERE

    <foreach collection="list" separator="or" item="item" index="index" >

        id=#{item.id}

    </foreach>

</update>

4、动态插入

选择插入，为空则不插入，在key 和value 两个部分都需要判断

<insert id="insertSyllabus" useGeneratedKeys="true" keyProperty="syllabusId"

        parameterType="com.ajz.course.entity.Syllabus">

    INSERT INTO

      syllabus

    <trim prefix="(" suffix=")" suffixOverrides=",">

        course_id,

        <if test="startTime != null">

            start_time,

        </if>

        <if test="endTime != null">

            end_time,

        </if>

    </trim>

    <trim prefix="values (" suffix=")" suffixOverrides=",">

        #{courseId},

        <if test="startTime != null">

            #{startTime},

        </if>

        <if test="endTime != null">

            #{endTime}

        </if>

    </trim>

</insert>

5、批量插入

只在VALUES 部分循环

<insert id="insertTimetableList" parameterType="java.util.List">

    INSERT INTO

        timetable (user_id, user_type, lesson_id, lesson_time, watch_state, status)

    VALUES

    <foreach collection="list" item="item" index="index" separator=",">

        (#{item.userId},

        #{item.userType},

        #{item.lessonId},

        #{item.lessonTime},

        #{item.watchState},

        #{item.status})

    </foreach>

</insert>

6、数据库根据主键自己选择插入还是更新，insertOrUpdate

相当于 jpa的 merge，关键在于唯一索引，也可以是主键，关键字：ON DUPLICATE KEY UPDATE

<!--user_id, user_type, lesson_id 三个字段建立了联合唯一索引-->

<insert id="insertOrUpdateTimetable" useGeneratedKeys="true" keyProperty="id" parameterType="com.ajz.course.entity.Timetable">

    INSERT INTO

        timetable (user_id, user_type, lesson_id, lesson_time, watch_state, status)

    VALUES (#{userId},

        #{userType},

        #{lessonId},

        #{lessonTime},

        #{watchState},

        #{status})

        ON DUPLICATE KEY UPDATE

            lesson_time = #{lessonTime}, watch_state = #{watchState}, status = #{status}

</insert>

7、批量insertOrUpdate

在5和6的基础上实现，注意 ON DUPLICATE KEY UPDATE 后面的 VALUES，是数据库的字段，而不是实体的字段， admin_name = VALUES(admin_name)

<insert id="insertOrUpdateStatSchoolHome" useGeneratedKeys="true" keyProperty="id" parameterType="com.ajz.course.entity.StatSchoolHome">

    INSERT INTO

        stat_school_home (sid,

        name,

        admin_name,

        type_key,

        status)

    VALUES

    <foreach collection="list" item="item" index="index" open="" close="" separator=",">

        (#{item.sid},

        #{item.name},

        #{item.adminName},

        #{item.typeKey},

        #{item.status})

    </foreach>

    ON DUPLICATE KEY UPDATE

        name = VALUES(name),

        admin_name = VALUES(admin_name),

        type_key = VALUES(type_key),

        status = VALUES(status)

</insert>

原创文章，欢迎转载，转载请注明出处！

码农公寓