mybatis.xml中sql编写规范
一、越少的代码，越强悍的功能，xml里面应该6个sql语句就够用了，修改，维护成本很低，见下表 下载英文名方法名称核心点建议insert1.新增数据如果是自增主键，应该返回主键IDdeleteById2. 根据主键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代码
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*] 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
[*]
[*]
[*]
[*]
[*] #{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}
[*]
[*]
[*]
[*]
[*] AND id = #{id}
[*] AND user_id = #{userId}
[*] AND amount = #{amount}
[*] AND earning = #{earning}
[*] AND type = #{type}
[*] AND status = #{status}
[*] AND product_id = #{productId}
[*] AND product_name = #{productName}
[*] AND card_no = #{cardNo}
[*] AND bank_code = #{bankCode}
[*] AND order_id = #{orderId}
[*] AND effective_date = #{effectiveDate}
[*] AND redeem_type = #{redeemType}
[*] AND init_amount = #{initAmount}
[*] AND init_earning = #{initEarning}
[*] AND redeeming_amount = #{redeemingAmount}
[*] AND redeeming_earning = #{redeemingEarning}
[*] AND redeemed_amount = #{redeemedAmount}
[*] AND redeemed_earning = #{redeemedEarning}
[*] AND punish_amount = #{punishAmount}
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*] AND remark = #{remark}
[*]
[*]
[*]
[*]
[*]
[*]
[*] = #{egtCreateTime, jdbcType=TIMESTAMP}
[*] ]]>
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*] ORDER BY
[*]
[*] ${item.column.columnName} ${item.sortMode.mode}
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*] AND status IN
[*]
[*] #{item}
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*] INSERT INTO assets (
[*] )
[*] VALUES (
[*] )
[*]
[*]
[*]
[*]
[*]
[*] delete from assets where id = #{id} limit 1
[*]
[*]
[*]
[*]
[*]
[*] UPDATE assets
[*]
[*]
[*] user_id = #{userId},
[*]
[*]
[*] amount = #{amount},
[*]
[*]
[*] earning = #{earning},
[*]
[*]
[*] type = #{type},
[*]
[*]
[*] status = #{status},
[*]
[*]
[*] product_name = #{productName},
[*]
[*]
[*] product_id = #{productId},
[*]
[*]
[*] card_no = #{cardNo},
[*]
[*]
[*] bank_code = #{bankCode},
[*]
[*]
[*] order_id = #{orderId},
[*]
[*]
[*] effective_date = #{effectiveDate},
[*]
[*]
[*] redeem_type = #{redeemType},
[*]
[*]
[*] init_amount = #{initAmount},
[*]
[*]
[*] init_earning = #{initEarning},
[*]
[*]
[*] redeeming_amount = #{redeemingAmount},
[*]
[*]
[*] redeeming_earning = #{redeemingEarning},
[*]
[*]
[*] redeemed_amount = #{redeemedAmount},
[*]
[*]
[*] redeemed_earning = #{redeemedEarning},
[*]
[*]
[*] punish_amount = #{punishAmount},
[*]
[*]
[*] latest_redeem_time = #{latestRedeemTime},
[*]
[*]
[*] maturity_date = #{maturityDate},
[*]
[*]
[*] modify_time = #{modifyTime},
[*]
[*]
[*] remark = #{remark},
[*]
[*]
[*]
[*]
[*] id = #{id} limit 1
[*]
[*]
[*]
[*]
[*]
[*]
[*] select * from assets where id = #{id}
[*]
[*]
[*]
[*]
[*]
[*] select * from assets where id = #{id} for update
[*]
[*]
[*]
[*]
[*]
[*] SELECT
[*]
[*] FROM
[*] assets
[*]
[*] 1 = 1
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*]
[*] limit #{offset}, #{rows}
[*]
[*]
[*]
[*]
[*]
[*]
[*] SELECT count(1) FROM assets
[*]
[*] 1 = 1
[*]
[*]
[*]
[*]
[*]
[*]
[*]
页:
[1]