mysql: 动态sql样例
批量更新sql注意问题
- 返回实际受影响的行数添加 &useAffectedRows=true 转载一篇文章: https://www.jb51.net/article/123076.htm
- jdbc 连接添加 &allowMultiQueries=true
插入百万级数据, 要在sql 链接上加上参数
spring.datasource.url=jdbc:mysql://localhost:3306/db_zhilian_coupon?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false
spring.datasource.username=隐藏
spring.datasource.password=隐藏
下面是插入百万级数据动态sql. service层
/**
* @Desc 创建一次性码
* @Date 2019/1/2 10:41
* @Param [record, userId]
*/
@Async
public void create(int couponId, int number, int userId) throws Exception{
ArrayList<CouponCode> list = new ArrayList<>();
if (number <= 100){
couponCodeMapper.batchInsertSelective(list);
return;
}
for (int i=0; i<number; i++){
CouponCode record = new CouponCode();
setPropertyCreate(record, userId);
record.setCode(RandomStringUtils.randomAlphanumeric(8));
record.setCouponId(couponId);
list.add(record);
if (list.size() >200){
couponCodeMapper.batchInsertSelective(list);
list.clear();
}
}
if (list.size() >0){
couponCodeMapper.batchInsertSelective(list);
list.clear();
}
}
下面是mapper 接口
void batchInsertSelective(@Param("list") List<CouponCode> list);
下面是sql
<insert id="batchInsertSelective" parameterType="java.util.List">
<foreach collection="list" separator=";" item="item">
insert into cms_coupon_code
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="item.id != null">
id,
</if>
<if test="item.couponId != null">
coupon_id,
</if>
<if test="item.code != null">
code,
</if>
<if test="item.createdTime != null">
created_time,
</if>
<if test="item.modifiedTime != null">
modified_time,
</if>
<if test="item.createdBy != null">
created_by,
</if>
<if test="item.modifiedBy != null">
modified_by,
</if>
<if test="item.remark != null">
remark,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="item.id != null">
#{item.id,jdbcType=INTEGER},
</if>
<if test="item.couponId != null">
#{item.couponId,jdbcType=INTEGER},
</if>
<if test="item.code != null">
#{item.code,jdbcType=VARCHAR},
</if>
<if test="item.createdTime != null">
#{item.createdTime,jdbcType=TIMESTAMP},
</if>
<if test="item.modifiedTime != null">
#{item.modifiedTime,jdbcType=TIMESTAMP},
</if>
<if test="item.createdBy != null">
#{item.createdBy,jdbcType=INTEGER},
</if>
<if test="item.modifiedBy != null">
#{item.modifiedBy,jdbcType=INTEGER},
</if>
<if test="item.remark != null">
#{item.remark,jdbcType=VARCHAR},
</if>
</trim>
</foreach>
</insert>
下面是, 动态sql,案例
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.chongdong.data.mapper.DmsProdSkuRelateMapper" >
<resultMap id="BaseResultMap" type="com.chongdong.data.entity.DmsProdSkuRelate" >
<id column="ID" property="id" jdbcType="BIGINT" />
<result column="SKU_ID" property="skuId" jdbcType="BIGINT" />
<result column="SKU_CODE" property="skuCode" jdbcType="VARCHAR" />
<result column="PARENT_ID" property="parentId" jdbcType="BIGINT" />
<result column="AUTHORIZE_ID" property="authorizeId" jdbcType="BIGINT" />
<result column="PRV_ID" property="prvId" jdbcType="BIGINT" />
<result column="TYPE" property="type" jdbcType="VARCHAR" />
<result column="DISCOUNT" property="discount" jdbcType="VARCHAR" />
<result column="START_TIME" property="startTime" jdbcType="TIMESTAMP" />
<result column="END_TIME" property="endTime" jdbcType="TIMESTAMP" />
<result column="RELATIONSHIP" property="relationship" jdbcType="VARCHAR" />
<result column="STATUS" property="status" jdbcType="TINYINT" />
<result column="CREATE_TIME" property="createTime" jdbcType="TIMESTAMP" />
<result column="CREATE_BY" property="createBy" jdbcType="VARCHAR" />
<result column="UPDATE_TIME" property="updateTime" jdbcType="TIMESTAMP" />
<result column="UPDATE_BY" property="updateBy" jdbcType="VARCHAR" />
<result column="UPLOWSTATUS" property="upLowStatus" jdbcType="TINYINT" />
<result column="SALE_PRICE" property="salePrice" jdbcType="DOUBLE" />
<!--自定义返回字段 依次表示sku名称，商品名称，公开价，货号，品牌名称-->
<result column="SKU_NAME" property="skuName" jdbcType="VARCHAR" />
<result column="PROD_NAME" property="prodName" jdbcType="VARCHAR" />
<result column="PROD_ID" property="prodId" jdbcType="BIGINT" />
<result column="OPEN_PRICE" property="openPrice" jdbcType="DOUBLE" />
<result column="SERIAL_NUMBER" property="serialNumber" jdbcType="VARCHAR" />
<result column="BRAND_NAME" property="brandName" jdbcType="VARCHAR" />
<result column="CATE_ID" property="cateId" jdbcType="BIGINT" />
<result column="APPROVAL_STATUS" property="approvalStatus" jdbcType="TINYINT" />
<!--商品线名称-->
<result column="PRD_LINE_NAME" property="prdLineName" jdbcType="VARCHAR" />
<result column="AUTHORIZE_NAME" property="authorizeName" jdbcType="VARCHAR" />
<result column="PRV_NAME" property="prvName" jdbcType="VARCHAR" />
</resultMap>
<sql id="Base_Column_List" >
ID, SKU_ID, SKU_CODE, PARENT_ID, AUTHORIZE_ID, PRV_ID, TYPE, DISCOUNT, START_TIME,
END_TIME, RELATIONSHIP, STATUS, CREATE_TIME, CREATE_BY, UPDATE_TIME, UPDATE_BY,UPLOWSTATUS,SALE_PRICE
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
select
<include refid="Base_Column_List" />
from dms_prod_sku_relate
where ID = #{id,jdbcType=BIGINT}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
delete from dms_prod_sku_relate
where ID = #{id,jdbcType=BIGINT}
</delete>
<insert id="insert" parameterType="com.chongdong.data.entity.DmsProdSkuRelate" >
insert into dms_prod_sku_relate (ID, SKU_ID, SKU_CODE,
PARENT_ID, AUTHORIZE_ID, PRV_ID,
TYPE, DISCOUNT, START_TIME,
END_TIME, RELATIONSHIP, STATUS,
CREATE_TIME, CREATE_BY, UPDATE_TIME,
UPDATE_BY,UPLOWSTATUS,SALE_PRICE)
values (#{id,jdbcType=BIGINT}, #{skuId,jdbcType=BIGINT}, #{skuCode,jdbcType=VARCHAR},
#{parentId,jdbcType=BIGINT}, #{authorizeId,jdbcType=BIGINT}, #{prvId,jdbcType=BIGINT},
#{type,jdbcType=VARCHAR}, #{discount,jdbcType=VARCHAR}, #{startTime,jdbcType=TIMESTAMP},
#{endTime,jdbcType=TIMESTAMP}, #{relationship,jdbcType=VARCHAR}, #{status,jdbcType=TINYINT},
#{createTime,jdbcType=TIMESTAMP}, #{createBy,jdbcType=VARCHAR}, #{updateTime,jdbcType=TIMESTAMP},
#{updateBy,jdbcType=VARCHAR},#{upLowStatus,jdbcType=TINYINT},#{salePrice,jdbcType=DOUBLE})
</insert>
<insert id="insertSelective" parameterType="com.chongdong.data.entity.DmsProdSkuRelate" >
insert into dms_prod_sku_relate
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
ID,
</if>
<if test="skuId != null" >
SKU_ID,
</if>
<if test="skuCode != null" >
SKU_CODE,
</if>
<if test="parentId != null" >
PARENT_ID,
</if>
<if test="authorizeId != null" >
AUTHORIZE_ID,
</if>
<if test="prvId != null" >
PRV_ID,
</if>
<if test="type != null" >
TYPE,
</if>
<if test="discount != null" >
DISCOUNT,
</if>
<if test="startTime != null" >
START_TIME,
</if>
<if test="endTime != null" >
END_TIME,
</if>
<if test="relationship != null" >
RELATIONSHIP,
</if>
<if test="status != null" >
STATUS,
</if>
<if test="createTime != null" >
CREATE_TIME,
</if>
<if test="createBy != null" >
CREATE_BY,
</if>
<if test="updateTime != null" >
UPDATE_TIME,
</if>
<if test="updateBy != null" >
UPDATE_BY,
</if>
<if test="upLowStatus != null" >
UPLOWSTATUS,
</if>
<if test="salePrice != null" >
SALE_PRICE,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=BIGINT},
</if>
<if test="skuId != null" >
#{skuId,jdbcType=BIGINT},
</if>
<if test="skuCode != null" >
#{skuCode,jdbcType=VARCHAR},
</if>
<if test="parentId != null" >
#{parentId,jdbcType=BIGINT},
</if>
<if test="authorizeId != null" >
#{authorizeId,jdbcType=BIGINT},
</if>
<if test="prvId != null" >
#{prvId,jdbcType=BIGINT},
</if>
<if test="type != null" >
#{type,jdbcType=VARCHAR},
</if>
<if test="discount != null" >
#{discount,jdbcType=VARCHAR},
</if>
<if test="startTime != null" >
#{startTime,jdbcType=TIMESTAMP},
</if>
<if test="endTime != null" >
#{endTime,jdbcType=TIMESTAMP},
</if>
<if test="relationship != null" >
#{relationship,jdbcType=VARCHAR},
</if>
<if test="status != null" >
#{status,jdbcType=TINYINT},
</if>
<if test="createTime != null" >
#{createTime,jdbcType=TIMESTAMP},
</if>
<if test="createBy != null" >
#{createBy,jdbcType=VARCHAR},
</if>
<if test="updateTime != null" >
#{updateTime,jdbcType=TIMESTAMP},
</if>
<if test="updateBy != null" >
#{updateBy,jdbcType=VARCHAR},
</if>
<if test="upLowStatus != null" >
#{upLowStatus,jdbcType=TINYINT},
</if>
<if test="salePrice != null" >
#{salePrice,jdbcType=DOUBLE},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.chongdong.data.entity.DmsProdSkuRelate" >
update dms_prod_sku_relate
<set >
<if test="skuId != null" >
SKU_ID = #{skuId,jdbcType=BIGINT},
</if>
<if test="skuCode != null" >
SKU_CODE = #{skuCode,jdbcType=VARCHAR},
</if>
<if test="parentId != null" >
PARENT_ID = #{parentId,jdbcType=BIGINT},
</if>
<if test="authorizeId != null" >
AUTHORIZE_ID = #{authorizeId,jdbcType=BIGINT},
</if>
<if test="prvId != null" >
PRV_ID = #{prvId,jdbcType=BIGINT},
</if>
<if test="type != null" >
TYPE = #{type,jdbcType=VARCHAR},
</if>
<if test="discount != null" >
DISCOUNT = #{discount,jdbcType=VARCHAR},
</if>
<if test="startTime != null" >
START_TIME = #{startTime,jdbcType=TIMESTAMP},
</if>
<if test="endTime != null" >
END_TIME = #{endTime,jdbcType=TIMESTAMP},
</if>
<if test="relationship != null" >
RELATIONSHIP = #{relationship,jdbcType=VARCHAR},
</if>
<if test="status != null" >
STATUS = #{status,jdbcType=TINYINT},
</if>
<if test="createTime != null" >
CREATE_TIME = #{createTime,jdbcType=TIMESTAMP},
</if>
<if test="createBy != null" >
CREATE_BY = #{createBy,jdbcType=VARCHAR},
</if>
<if test="updateTime != null" >
UPDATE_TIME = #{updateTime,jdbcType=TIMESTAMP},
</if>
<if test="updateBy != null" >
UPDATE_BY = #{updateBy,jdbcType=VARCHAR},
</if>
<if test="upLowStatus != null" >
UPLOWSTATUS = #{upLowStatus,jdbcType=TINYINT},
</if>
<if test="salePrice != null" >
SALE_PRICE = #{salePrice,jdbcType=DOUBLE},
</if>
</set>
where ID = #{id,jdbcType=BIGINT}
</update>
<update id="updateByPrimaryKey" parameterType="com.chongdong.data.entity.DmsProdSkuRelate" >
update dms_prod_sku_relate
set SKU_ID = #{skuId,jdbcType=BIGINT},
SKU_CODE = #{skuCode,jdbcType=VARCHAR},
PARENT_ID = #{parentId,jdbcType=BIGINT},
AUTHORIZE_ID = #{authorizeId,jdbcType=BIGINT},
PRV_ID = #{prvId,jdbcType=BIGINT},
TYPE = #{type,jdbcType=VARCHAR},
DISCOUNT = #{discount,jdbcType=VARCHAR},
START_TIME = #{startTime,jdbcType=TIMESTAMP},
END_TIME = #{endTime,jdbcType=TIMESTAMP},
RELATIONSHIP = #{relationship,jdbcType=VARCHAR},
STATUS = #{status,jdbcType=TINYINT},
CREATE_TIME = #{createTime,jdbcType=TIMESTAMP},
CREATE_BY = #{createBy,jdbcType=VARCHAR},
UPDATE_TIME = #{updateTime,jdbcType=TIMESTAMP},
UPDATE_BY = #{updateBy,jdbcType=VARCHAR},
UPLOWSTATUS = #{upLowStatus,jdbcType=TINYINT},
SALE_PRICE = #{salePrice,jdbcType=DOUBLE},
where ID = #{id,jdbcType=BIGINT}
</update>
<select id="selectDetailByPrvId" parameterType="java.util.Map" resultMap="BaseResultMap">
select a.*,b.`NAME` as SKU_NAME,b.SERIAL_NUMBER,b.OPEN_PRICE,
c.`NAME` as PROD_NAME,c.ID as PROD_ID,
d3.ID as CATE_ID,g.`NAME` as BRAND_NAME,p.`NAME` AS PRV_NAME
from dms_prod_sku_relate a
LEFT JOIN dms_prod_sku_base b ON a.SKU_ID=b.ID
LEFT JOIN sys_provider p ON a.PRV_ID=p.ID
LEFT JOIN dms_prod_base c ON b.PROD_ID=c.ID
LEFT JOIN prd_category d3 ON c.CATEGORY_ID=d3.ID
LEFT JOIN prd_category d2 ON d3.PARENT_ID=d2.ID
LEFT JOIN prd_category d1 ON d2.PARENT_ID=d1.ID
LEFT JOIN prd_brand g ON c.BRAND_ID=g.ID
where 1=1
<if test="brandName != null and brandName != ''">
AND g.`NAME` LIKE '%${brandName}%'
</if>
<if test="searchText != null and searchText != ''">
AND (c.`NAME` LIKE CONCAT('%',#{searchText,jdbcType=VARCHAR},'%') OR b.SERIAL_NUMBER LIKE CONCAT('%',#{searchText,jdbcType=VARCHAR},'%')
OR b.CAS_NO LIKE CONCAT('%',#{searchText,jdbcType=VARCHAR},'%'))
</if>
<if test="category3 != null and category3 != 0">
AND c.CATEGORY_ID = #{category3,jdbcType=BIGINT}
</if>
<if test="category2 != null and category2 != 0">
AND d2.id = #{category2,jdbcType=BIGINT}
</if>
<if test="category1 != null and category1 != 0">
AND d1.id = #{category1,jdbcType=BIGINT}
</if>
<if test="beginTime != null and beginTime != ''">
<!-- <![CDATA[ AND a.UPDATE_TIME >= #{beginTime} ]]>-->
AND a.UPDATE_TIME <![CDATA[>=]]> #{beginTime}
</if>
<if test="endTime != null and endTime != ''">
<!-- <![CDATA[ AND a.UPDATE_TIME >= #{endTime} ]]>-->
AND a.UPDATE_TIME <![CDATA[<=]]>#{endTime}
</if>
AND a.AUTHORIZE_ID = #{prvId,jdbcType=BIGINT}
</select>
<!---->
<select id="selectSkuByPrvId" parameterType="java.util.Map" resultMap="BaseResultMap">
select a.*,b.`NAME` as SKU_NAME,b.SERIAL_NUMBER,b.OPEN_PRICE,b.APPROVAL_STATUS,
c.`NAME` as PROD_NAME,c.ID as PROD_ID,
d3.ID as CATE_ID,g.`NAME` as BRAND_NAME,p.`NAME` AS PRV_NAME
from dms_prod_sku_relate a
LEFT JOIN dms_prod_sku_base b ON a.SKU_ID=b.ID
LEFT JOIN dms_prod_base c ON b.PROD_ID=c.ID
LEFT JOIN sys_provider p ON a.PRV_ID=p.ID
LEFT JOIN prd_category d3 ON c.CATEGORY_ID=d3.ID
LEFT JOIN prd_category d2 ON d3.PARENT_ID=d2.ID
LEFT JOIN prd_category d1 ON d2.PARENT_ID=d1.ID
LEFT JOIN prd_brand g ON c.BRAND_ID=g.ID
LEFT JOIN prd_line_relate e ON a.ID=e.PROD_ID
LEFT JOIN prd_line f ON e.LINE_ID=f.ID
WHERE 1=1 /*AND e.TYPE = 2*/
<if test="brandId != null and brandId != ''">
AND g.ID=#{brandId,jdbcType=BIGINT}
</if>
<if test="upLowStatus != null ">
AND a.UPLOWSTATUS=#{upLowStatus,jdbcType=TINYINT}
</if>
<if test="prdLineId != null and prdLineId !=0">
AND f.ID=#{prdLineId,jdbcType=BIGINT}
</if>
<if test="searchText != null and searchText != ''">
AND (b.`NAME` LIKE CONCAT('%',#{searchText,jdbcType=VARCHAR},'%') OR b.SERIAL_NUMBER LIKE CONCAT('%',#{searchText,jdbcType=VARCHAR},'%')
OR b.CAS_NO LIKE CONCAT('%',#{searchText,jdbcType=VARCHAR},'%'))
</if>
<if test="category3 != null and category3 != 0">
AND c.CATEGORY_ID = #{category3,jdbcType=BIGINT}
</if>
<if test="category2 != null and category2 != 0">
AND d2.id = #{category2,jdbcType=BIGINT}
</if>
<if test="category1 != null and category1 != 0">
AND d1.id = #{category1,jdbcType=BIGINT}
</if>
<if test="beginTime != null and beginTime != ''">
<!-- <![CDATA[ AND a.UPDATE_TIME >= #{beginTime} ]]>-->
AND a.UPDATE_TIME <![CDATA[>=]]> #{beginTime}
</if>
<if test="endTime != null and endTime != ''">
<!-- <![CDATA[ AND a.UPDATE_TIME >= #{endTime} ]]>-->
AND a.UPDATE_TIME <![CDATA[<=]]>#{endTime}
</if>
<if test="skuCodeList != null">
AND a.SKU_CODE NOT IN
<foreach collection="skuCodeList" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<!-- sku状态，审核通过-->
AND b.APPROVAL_STATUS=1
AND a.UPLOWSTATUS<![CDATA[!= ]]>-1
AND a.PRV_ID = #{prvId,jdbcType=BIGINT}
AND a.`STATUS` IN (3 , 0 ,-1)
</select>
<select id="selectMySkuByParams" parameterType="java.util.Map" resultMap="BaseResultMap">
select a.*,b.`NAME` as SKU_NAME,b.SERIAL_NUMBER,b.OPEN_PRICE,b.APPROVAL_STATUS,
c.`NAME` as PROD_NAME,c.ID as PROD_ID,
d3.ID as CATE_ID,g.`NAME` as BRAND_NAME
from dms_prod_sku_relate a
LEFT JOIN dms_prod_sku_base b ON a.SKU_ID=b.ID
LEFT JOIN dms_prod_base c ON b.PROD_ID=c.ID
LEFT JOIN prd_category d3 ON c.CATEGORY_ID=d3.ID
LEFT JOIN prd_category d2 ON d3.PARENT_ID=d2.ID
LEFT JOIN prd_category d1 ON d2.PARENT_ID=d1.ID
LEFT JOIN prd_brand g ON c.BRAND_ID=g.ID
LEFT JOIN prd_line_relate e ON a.ID=e.PROD_ID
LEFT JOIN prd_line f ON e.LINE_ID=f.ID
WHERE 1=1 /*AND e.TYPE = 2 */
<if test="brandId != null and brandId != ''">
AND g.ID=#{brandId,jdbcType=BIGINT}
</if>
<if test="status != null and status != ''">
<choose>
<when test="status ==6">
AND ( a.START_TIME <![CDATA[<=]]> #{nowTime})
AND ( a.END_TIME <![CDATA[>=]]> #{nowTime})
AND a.`STATUS`=3
</when>
<when test="status ==5">
AND ( a.END_TIME <![CDATA[<=]]> #{nowTime})
AND a.`STATUS`=3
</when>
<when test="status ==7">
AND ( a.START_TIME <![CDATA[>=]]> #{nowTime})
AND a.`STATUS`=3
</when>
<otherwise>
AND a.`STATUS`=#{status,jdbcType=TINYINT}
</otherwise>
</choose>
</if>
<if test="searchText != null and searchText != ''">
AND (c.`NAME` LIKE CONCAT('%',#{searchText,jdbcType=VARCHAR},'%') OR b.SERIAL_NUMBER LIKE CONCAT('%',#{searchText,jdbcType=VARCHAR},'%')
OR b.CAS_NO LIKE CONCAT('%',#{searchText,jdbcType=VARCHAR},'%'))
</if>
<if test="category3 != null and category3 != 0">
AND c.CATEGORY_ID = #{category3,jdbcType=BIGINT}
</if>
<if test="category2 != null and category2 != 0">
AND d2.id = #{category2,jdbcType=BIGINT}
</if>
<if test="category1 != null and category1 != 0">
AND d1.id = #{category1,jdbcType=BIGINT}
</if>
<if test="beginTime != null and beginTime != ''">
<!-- <![CDATA[ AND a.UPDATE_TIME >= #{beginTime} ]]>-->
AND a.UPDATE_TIME <![CDATA[>=]]> #{beginTime}
</if>
<if test="endTime != null and endTime != ''">
<!-- <![CDATA[ AND a.UPDATE_TIME >= #{endTime} ]]>-->
AND a.UPDATE_TIME <![CDATA[<=]]>#{endTime}
</if>
<if test="prvId != null and prvId != ''">
AND a.PRV_ID=#{prvId,jdbcType=BIGINT}
</if>
<if test="str != null and str != ''">
AND a.RELATIONSHIP LIKE #{str}
</if>
<if test="authorizeId != null and authorizeId != ''">
AND a.AUTHORIZE_ID = #{authorizeId,jdbcType=BIGINT}
</if>
AND a.STATUS <![CDATA[<>]]> -1
AND a.UPLOWSTATUS<![CDATA[<>]]>-1
/*AND b.STATUS <![CDATA[<>]]> -1*/
AND c.`STATUS`<![CDATA[<>]]>-1
</select>
<select id="selectSkuByParams" parameterType="java.util.Map" resultMap="BaseResultMap">
select a.*,b.`NAME` as SKU_NAME,b.SERIAL_NUMBER,b.OPEN_PRICE,b.APPROVAL_STATUS,
c.`NAME` as PROD_NAME,c.ID as PROD_ID,d3.ID as CATE_ID,g.`NAME` as BRAND_NAME,f.`NAME` as PRD_LINE_NAME
from dms_prod_sku_relate a
LEFT JOIN dms_prod_sku_base b ON a.SKU_ID=b.ID
LEFT JOIN dms_prod_base c ON b.PROD_ID=c.ID
LEFT JOIN prd_category d3 ON c.CATEGORY_ID=d3.ID
LEFT JOIN prd_category d2 ON d3.PARENT_ID=d2.ID
LEFT JOIN prd_category d1 ON d2.PARENT_ID=d1.ID
LEFT JOIN prd_brand g ON c.BRAND_ID=g.ID
LEFT JOIN prd_line_relate e ON a.PARENT_ID=e.PROD_ID
LEFT JOIN prd_line f ON e.LINE_ID=f.ID
where 1=1
<if test="searchText != null and searchText != ''">
AND (c.`NAME` LIKE CONCAT('%',#{searchText,jdbcType=VARCHAR},'%') OR b.SERIAL_NUMBER LIKE CONCAT('%',#{searchText,jdbcType=VARCHAR},'%')
OR b.CAS_NO LIKE CONCAT('%',#{searchText,jdbcType=VARCHAR},'%'))
</if>
<if test="category3 != null and category3 != 0">
AND c.CATEGORY_ID = #{category3,jdbcType=BIGINT}
</if>
<if test="category2 != null and category2 != 0">
AND d2.id = #{category2,jdbcType=BIGINT}
</if>
<if test="category1 != null and category1 != 0">
AND d1.id = #{category1,jdbcType=BIGINT}
</if>
<if test="brandId != null and brandId != 0">
AND g.id = #{brandId,jdbcType=BIGINT}
</if>
<if test="prdLineId != null and prdLineId != 0">
AND f.id = #{prdLineId,jdbcType=BIGINT}
</if>
<if test="approvalStatus != null and approvalStatus != 0">
AND b.APPROVAL_STATUS = #{approvalStatus,jdbcType=TINYINT}
</if>
<if test="upLowStatus != null ">
AND a.UPLOWSTATUS=#{upLowStatus,jdbcType=TINYINT}
</if>
<if test="beginTime != null and beginTime != ''">
<!-- <![CDATA[ AND a.UPDATE_TIME >= #{beginTime} ]]>-->
AND a.UPDATE_TIME <![CDATA[>=]]> #{beginTime}
</if>
<if test="endTime != null and endTime != ''">
<!-- <![CDATA[ AND a.UPDATE_TIME >= #{endTime} ]]>-->
AND a.UPDATE_TIME <![CDATA[<=]]>#{endTime}
</if>
<if test="skuCodeList != null">
AND a.SKU_CODE NOT IN
<foreach collection="skuCodeList" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="skuList != null">
AND a.PARENT_ID IN
<foreach collection="skuList" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
AND a.PRV_ID =#{prvId,jdbcType=BIGINT}
AND a.AUTHORIZE_ID=#{authorizeId,jdbcType=BIGINT}
AND a.`STATUS` = 1 /*AND e.TYPE = 2 */
/*AND (a.`STATUS` = 1 OR a.`STATUS`=-1 )*/
</select>
<select id="selectConfirmSku" parameterType="java.util.Map" resultMap="BaseResultMap">
select a.*,b.`NAME` as SKU_NAME,b.SERIAL_NUMBER,b.OPEN_PRICE,b.APPROVAL_STATUS,
c.`NAME` as PROD_NAME,c.ID as PROD_ID,
d3.ID as CATE_ID,g.`NAME` as BRAND_NAME,h.`NAME` as AUTHORIZE_NAME
from dms_prod_sku_relate a
LEFT JOIN dms_prod_sku_base b ON a.SKU_ID=b.ID
LEFT JOIN dms_prod_base c ON b.PROD_ID=c.ID
LEFT JOIN prd_category d3 ON c.CATEGORY_ID=d3.ID
LEFT JOIN prd_category d2 ON d3.PARENT_ID=d2.ID
LEFT JOIN prd_category d1 ON d2.PARENT_ID=d1.ID
LEFT JOIN prd_brand g ON c.BRAND_ID=g.ID
LEFT JOIN sys_provider h ON a.AUTHORIZE_ID=h.ID
LEFT JOIN sys_area s2 ON h.AREA_ID=s2.ID
LEFT JOIN sys_area s1 ON s2.PARENT_ID=s1.ID
WHERE 1=1
<if test="brandId != null and brandId != ''">
AND g.ID=#{brandId,jdbcType=BIGINT}
</if>
<if test="status != null and status != ''">
AND a.`STATUS`=#{status,jdbcType=TINYINT}
<!-- <if test="status ==3">
AND (a.`STATUS`=#{status,jdbcType=TINYINT} OR a.`STATUS`=0)
</if>
<if test="status ==2">
AND a.`STATUS`=#{status,jdbcType=TINYINT}
</if>-->
</if>
<if test="prvName != null ">
AND h.`NAME`LIKE CONCAT('%',#{prvName,jdbcType=VARCHAR},'%')
</if>
<if test="searchText != null and searchText != ''">
AND (c.`NAME` LIKE CONCAT('%',#{searchText,jdbcType=VARCHAR},'%') OR b.SERIAL_NUMBER LIKE CONCAT('%',#{searchText,jdbcType=VARCHAR},'%')
OR b.CAS_NO LIKE CONCAT('%',#{searchText,jdbcType=VARCHAR},'%'))
</if>
<if test="category3 != null and category3 != 0">
AND c.CATEGORY_ID = #{category3,jdbcType=BIGINT}
</if>
<if test="category2 != null and category2 != 0">
AND d2.id = #{category2,jdbcType=BIGINT}
</if>
<if test="category1 != null and category1 != 0">
AND d1.id = #{category1,jdbcType=BIGINT}
</if>
<if test="cityId != null and cityId != 0">
AND s2.id = #{cityId,jdbcType=BIGINT}
</if>
<if test="provinceId != null and provinceId != 0">
AND s1.id = #{provinceId,jdbcType=BIGINT}
</if>
<if test="beginTime != null and beginTime != '' and endTime != null and endTime != ''">
AND (
a.END_TIME <![CDATA[>=]]> #{beginTime} AND a.START_TIME <![CDATA[<=]]> #{endTime}
)
</if>
AND a.PRV_ID = #{prvId,jdbcType=BIGINT}
/* AND b.`STATUS`!=-1*/
</select>
<select id="selectRelateSku" parameterType="java.util.Map" resultMap="BaseResultMap">
select a.*,b.`NAME` as SKU_NAME,b.SERIAL_NUMBER,b.OPEN_PRICE,b.APPROVAL_STATUS,
c.`NAME` as PROD_NAME,c.ID as PROD_ID,
d3.ID as CATE_ID,g.`NAME` as BRAND_NAME,h.`NAME` as AUTHORIZE_NAME
from dms_prod_sku_relate a
LEFT JOIN dms_prod_sku_base b ON a.SKU_ID=b.ID
LEFT JOIN dms_prod_base c ON b.PROD_ID=c.ID
LEFT JOIN prd_category d3 ON c.CATEGORY_ID=d3.ID
LEFT JOIN prd_category d2 ON d3.PARENT_ID=d2.ID
LEFT JOIN prd_category d1 ON d2.PARENT_ID=d1.ID
LEFT JOIN prd_brand g ON c.BRAND_ID=g.ID
LEFT JOIN sys_provider h ON a.AUTHORIZE_ID=h.ID
LEFT JOIN sys_area s2 ON h.AREA_ID=s2.ID
LEFT JOIN sys_area s1 ON s2.PARENT_ID=s1.ID
WHERE 1=1
<if test="brandId != null and brandId != ''">
AND g.ID=#{brandId,jdbcType=BIGINT}
</if>
<if test="status != null and status != ''">
<choose>
<when test="status ==6">
AND ( a.START_TIME <![CDATA[<=]]> now())
AND ( a.END_TIME <![CDATA[>=]]> now())
AND a.`STATUS`=3
</when>
<when test="status ==5">
AND ( a.END_TIME <![CDATA[<=]]> now())
AND a.`STATUS`=3
</when>
<when test="status ==7">
AND ( a.START_TIME <![CDATA[>=]]> now())
AND a.`STATUS`=3
</when>
<otherwise>
AND a.`STATUS`=#{status,jdbcType=TINYINT}
</otherwise>
</choose>
</if>
<if test="prvName != null ">
AND h.`NAME`=#{prvName,jdbcType=VARCHAR}
</if>
<if test="searchText != null and searchText != ''">
AND (c.`NAME` LIKE CONCAT('%',#{searchText,jdbcType=VARCHAR},'%') OR b.SERIAL_NUMBER LIKE CONCAT('%',#{searchText,jdbcType=VARCHAR},'%')
OR b.CAS_NO LIKE CONCAT('%',#{searchText,jdbcType=VARCHAR},'%'))
</if>
<if test="category3 != null and category3 != 0">
AND c.CATEGORY_ID = #{category3,jdbcType=BIGINT}
</if>
<if test="category2 != null and category2 != 0">
AND d2.id = #{category2,jdbcType=BIGINT}
</if>
<if test="category1 != null and category1 != 0">
AND d1.id = #{category1,jdbcType=BIGINT}
</if>
<if test="cityId != null and cityId != 0">
AND s2.id = #{cityId,jdbcType=BIGINT}
</if>
<if test="provinceId != null and provinceId != 0">
AND s1.id = #{provinceId,jdbcType=BIGINT}
</if>
<if test="beginTime != null and beginTime != ''">
<!-- <![CDATA[ AND a.UPDATE_TIME >= #{beginTime} ]]>-->
AND a.UPDATE_TIME <![CDATA[>=]]> #{beginTime}
</if>
<if test="endTime != null and endTime != ''">
<!-- <![CDATA[ AND a.UPDATE_TIME >= #{endTime} ]]>-->
AND a.UPDATE_TIME <![CDATA[<=]]>#{endTime}
</if>
AND h.ID = #{authorizeId,jdbcType=BIGINT}
AND a.STATUS!=0
AND b.STATUS!=-1
AND a.PRV_ID = #{prvId,jdbcType=BIGINT}
</select>
<select id="seleteSkuByRelationShip" parameterType="java.util.Map" resultMap="BaseResultMap">
select a.*
from dms_prod_sku_relate a
WHERE a.PRV_ID = #{prvId,jdbcType=BIGINT}
AND a.RELATIONSHIP LIKE #{relationShip}
</select>
<select id="seleteSkuBySkuId" parameterType="java.util.Map" resultMap="BaseResultMap">
select a.*
from dms_prod_sku_relate a
WHERE a.PRV_ID = #{prvId,jdbcType=BIGINT}
AND a.RELATIONSHIP=#{relationShip,jdbcType=VARCHAR}
AND a.SKU_ID=#{skuId,jdbcType=BIGINT}
AND a.STATUS=#{status,jdbcType=TINYINT}
</select>
<select id="getSkuRelateByParams" parameterType="java.util.Map" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List" />
FROM dms_prod_sku_relate
WHERE ID IN (
SELECT PARENT_ID FROM dms_prod_sku_relate
WHERE ID = #{relateId,jdbcType=BIGINT}
)
</select>
<select id="getPrvCode" parameterType="java.lang.Long" resultMap="BaseResultMap">
SELECT * from dms_prod_sku_relate WHERE PRV_ID =#{prvId,jdbcType=BIGINT} and id LIMIT 1
</select>
<select id="getCategoryProd" parameterType="java.util.Map" resultMap="BaseResultMap">
SELECT DPSR.* from prd_category PC1 LEFT JOIN prd_category PC2
on PC2.parent_id = PC1.id LEFT JOIN prd_category PC3
on PC2.id = PC3.parent_id LEFT JOIN dms_prod_base DPB
ON DPB.CATEGORY_ID = PC3.ID RIGHT JOIN dms_prod_sku_base DPSB
ON DPB.ID = DPSB.PROD_ID RIGHT JOIN dms_prod_sku_relate DPSR
ON DPSR.SKU_ID = DPSB.ID WHERE DPSR.PRV_ID = #{prvId} AND PC3.ID IN(
SELECT PC3.ID from prd_category PC1 LEFT JOIN prd_category PC2
on PC2.parent_id = PC1.id LEFT JOIN prd_category PC3
on PC2.id = PC3.parent_id where 1=1
<if test="category1!=null">
AND PC1.ID = #{category1}
</if>
<if test="category2!=null">
AND PC2.ID = #{category2}
</if>
<if test="category3!=null">
AND PC3.ID = #{category3}
</if>
)
</select>
<!--查询是否授权过-->
<select id="isHasRelate" resultMap="BaseResultMap" parameterType="java.util.Map">
SELECT
<include refid="Base_Column_List" />
FROM dms_prod_sku_relate
WHERE PRV_ID = #{prvId,jdbcType=BIGINT}
AND SKU_ID = #{skuId,jdbcType=BIGINT}
AND AUTHORIZE_ID = #{authorizeId,jdbcType=BIGINT}
AND STATUS=3
</select>
<select id="getSkuRelateByPrvId" resultMap="BaseResultMap" parameterType="java.util.Map">
SELECT * FROM dms_prod_sku_relate WHERE UPLOWSTATUS <![CDATA[<>]]> -1 AND PRV_ID = #{prvId}
</select>
<!--批量删除-->
<delete id="batchDeleteSkuRelate">
delete from dms_prod_sku_relate
where ID IN
<foreach item="id" collection="list" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
<select id="getPrdLinePrd" resultMap="BaseResultMap" parameterType="java.util.Map">
SELECT dpsr.* FROM prd_line pl RIGHT JOIN prd_line_relate plr
ON pl.ID = plr.LINE_ID LEFT JOIN dms_prod_sku_relate dpsr
ON plr.PROD_ID = dpsr.ID WHERE pl.PRV_ID = #{prvId} AND
pl.TYPE =2 AND dpsr.ID is NOT NULL AND plr.`STATUS`!=-1
</select>
<update id="auditPrd" parameterType="java.util.Map" >
update dms_prod_sku_base set `APPROVAL_STATUS` = #{type} WHERE ID in
<foreach item="id" collection="list" open="(" separator="," close=")">
#{id}
</foreach>
</update>
<select id="countSku" parameterType="java.util.Map" resultType="java.lang.Integer">
select COUNT(*) from dms_prod_sku_relate a
where a.AUTHORIZE_ID=#{authorizeId}
AND a.PRV_ID=#{prvId}
</select>
<select id="countRefuse" parameterType="java.util.Map" resultType="java.lang.Integer">
select COUNT(*) from dms_prod_sku_relate a
where a.AUTHORIZE_ID=#{authorizeId}
AND a.PRV_ID=#{prvId}
AND a.STATUS=4
</select>
<select id="selectSkuCode" parameterType="java.util.Map" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from dms_prod_sku_relate a
WHERE a.AUTHORIZE_ID=#{authorizeId}
AND a.PRV_ID=#{prvId}
AND a.`STATUS`=3
AND (
(a.START_TIME <![CDATA[<=]]> now() AND a.END_TIME <![CDATA[>=]]> now())
OR a.START_TIME <![CDATA[>=]]> now()
)
</select>
<select id="getOriginalIds" parameterType="java.util.Map" resultMap="BaseResultMap">
SELECT * FROM dms_prod_sku_relate DPSR WHERE TYPE = #{type} AND SKU_ID IN
<foreach item="id" collection="list" open="(" separator="," close=")">
#{id}
</foreach>
</select>
<update id="updateUplowstatusByParam" parameterType="java.util.Map" >
update dms_prod_sku_relate
<set >
<if test="uplowstatus != null" >
UPLOWSTATUS = #{uplowstatus,jdbcType=TINYINT},
</if>
<if test="updateTime != null" >
UPDATE_TIME = #{updateTime,jdbcType=TIMESTAMP},
</if>
<if test="updateBy != null" >
UPDATE_BY = #{updateBy,jdbcType=VARCHAR},
</if>
</set>
where SKU_ID = #{id,jdbcType=BIGINT} AND PRV_ID=#{prvId,jdbcType=BIGINT}
</update>
<update id="updateUplowstatusForSkuEdit" parameterType="java.util.Map" >
update dms_prod_sku_relate
<set >
<if test="uplowstatus != null" >
UPLOWSTATUS = #{uplowstatus,jdbcType=TINYINT},
</if>
<if test="salePrice != null" >
SALE_PRICE = #{salePrice,jdbcType=DOUBLE},
</if>
<if test="updateTime != null" >
UPDATE_TIME = #{updateTime,jdbcType=TIMESTAMP},
</if>
<if test="updateBy != null" >
UPDATE_BY = #{updateBy,jdbcType=VARCHAR},
</if>
</set>
where SKU_ID = #{skuId,jdbcType=BIGINT} AND PARENT_ID IS NULL;
</update>
<select id="allSkuRelate" parameterType="java.util.Map" resultMap="BaseResultMap" >
SELECT a.* FROM dms_prod_sku_relate a
where 1=1
<if test="prvId != null and prvId != ''">
AND a.PRV_ID=#{prvId,jdbcType=BIGINT}
</if>
<if test="authorizeId != null and authorizeId != ''">
AND a.AUTHORIZE_ID=#{authorizeId,jdbcType=BIGINT}
</if>
AND a.STATUS!=3
</select>
<select id="getHtcSeller" resultMap="BaseResultMap">
select a.*,b.`NAME`AS PRV_NAME from dms_prod_sku_relate a
LEFT JOIN sys_provider b
ON a.PRV_ID=b.ID
WHERE a.AUTHORIZE_ID IS NULL AND
a.PARENT_ID IS NULL
</select>
<select id="getSellerByRelationShip" parameterType="java.util.Map" resultMap="BaseResultMap" >
SELECT a.* from dms_prod_sku_relate a
WHERE a.RELATIONSHIP=#{relationship,jdbcType=VARCHAR}
AND a.STATUS <![CDATA[<>]]> -1
</select>
<select id="getSkuRelateBySkuIdAndPrvId" parameterType="java.util.Map" resultMap="BaseResultMap" >
SELECT a.* FROM dms_prod_sku_relate a
where 1=1
<if test="skuId != null and skuId != ''">
AND a.SKU_ID=#{skuId,jdbcType=BIGINT}
</if>
<if test="prvId != null and prvId != ''">
AND a.PRV_ID=#{prvId,jdbcType=BIGINT}
</if>
</select>
<select id="getSellerBySkuCode" resultMap="BaseResultMap" parameterType="java.util.Map">
select a.* from dms_prod_sku_relate a
WHERE 1=1
<if test="code != null and code != ''">
and a.`SKU_CODE` LIKE #{code}
</if>
</select>
<insert id="insertSkuBatch" parameterType="java.util.List">
<selectKey resultType ="java.lang.Integer" keyProperty= "id"
order= "AFTER">
SELECT LAST_INSERT_ID()
</selectKey >
INSERT INTO dms_prod_sku_relate
(SKU_ID, SKU_CODE,
PARENT_ID, AUTHORIZE_ID, PRV_ID,
TYPE, DISCOUNT, START_TIME,
END_TIME, RELATIONSHIP, STATUS,
CREATE_TIME, CREATE_BY, UPDATE_TIME,
UPDATE_BY,UPLOWSTATUS,SALE_PRICE)
values
<foreach collection ="list" item="d" index= "index" separator =",">
(#{d.skuId,jdbcType=BIGINT}, #{d.skuCode,jdbcType=VARCHAR},#{d.parentId,jdbcType=BIGINT},
#{d.authorizeId,jdbcType=BIGINT}, #{d.prvId,jdbcType=BIGINT},#{d.type,jdbcType=VARCHAR},
#{d.discount,jdbcType=VARCHAR}, #{d.startTime,jdbcType=TIMESTAMP},#{d.endTime,jdbcType=TIMESTAMP},
#{d.relationship,jdbcType=VARCHAR}, #{d.status,jdbcType=TINYINT},#{d.createTime,jdbcType=TIMESTAMP},
#{d.createBy,jdbcType=VARCHAR}, #{d.updateTime,jdbcType=TIMESTAMP},#{d.updateBy,jdbcType=VARCHAR},
#{d.upLowStatus,jdbcType=TINYINT},#{d.salePrice,jdbcType=DOUBLE})
</foreach>
</insert>
<update id="updateSkuBatch" parameterType="java.util.List">
<foreach collection="list" item="d" separator=";">
UPDATE dms_prod_sku_relate
<set>
<if test="d.status != null">
STATUS = #{d.status,jdbcType=TINYINT},
</if>
<if test="d.discount != null">
DISCOUNT = #{d.discount},
</if>
<if test="d.salePrice != null">
SALE_PRICE = #{d.salePrice},
</if>
<if test="d.endTime != null">
END_TIME = #{d.endTime},
</if>
<if test="d.startTime != null">
START_TIME = #{d.startTime},
</if>
<if test="d.updateTime != null">
UPDATE_TIME = #{d.updateTime},
</if>
</set>
WHERE ID = #{d.id,jdbcType=BIGINT}
</foreach>
</update>
<update id="batchUpdate" parameterType="java.util.List">
<foreach collection="list" item="relate" separator=";">
UPDATE dms_prod_sku_relate
<set>
<if test="relate.upLowStatus != null">
UPLOWSTATUS = #{relate.upLowStatus,jdbcType=TINYINT},
</if>
<if test="relate.updateBy != null">
UPDATE_BY = #{relate.updateBy,jdbcType=VARCHAR},
</if>
<if test="relate.updateTime != null">
UPDATE_TIME = #{relate.updateTime},
</if>
</set>
WHERE ID = #{relate.id,jdbcType=BIGINT}
</foreach>
</update>
<insert id ="batchInsert" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
INSERT INTO dms_prod_sku_relate (SKU_ID, SKU_CODE, UPLOWSTATUS,PRV_ID,TYPE,RELATIONSHIP,SALE_PRICE,STATUS,CREATE_TIME,CREATE_BY) VALUES
<foreach collection ="list" item="relate" index= "index" separator =",">
(
#{relate.skuId},
#{relate.skuCode},
#{relate.upLowStatus},
#{relate.prvId},
#{relate.type},
#{relate.relationship},
#{relate.salePrice},
#{relate.status},
NOW(),
#{relate.createBy}
)
</foreach >
</insert>
</mapper>
685
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
