Springboot+mybatis实现增删改查

由于之前主要使用的是Springboot+JPA实现数据的查询，在使用Springboot+mybatis开发时，查询了许多文章，现在整理如下，供大家参阅！
1、CarBaseinfo对象结构

/**
 * @author yyb
 * @date 2022年04月06日 9:05
 * 车辆表
 */
public class CarBaseinfo {
    /**
     * id
     */
    private Integer id;
    /**
     * 小区Id
     */
    private String communityId;
    /**
     * 人员Id
     */
    private Integer peopleId;
    /**
     * 车辆颜色
     */
    private String color;
    /**
     * 车辆品牌
     */
    private String brand;

    /**
     * 车牌
     */
    private String plate;

    /**
     * 车牌颜色
     */
    private String plateColor;

    /**
     * AI布控（1、是 2、否）
     */
    private Integer isApply;


    /**
     * 创建时间
     */
    private long createTime;

    /**
     * 更新时间
     */
    private long updateTime;

    //省略getter、setter方法
}

2、Mapper实现代码

import com.genersoft.iot.vmp.info.entity.CarBaseinfo;
import org.apache.ibatis.annotations.*;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * (CarBaseinfo)表数据库访问层
 *
 * @author yyb
 * @date 2022年04月06日 9:05
 */
@Mapper
@Repository
public interface CarBaseinfoMapper {

    /**
     * 通过ID查询单条数据
     *
     * @param id 主键
     * @return 实例对象
     */
    @Select("SELECT id," +
            "community_id    AS communityId, " +
            "people_id       AS peopleId, " +
            "color           AS color, " +
            "brand           AS brand, " +
            "plate, " +
            "plate_color     AS plateColor, " +
            "is_apply        AS isApply, " +
            "create_time     AS createTime," +
            "update_time     AS updateTime " +
            "FROM car_baseinfo WHERE id = #{id}")
    CarBaseinfo queryById(Integer id);

    /**
     * 查询指定行数据
     *
     * @param carBaseinfo 查询条件
     * @return 对象列表
     */
    @Select(value = {" <script>" +
            "SELECT id," +
            "community_id    AS communityId, " +
            "people_id       AS peopleId, " +
            "color           AS color, " +
            "brand           AS brand, " +
            "plate, " +
            "plate_color     AS plateColor, " +
            "is_apply        AS isApply, " +
            "create_time     AS createTime," +
            "update_time     AS updateTime " +
            "FROM car_baseinfo " +
            " WHERE 1=1 " +
            "<if test=\"communityId != null\"> AND community_id LIKE '%${communityId}%'</if>" +
            "<if test=\"peopleId != null\"> AND people_id LIKE '%${peopleId}%'</if>" +
            "<if test=\"color != null\"> AND color LIKE '%${color}%'</if>" +
            "<if test=\"brand != null\"> AND brand LIKE '%${brand}%'</if>" +
            "<if test=\"plate != null\"> AND plate  LIKE '%${plate}%'</if>" +
            "<if test=\"plateColor != null\"> AND plate_color  LIKE '%${plateColor}%'</if>" +
            "<if test=\"isApply != null\"> AND is_apply  LIKE '%${isApply}%'</if>" +
            " ORDER BY update_time DESC "+
            " </script>"})
    List<CarBaseinfo> queryByPage(CarBaseinfo carBaseinfo);

    /**
     * 统计总行数
     *
     * @param carBaseinfo 查询条件
     * @return 总行数
     */
    @Select("SELECT count(1) FROM car_baseinfo ")
    long count(CarBaseinfo carBaseinfo);

    /**
     * 新增数据
     *
     * @param carBaseinfo 实例对象
     * @return 影响行数
     */
    @Insert("INSERT INTO car_baseinfo (" +
            "community_id, " +
            "people_id, " +
            "color, " +
            "brand, " +
            "plate, " +
            "plate_color, " +
            "is_apply, " +
            "create_time," +
            "update_time" +
            ") VALUES (" +
            "#{communityId}," +
            "#{peopleId}," +
            "#{color}," +
            "#{brand}," +
            "#{plate}," +
            "#{plateColor}," +
            "#{isApply}," +
            "#{createTime}," +
            "#{updateTime}" +
            ")")
    int insert(CarBaseinfo carBaseinfo);

