溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇文章為大家展示了mybatis.xml中有哪些sql編寫規范，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。
一、越少的代碼，越強悍的功能，xml里面應該6個sql語句就夠用了，修改，維護成本很低，見下表 下載
| 英文名 | 方法名稱 | 核心點 | 建議 |
| insert | 1.新增數據 | 如果是自增主鍵，應該返回主鍵ID | |
| deleteById | 2. 根據主鍵ID刪除數據 | sql默認加limit 1，防止多刪數據 | 此方法不建議有，建議邏輯刪除 |
| updateById | 3. 根據主鍵ID修改數據 | sql默認加limit 1，防止多修改數據 | |
| selectById | 4. 根據主鍵查詢數據 | 查詢一條數據 | |
| selectByIdForUpdate | 5. 根據主鍵加鎖查詢數據 | 加鎖查詢一條數據，事務處理用 | |
| queryListByParam | 6. 根據輸入條件查詢數據列表 | 和7配合使用 | |
| queryCountByParam | 7. 根據輸入條件查詢總數 | 和6配合使用 |
二、公共的查詢條件和字段列表等抽出公共sql段，方便使用
| 英文名 | 方法名稱 | 核心點 | 建議 |
| _field_list | 1.字段列表 | 修改方便，方便字段排序 | |
| _value_list | 2. 字段值列表 | 修改方便，方便字段值排序 | |
| _common_where | 3. 通用查詢條件 | 每個字段的等值判斷 | |
| _regin_where | 4. 通用范圍區間條件 | 字段的時間區間，字段的金額區間等的判斷 | |
| _contain_where | 5. 包含字段值范圍條件 | 字段的常量值包含判斷，in ，not in | |
| _common_sorts | 6. 通用排序條件 | order by |
三、一個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">
<!-- 設置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>上述內容就是mybatis.xml中有哪些sql編寫規范，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
