mybatis_generate_demo

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 t
		INNER 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>