mybatis.xml中有哪些sql編寫規(guī)范

本篇文章為大家展示了mybatis.xml中有哪些sql編寫規(guī)范，內(nèi)容簡(jiǎn)明扼要并且容易理解，絕對(duì)能使你眼前一亮，通過這篇文章的詳細(xì)介紹希望你能有所收獲。

一、越少的代碼，越強(qiáng)悍的功能，xml里面應(yīng)該6個(gè)sql語句就夠用了，修改，維護(hù)成本很低，見下表 下載

二、公共的查詢條件和字段列表等抽出公共sql段，方便使用 

三、一個(gè)mybatis.xml例子  下載

Sql代碼  

<?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">  
  
  
    <!-- 設(shè)置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>  
      
      
    <!-- 通用范圍區(qū)間查詢 -->  
    <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>  
  
  
    <!-- 根據(jù)ID主鍵進(jìn)行刪除，注意limit 1 -->  
    <delete id="deleteById"  parameterType="java.lang.String" >  
        delete from assets where id = #{id} limit 1  
    </delete>   
  
  
    <!-- 根據(jù)主鍵ID進(jìn)行更新，注意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>  
  
  
    <!-- 根據(jù)ID進(jìn)行查詢 -->  
    <select id="selectById" resultMap="AssetsResultMap">  
        select * from assets where id = #{id}  
    </select>  
      
      
    <!-- 根據(jù)ID進(jìn)行加行鎖查詢 -->  
    <select id="selectByIdForUpdate" resultMap="AssetsResultMap">  
        select * from assets where id = #{id} for update  
    </select>  
  
      
    <!-- 根據(jù)查詢條件查詢數(shù)據(jù)和queryCountByParam方法配對(duì)使用 -->  
    <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>  
      
      
    <!-- 根據(jù)查詢條件查詢總數(shù)和queryListByParam方法配對(duì)使用 -->  
    <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>

上述內(nèi)容就是mybatis.xml中有哪些sql編寫規(guī)范，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注億速云行業(yè)資訊頻道。