Wrapper和XML常用案例

1、xml和Wrapper混合
List getProductList(@Param(Constants.WRAPPER) Wrapper queryWrapper);
SELECT product.*,tenantProduct.price FROM dms_tenant_style AS tenantProduct ${ew.customSqlSegment}
2、if else




order by w.create_time desc


order by w.create_time asc


order by w.create_time desc




3、if

productStyle.width_max,
productStyle.width_min,


and uw.user_id = #{data.userId}

4、for循环
WHERE spu.agent_spu_id in

#{i}

5、增删改查

增

SELECT @style_number := 0,@glass_number := 0; INSERT INTO `dms_tenant_style` (`id`, `product_id`, `price`, `tenant_id`, `type`, `create_time`, `create_user`, `update_time`, `update_user`) SELECT (${styleId} + (@style_number := @style_number + 1)) AS id, `product_id`, price, #{tenantId}, type, NOW(), #{userId}, NOW(), #{userId} FROM `dms_tenant_style` AS t WHERE t.tenant_id=#{supplierId} AND NOT EXISTS(SELECT 1 FROM dms_tenant_style WHERE `product_id` = t.`product_id` AND tenant_id = #{tenantId}); #删： DELETE oms_order, oms_order_item, oms_order_material, oms_order_log, oms_plan, oms_plan_item, oms_plan_material_item, oms_measure_record, oms_measure_item, oms_install_record, oms_install_item FROM oms_order LEFT JOIN oms_order_item ON oms_order_item.order_id = oms_order.id LEFT JOIN oms_order_material ON oms_order_material.order_id = oms_order.id LEFT JOIN oms_order_log ON oms_order_log.order_id = oms_order.id LEFT JOIN oms_plan ON oms_plan.order_id = oms_order.id LEFT JOIN oms_plan_item ON oms_plan_item.plan_id = oms_plan.id LEFT JOIN oms_plan_material_item ON oms_plan_material_item.plan_item_id = oms_plan_item.id LEFT JOIN oms_measure_record ON oms_measure_record.order_id = oms_order.id LEFT JOIN oms_measure_item ON oms_measure_record.id = oms_measure_item.measure_id LEFT JOIN oms_install_record ON oms_install_record.order_id = oms_order.id LEFT JOIN oms_install_item ON oms_install_item.install_id = oms_install_record.id WHERE oms_order.id = #{id} #改： UPDATE pms_sku SET stock = #{map.库存} WHERE id = #{map.skuID} UPDATE pms_sku INNER JOIN pms_sku agent ON pms_sku.agent_sku_id = agent.id SET pms_sku.cost_price = FLOOR(agent.price * #{ratio} ), pms_sku.price = agent.price, pms_sku.suggest_price = FLOOR(agent.price * 0.8), pms_sku.retail_lower_price = FLOOR(agent.price * 0.75) WHERE pms_sku.tenant_id = #{tenantId} #查：

6、json字段和path查询
SELECT

dps.glass_info ->> '$.baseGlassPrice' AS baseGlassPrice,

dcc.*
FROM
dulles_custom_category dcc

LEFT JOIN dms_product_style dps ON dps.id = dcc.style_id

WHERE
INSTR( dcc.path, #{pid} ) > 0
ORDER BY
dcc.sort ASC,
dcc.id ASC
7、and 括号用法
List tenantList = getTenant().selectList(new LambdaQueryWrapper()
.eq(TmsTenant::getStatus, CommonStatusEnum.ENABLE.getCode())
.and(item->{
item.eq(TmsTenant::getId,tenantId)
.or()
.eq(TmsTenant::getTenantType, CommonConstant.TENANT_PLATFORM);
})
);
8、循环更新


update tpmpo_tooling_inventory


IN_WAREHOUSE =#{param.inWarehouseScrapNum},


OUT_WAREHOUSE =#{param.outWarehouseScrapNum},


SEALED_QUANTITY =#{param.sealedScrapNum},


SEALED_QUANTITY =#{param.sealedScrapNum},

LAST_UPDATE_DATE =now()

where id =#{param.id}