主题切换

jerry-up2026-05-272026-05-24日常积累模板代码javamybatis
969
5.6m

MyBatis ​

下面这套围绕 user_account 演示，改表名、字段名、包名就能直接用。

Query 对象和 Mapper 接口 ​

java
@Data
public class UserAccountQuery extends PageQuery {
    private Long id;
    private String userName;
    private Integer status;
    private List<Integer> statusList;
    private LocalDateTime beginTime;
    private LocalDateTime endTime;
}

@Mapper
public interface UserAccountMapper {
    UserAccountDO selectById(@Param("id") Long id);

    Long countByQuery(@Param("query") UserAccountQuery query);

    List<UserAccountDO> selectByQuery(@Param("query") UserAccountQuery query);

    int insertSelective(UserAccountDO entity);

    int updateSelective(UserAccountDO entity);

    int batchInsert(@Param("list") List<UserAccountDO> list);

    int batchUpdateStatus(@Param("list") List<UserAccountDO> list);

    int batchUpsert(@Param("list") List<UserAccountDO> list);

    boolean existsByUserNo(@Param("userNo") String userNo);
}

XML 基础结构 ​

xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.user.UserAccountMapper">
  <resultMap id="BaseResultMap" type="com.example.user.UserAccountDO">
    <id column="id" property="id" jdbcType="BIGINT"/>
    <result column="user_no" property="userNo" jdbcType="VARCHAR"/>
    <result column="user_name" property="userName" jdbcType="VARCHAR"/>
    <result column="mobile" property="mobile" jdbcType="VARCHAR"/>
    <result column="status" property="status" jdbcType="TINYINT"/>
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
  </resultMap>

  <sql id="BaseColumnList">
    id, user_no, user_name, mobile, status, create_time, update_time
  </sql>
</mapper>

分页查询模板 ​

xml
<select id="countByQuery" resultType="java.lang.Long">
  select count(1)
  from user_account
  <include refid="QueryCondition"/>
</select>

<select id="selectByQuery" resultMap="BaseResultMap">
  select
    <include refid="BaseColumnList"/>
  from user_account
  <include refid="QueryCondition"/>
  order by id desc
  limit #{query.offset,jdbcType=INTEGER}, #{query.pageSize,jdbcType=INTEGER}
</select>

<sql id="QueryCondition">
  <where>
    deleted = 0
    <if test="query.id != null">
      and id = #{query.id,jdbcType=BIGINT}
    </if>
    <if test="query.userName != null and query.userName != ''">
      <bind name="userNameLike" value="'%' + query.userName + '%'"/>
      and user_name like #{userNameLike,jdbcType=VARCHAR}
    </if>
    <if test="query.status != null">
      and status = #{query.status,jdbcType=TINYINT}
    </if>
    <if test="query.statusList != null and query.statusList.size() > 0">
      and status in
      <foreach collection="query.statusList" item="item" open="(" close=")" separator=",">
        #{item,jdbcType=TINYINT}
      </foreach>
    </if>
    <if test="query.beginTime != null">
      and create_time <![CDATA[ >= ]]> #{query.beginTime,jdbcType=TIMESTAMP}
    </if>
    <if test="query.endTime != null">
      and create_time <![CDATA[ < ]]> #{query.endTime,jdbcType=TIMESTAMP}
    </if>
  </where>
</sql>

单条插入和更新模板 ​

xml
<insert id="insertSelective" useGeneratedKeys="true" keyProperty="id">
  insert into user_account
  <trim prefix="(" suffix=")" suffixOverrides=",">
    <if test="userNo != null">user_no,</if>
    <if test="userName != null">user_name,</if>
    <if test="mobile != null">mobile,</if>
    <if test="status != null">status,</if>
    create_time,
    update_time
  </trim>
  <trim prefix="values (" suffix=")" suffixOverrides=",">
    <if test="userNo != null">#{userNo,jdbcType=VARCHAR},</if>
    <if test="userName != null">#{userName,jdbcType=VARCHAR},</if>
    <if test="mobile != null">#{mobile,jdbcType=VARCHAR},</if>
    <if test="status != null">#{status,jdbcType=TINYINT},</if>
    now(),
    now()
  </trim>
</insert>

<update id="updateSelective">
  update user_account
  <set>
    <if test="userName != null">user_name = #{userName,jdbcType=VARCHAR},</if>
    <if test="mobile != null">mobile = #{mobile,jdbcType=VARCHAR},</if>
    <if test="status != null">status = #{status,jdbcType=TINYINT},</if>
    update_time = now()
  </set>
  where id = #{id,jdbcType=BIGINT}
    and deleted = 0
