SQL批量操作大全

java后生

已于 2022-03-07 15:42:43 修改

阅读量1.7k

点赞数

分类专栏：每日学习一个小技术文章标签： sql linq 数据库

于 2022-02-18 17:59:04 首次发布

本文链接：https://blog.csdn.net/weixin_44840242/article/details/123008701

版权

每日学习一个小技术专栏收录该内容

32 篇文章 0 订阅

订阅专栏

这篇博客展示了如何使用SQL进行复杂的查询和批量操作。第一个例子是一个嵌套查询，用于从订单表中检索包含商品详细信息的订单列表。第二个例子是批量插入，通过foreach标签将多个对象插入到数据库中。最后，第三个例子演示了如何批量更新设备信息，根据不同的条件更新设备的坐标和地址。

摘要由CSDN通过智能技术生成

1.list嵌套list查询SQL
  
    <resultMap id="queryOrderGoodsOD" type="com.mengniu.vo.OrderVO">
        <id column="orderId" property="orderId" jdbcType="INTEGER"/>
        <result column="orderNumber" property="orderNumber" jdbcType="VARCHAR"/>
        <result column="orderDate" property="orderDate" jdbcType="TIMESTAMP"/>
        <result column="orderStatus" property="orderStatus" jdbcType="VARCHAR"/>
        <result column="totalQuantity" property="totalQuantity" jdbcType="INTEGER"/>
        <result column="totalActualPay" property="totalActualPay" jdbcType="VARCHAR"/>
        <collection property="goods" javaType="java.util.ArrayList" select="com.mengniu.mapper.ConsumerMapper.queryGoodList"
                     ofType = "com.mengniu.vo.GoodsVO" column="{orderId=orderId,totalActualPay=totalActualPay}">
            <result column="name" property="name" jdbcType="VARCHAR"/>
            <result column="size" property="size" jdbcType="INTEGER"/>
            <result column="url" property="url" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>

    <select id="queryGoodList" resultType="com.mengniu.vo.GoodsVO">
        SELECT
            top.name name,
            top.num size,
            top.mainUrl url
        FROM
            t_order_product top
        WHERE
            top.orderId = #{orderId}
   </select>


    <select id="queryOrderPageList" resultMap="queryOrderGoodsOD">
        SELECT
            too.id orderId,
            too.orderNumber, -- 订单编号
            too.createdAt orderDate, -- 订单时间
            too.status orderStatus, -- 订单状态
            too.totalQuantity totalQuantity, -- 购物订单总数
            too.payAmount totalActualpay -- 购物订单实际支付金额
        FROM
            t_ordinary_order too
        <where>
            too.cusId = #{customerId}
            <if test="dto.type != null">
                and too.status = #{dto.type}
            </if>
        </where>
        <if test="dto.start != null and dto.pageSize != null">
            LIMIT #{dto.start},#{dto.pageSize}
        </if>
    </select>

2.批量新增SQL
   <insert>
       INSERT INTO
            dpo_personnel_project_summary_report ( id, staff_id, staff_name, staff_depart_name, pteaioi_type, staff_rank, user_status, user_skill, base_address, work_address, create_time )
        <foreach collection="list" item="obj" open="VALUES" close=";" separator=",">
            ( #{obj.id}, #{obj.staffId}, #{obj.staffName}, #{obj.staffDepartName}, #{obj.pteaioiType}, #{obj.staffRank}, #{obj.userStatus}, #{obj.userSkill}, #{obj.baseAddress}, #{obj.workAddress}, #{obj.createTime} )
        </foreach>
</insert>

3.批量编辑SQL
入参：List<QueryDeviceVO> list
<update id="batchUpdateOrder" parameterType="java.util.List" >
        update t_device
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="device_longitude =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.deviceLongitude!=null">
                        when device_code=#{i.deviceCode} then #{i.deviceLongitude}
                    </if>
                </foreach>
            </trim>
            <trim prefix="device_latitude =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.deviceLatitude!=null">
                        when device_code=#{i.deviceCode} then #{i.deviceLatitude}
                    </if>
                </foreach>
            </trim>
            <trim prefix="device_address =case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.deviceAddress!=null">
                        when device_code=#{i.deviceCode} then #{i.deviceAddress}
                    </if>
                </foreach>
            </trim>
        </trim>
        where
        <foreach collection="list" separator="or" item="i" index="index" >
            device_code=#{i.deviceCode}
        </foreach>
</update>