当前位置：首页 > news >正文

MySQL：CRUD操作

news 2025/7/1 15:36:51

XML模版

```xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxx.mapper.xxxMapper"></mapper>

一、结果返回集

<!--  返回实体  -->
<resultMap id="ZjumbResult" type="ZjumbVo"><result property="bbm" column="BBM"/><result property="mbmc" column="MBMC"/><result property="zjbh" column="ZJBH"/><result property="px" column="PX"/>
</resultMap>

二、查询

<!--  条件查询  -->
<select id="list" parameterType="ZjumbQuery" resultMap="ZjumbResult"><include refid="selectCommon"></include><where><!--  <if test="dh != null and dh !=''">AND (dept.dept_id = #{dh} OR FIND_IN_SET(#{dh}, dept.ancestors))</if>  --><!--  <if test="xxx != null and xxx !=''">AND xxx = #{xxx}</if>  --><!--  <if test="xxx != null and xxx !=''">AND xxx like concat('%', #{xxx}, '%')</if>  --><!--  <if test="xxx != null">AND xxx &gt;= #{xxx}</if>  --><!--  <if test="xxx != null">AND xxx &lt;= #{xxx}</if>  --></where>
ORDER BY xxx
</select>

三、查询详情

<!--  查询详情  -->
<select id="info" parameterType="long" resultMap="ZjumbResult"><include refid="selectCommon"></include> WHERE xxx = #{xxx}
</select>

四、新增

4.1 不含逗号

<!--  新增  -->
<insert id="add" parameterType="Zjumb">INSERT INTO xxx(<if test="name != null and name != ''">NAME,</if><if test="age != null">AGE,</if>GXSJ) VALUES (<if test="name != null and name != ''">#{name},</if><if test="age != null">#{age},</if>sysdate())
</insert>

4.1 含逗号

<insert id="add" parameterType="Zjumb">INSERT INTO xxx<trim prefix="(" suffix=")" suffixOverrides=","><if test="name != null and name != ''">NAME,</if><if test="age != null">AGE,</if></trim><trim prefix="values (" suffix=")" suffixOverrides=","><if test="name != null and name != ''">#{name},</if><if test="age != null">#{age},</if></trim>
</insert>

五、修改

5.1 不含逗号

<!--  修改  -->
<update id="edit" parameterType="Zjumb">UPDATE zb_gzgl_zjzh_zjumb<set><if test="name != null and name != ''">NAME=#{name},</if><if test="age != null">AGE=#{age},</if>GXSJ=sysdate()</set>WHERE xxx=#{xxx}
</update>

5.2 含逗号

<update id="edit" parameterType="Zjumb">UPDATE zb_gzgl_zjzh_zjumb<trim prefix="SET" suffixOverrides=","><if test="name != null and name != ''">NAME=#{name},</if><if test="age != null">AGE=#{age},</if></trim>WHERE xxx=#{xxx}
</update>

六、删除

<!--  删除  -->
<delete id="delete" parameterType="long">DELETE FROM zb_gzgl_zjzh_zjumb WHERE BBM IN<foreach item="id" collection="array" open="(" separator="," close=")">#{id}</foreach>
</delete>