    /**
     * 批量新增数据（MyBatis原生foreach方法）
     *
     * @param carBaseinfos List<CarBaseinfo> 实例对象列表
     * @return 影响行数
     */
    @Insert(" <script>" +
            "INSERT INTO car_baseinfo (" +
            "community_id, " +
            "people_id, " +
            "color, " +
            "brand, " +
            "plate, " +
            "plate_color, " +
            "is_apply, " +
            "create_time," +
            "update_time" +
            ") VALUES " +
            "<foreach collection=\"carBaseinfos\" item=\"CarBaseinfo\" separator=\",\" close=\";\">(" +
            "#{CarBaseinfo.communityId}," +
            "#{CarBaseinfo.peopleId}," +
            "#{CarBaseinfo.color}," +
            "#{CarBaseinfo.brand}," +
            "#{CarBaseinfo.plate}," +
            "#{CarBaseinfo.plateColor}," +
            "#{CarBaseinfo.isApply}," +
            "UNIX_TIMESTAMP()," +
            "UNIX_TIMESTAMP()" +
            ")</foreach>"+
            " </script>")
    int insertBatch(@Param("carBaseinfos") List<CarBaseinfo> carBaseinfos);

    /**
     * 批量新增或按主键更新数据（MyBatis原生foreach方法）
     *
     * @param carBaseinfos List<CarBaseinfo> 实例对象列表
     * @return 影响行数
     * @throws org.springframework.jdbc.BadSqlGrammarException 入参是空List的时候会抛SQL语句错误的异常，请自行校验入参
     */
    @Insert(" <script>" +
            "INSERT INTO car_baseinfo (" +
            "id, " +
            "community_id, " +
            "people_id, " +
            "color, " +
            "brand, " +
            "plate, " +
            "plate_color, " +
            "is_apply, " +
            "create_time," +
            "update_time" +
            ") VALUES " +
            "<foreach collection=\"carBaseinfos\" item=\"CarBaseinfo\" separator=\",\">(" +
            "#{CarBaseinfo.id}," +
            "#{CarBaseinfo.communityId}," +
            "#{CarBaseinfo.peopleId}," +
            "#{CarBaseinfo.color}," +
            "#{CarBaseinfo.brand}," +
            "#{CarBaseinfo.plate}," +
            "#{CarBaseinfo.plateColor}," +
            "#{CarBaseinfo.isApply}," +
            "#{CarBaseinfo.createTime}," +
            "#{CarBaseinfo.updateTime}" +
            ")</foreach>" +
            " ON DUPLICATE KEY UPDATE " +
            "id = values(id)," +
            "community_id = values(community_id)," +
            "people_id = values(people_id)," +
            "color = values(color)," +
            "brand = values(brand)," +
            "plate = values(plate)," +
            "plate_color = values(plate_color)," +
            "is_apply = values(is_apply)," +
            "create_time = values(create_time)," +
            "update_time = values(update_time)" +
            " </script>")
    int insertOrUpdateBatch(@Param("carBaseinfos") List<CarBaseinfo> carBaseinfos);

    /**
     * 修改数据
     *
     * @param carBaseinfo 实例对象
     * @return 影响行数
     */
    @Update(value = {" <script>" +
            "UPDATE car_baseinfo " +
            "SET update_time='${updateTime}'" +
            "<if test=\"communityId != null\">, community_id='${communityId}'</if>" +
            "<if test=\"peopleId != null\">, people_id='${peopleId}'</if>" +
            "<if test=\"color != null\">, color='${color}'</if>" +
            "<if test=\"brand != null\">, brand='${brand}'</if>" +
            "<if test=\"plate != null\">, plate='${plate}'</if>" +
            "<if test=\"plateColor != null\">, plate_color='${plateColor}'</if>" +
            "<if test=\"isApply != null\">, is_apply='${isApply}'</if>" +
            "WHERE id='${id}'"+
            " </script>"})
    int update(CarBaseinfo carBaseinfo);

    /**
     * 通过主键删除数据
     *
     * @param id 主键
     * @return 影响行数
     */
    @Delete("DELETE FROM car_baseinfo WHERE id=#{id}")
    int deleteById(Integer id);

}