首页 > 代码库 > mybatis.xml中sql编写规范

mybatis.xml中sql编写规范

一、越少的代码,越强悍的功能,xml里面应该6个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



三、一个mybatis.xml例子  下载

Sql代码  

  1. <?xml version="1.0" encoding="UTF-8"?>  

  2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">  

  3. <mapper namespace="Assets">  

  4.   

  5.   

  6.     <!-- 设置1分钟缓存,缓存大小1024,采用最近最少使用算法 -->  

  7.     <cache readOnly="true" flushInterval="60000" size="10" eviction="LRU" />  

  8.   

  9.     <resultMap type="Assets" id="AssetsResultMap">  

  10.         <id property="id" column="id" />  

  11.         <result property="userId" column="user_id" />  

  12.         <result property="amount" column="amount" />  

  13.         <result property="earning" column="earning" />  

  14.         <result property="type" column="type" />  

  15.         <result property="status" column="status" />  

  16.         <result property="productId" column="product_id" />  

  17.         <result property="productName" column="product_name" />  

  18.         <result property="cardNo" column="card_no" />  

  19.         <result property="bankCode" column="bank_code" />  

  20.         <result property="orderId" column="order_id" />  

  21.         <result property="effectiveDate" column="effective_date" />  

  22.         <result property="redeemType" column="redeem_type"/>  

  23.         <result property="initAmount" column="init_amount"/>  

  24.         <result property="initEarning" column="init_earning"/>  

  25.         <result property="redeemingAmount" column="redeeming_amount"/>  

  26.         <result property="redeemingEarning" column="redeeming_earning"/>  

  27.         <result property="redeemedAmount" column="redeemed_amount"/>  

  28.         <result property="redeemedEarning" column="redeemed_earning"/>  

  29.         <result property="punishAmount" column="punish_amount"/>  

  30.         <result property="latestRedeemTime" column="latest_redeem_time"/>  

  31.         <result property="maturityDate" column="maturity_date"/>  

  32.         <result property="createTime" column="create_time" />  

  33.         <result property="modifyTime" column="modify_time" />  

  34.         <result property="remark" column="remark" />  

  35.     </resultMap>  

  36.   

  37.     <!-- 字段列表 -->  

  38.     <sql id="_field_list">  

  39.         id,   

  40.         user_id,   

  41.         amount,   

  42.         earning,   

  43.         type,   

  44.         status,   

  45.         product_id,   

  46.         product_name,  

  47.         card_no,   

  48.         bank_code,   

  49.         order_id,   

  50.         effective_date,   

  51.         redeem_type,   

  52.         init_amount,   

  53.         init_earning,   

  54.         redeeming_amount,  

  55.         redeeming_earning,  

  56.         redeemed_amount,   

  57.         redeemed_earning,   

  58.         punish_amount,  

  59.         latest_redeem_time,   

  60.         maturity_date,  

  61.         create_time,   

  62.         modify_time,  

  63.         remark  

  64.     </sql>  

  65.   

  66.     <!-- 字段值列表 -->  

  67.     <sql id="_value_list">  

  68.         #{id},   

  69.         #{userId},  

  70.         #{amount},   

  71.         #{earning},   

  72.         #{type},   

  73.         #{status},   

  74.         #{productId},   

  75.         #{productName},   

  76.         #{cardNo},   

  77.         #{bankCode},   

  78.         #{orderId},   

  79.         #{effectiveDate},   

  80.         #{redeemType},   

  81.         #{initAmount},   

  82.         #{initEarning},   

  83.         #{redeemingAmount},  

  84.         #{redeemingEarning},  

  85.         #{redeemedAmount},   

  86.         #{redeemedEarning},   

  87.         #{punishAmount},  

  88.         #{latestRedeemTime},   

  89.         #{maturityDate},  

  90.         #{createTime},  

  91.         #{modifyTime},   

  92.         #{remark}  

  93.     </sql>  

  94.   

  95.     <!-- 通用查询条件  不支持ID查询条件,ID的直接通过ID即可以查 -->  

  96.     <sql id="_common_where">  

  97.         <if test="id != null"AND id = #{id}</if>  

  98.         <if test="userId != null"AND user_id = #{userId}</if>  

  99.         <if test="amount != null"AND amount = #{amount}</if>  

  100.         <if test="earning != null"AND earning = #{earning}</if>  

  101.         <if test="type != null"AND type = #{type}</if>  

  102.         <if test="status != null"AND status = #{status}</if>  

  103.         <if test="productId != null"AND product_id = #{productId}</if>  

  104.         <if test="productName != null"AND product_name = #{productName}</if>  

  105.         <if test="cardNo != null"AND card_no = #{cardNo}</if>  

  106.         <if test="bankCode != null"AND bank_code = #{bankCode}</if>  

  107.         <if test="orderId != null"AND order_id = #{orderId}</if>  

  108.         <if test="effectiveDate != null"AND effective_date = #{effectiveDate}</if>  

  109.         <if test="redeemType != null"AND redeem_type = #{redeemType}</if>  

  110.         <if test="initAmount != null"AND init_amount = #{initAmount}</if>  

  111.         <if test="initEarning != null"AND init_earning = #{initEarning}</if>  

  112.         <if test="redeemingAmount != null"AND redeeming_amount = #{redeemingAmount}</if>  

  113.         <if test="redeemingEarning != null"AND redeeming_earning = #{redeemingEarning}</if>  

  114.         <if test="redeemedAmount != null"AND redeemed_amount = #{redeemedAmount}</if>  

  115.         <if test="redeemedEarning != null"AND redeemed_earning = #{redeemedEarning}</if>  

  116.         <if test="punishAmount != null"AND punish_amount = #{punishAmount}</if>  

  117.         <if test="latestRedeemTime != null">  

  118.             <![CDATA[  

  119.                 AND latest_redeem_time = #{latestRedeemTime, jdbcType=TIMESTAMP}   

  120.             ]]>  

  121.         </if>  

  122.         <if test="maturityDate != null">  

  123.             <![CDATA[  

  124.                 AND maturity_date = #{maturityDate, jdbcType=TIMESTAMP}   

  125.             ]]>  

  126.         </if>  

  127.         <if test="createTime != null">  

  128.             <![CDATA[  

  129.                 AND create_time = #{createTime, jdbcType=TIMESTAMP}   

  130.             ]]>  

  131.         </if>  

  132.         <if test="modifyTime != null">  

  133.             <![CDATA[  

  134.                 AND modify_time = #{modifyTime, jdbcType=TIMESTAMP}   

  135.             ]]>  

  136.         </if>  

  137.         <if test="remark != null"AND remark = #{remark}</if>  

  138.     </sql>  

  139.       

  140.       

  141.     <!-- 通用范围区间查询 -->  

  142.     <sql id="_regin_where">  

  143.         <if test="egtCreateTime != null">  

  144.             <![CDATA[  

  145.                 AND create_time >= #{egtCreateTime, jdbcType=TIMESTAMP}   

  146.             ]]>  

  147.         </if>  

  148.         <if test="ltCreateTime != null">  

  149.             <![CDATA[  

  150.                 AND create_time < #{ltCreateTime, jdbcType=TIMESTAMP}   

  151.             ]]>  

  152.         </if>  

  153.     </sql>  

  154.       

  155.       

  156.     <!-- 通用排序处理 -->  

  157.     <sql id="_common_sorts">  

  158.         <if test="sorts != null">  

  159.             ORDER BY  

  160.             <foreach collection="sorts" item="item" separator=",">  

  161.                 ${item.column.columnName} ${item.sortMode.mode}  

  162.             </foreach>  

  163.         </if>  

  164.     </sql>  

  165.       

  166.       

  167.     <!-- in 和 not in的通用查询where -->  

  168.     <sql id="_contain_where">  

  169.         <if test="containStatusSet!=null">  

  170.             AND status IN  

  171.             <foreach item="item" index="i" collection="containStatusSet" separator="," open="(" close=")" >    

  172.                 #{item}    

  173.             </foreach>  

  174.         </if>  

  175.           

  176.     </sql>  

  177.       

  178.       

  179.     <!-- 插入操作 -->  

  180.     <insert id="insert" parameterType="Assets">  

  181.         INSERT INTO assets (  

  182.             <include refid="_field_list"/>)  

  183.         VALUES (  

  184.             <include refid="_value_list"/>)  

  185.     </insert>  

  186.   

  187.   

  188.     <!-- 根据ID主键进行删除,注意limit 1 -->  

  189.     <delete id="deleteById"  parameterType="java.lang.String" >  

  190.         delete from assets where id = #{id} limit 1  

  191.     </delete>   

  192.   

  193.   

  194.     <!-- 根据主键ID进行更新,注意limit 1 -->  

  195.     <update id="updateById" parameterType="Assets">  

  196.         UPDATE assets  

  197.         <set>  

  198.             <if test="userId != null">  

  199.                 user_id = #{userId},  

  200.             </if>  

  201.             <if test="amount != null">  

  202.                 amount = #{amount},  

  203.             </if>  

  204.             <if test="earning != null">  

  205.                 earning = #{earning},  

  206.             </if>  

  207.             <if test="type != null">  

  208.                 type = #{type},  

  209.             </if>  

  210.             <if test="status != null">  

  211.                 status = #{status},  

  212.             </if>  

  213.             <if test="productName != null">  

  214.                 product_name = #{productName},  

  215.             </if>  

  216.             <if test="productId != null">  

  217.                 product_id = #{productId},  

  218.             </if>  

  219.             <if test="cardNo != null">  

  220.                 card_no = #{cardNo},  

  221.             </if>  

  222.             <if test="bankCode != null">  

  223.                 bank_code = #{bankCode},  

  224.             </if>  

  225.             <if test="orderId != null">  

  226.                 order_id = #{orderId},  

  227.             </if>  

  228.             <if test="effectiveDate != null">  

  229.                 effective_date = #{effectiveDate},  

  230.             </if>  

  231.             <if test="redeemType != null">  

  232.                 redeem_type = #{redeemType},  

  233.             </if>  

  234.             <if test="initAmount != null">  

  235.                 init_amount = #{initAmount},  

  236.             </if>  

  237.             <if test="initEarning != null">  

  238.                 init_earning = #{initEarning},  

  239.             </if>  

  240.             <if test="redeemingAmount != null">  

  241.                 redeeming_amount = #{redeemingAmount},  

  242.             </if>  

  243.             <if test="redeemingEarning != null">  

  244.                 redeeming_earning = #{redeemingEarning},  

  245.             </if>  

  246.             <if test="redeemedAmount != null">  

  247.                 redeemed_amount = #{redeemedAmount},  

  248.             </if>  

  249.             <if test="redeemedEarning != null">  

  250.                 redeemed_earning = #{redeemedEarning},  

  251.             </if>  

  252.             <if test="punishAmount != null">  

  253.                 punish_amount = #{punishAmount},  

  254.             </if>  

  255.             <if test="latestRedeemTime != null">  

  256.                 latest_redeem_time = #{latestRedeemTime},  

  257.             </if>  

  258.             <if test="maturityDate != null">  

  259.                 maturity_date = #{maturityDate},  

  260.             </if>  

  261.             <if test="modifyTime != null">  

  262.                 modify_time = #{modifyTime},  

  263.             </if>  

  264.             <if test="remark != null">  

  265.                 remark = #{remark},  

  266.             </if>  

  267.         </set>  

  268.   

  269.         <where>  

  270.             id = #{id} limit 1  

  271.         </where>  

  272.     </update>  

  273.   

  274.   

  275.     <!-- 根据ID进行查询 -->  

  276.     <select id="selectById" resultMap="AssetsResultMap">  

  277.         select * from assets where id = #{id}  

  278.     </select>  

  279.       

  280.       

  281.     <!-- 根据ID进行加行锁查询 -->  

  282.     <select id="selectByIdForUpdate" resultMap="AssetsResultMap">  

  283.         select * from assets where id = #{id} for update  

  284.     </select>  

  285.   

  286.       

  287.     <!-- 根据查询条件查询数据和queryCountByParam方法配对使用 -->  

  288.     <select id="queryListByParam" parameterType="map" resultMap="AssetsResultMap">  

  289.         SELECT   

  290.             <include refid="_field_list"/>  

  291.         FROM   

  292.             assets  

  293.         <where>  

  294.             1 = 1  

  295.             <include refid="_common_where"/>  

  296.             <include refid="_regin_where"/>  

  297.             <include refid="_contain_where"/>  

  298.         </where>  

  299.           

  300.         <include refid="_common_sorts"/>  

  301.           

  302.         <if test="offset != null and rows != null">  

  303.             limit #{offset}, #{rows}  

  304.         </if>  

  305.     </select>  

  306.       

  307.       

  308.     <!-- 根据查询条件查询总数和queryListByParam方法配对使用 -->  

  309.     <select id="queryCountByParam" parameterType="map" resultType="java.lang.Integer">  

  310.         SELECT count(1) FROM assets  

  311.         <where>  

  312.             1 = 1  

  313.             <include refid="_common_where"/>  

  314.             <include refid="_regin_where"/>  

  315.             <include refid="_contain_where"/>  

  316.         </where>  

  317.     </select>  

  318.       

  319. </mapper>  


mybatis.xml中sql编写规范