mybatis中遇到的棘手问题

2018-08-23 13:44:00    admin    988    原创
摘要:mybatis中遇到的棘手问题

1、在一个表中的数据进行插入的时候,首先判断某个字段的值是否存在,如果存在则进行更新

当用一条sql进行写时候,需要确认这个要判断的字段是主键,然后用insert into values  ON DUPLICATE KEY UPDATE ,

但是我在update后面要判断参数是否为空,如果不为空进行更新,为空的就不进行更新了


	insert into sys_hk_goods
		(
		`sku_code`,
		`product_code`,
		`sku_name`,
		`uom`,
		`wrapping`,
		`weight`,
		`length`,
		`width`,
		`height`,
		`logistics_package`,
		`package_material`,
		`picture_url`
		)
		values
		(
		#{skuCode},
		#{productCode},
		#{skuName},
		#{uom},
		#{wrapping},
		#{weight},
		#{length},
		#{width},
		#{height},
		#{logisticsPackage},
		#{packageMaterial},
		#{pictureUrl}
		)
		ON DUPLICATE KEY UPDATE
			<if test="productCode != null">`product_code` = #{productCode}, </if>
			<if test="skuName != null">`sku_name` = #{skuName}, </if>
			<if test="uom != null">`uom` = #{uom}, </if>
			<if test="wrapping != null">`wrapping` = #{wrapping}, </if>
			<if test="weight != null">`weight` = #{weight}, </if>
			<if test="length != null">`length` = #{length}, </if>
			<if test="width != null">`width` = #{width}, </if>
			<if test="height != null">`height` = #{height}, </if>
			<if test="logisticsPackage != null">`logistics_package` = #{logisticsPackage}, </if>
			<if test="packageMaterial != null">`package_material` = #{packageMaterial}, </if>
			<if test="pictureUrl != null">`picture_url` = #{pictureUrl}</if>
	</insert>


当最后一个pictureUrl为空的时候,会报异常,因为不走最后一条数据了,多了一个”,”,

处理方式可以用mybatis中的trim进行判断

正确的做法如下:

	insert into sys_hk_goods
		(
		`sku_code`,
		`product_code`,
		`sku_name`,
		`uom`,
		`wrapping`,
		`weight`,
		`length`,
		`width`,
		`height`,
		`logistics_package`,
		`package_material`,
		`picture_url`
		)
		values
		(
		#{skuCode},
		#{productCode},
		#{skuName},
		#{uom},
		#{wrapping},
		#{weight},
		#{length},
		#{width},
		#{height},
		#{logisticsPackage},
		#{packageMaterial},
		#{pictureUrl}
		)
		ON DUPLICATE KEY UPDATE
		<trim prefix="" suffixOverrides=",">
			<if test="productCode != null">`product_code` = #{productCode}, </if>
			<if test="skuName != null">`sku_name` = #{skuName}, </if>
			<if test="uom != null">`uom` = #{uom}, </if>
			<if test="wrapping != null">`wrapping` = #{wrapping}, </if>
			<if test="weight != null">`weight` = #{weight}, </if>
			<if test="length != null">`length` = #{length}, </if>
			<if test="width != null">`width` = #{width}, </if>
			<if test="height != null">`height` = #{height}, </if>
			<if test="logisticsPackage != null">`logistics_package` = #{logisticsPackage}, </if>
			<if test="packageMaterial != null">`package_material` = #{packageMaterial}, </if>
			<if test="pictureUrl != null">`picture_url` = #{pictureUrl}</if>
		</trim>
	</insert>
2、第二个问题,当用set的时候不会出现这个问题,具体代码如下:

<update id="update" parameterType="io.sbed.modules.common.hk.entity.SysHkGoods">
		update sys_hk_goods 
		<set>
			<if test="productCode != null">`product_code` = #{productCode}, </if>
			<if test="skuName != null">`sku_name` = #{skuName}, </if>
			<if test="uom != null">`uom` = #{uom}, </if>
			<if test="wrapping != null">`wrapping` = #{wrapping}, </if>
			<if test="weight != null">`weight` = #{weight}, </if>
			<if test="length != null">`length` = #{length}, </if>
			<if test="width != null">`width` = #{width}, </if>
			<if test="height != null">`height` = #{height}, </if>
			<if test="logisticsPackage != null">`logistics_package` = #{logisticsPackage}, </if>
			<if test="packageMaterial != null">`package_material` = #{packageMaterial}, </if>
			<if test="pictureUrl != null">`picture_url` = #{pictureUrl}</if>
		</set>
		where sku_code = #{skuCode}
	</update>
这是因为mybatis中的set标签会默认把最后一个逗号去掉,所以不会出现执行错误



豫ICP备15006410号
蝉知 蝉知4.5.2