</update>

批量插入 / 更新 / Upsert ​

xml
<insert id="batchInsert">
  insert into user_account (user_no, user_name, mobile, status, create_time, update_time)
  values
  <foreach collection="list" item="item" separator=",">
    (
      #{item.userNo,jdbcType=VARCHAR},
      #{item.userName,jdbcType=VARCHAR},
      #{item.mobile,jdbcType=VARCHAR},
      #{item.status,jdbcType=TINYINT},
      now(),
      now()
    )
  </foreach>
</insert>

<update id="batchUpdateStatus">
  update user_account
  set
    status = case id
      <foreach collection="list" item="item">
        when #{item.id,jdbcType=BIGINT} then #{item.status,jdbcType=TINYINT}
      </foreach>
    end,
    update_time = now()
  where id in
  <foreach collection="list" item="item" open="(" close=")" separator=",">
    #{item.id,jdbcType=BIGINT}
  </foreach>
</update>

<insert id="batchUpsert">
  insert into user_account (id, user_no, user_name, mobile, status, create_time, update_time)
  values
  <foreach collection="list" item="item" separator=",">
    (
      #{item.id,jdbcType=BIGINT},
      #{item.userNo,jdbcType=VARCHAR},
      #{item.userName,jdbcType=VARCHAR},
      #{item.mobile,jdbcType=VARCHAR},
      #{item.status,jdbcType=TINYINT},
      now(),
      now()
    )
  </foreach>
  on duplicate key update
    user_name = values(user_name),
    mobile = values(mobile),
    status = values(status),
    update_time = now()
</insert>

exists 和空集合保护 ​

xml
<select id="existsByUserNo" resultType="java.lang.Boolean">
  select count(1) > 0
  from user_account
  where user_no = #{userNo,jdbcType=VARCHAR}
    and deleted = 0
</select>

<choose>
  <when test="ids != null and ids.size() > 0">
    and id in
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
      #{id,jdbcType=BIGINT}
    </foreach>
  </when>
  <otherwise>
    and 1 = 0
  </otherwise>
</choose>

安全排序模板 ​

xml
<sql id="OrderBy">
  <choose>
    <when test="query.sortField == 'createTime' and query.sortOrder == 'asc'">
      order by create_time asc
    </when>
    <when test="query.sortField == 'createTime' and query.sortOrder == 'desc'">
      order by create_time desc
    </when>
    <when test="query.sortField == 'status' and query.sortOrder == 'asc'">
      order by status asc
    </when>
    <when test="query.sortField == 'status' and query.sortOrder == 'desc'">
      order by status desc
    </when>
    <otherwise>
      order by id desc
    </otherwise>
  </choose>
</sql>

MyBatis-Plus 查询 / 分页 / 更新 ​

java
LambdaQueryWrapper<UserAccountDO> queryWrapper = Wrappers.lambdaQuery(UserAccountDO.class)
    .eq(UserAccountDO::getDeleted, 0)
    .eq(query.getStatus() != null, UserAccountDO::getStatus, query.getStatus())
    .like(StringUtils.hasText(query.getUserName()), UserAccountDO::getUserName, query.getUserName())
    .ge(query.getBeginTime() != null, UserAccountDO::getCreateTime, query.getBeginTime())
    .lt(query.getEndTime() != null, UserAccountDO::getCreateTime, query.getEndTime())
    .orderByDesc(UserAccountDO::getId);

Page<UserAccountDO> page = Page.of(query.getPageNo(), query.getPageSize());
IPage<UserAccountDO> result = userAccountMapper.selectPage(page, queryWrapper);

LambdaUpdateWrapper<UserAccountDO> updateWrapper = Wrappers.lambdaUpdate(UserAccountDO.class)
    .set(UserAccountDO::getStatus, 1)
    .set(UserAccountDO::getUpdateTime, LocalDateTime.now())
    .eq(UserAccountDO::getId, id)
    .eq(UserAccountDO::getDeleted, 0);

userAccountMapper.update(null, updateWrapper);

使用约定 ​

  1. 查询参数一律用 #{}，只有白名单排序才允许 ${}。
  2. 时间范围统一左闭右开：>= beginTime and < endTime。
  3. in 条件必须处理空集合，否则容易误查全表。
  4. 批量写入建议 300 到 1000 条一批。
最近更新