目录
前言
先贴一个字段集合,为了简便,就留五个字段来举例,下文中设计到指定字段的地方,可以使用这种方式替代
<resultMap id="BaseResultMap" type="com.wx.eduserver.entity.EquipmentOperation">
<id column="operation_id" jdbcType="INTEGER" property="operationId"/>
<result column="equipment_sn" jdbcType="VARCHAR" property="equipmentSn"/>
<result column="usb_switch" jdbcType="TINYINT" property="usbSwitch"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
</resultMap>
<sql id="Base_Column_List">
operation_id, equipment_sn, usb_switch, create_time, update_time
</sql>
一、查询
1、根据主键查询:
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from control_operation
where operation_id = #{operationId,jdbcType=INTEGER}
</select>
查询此处不多介绍,根据自己所需条件查即可
二、插入
1、单量插入
(1)单条数据指定字段插入
<insert id="insert" parameterType="com.wx.eduserver.entity.ControlOperation">
insert into control_operation (operation_id, equipment_sn, usb_switch, create_time, update_time)
values (#{operationId,jdbcType=INTEGER}, #{equipmentSn,jdbcType=VARCHAR}, #{usbSwitch,jdbcType=TINYINT}, #{createTime,jdbcType=TIMESTAMP}, #{updateTime,jdbcType=TIMESTAMP})
</insert>
(2)单条数据非空字段插入
<insert id="insertSelective" parameterType="com.wx.eduserver.entity.ControlOperation">
insert into control_operation
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="operationId != null">
operation_id,
</if>
<if test="equipmentSn != null">
equipment_sn,
</if>
<if test="usbSwitch != null">
usb_switch,
</if>
<if test="createTime != null">
create_time,
</if>
<if test="updateTime != null">
update_time,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="operationId != null">
#{operationId,jdbcType=INTEGER},
</if>
<if test="equipmentSn != null">
#{equipmentSn,jdbcType=VARCHAR},
</if>
<if test="usbSwitch != null">
#{usbSwitch,jdbcType=TINYINT},
</if>
<if test="createTime != null">
#{createTime,jdbcType=TIMESTAMP},
</if>
<if test="updateTime != null">
#{updateTime,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
2、批量插入
(1)批量数据指定字段插入
<insert id="batchInsert" parameterType="java.util.List">
insert into control_operation (operation_id, equipment_sn, usb_switch, create_time, update_time)
values
<foreach collection="list" item="item" separator=",">
(#{item.operationId,jdbcType=INTEGER}, #{item.equipmentSn,jdbcType=VARCHAR},
#{item.usbSwitch,jdbcType=TINYINT}, #{item.createTime,jdbcType=TIMESTAMP},
#{item.updateTime,jdbcType=TIMESTAMP}
)
</foreach>
</insert>
(2)批量数据非空字段插入
<insert id="batchInsertSelective" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" separator=";">
insert into control_operation
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="item.operationId != null">
operation_id,
</if>
<if test="item.equipmentSn != null">
equipment_sn,
</if>
<if test="item.usbSwitch != null">
usb_switch,
</if>
<if test="item.createTime != null">
create_time,
</if>
<if test="item.updateTime != null">
update_time
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="item.operationId != null">
#{item.operationId,jdbcType=INTEGER},
</if>
<if test="item.equipmentSn != null">
#{item.equipmentSn,jdbcType=VARCHAR},
</if>
<if test="item.usbSwitch != null">
#{item.usbSwitch,jdbcType=TINYINT},
</if>
<if test="item.createTime != null">
#{item.createTime,jdbcType=TIMESTAMP},
</if>
<if test="item.updateTime != null">
#{item.updateTime,jdbcType=TIMESTAMP}
</if>
</trim>
</foreach>
</insert>
三、更新
1、单量更新
(1)单条数据指定字段更新
<update id="updateByPrimaryKey" parameterType="com.wx.eduserver.entity.ControlOperation">
update control_operation
set equipment_sn = #{equipmentSn,jdbcType=VARCHAR},
usb_switch = #{usbSwitch,jdbcType=TINYINT},
create_time = #{createTime,jdbcType=TIMESTAMP},
update_time = #{updateTime,jdbcType=TIMESTAMP}
where operation_id = #{operationId,jdbcType=INTEGER}
</update>
(2)单条数据非空字段更新
<update id="updateByPrimaryKeySelective" parameterType="com.wx.eduserver.entity.ControlOperation">
update control_operation
<set>
<if test="equipmentSn != null">
equipment_sn = #{equipmentSn,jdbcType=VARCHAR},
</if>
<if test="usbSwitch != null">
usb_switch = #{usbSwitch,jdbcType=TINYINT},
</if>
<if test="createTime != null">
create_time = #{createTime,jdbcType=TIMESTAMP},
</if>
<if test="updateTime != null">
update_time = #{updateTime,jdbcType=TIMESTAMP},
</if>
</set>
where operation_id = #{operationId,jdbcType=INTEGER}
</update>
2、批量更新
(1)批量数据指定字段更新
<!--批量更新-->
<update id="batchUpdate" parameterType="java.util.List">
update control_operation
<trim prefix="set" suffixOverrides=",">
<trim prefix="equipment_sn = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when operation_id = #{item.operationId,jdbcType=INTEGER} then #{item.equipmentSn,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="usb_switch = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when operation_id = #{item.operationId,jdbcType=INTEGER} then #{item.usbSwitch,jdbcType=TINYINT}
</foreach>
</trim>
<trim prefix="create_time = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when operation_id = #{item.operationId,jdbcType=INTEGER} then #{item.createTime,jdbcType=TIMESTAMP}
</foreach>
</trim>
<trim prefix="update_time = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when operation_id = #{item.operationId,jdbcType=INTEGER} then #{item.updateTime,jdbcType=TIMESTAMP}
</foreach>
</trim>
</trim>
where operation_id in
<foreach close=")" collection="list" item="item" open="(" separator=", ">
#{item.operationId,jdbcType=INTEGER}
</foreach>
</update>
(2)批量数据非空字段更新
<update id="batchUpdateSelective" parameterType="java.util.List">
update control_operation
<trim prefix="set" suffixOverrides=",">
<trim prefix="equipment_sn = case" suffix="end,">
<foreach collection="list" index="index" item="item">
<if test="item.equipmentSn != null">
when operation_id = #{item.operationId,jdbcType=INTEGER} then
#{item.equipmentSn,jdbcType=VARCHAR}
</if>
</foreach>
</trim>
<trim prefix="usb_switch = case" suffix="end,">
<foreach collection="list" index="index" item="item">
<if test="item.usbSwitch != null">
when operation_id = #{item.operationId,jdbcType=INTEGER} then #{item.usbSwitch,jdbcType=TINYINT}
</if>
</foreach>
</trim>
<trim prefix="create_time = case" suffix="end,">
<foreach collection="list" index="index" item="item">
<if test="item.createTime != null">
when operation_id = #{item.operationId,jdbcType=INTEGER} then
#{item.createTime,jdbcType=TIMESTAMP}
</if>
</foreach>
</trim>
<trim prefix="update_time = case" suffix="end,">
<foreach collection="list" index="index" item="item">
<if test="item.updateTime != null">
when operation_id = #{item.operationId,jdbcType=INTEGER} then
#{item.updateTime,jdbcType=TIMESTAMP}
</if>
</foreach>
</trim>
</trim>
where operation_id in
<foreach close=")" collection="list" item="item" open="(" separator=", ">
#{item.operationId,jdbcType=INTEGER}
</foreach>
</update>
四、插入或更新
PS:这里用到个关键语句:ON DUPLICATE KEY UPDATE(存在即更新)
解释:如果主键或唯一索引在现有表中已经存在,则会执行更新操作,否则执行插入操作
1、单量插入或更新
(1)单条数据指定字段插入或更新
<insert id="insertOrUpdate">
insert into control_operation
(operation_id, equipment_sn, usb_switch, create_time, update_time)
values (#{operationId,jdbcType=INTEGER}, #{equipmentSn,jdbcType=VARCHAR}, #{usbSwitch,jdbcType=TINYINT},
#{createTime,jdbcType=TIMESTAMP}, #{updateTime,jdbcType=TIMESTAMP}) ON DUPLICATE KEY
UPDATE
equipment_sn = #{equipmentSn,jdbcType=VARCHAR},
usb_switch = #{usbSwitch,jdbcType=TINYINT},
create_time = #{createTime,jdbcType=TIMESTAMP},
update_time = #{updateTime,jdbcType=TIMESTAMP}
</insert>
(2)单条数据非空字段插入或更新
<insert id="insertOrUpdateSelective">
insert into control_operation
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="operationId != null">
operation_id,
</if>
<if test="equipmentSn != null and equipmentSn != ''">
equipment_sn,
</if>
<if test="usbSwitch != null">
usb_switch,
</if>
<if test="createTime != null">
create_time,
</if>
<if test="updateTime != null">
update_time
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="operationId != null">
#{operationId,jdbcType=INTEGER},
</if>
<if test="equipmentSn != null and equipmentSn != ''">
#{equipmentSn,jdbcType=VARCHAR},
</if>
<if test="usbSwitch != null">
#{usbSwitch,jdbcType=TINYINT},
</if>
<if test="createTime != null">
#{createTime,jdbcType=TIMESTAMP},
</if>
<if test="updateTime != null">
#{updateTime,jdbcType=TIMESTAMP}
</if>
</trim>
ON DUPLICATE KEY UPDATE
<if test="equipmentSn != null">
equipment_sn = #{equipmentSn,jdbcType=VARCHAR},
</if>
<if test="usbSwitch != null">
usb_switch = #{usbSwitch,jdbcType=TINYINT},
</if>
<if test="createTime != null">
create_time = #{createTime,jdbcType=TIMESTAMP},
</if>
<if test="updateTime != null">
update_time = #{updateTime,jdbcType=TIMESTAMP}
</if>
</insert>
2、批量插入或更新
(1)批量数据指定字段插入或更新
<insert id="batchInsertOrUpdate">
insert into control_operation
(operation_id, equipment_sn, usb_switch, create_time, update_time)
values
<foreach collection="list" item="item" separator=",">
(#{item.operationId,jdbcType=INTEGER}, #{item.equipmentSn,jdbcType=VARCHAR},
#{item.usbSwitch,jdbcType=TINYINT}, #{item.createTime,jdbcType=TIMESTAMP},
#{item.updateTime,jdbcType=TIMESTAMP}
)
</foreach>
ON DUPLICATE KEY UPDATE
equipment_sn = VALUES(equipment_sn),
usb_switch = VALUES(usb_switch),
create_time = VALUES(create_time),
update_time = VALUES(update_time)
</insert>
(2)批量数据非空字段插入或更新
<insert id="batchInsertOrUpdateSelective">
<foreach collection="list" item="item" index="index" separator=";">
insert into control_operation
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="item.operationId != null">
operation_id,
</if>
<if test="item.equipmentSn != null">
equipment_sn,
</if>
<if test="item.usbSwitch != null">
usb_switch,
</if>
<if test="item.createTime != null">
create_time,
</if>
<if test="item.updateTime != null">
update_time,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="item.operationId != null">
#{item.operationId,jdbcType=INTEGER},
</if>
<if test="item.equipmentSn != null">
#{item.equipmentSn,jdbcType=VARCHAR},
</if>
<if test="item.usbSwitch != null">
#{item.usbSwitch,jdbcType=TINYINT},
</if>
<if test="item.createTime != null">
#{item.createTime,jdbcType=TIMESTAMP},
</if>
<if test="item.updateTime != null">
#{item.updateTime,jdbcType=TIMESTAMP},
</if>
</trim>
ON DUPLICATE KEY UPDATE
<trim prefix="" suffixOverrides=",">
<if test="item.equipmentSn != null">
equipment_sn = #{item.equipmentSn,jdbcType=VARCHAR},
</if>
<if test="item.usbSwitch != null">
usb_switch = #{item.usbSwitch,jdbcType=TINYINT},
</if>
<if test="item.createTime != null">
create_time = #{item.createTime,jdbcType=TIMESTAMP},
</if>
<if test="item.updateTime != null">
update_time = #{item.updateTime,jdbcType=TIMESTAMP},
</if>
</trim>
</foreach>
</insert>