您好,登录后才能下订单哦!
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
本篇文章为大家展示了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进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。