mybatis 常用sql写法

最新推荐文章于 2024-07-29 14:21:29 发布

qq_31594647

最新推荐文章于 2024-07-29 14:21:29 发布

阅读量970

点赞数

文章标签： mybatis

本文链接：https://blog.csdn.net/qq_31594647/article/details/103368473

版权

1.mybatis 循环string(用逗号隔开的字符串) ，两种写法

List<String> idlist2 = getUserIds(userIdlist);
String userIds = String.join(",",idlist);
map.put("userIds",userIds);

List<CoreChannelVo> mList = coreProductCorrelationMapper.selectCoreChannelVoList(map);

<select id="selectCoreChannelVoList" resultType="com.ssish.saas.constant.db.vo.CoreChannelVo" parameterType="java.util.HashMap" >
   select
   t1.id id,
   t1.channel_name channelName,
   t1.channel_type channelType,
   t1.nymanager nymanager  
   from core_channel t1 where 1 = 1
   <if test="userType=='1'.toString()">
      AND t1.`ny_ae` in
      <foreach item="item" index="index" collection="userIds.split(',')"  open="(" separator="," close=")">
         '${item}'
      </foreach>
   </if>   
   order by t1.id
</select>

String productIds = "tpi001,tpi002,tpi003";

<insert id="insertByChannelId"
parameterType="com.ssish.saas.constant.db.model.CoreChannelProductManager">
insert into core_channel_product_manager (
   id, channel_id, product_id,
   product_name, payment, sell_price, insurance_company_id,
   agency_id, start_date, end_date, CREATOR, create_date, MODIFIER, modify_date,batch_id)
select
   null,
   #{channelId},
   t1.id,
   t1.product_name,
   IFNULL(t1.payment, '1'),
   IFNULL(#{sellPrice}, t1.price),
   t1.default_company,
   t1.default_agency,
   #{startDate},
   #{endDate},
   #{creator},
   NOW(),
   #{modifier},
   NOW(),
   #{batchId}
   from core_product t1 where t1.id in (${productIds})
   </insert>

2.mybatis 批量插入循环list

<!--批量插入临时表-->
<insert id="insertChannelProducttempBatch" parameterType="java.util.List">
   insert into core_channel_product_manager_temp
   (
   channel_id,
   product_id,
   sell_price,
   start_date,
   end_date,
   CREATOR,
   MODIFIER,
   batch_id
   )
   values
   <foreach collection ="list" item="item" index= "index" separator =",">
      (
      #{item.channelId},
      #{item.productId},
      #{item.sellPrice},
      #{item.startDate},
      #{item.endDate},
      #{item.creator},
      #{item.modifier},
      #{item.batchId}
      )
   </foreach >
</insert>

3.一对多查询

<select id="selectResourceByRoleId"  resultMap="ResourceVoMap">
      SELECT
       cr.id id,
       cr.NAME name,
       #{roleId} roleId
      FROM
        core_resource cr,
        core_resource_role crr
      WHERE cr.id = crr.resource_id
        AND cr.type = '1'
        AND cr.parent_id = '0'
        AND crr.role_id = #{roleId}
      ORDER BY
          cr.priority ASC
</select>

  <resultMap type="com.ssish.saas.constant.db.vo.ResourceTreeVo" id="ResourceVoMap">
      <id property="id" column="id"/>
      <result property="name" column="name"/>
      <association property="children" column="{id=id,roleId=roleId}" select="selectChildResoueVo"/>
  </resultMap>

若只有两级，则，这里面的返回类型写成 resultType="com.ssish.saas.constant.db.vo.ResourceTreeVo",
若是大于两级，则多次循序，故返回类型写  resultMap="ResourceVoMap"
  <select id="selectChildResoueVo" parameterType="java.util.Map" resultMap="ResourceVoMap">
      SELECT
        cr.id id,
        cr.name name,
        #{roleId} roleId
      FROM
        core_resource cr,
        core_resource_role crr
      WHERE cr.parent_id = #{id} AND
        cr.id = crr.resource_id
        AND cr.type = '1'
        AND crr.role_id  = #{roleId}
      ORDER BY
          cr.priority ASC
  </select>