Nine MyBatis SQL Tricks for SpringBoot Web Applications (MySQL Example)

Background – SpringBoot web development increasingly relies on MyBatis for database CRUD operations. Using MySQL as an example, the author summarizes nine categories of MyBatis SQL tricks.

01 Pagination Query

Use limit and offset to set page size and index.

select * from sys_user u
LEFT JOIN sys_user_site s ON u.user_id = s.user_id
LEFT JOIN sys_dept d ON d.dept_id = s.dept_id
LEFT JOIN sys_emailinfo e ON u.user_id = e.userid AND e.MAIN_FLAG = 'Y'
<where>
  <include refid="userCondition"/>
</where>
limit #{offset}, #{limit}

02 Preset SQL Query Columns

<sql id="columns">
  id,title,content,original_img,is_user_edit,province_id,status,porder
</sql>

Reference the column list in a select statement:

<select id="selectById" resultMap="RM_MsShortcutPanel">
  select
  <include refid="columns"/>
  from cms_self_panel
  where id = #{_parameter}
</select>

03 One‑to‑Many Cascade Query

Use MyBatis collection tag with queryparaminstancelist to fetch related table data.

<resultMap id="BaseResultMap" type="com.unicom.portal.pcm.entity.ArticleEntity">
  <id column="id" jdbcType="BIGINT" property="id"/>
  <collection property="paramList" column="id" select="queryparaminstancelist"/>
</resultMap>

Corresponding SQL:

<select id="queryparaminstancelist" resultMap="ParamInstanceResultMap">
  select * from `cms_article_flow_param_instance` where article_id=#{id}
</select>

04 One‑to‑One Association Query

Use MyBatis association tag to retrieve related table data.

<resultMap id="BaseResultMap" type="com.unicom.portal.pcm.entity.ArticleEntity">
  <association property="articleCount" javaType="com.unicom.portal.pcm.entity.MsArticleCount"/>
</resultMap>

The resulting SQL (illustrated as an image in the original article) maps the fields to the MsArticleCount entity.

05 foreach with in Query

Iterate an array collection to build an IN clause.

<foreach collection="array" index="index" item="item" open="(" separator="," close=")">
  #{item}
</foreach>

06 Dynamic where with if Tag

select r.*, (select d.org_name from sys_dept d where d.dept_id = r.dept_id) deptName from sys_role r
<where>
  r.wid = #{wid}
  <if test="roleName != null and roleName.trim() != ''">
    and r.`role_name` like concat('%', #{roleName}, '%')
  </if>
  <if test="status != null and status.trim() != ''">
    and r.`status` = #{status}
  </if>
</where>

07 choose / otherwise Combination

<choose>
  <when test="sidx != null and sidx.trim() != ''">
    order by r.${sidx} ${order}
  </when>
  <otherwise>
    order by r.role_id asc
  </otherwise>
</choose>

08 Hidden Parameter _parameter

The _parameter placeholder represents the sole method argument when using Mapper, association, or collection tags.

SELECT id, grp_no grpNo, province_id provinceId, status FROM tj_group_province
<where>
  ...
  <if test="_parameter!=null">
    and grp_no = #{_parameter}
  </if>
</where>

09 Dynamic set for Update

<update id="updateById">
  UPDATE cms_label
  <set>
    <if test="labelGroupId != null">
      label_group_id = #{labelGroupId},
    </if>
    dept_id = #{deptId},
    <if test="recommend != null">
      is_recommend = #{recommend},
    </if>
  </set>
  WHERE label_id = #{labelId}
</update>

The article concludes with a list of recommended reading links (advertisements) that are not part of the technical content.