本篇文章为大家展示了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进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
