[Java知识库]MyBatis中的简单增删改查以及批量的增删改查

1 前言

MyBatis是我们最常用的持久层框架，它可以使我们方便并高效的完成业务且对数据源的处理，所以为以后在业务的开发中少犯错误和迷糊，故对经常用的简单的增删改查以及批量的增删改查做一次总结记录。以下是以MySQL数据库为例

2基础数据源的建立

2.1 ?基础库表

CREATE TABLE `dtx_education_recommend_step_record` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `record_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '记录唯一id',
  `user_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '患者id',
  `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '标题',
  `introduce` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '小内容（简介）',
  `cover_pic` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '封面图',
  `read_time` timestamp NULL DEFAULT NULL COMMENT '已读时间',
  `department_id` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '科室id',
  `step` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '阶段id',
  `scene` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '场景id',
  `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `step` (`step`,`user_id`,`department_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='患教推荐文档记录';

2.2 实体类对象的建立

package com.aistarfish.sfbizcore.common.dal.model;

import java.util.Date;
import java.io.Serializable;

import lombok.Data;

/**
 * 患教推荐文档记录(DtxEducationRecommendStepRecord)实体类
 *
 * @author Lanan_Lee
 * Created by on 2021-08-23 16:32
 */
@Data
public class DtxEducationRecommendStepRecordDO implements Serializable {
    private static final long serialVersionUID = -98447592484324429L;
    /**
     * 主键
     */
    private Long id;
    /**
     * 记录唯一id
     */
    private String recordId;
    /**
     * 患者id
     */
    private String userId;
    /**
     * 标题
     */
    private String title;
    /**
     * 小内容（简介）
     */
    private String introduce;
    /**
     * 封面图
     */
    private String coverPic;
    /**
     * 已读时间
     */
    private Date readTime;
    /**
     * 科室id
     */
    private String departmentId;
    /**
     * 阶段id
     */
    private String step;
    /**
     * 场景id
     */
    private String scene;
    /**
     * 创建时间
     */
    private Date gmtCreate;
    /**
     * 修改时间
     */
    private Date gmtModified;


}

3 废话不多说，直接上代码

3.1简单的添加

3.1.1DAO层的两种写法

//mapper.java 层代码 ---第一种写法
int insert(DtxEducationRecommendStepRecordDO dtxEducationRecommendStepRecord);

//或者这样写也可 ---第二种写法
int insert(@Param("recordId") String recordId,@Param("userId") String userId,@Param("title") String title, @Param("introduce")String introduce,@Param("coverPic") String coverPic,
               @Param("readTime") Date readTime,@Param("departmentId") String departmentId, @Param("step") String step, @Param("scene") String scene,@Param("gmtCreate") Date gmtCreate,
              @Param("gmtModified") Date gmtModified);

3.1.2 mapper. xml文件写法

 <insert id="insert" keyProperty="id" useGeneratedKeys="true">
        insert into ohappcore.dtx_education_recommend_step_record(record_id, user_id, title, introduce, cover_pic,
                                                                  read_time, department_id, step, scene, gmt_create,
                                                                  gmt_modified)
        values (#{recordId}, #{userId}, #{title}, #{introduce}, #{coverPic}, #{readTime}, #{departmentId}, #{step},
                #{scene}, #{gmtCreate}, #{gmtModified})
    </insert>

解释说明：①只要说数据库表中规定该字段不能为null，传入字段为null（例如上表中的userId字段），则会抛错；规定默认可以为null，传入字段为null，则不抛错（例如上表中的title字段等）；

?②useGeneratedKeys="true"表示添加后返回该数据的主键ID，注意DAO层中的第二种写法? ?不返回主键ID

3.2 批量的添加

3.2.1?DAO层的写法?

/**
     * 批量新增数据（MyBatis原生foreach方法）
     *
     * @param entities List<DtxEducationRecommendStepRecord> 实例对象列表
     * @return 影响行数
     */
int insertBatch(@Param("entities") List<DtxEducationRecommendStepRecordDO> entities);

?3.2.2?mapper. xml文件写法

 <insert id="insertBatch" keyProperty="id" useGeneratedKeys="true">
        insert into ohappcore.dtx_education_recommend_step_record(record_id, user_id, title, introduce, cover_pic,
        read_time, department_id, step, scene, gmt_create, gmt_modified)
        values
        <foreach collection="entities" item="entity" separator=",">
            (#{entity.recordId}, #{entity.userId}, #{entity.title}, #{entity.introduce}, #{entity.coverPic},
            #{entity.readTime}, #{entity.departmentId}, #{entity.step}, #{entity.scene}, #{entity.gmtCreate},
            #{entity.gmtModified})
        </foreach>
    </insert>

3.3 简单的删除

3.3.1?DAO层的写法?

    /**
     * 通过主键删除数据
     *
     * @param id 主键（本次演示是用主键id作为条件为例，当然也可以用其他字段作为条件）
     * @return 影响行数
     */
    int deleteById(Long id);

?3.3.2?mapper. xml文件写法

 <!--通过主键删除-->
    <delete id="deleteById">
        delete
        from ohappcore.dtx_education_recommend_step_record
        where id = #{id}
    </delete>

3.4 批量的删除

3.4.1?DAO层的写法?

/**
     * 批量删除操作
     * @param ids
     */
    public void batchDeleteByIds(List ids);

?3.4.2?mapper. xml文件写法?

<!-- 批量删除操作 -->
	<delete id="batchDeleteByIds" parameterType="java.util.List">
		delete from ohappcore.dtx_education_recommend_step_record where id in
		<foreach collection="list" index="index" item="item" open="(" close=")" separator=",">
			#{item}
		</foreach>
	</delete>

3.5 简单的修改

3.5.1?DAO层的写法?

    /**
     * 修改数据
     *
     * @param dtxEducationRecommendStepRecord 实例对象
     * @return 影响行数
     */
    int update(DtxEducationRecommendStepRecordDO dtxEducationRecommendStepRecord);

3.5.2 mapper.xml文件中的写法

<update id="update">
        update ohappcore.dtx_education_recommend_step_record
        <set>
            <if test="recordId != null and recordId != ''">
                record_id = #{recordId},
            </if>
            <if test="userId != null and userId != ''">
                user_id = #{userId},
            </if>
            <if test="title != null and title != ''">
                title = #{title},
            </if>
            <if test="introduce != null and introduce != ''">
                introduce = #{introduce},
            </if>
            <if test="coverPic != null and coverPic != ''">
                cover_pic = #{coverPic},
            </if>
            <if test="readTime != null">
                read_time = #{readTime},
            </if>
            <if test="departmentId != null and departmentId != ''">
                department_id = #{departmentId},
            </if>
            <if test="step != null and step != ''">
                step = #{step},
            </if>
            <if test="scene != null and scene != ''">
                scene = #{scene},
            </if>
            <if test="gmtCreate != null">
                gmt_create = #{gmtCreate},
            </if>
            <if test="gmtModified != null">
                gmt_modified = #{gmtModified},
            </if>
        </set>
        where id = #{id}
    </update>

3.6?批量的修改

3.6.1?DAO层的写法?

/**
     * 批量更新操作 
     * @param ids
     */
    public void batchUpdateUsers(List<DtxEducationRecommendStepRecordDO> entities);

3.6.2 mapper.xml文件中的写法

<update id="batchUpdateUsers" parameterType="java.util.List">
		<foreach collection="list" item="item" index="index" open="" close="" separator=";">
		update ohappcore.dtx_education_recommend_step_record
		<set>
			user_id = #{item.userId}, title = #{item.title}
		</set>
		where id = #{item.id}
		</foreach>
	</update>

3.7?批量的查询

3.7.1?DAO层的写法?

/**
    * 批量查询操作 
    * @param ids
    * @return
    */
    public List<DtxEducationRecommendStepRecordDO> batchSelectByIds(List ids);

3.7.2 mapper.xml文件中的写法

<!-- 批量查询操作 -->
	<select id="batchSelectByIds" resultMap="DtxEducationRecommendStepRecordMap">
		select id, record_id, user_id, title, introduce, cover_pic, read_time, department_id, step, scene, gmt_create,gmt_modified
		from ohappcore.dtx_education_recommend_step_record where id in
		<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
		#{item}
		</foreach>
	</select>

4 完结