菜到怀疑人生

0%

mybatis增删改查

发表于 更新于 分类于
本文字数： 6.7k 阅读时长 ≈ 11 分钟

前言：总结一下自己平时写的mybatis增删改查语句，方便以后参考

查询

分页查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<select id="studentPageQuery" parameterType="map" resultType="com.scy.sms.dto.StudentDTO">
    SELECT
    student_id,
    student_name,
    student_number,
    student_sex,
    student_phone_number,
    student_email,
    create_time,
    modify_time,
    student_enrollment_date
    FROM
    student
    <where>
        <if test="studentId != null and studentId != ''">
            and student_id = #{studentId}
        </if>
        <if test="studentName != null and studentName != ''">
            and student_name = #{studentName}
        </if>
        <if test="studentNumber != null and studentNumber != ''">
            and student_number = #{studentNumber}
        </if>
    </where>
    order by student_id desc
    limit #{offset} , #{limit}
</select>
模糊查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
<select id="mngOperationRecordPageQuery" parameterType="map" resultType="com.husky.ssm.dto.MngOperationRecordDTO">
SELECT
  t.id,
  t.user_name,
  t.user_account,
  t.menu_name,
  t.method,
  t.url,
  t.operation_name,
  t.request_params,
  t.client_ip,
  t.create_time,
  t.modify_time
FROM
  mng_operation_record t
<where>
  <if test="userName != null  and userName != ''">
    and instr(t.user_name,#{userName})>0
  </if>
  <if test="userAccount != null  and userAccount != ''">
    and instr(t.user_account,#{userAccount})>0
  </if>
  <if test="menuName != null  and menuName != ''">
    and instr(t.menu_name,#{menuName})>0
  </if>
  <if test="operationName != null  and operationName != ''">
    and instr(t.operation_name,#{operationName})>0
  </if>
  <if test="requestParams != null  and requestParams != ''">
    and instr(t.request_params,#{requestParams})>0
  </if>
  <if test="startTime != null  and startTime != ''">
    <![CDATA[and t.create_time >= #{startTime} ]]>
  </if>
  <if test="endTime != null  and endTime != ''">
    <![CDATA[and t.create_time < DATE_ADD(#{endTime},INTERVAL 1 DAY) ]]>
  </if>
</where>
ORDER BY create_time DESC
</select>
根据主键查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<select id="studentDetailQuery" parameterType="java.lang.String" resultType="com.scy.sms.dto.StudentDTO">
    SELECT
        student_id,
        student_name,
        student_number,
        student_sex,
        student_phone_number,
        student_email,
        create_time,
        modify_time,
        student_enrollment_date
    FROM
        student
    WHERE
        student_id = #{studentId}
</select>

多选

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select 
    refund_id,
    ……
from cash_refund
<where>
    <if test="refundId != null and refundId != ''">
        and refund_id = #{refundId}
    </if>
    <if test="payTypeList != null and payTypeList.size != 0">
        and pay_type in
        <foreach collection="payTypeList" index="index" item="item" open="(" separator="," close=")">
          #{item}
        </foreach>
    </if>
</where>

动态更新sql 使用

<trim>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<update id="studentUpdate" parameterType="com.scy.smso.StudentDTO">
    update student
    <trim prefix="set" suffixOverrides=",">
        <if test="studentName != null and studentName != ''">
            student_name = #{studentName},
        </if>
        <if test="studentNumber != null and studentNumber != ''">
            student_number = #{studentNumber},
        </if>
        <if test="studentSex != null and studentSex != ''">
            student_sex = #{studentSex},
        </if>
        <if test="studentPhoneNumber != null and studentPhoneNumber != ''">
            student_phone_number = #{studentPhoneNumber},
        </if>
        <if test="studentEmail != null and studentEmail != ''">
            studentEmail = #{studentEmail},
        </if>
        <if test="studentEnrollmentDate != null and studentEnrollmentDate != ''">
            student_enrollment_date = #{studentEnrollmentDate},
        </if>
    </trim>
    where
    student_id = #{studentId,jdbcType=VARCHAR}
</update>

动态更新sql 使用

<set>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<update id="studentUpdate" parameterType="com.scy.sms.dto.StudentDTO">
    update student
    <set>
        <if test="studentName != null and studentName != ''">
            student_name = #{studentName},
        </if>
        <if test="studentNumber != null and studentNumber != ''">
            student_number = #{studentNumber},
        </if>
        <if test="studentSex != null and studentSex != ''">
            student_sex = #{studentSex},
        </if>
        <if test="studentPhoneNumber != null and studentPhoneNumber != ''">
            student_phone_number = #{studentPhoneNumber},
        </if>
        <if test="studentEmail != null and studentEmail != ''">
            student_email = #{studentEmail},
        </if>
        <if test="studentEnrollmentDate != null and studentEnrollmentDate != ''">
            student_enrollment_date = #{studentEnrollmentDate},
        </if>
    </set>
    where
    student_id = #{studentId,jdbcType=VARCHAR}
</update>

insert

key 用于插入
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<sql id="insertKey">
    <trim suffixOverrides=",">
        student_id,
        <if test="studentName != null and studentName != ''">
            student_name,
        </if>
        <if test="studentNumber != null and studentNumber != ''">
            student_number,
        </if>
        <if test="studentSex != null and studentSex != ''">
            student_sex,
        </if>
        <if test="studentPhoneNumber != null and studentPhoneNumber != ''">
            student_phone_number,
        </if>
        <if test="studentEmail != null and studentEmail != ''">
            student_email,
        </if>
        <if test="studentEnrollmentDate != null and studentEnrollmentDate != ''">
            student_enrollment_date,
        </if>
    </trim>
</sql>
value 用于插入
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

<sql id="insertValues">
    <trim suffixOverrides=",">
        #{studentId},
        <if test="studentName != null and studentName != ''">
            #{studentName},
        </if>
        <if test="studentNumber != null and studentNumber != ''">
            #{studentNumber},
        </if>
        <if test="studentSex != null and studentSex != ''">
            #{studentSex},
        </if>
        <if test="studentPhoneNumber != null and studentPhoneNumber != ''">
            #{studentPhoneNumber},
        </if>
        <if test="studentEmail != null and studentEmail != ''">
            #{studentEmail},
        </if>
        <if test="studentEnrollmentDate != null and studentEnrollmentDate != ''">
            #{studentEnrollmentDate},
        </if>
    </trim>
</sql>
动态insert sql
1
2
3
4
<insert id="studentInsert" parameterType="com.scy.sms.dto.StudentDTO">
    insert into student(<include refid="insertKey"/>)
    values(<include refid="insertValues"/>)
</insert>
批量insert
1
2
3
4
5
6
7
8
9
10
11
12
13
<insert id="insertForeach" parameterType="java.util.List">
INSERT INTO student(id,name,create_time,modify_time,memo)
values
<foreach collection="list" item="item" index="index" separator=",">
(
  #{item.id},
  #{item.name},
  now(),
  now(),
  #{item.memo}
)
</foreach>
</insert>

delete sql

1
2
3
<delete id="studentDelete" parameterType="com.scy.sms.dto.StudentDTO">
    delete from student where student_id=#{studentId}
</delete>
-------------本文结束感谢您的阅读-------------