MyBatis,这个优秀的持久层引擎让开发者在追求性能的同时可以集中维护SQL。虽然它已经提供了一些模板化的支持,然而在面对表结构变动以及复杂联合查询时,所产生的Mapper文件将会过度增长,让维护变得困难。我是一个懒惰的码猿,现在我们有了MyBatis Scripting for Freemarker/Velocity插件,是时候进一步简化我们的Mapper文件咯。在MyBatis引擎加载Mapper文件的时候,会调用脚本引擎解释特定标签,动态生成满足MyBatis XML语义的文本,然后交由MyBatis继续执行。在MyBatis官网给出的例子过于简单,经过摸索,我采用xml的include以及freemarker宏调用相结合,大幅简化了mapper文件。有了真正模板的支持,维护工作可能简单到只需维护字段定义了。代码骨架已发布到Github,宏代码经过测试,项目地址: https://github.com/Joycean/skeleton-mybatis-persistence
使用Freemarker模板之后,我们的Mapper文件看起来是这样子的:
<?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="<span style="font-family: Arial, Helvetica, sans-serif;">skeleton.persistence</span><span style="font-family: Arial, Helvetica, sans-serif;">.persistence.mapper.UserMapper"></span>
<sql id="def">
<![CDATA[
<#assign tableName = 'Users'>
<#include 'common.ftl'>
]]>
</sql>
<insert id="insert" parameterType="User">
<include refid="def"/><![CDATA[<@insert />]]>
</insert>
<update id="update" parameterType="User">
<include refid="def"/><![CDATA[<@update />]]>
</update>
<update id="delete" parameterType="User">
<include refid="def"/><![CDATA[<@delete />]]>
</update>
<select id="get" parameterType="long" resultType="User">
<include refid="def"/><![CDATA[<@get />]]>
</select>
<select id="count" parameterType="map" resultType="int">
<include refid="def"/><![CDATA[<@count />]]>
</select>
<select id="select" parameterType="map" resultType="User">
<include refid="def"/><![CDATA[<@select />]]>
</select>
</mapper>
/*=======分割线========*/
而在使用Freemarker之前,我们的Mapper看起来可能是这样子的:
<?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.seefond.server.persistence.mapper.UserMapper">
<sql id="tableName">t_user</sql>
<sql id="insertColumns">
company_id,login_name,real_name,employee_no,password,mobile,email,title,introduction,
last_login,role,last_device_id,enabled
</sql>
<sql id="insertValues">
#{companyId,javaType=long,jdbcType=BIGINT},
#{loginName,javaType=String,jdbcType=VARCHAR},
#{realName,javaType=String,jdbcType=VARCHAR},
#{employeeNo,javaType=String,jdbcType=VARCHAR},
#{password,javaType=String,jdbcType=VARCHAR},
#{mobile,javaType=String,jdbcType=VARCHAR},
#{email,javaType=String,jdbcType=VARCHAR},
#{title,javaType=String,jdbcType=VARCHAR},
#{introduction,javaType=String,jdbcType=VARCHAR},
#{lastLogin,javaType=Date,jdbcType=TIMESTAMP},
#{role,javaType=int,jdbcType=TINYINT},
#{lastDeviceId,javaType=long,jdbcType=BIGINT},
#{enabled,javaType=boolean,jdbcType=TINYINT}
</sql>
<sql id="updateColumnValues">
company_id=#{companyId,javaType=long,jdbcType=BIGINT},
login_name=#{loginName,javaType=String,jdbcType=VARCHAR},
real_name=#{realName,javaType=String,jdbcType=VARCHAR},
employee_no=#{employeeNo,javaType=String,jdbcType=VARCHAR},
password=#{password,javaType=String,jdbcType=VARCHAR},
mobile=#{mobile,javaType=String,jdbcType=VARCHAR},
email=#{email,javaType=String,jdbcType=VARCHAR},
title=#{title,javaType=String,jdbcType=VARCHAR},
introduction=#{introduction,javaType=String,jdbcType=VARCHAR},
last_login=#{lastLogin,javaType=Date,jdbcType=TIMESTAMP},
role=#{role,javaType=int,jdbcType=TINYINT},
last_device_id=#{lastDeviceId,javaType=long,jdbcType=BIGINT},
enabled=#{enabled,javaType=boolean,jdbcType=TINYINT}
</sql>
<sql id="selectAliases">
company_id companyId,
login_name loginName,
real_name realName,
employee_no employeeNo,
password password,
mobile mobile,
email email,
title title,
introduction introduction,
last_login lastLogin,
role role,
last_device_id lastDeviceId,
enabled enabled
</sql>
<sql id="selectCriteria">
<if test="criteria.companyId != null">
AND company_id=#{criteria.companyId,javaType=long,jdbcType=BIGINT}
</if>
<if test="criteria.loginName != null">
AND login_name=#{criteria.loginName,javaType=String,jdbcType=VARCHAR}
</if>
<if test="criteria.realName != null">
AND real_name LIKE #{criteria.realName,javaType=String,jdbcType=VARCHAR}
</if>
<if test="criteria.employeeNo != null">
AND employee_no=#{criteria.employeeNo,javaType=String,jdbcType=VARCHAR}
</if>
<if test="criteria.mobile != null">
AND mobile=#{criteria.mobile,javaType=String,jdbcType=VARCHAR}
</if>
<if test="criteria.email != null">
AND email LIKE #{criteria.email,javaType=String,jdbcType=VARCHAR}
</if>
<if test="criteria.title != null">
AND title LIKE #{criteria.title,javaType=String,jdbcType=VARCHAR}
</if>
<if test="criteria.role != null">
AND role=#{criteria.role,javaType=int,jdbcType=TINYINT}
</if>
<if test="criteria.lastDeviceId != null">
AND last_device_id=#{criteria.lastDeviceId,javaType=long,jdbcType=BIGINT}
</if>
<if test="criteria.enabled != null">
AND enabled=#{criteria.enabled,javaType=boolean,jdbcType=TINYINT}
</if>
</sql>
<select id="getByLoginName" parameterType="map" resultType="User">
SELECT
<include refid="com.seefond.server.persistence.mapper.BaseMapper.commonSelectAliases"/>
<include refid="selectAliases"/>
FROM t_user WHERE deleted=0 AND login_name=#{loginName} AND company_id=#{companyId}
</select>
<select id="getSubordinateUsersInManagedStores" parameterType="long" resultType="User">
SELECT
<include refid="com.seefond.server.persistence.mapper.BaseMapper.commonSelectAliases"/>
<include refid="selectAliases"/>
FROM (
<![CDATA[
SELECT u2.* FROM
t_user u1,
t_store_user su1,
t_store_user su2,
t_user u2
WHERE u1.id=#{id}
AND u1.id=su1.user_id
AND su1.store_id=su2.store_id
AND su2.user_id=u2.id
AND u2.role < u1.role
AND u1.deleted=0
AND u2.deleted=0
AND su1.deleted=0
AND su2.deleted=0
]]>
) _T ORDER BY role DESC, employeeNo
</select>
<select id="getSubordinateUsersInManagedStore" parameterType="map" resultType="User">
SELECT
<include refid="com.seefond.server.persistence.mapper.BaseMapper.commonSelectAliases"/>
<include refid="selectAliases"/>
FROM (
<![CDATA[
SELECT u2.* FROM
t_user u1,
t_store_user su1,
t_store_user su2,
t_user u2
WHERE u1.id=#{userId,javaType=long,jdbcType=BIGINT}
AND u1.id=su1.user_id
AND su1.store_id=#{storeId,javaType=long,jdbcType=BIGINT}
AND su1.store_id=su2.store_id
AND su2.user_id=u2.id
AND u2.role < u1.role
AND u1.deleted=0
AND u2.deleted=0
AND su1.deleted=0
AND su2.deleted=0
]]>
) _T ORDER BY role DESC, employeeNo
</select>
<select id="getByStoreId" parameterType="long" resultType="User">
SELECT
<include refid="com.seefond.server.persistence.mapper.BaseMapper.commonSelectAliases"/>
<include refid="selectAliases"/>
FROM (
<![CDATA[
SELECT u.* FROM
t_user u,
t_store_user su
WHERE u.id=su.user_id
AND su.store_id=#{storeId,javaType=long,jdbcType=BIGINT}
AND u.deleted=0
AND su.deleted=0
]]>
) _T ORDER BY role DESC, employeeNo
</select>
<insert id="insert" parameterType="User" useGeneratedKeys="true" keyProperty="id">
<include refid="com.seefond.server.persistence.mapper.BaseMapper.commonInsertWithAutoIdStatement"/>
</insert>
<update id="update" parameterType="User">
<include refid="com.seefond.server.persistence.mapper.BaseMapper.commonUpdateStatement"/>
</update>
<update id="delete" parameterType="User">
<include refid="com.seefond.server.persistence.mapper.BaseMapper.commonDeleteStatement"/>
</update>
<delete id="purge" parameterType="User">
<include refid="com.seefond.server.persistence.mapper.BaseMapper.commonPurgeStatement"/>
</delete>
<select id="get" parameterType="long" resultType="User">
<include refid="com.seefond.server.persistence.mapper.BaseMapper.commonGetStatement"/>
</select>
<select id="count" parameterType="map" resultType="int">
<include refid="com.seefond.server.persistence.mapper.BaseMapper.commonCountByCriteriaStatement"/>
</select>
<select id="select" parameterType="map" resultType="User">
<include refid="com.seefond.server.persistence.mapper.BaseMapper.commonSelectByCriteriaStatement"/>
</select>
<sql id="isolationFilter">
id=#{isolationId,javaType=long,jdbcType=BIGINT}
</sql>
<insert id="syncInsert" parameterType="User">
<include refid="com.seefond.server.persistence.mapper.SyncableMapper.commonSyncInsertStatement"/>
</insert>
<update id="syncUpdate" parameterType="User">
<include refid="com.seefond.server.persistence.mapper.SyncableMapper.commonSyncUpdateStatement"/>
</update>
<update id="syncDelete" parameterType="User">
<include refid="com.seefond.server.persistence.mapper.SyncableMapper.commonSyncDeleteStatement"/>
</update>
<select id="getIdsByStatus" parameterType="map" resultType="long">
<include refid="com.seefond.server.persistence.mapper.SyncableMapper.commonChangedStoreBasedIdSelectStatement"/>
</select>
<select id="getItemsByStatus" parameterType="map" resultType="User">
<include refid="com.seefond.server.persistence.mapper.SyncableMapper.commonChangedStoreBasedItemSelectStatement"/>
</select>
</mapper>
细心的读者可能会说两个Mapper定义的方法不一样,不可比。这两个文件均来自真实项目,而重点是我们可以看到Freemarker已将我们从繁琐的<sql>片段中解脱了。当然,为了迎合mapper xml的语法规约,现在还做不到极简。如果MyBatis在将来弱化xml的schema限制,比如取消<select><update><resultMap>等标签限制,我们可以让事情极简。虽然现在已经可以完全使用原生模板,但插件引擎只支持到方法级,一个方法一个模板文件,不实用。希望这个偷懒方法对你有用。