Mybatis XML文件sql编写规范

一、sql语句

英文名方法名称核心点建议
insert1.新增数据如果是自增主键,应该返回主键ID 
deleteById2. 根据主键ID删除数据sql默认加limit 1,防止多删数据此方法不建议有,建议逻辑删除
updateById3. 根据主键ID修改数据sql默认加limit 1,防止多修改数据 
selectById4. 根据主键查询数据查询一条数据 
selectByIdForUpdate5. 根据主键加锁查询数据加锁查询一条数据,事务处理用 
queryListByParam6. 根据输入条件查询数据列表和7配合使用 
queryCountByParam7. 根据输入条件查询总数和6配合使用 

二、公共的查询条件和字段列表等抽出公共sql段

英文名方法名称核心点建议
_field_list1.字段列表修改方便,方便字段排序 
_value_list2. 字段值列表修改方便,方便字段值排序 
_common_where3. 通用查询条件每个字段的等值判断 
_regin_where4. 通用范围区间条件字段的时间区间,字段的金额区间等的判断 
_contain_where5. 包含字段值范围条件字段的常量值包含判断,in ,not in 
_common_sorts6. 通用排序条件order by 

三、例子

<?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="Assets">  
  
    <!-- 设置1分钟缓存,缓存大小1024,采用最近最少使用算法 -->  
    <cache readOnly="true" flushInterval="60000" size="10" eviction="LRU" />  
  
    <resultMap type="Assets" id="AssetsResultMap">  
        <id property="id" column="id" />  
        <result property="userId" column="user_id" />  
        <result property="amount" column="amount" />  
        <result property="earning" column="earning" />  
        <result property="type" column="type" />  
        <result property="status" column="status" />  
        <result property="productId" column="product_id" />  
        <result property="productName" column="product_name" />  
        <result property="cardNo" column="card_no" />  
        <result property="bankCode" column="bank_code" />  
        <result property="orderId" column="order_id" />  
        <result property="effectiveDate" column="effective_date" />  
        <result property="redeemType" column="redeem_type"/>  
        <result property="initAmount" column="init_amount"/>  
        <result property="initEarning" column="init_earning"/>  
        <result property="redeemingAmount" column="redeeming_amount"/>  
        <result property="redeemingEarning" column="redeeming_earning"/>  
        <result property="redeemedAmount" column="redeemed_amount"/>  
        <result property="redeemedEarning" column="redeemed_earning"/>  
        <result property="punishAmount" column="punish_amount"/>  
        <result property="latestRedeemTime" column="latest_redeem_time"/>  
        <result property="maturityDate" column="maturity_date"/>  
        <result property="createTime" column="create_time" />  
        <result property="modifyTime" column="modify_time" />  
        <result property="remark" column="remark" />  
    </resultMap>  
  
    <!-- 字段列表 -->  
    <sql id="_field_list">  
        id,   
        user_id,   
        amount,   
        earning,   
        type,   
        status,   
        product_id,   
        product_name,  
        card_no,   
        bank_code,   
        order_id,   
        effective_date,   
        redeem_type,   
        init_amount,   
        init_earning,   
        redeeming_amount,  
        redeeming_earning,  
        redeemed_amount,   
        redeemed_earning,   
        punish_amount,  
        latest_redeem_time,   
        maturity_date,  
        create_time,   
        modify_time,  
        remark  
    </sql>  
  
    <!-- 字段值列表 -->  
    <sql id="_value_list">  
        #{id},   
        #{userId},  
        #{amount},   
        #{earning},   
        #{type},   
        #{status},   
        #{productId},   
        #{productName},   
        #{cardNo},   
        #{bankCode},   
        #{orderId},   
        #{effectiveDate},   
        #{redeemType},   
        #{initAmount},   
        #{initEarning},   
        #{redeemingAmount},  
        #{redeemingEarning},  
        #{redeemedAmount},   
        #{redeemedEarning},   
        #{punishAmount},  
        #{latestRedeemTime},   
        #{maturityDate},  
        #{createTime},  
        #{modifyTime},   
        #{remark}  
    </sql>  
  
    <!-- 通用查询条件  不支持ID查询条件,ID的直接通过ID即可以查 -->  
    <sql id="_common_where">  
        <if test="id != null"> AND id = #{id}</if>  
        <if test="userId != null"> AND user_id = #{userId}</if>  
        <if test="amount != null"> AND amount = #{amount}</if>  
        <if test="earning != null"> AND earning = #{earning}</if>  
        <if test="type != null"> AND type = #{type}</if>  
        <if test="status != null"> AND status = #{status}</if>  
        <if test="productId != null"> AND product_id = #{productId}</if>  
        <if test="productName != null"> AND product_name = #{productName}</if>  
        <if test="cardNo != null"> AND card_no = #{cardNo}</if>  
        <if test="bankCode != null"> AND bank_code = #{bankCode}</if>  
        <if test="orderId != null"> AND order_id = #{orderId}</if>  
        <if test="effectiveDate != null"> AND effective_date = #{effectiveDate}</if>  
        <if test="redeemType != null"> AND redeem_type = #{redeemType}</if>  
        <if test="initAmount != null"> AND init_amount = #{initAmount}</if>  
        <if test="initEarning != null"> AND init_earning = #{initEarning}</if>  
        <if test="redeemingAmount != null"> AND redeeming_amount = #{redeemingAmount}</if>  
        <if test="redeemingEarning != null"> AND redeeming_earning = #{redeemingEarning}</if>  
        <if test="redeemedAmount != null"> AND redeemed_amount = #{redeemedAmount}</if>  
        <if test="redeemedEarning != null"> AND redeemed_earning = #{redeemedEarning}</if>  
        <if test="punishAmount != null"> AND punish_amount = #{punishAmount}</if>  
        <if test="latestRedeemTime != null">  
            <![CDATA[  
                AND latest_redeem_time = #{latestRedeemTime, jdbcType=TIMESTAMP}   
            ]]>  
        </if>  
        <if test="maturityDate != null">  
            <![CDATA[  
                AND maturity_date = #{maturityDate, jdbcType=TIMESTAMP}   
            ]]>  
        </if>  
        <if test="createTime != null">  
            <![CDATA[  
                AND create_time = #{createTime, jdbcType=TIMESTAMP}   
            ]]>  
        </if>  
        <if test="modifyTime != null">  
            <![CDATA[  
                AND modify_time = #{modifyTime, jdbcType=TIMESTAMP}   
            ]]>  
        </if>  
        <if test="remark != null"> AND remark = #{remark}</if>  
    </sql>  
      
    <!-- 通用范围区间查询 -->  
    <sql id="_regin_where">  
        <if test="egtCreateTime != null">  
            <![CDATA[  
                AND create_time >= #{egtCreateTime, jdbcType=TIMESTAMP}   
            ]]>  
        </if>  
        <if test="ltCreateTime != null">  
            <![CDATA[  
                AND create_time < #{ltCreateTime, jdbcType=TIMESTAMP}   
            ]]>  
        </if>  
    </sql>  
      
    <!-- 通用排序处理 -->  
    <sql id="_common_sorts">  
        <if test="sorts != null">  
            ORDER BY  
            <foreach collection="sorts" item="item" separator=",">  
                ${item.column.columnName} ${item.sortMode.mode}  
            </foreach>  
        </if>  
    </sql> 
      
    <!-- in 和 not in的通用查询where -->  
    <sql id="_contain_where">  
        <if test="containStatusSet!=null">  
            AND status IN  
            <foreach item="item" index="i" collection="containStatusSet" separator="," open="(" close=")" >    
                #{item}    
            </foreach>  
        </if>  
    </sql> 
      
    <!-- 插入操作 -->  
    <insert id="insert" parameterType="Assets">  
        INSERT INTO assets (  
            <include refid="_field_list"/>)  
        VALUES (  
            <include refid="_value_list"/>)  
    </insert>  
  
    <!-- 根据ID主键进行删除,注意limit 1 -->  
    <delete id="deleteById"  parameterType="java.lang.String" >  
        delete from assets where id = #{id} limit 1  
    </delete>   
  
    <!-- 根据主键ID进行更新,注意limit 1 -->  
    <update id="updateById" parameterType="Assets">  
        UPDATE assets  
        <set>  
            <if test="userId != null">  
                user_id = #{userId},  
            </if>  
            <if test="amount != null">  
                amount = #{amount},  
            </if>  
            <if test="earning != null">  
                earning = #{earning},  
            </if>  
            <if test="type != null">  
                type = #{type},  
            </if>  
            <if test="status != null">  
                status = #{status},  
            </if>  
            <if test="productName != null">  
                product_name = #{productName},  
            </if>  
            <if test="productId != null">  
                product_id = #{productId},  
            </if>  
            <if test="cardNo != null">  
                card_no = #{cardNo},  
            </if>  
            <if test="bankCode != null">  
                bank_code = #{bankCode},  
            </if>  
            <if test="orderId != null">  
                order_id = #{orderId},  
            </if>  
            <if test="effectiveDate != null">  
                effective_date = #{effectiveDate},  
            </if>  
            <if test="redeemType != null">  
                redeem_type = #{redeemType},  
            </if>  
            <if test="initAmount != null">  
                init_amount = #{initAmount},  
            </if>  
            <if test="initEarning != null">  
                init_earning = #{initEarning},  
            </if>  
            <if test="redeemingAmount != null">  
                redeeming_amount = #{redeemingAmount},  
            </if>  
            <if test="redeemingEarning != null">  
                redeeming_earning = #{redeemingEarning},  
            </if>  
            <if test="redeemedAmount != null">  
                redeemed_amount = #{redeemedAmount},  
            </if>  
            <if test="redeemedEarning != null">  
                redeemed_earning = #{redeemedEarning},  
            </if>  
            <if test="punishAmount != null">  
                punish_amount = #{punishAmount},  
            </if>  
            <if test="latestRedeemTime != null">  
                latest_redeem_time = #{latestRedeemTime},  
            </if>  
            <if test="maturityDate != null">  
                maturity_date = #{maturityDate},  
            </if>  
            <if test="modifyTime != null">  
                modify_time = #{modifyTime},  
            </if>  
            <if test="remark != null">  
                remark = #{remark},  
            </if>  
        </set>  
  
        <where>  
            id = #{id} limit 1  
        </where>  
    </update>  
  
    <!-- 根据ID进行查询 -->  
    <select id="selectById" resultMap="AssetsResultMap">  
        select * from assets where id = #{id}  
    </select>  
      
    <!-- 根据ID进行加行锁查询 -->  
    <select id="selectByIdForUpdate" resultMap="AssetsResultMap">  
        select * from assets where id = #{id} for update  
    </select>  
      
    <!-- 根据查询条件查询数据和queryCountByParam方法配对使用 -->  
    <select id="queryListByParam" parameterType="map" resultMap="AssetsResultMap">  
        SELECT   
            <include refid="_field_list"/>  
        FROM   
            assets  
        <where>  
            1 = 1  
            <include refid="_common_where"/>  
            <include refid="_regin_where"/>  
            <include refid="_contain_where"/>  
        </where>  
          
        <include refid="_common_sorts"/>  
          
        <if test="offset != null and rows != null">  
            limit #{offset}, #{rows}  
        </if>  
    </select>  
      
    <!-- 根据查询条件查询总数和queryListByParam方法配对使用 -->  
    <select id="queryCountByParam" parameterType="map" resultType="java.lang.Integer">  
        SELECT count(1) FROM assets  
        <where>  
            1 = 1  
            <include refid="_common_where"/>  
            <include refid="_regin_where"/>  
            <include refid="_contain_where"/>  
        </where>  
    </select>  
</mapper>  

发表评论