MyBatis常用场景①批量处理

最新推荐文章于 2021-09-23 10:33:02 发布

小鱼Lee

最新推荐文章于 2021-09-23 10:33:02 发布

阅读量272

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_35161554/article/details/81166902

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

mybatis中${}与#{}区别

#{} 解析的是占位符，可以防止SQL注入。打印出来的语句是：select * from table where id=?
${}不能防止SQL注入，打印出来的语句是实际的参数select * from table where id = 123
#{}解析传传递的参数带单引号，${}解析传递的参数值不带单引号。
参数是int型的时候，必须使用$。

mybatis日志配置

1.查询select：返回List<Object>对象

field_key为两个表关联的键

List<ProductPersonInfo> getPersonInfoByProduct(long productId, long clientId);

<resultMap type="ProductPersonInfo" id="resultMap">
<id property="id" column="id"/>
<result property="fieldKey" column="field_key"/>
<collection property="distList" column="fieldKey" ofType="LdcarProductDist" javaType="ArrayList">
<id property="id" column="distId"/>
<result property="datacode" column="dataCode"/>
<result property="datavalue" column="dataValue"/>
</collection>
</resultMap>
<select id="getPersonInfoByProduct" resultMap="resultMap" parameterType="java.lang.Long" >
SELECT
t1.id id,
t1.field_key field_key,
t2.field_key fieldKey,
t2.id distId,
t2.datacode dataCode,
t2.datavalue dataValue
FROM
ldcar_product_basicinfo t1
LEFT JOIN ldcar_product_dist t2 ON t1.field_key = t2.field_key
</select>

ProductPersonInfo.java

private Integer id;
private String fieldKey;
private List<LdcarProductDist> distList;

LdcarProductDist.java

private Long id;
private String fieldKey;
private String datacode;
private String datavalue;

2.新增insert

int saveProductPersonInfo(@Param("listParam") List<ProductPersonInfo> personInfoList);

<insert id="saveProductPersonInfo" parameterType="java.util.List">
INSERT INTO ldcar_loan_basic_info
(order_id, client_id, field_key, field_value, status, create_date, createby)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.orderId,jdbcType=BIGINT},#{item.clientId,jdbcType=BIGINT},
#{item.fieldKey,jdbcType=VARCHAR},
#{item.fieldValue,jdbcType=VARCHAR},
#{'0',jdbcType=INTEGER},
#{now(),jdbcType=TIMESTAMP},
#{item.createBy,jdbcType=BIGINT}
)
</foreach>
</insert>

3.更新update 参考

<update id="updateBatch"  parameterType="java.util.List">
      <foreach collection="list" item="item" index="index" open="" close="" separator=";">
          update ldcar_loan_basic_info
           <set>
                    field_value=${item.fieldValue}
            </set>
            where order_id = ${item.orderId}
                       and field_key = ${item.fieldKey}
      </foreach>
</update>

4.删除delete

<delete id="deleteByOrderId">
  DELETE FROM ldcar_loan_basic_info
  WHERE order_id
  IN
     <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
            #{item}
      </foreach>
 </delete>