由于之前主要使用的是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);
}