UserInfoMapper
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;import java.util.List;@Repository
public interface UserInfoMapper {/*** 新增 1 条记录,若成功则返回 1,若因唯一键重复则抛 DuplicateKeyException** @param userInfo* @return*/Integer add(UserInfo userInfo);/*** 根据主键物理删除一条记录,若成功则返回 1;若删除的数据不存在则返回 0** @param id* @return*/Integer physicalDeleteById(Long id);/*** 根据主键逻辑删除一条记录,若成功则返回 1;若删除的数据不存在则返回 0** @param id* @return*/Integer logicalDeleteById(Long id);/*** 更新一条记录,若成功则返回 1;若更新的数据不存在则返回 0.(注意: 入参 createdTime 不需赋值)** @param userInfo* @return*/Integer updateById(UserInfo userInfo);/*** 根据主键查询一条记录** @param id* @return*/UserInfo queryOneById(Long id);/*** 查询 1 条记录,若有多条记录符合条件,也只返回 1 条记录(LIMIT 1)** @param userInfo* @return*/UserInfo queryOne(UserInfo userInfo);/*** 查询列表,若入参为 null,则返回所有记录** @param ids* @return*/List<UserInfo> queryListByIds(@Param("ids") List<Long> ids);/*** 查询列表,若入参为 null,则返回所有记录* time between start and end 等价于 time >= start and time <= end,包括首尾* <if test='null!=startTime and null!=endTime'>AND create_time BETWEEN #{startTime} AND #{endTime}</if>* <if test='null!=startTime'><![CDATA[AND create_time >= #{startTime}]]></if>* <if test='null!=endTime'><![CDATA[AND create_time <= #{endTime}]]></if>** @param userInfo* @return*/List<UserInfo> queryList(@Param("userInfo") UserInfo userInfo);/*** 分页查询** @param userInfo* @param offset = (pageNum - 1) * pageSize,pageNum 从 1 开始* @param rowCount = pageSize* @return*/List<UserInfo> queryPage(@Param("userInfo") UserInfo userInfo,@Param("offset") Integer offset,@Param("rowCount") Integer rowCount);/*** 查询符合条件的记录总数** @param userInfo* @return*/Integer count(@Param("userInfo") UserInfo userInfo);/*** 批量新增,若成功则返回新增的记录条数,若因唯一键重复则抛 DuplicateKeyException** @param userInfos* @return*/Integer addBatch(List<UserInfo> userInfos);/*** 批量物理删除,若成功则返回删除的记录条数;若删除的记录其中有不存在的记录,则只返回成功删除的记录数;若删除的记录 1 条都不存在,则返回 0** @param ids* @return*/Integer physicalDeleteByIds(@Param("ids") List<Long> ids);/*** 批量逻辑删除,若成功则返回删除的记录条数;若删除的记录其中有不存在的记录,则只返回成功删除的记录数;若删除的记录 1 条都不存在,则返回 0** @param ids* @return*/Integer logicalDeleteByIds(@Param("ids") List<Long> ids);/*** 批量更新,若成功则返回更新的记录条数.(注意: 入参 createdTime 不需赋值;若属性为空则不更新对应的字段.)** @param userInfos* @return*/Integer updateBatch(List<UserInfo> userInfos);}
DemoMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.test.mapper.DemoMapper"><sql id="selectedFields"><![CDATA[id,name,age,height,create_time AS createTime]]></sql><sql id="whereClause"><where><if test="null!=id">AND id = #{id}</if><if test="null!=name and ''!=name">AND name = #{name}</if><if test="null!=age">AND age = #{age}</if><if test="null!=height">AND height = #{height}</if>AND is_deleted = 0</where></sql><sql id="listWhereClause"><where><if test="null!=demo.id">AND id = #{demo.id}</if><if test="null!=demo.name and ''!=demo.name">AND name = #{demo.name}</if><if test="null!=demo.age">AND age = #{demo.age}</if><if test="null!=demo.height">AND height = #{demo.height}</if>AND is_deleted = 0</where>ORDER BY create_time DESC</sql><sql id="pageOrCountWhereClause"><where><if test="null!=demo.id">AND id = #{demo.id}</if><if test="null!=demo.name and ''!=demo.name">AND name = #{demo.name}</if><if test="null!=demo.age">AND age = #{demo.age}</if><if test="null!=demo.height">AND height = #{demo.height}</if>AND is_deleted = 0</where>ORDER BY create_time DESC</sql><insert id="add" parameterType="com.test.entity.Demo" keyProperty="id">INSERT INTO demo<trim prefix="(" suffix=")" prefixOverrides=","><if test="null!=id">,id</if><if test="null!=name and ''!=name">,name</if><if test="null!=age">,age</if><if test="null!=height">,height</if><if test="null!=createTime">,create_time</if></trim>VALUES<trim prefix="(" suffix=")" prefixOverrides=","><if test="null!=id">,#{id}</if><if test="null!=name and ''!=name">,#{name}</if><if test="null!=age">,#{age}</if><if test="null!=height">,#{height}</if><if test="null!=createTime">,#{createTime}</if></trim></insert><!-- 物理删除 --><delete id="physicalDeleteById" parameterType="long">DELETE FROM demo WHERE id = #{id}</delete><!-- 逻辑删除 --><update id="logicalDeleteById" parameterType="long">UPDATE demo SET is_deleted = 1,update_time = now()WHERE id = #{id}</update><update id="updateById" parameterType="com.test.entity.Demo">UPDATE demo<trim prefix="SET" prefixOverrides=","><if test="null!=name and ''!=name">,name = #{name}</if><if test="null!=age">,age = #{age}</if><if test="null!=height">,height = #{height}</if><if test="null!=createTime">,create_time = #{createTime}</if></trim>WHERE id = #{id}</update><select id="queryOneById" parameterType="long" resultType="com.test.entity.Demo">SELECT <include refid="selectedFields"/> FROM demo WHERE id = #{id}</select><select id="queryOne" parameterType="com.test.entity.Demo" resultType="com.test.entity.Demo">SELECT <include refid="selectedFields"/> FROM demo<include refid="whereClause" />LIMIT 1</select><select id="queryListByIds" parameterType="java.util.List" resultType="com.test.entity.Demo">SELECT <include refid="selectedFields"/> FROM demo<where><if test="ids!=null and ids.size()>0">AND id IN<foreach collection="ids" item="item" index="index" open="(" separator="," close=")">#{item}</foreach></if></where></select><select id="queryList" parameterType="com.test.entity.Demo" resultType="com.test.entity.Demo">SELECT <include refid="selectedFields"/> FROM demo<include refid="listWhereClause" /></select><select id="queryPage" resultType="com.test.entity.Demo">SELECT <include refid="selectedFields"/>FROM demo tINNER JOIN (SELECT id temp_id FROM demo) temp ON t.id = temp.temp_id<include refid="pageOrCountWhereClause" /><if test="null!=offset and null!=rowCount">LIMIT #{offset}, #{rowCount}</if></select><select id="count" resultType="int">SELECT COUNT(1) FROM demo <include refid="pageOrCountWhereClause" /></select><insert id="addBatch" parameterType="java.util.List">INSERT INTO demo (id,name,age,height,create_time)VALUES <foreach collection="list" item="item" index="index" separator=","> <trim prefix="(" suffix=")" prefixOverrides=",">,#{item.id},#{item.name},#{item.age},#{item.height},#{item.createTime}</trim></foreach></insert><!-- 物理删除 --><delete id="physicalDeleteByIds" parameterType="java.util.List">DELETE FROM demo<where><if test="ids!=null and ids.size()>0">AND id IN<foreach collection="ids" item="item" index="index" open="(" separator="," close=")">#{item}</foreach></if></where></delete><!-- 批量逻辑删除 --><update id="logicalDeleteByIds" parameterType="java.util.List">UPDATE demo SET is_deleted = 1,update_time = now()<where><if test="ids!=null and ids.size()>0">AND id IN<foreach collection="ids" item="item" index="index" open="(" separator="," close=")">#{item}</foreach></if></where></update><update id="updateBatch" parameterType="java.util.List">UPDATE demo<trim prefix="SET" suffixOverrides=","><trim prefix="name=CASE" suffix="END,"><foreach collection="list" item="item" index="index"><if test="null==item.name or ''==item.name">WHEN id=#{item.id} THEN name</if><if test="null!=item.name and ''!=item.name">WHEN id=#{item.id} THEN #{item.name}</if></foreach></trim><trim prefix="age=CASE" suffix="END,"><foreach collection="list" item="item" index="index"><if test="null==item.age">WHEN id=#{item.id} THEN age</if><if test="null!=item.age">WHEN id=#{item.id} THEN #{item.age}</if></foreach></trim><trim prefix="height=CASE" suffix="END,"><foreach collection="list" item="item" index="index"><if test="null==item.height">WHEN id=#{item.id} THEN height</if><if test="null!=item.height">WHEN id=#{item.id} THEN #{item.height}</if></foreach></trim><trim prefix="create_time=CASE" suffix="END,"><foreach collection="list" item="item" index="index"><if test="null==item.createTime">WHEN id=#{item.id} THEN create_time</if><if test="null!=item.createTime">WHEN id=#{item.id} THEN #{item.createTime}</if></foreach></trim></trim>WHERE id IN<foreach collection="list" index="index" item="item" open="(" separator="," close=")">#{item.id}</foreach></update></mapper>