使用postman测试一个查询接口,数据为空。
查询的代码,没有问题。
public List<Long> getByLikeName(String name) {
return skuDao.getByLikeName("%" + name + "%");
}
<!-- sku模糊名字编码编号查询-->
<select id="getByLikeName" parameterType="java.lang.String" resultType="Long">
SELECT s.id
FROM sku s
WHERE s.sku_name LIKE #{name} OR s.sku_code LIKE #{name} OR s.bar_code LIKE #{name} AND deleted = '0'
</select>
明明有数据,为什么查询出来的结果为空呢?后来改了一下查询方式,在mapper.xml中使用concat拼接字符串。
public List<Long> getByLikeName(String name) {
return skuDao.getByLikeName(name);
}
<!-- sku模糊名字编码编号查询-->
<select id="getByLikeName" parameterType="java.lang.String" resultType="Long">
SELECT s.id
FROM sku s
WHERE deleted = 0 AND
(s.sku_name LIKE concat('%', #{name,jdbcType=VARCHAR}, '%')
OR s.sku_code LIKE concat('%', #{name,jdbcType=VARCHAR}, '%')
OR s.bar_code LIKE concat('%', #{name,jdbcType=VARCHAR}, '%'))
</select>
可以看到,筛选出的数据仍然是0.
Preparing: SELECT s.id FROM sku s WHERE deleted = 0 AND (s.sku_name LIKE concat('%', ?, '%') OR s.sku_code LIKE concat('%', ?, '%') OR s.bar_code LIKE concat('%', ?, '%'))
2019-06-27 16:54:44 [http-nio-8003-exec-1] DEBUG c.r.s.o.m.S.getByLikeName - ==> Parameters: "铅笔"(String), "铅笔"(String), "铅笔"(String)
2019-06-27 16:54:44 [http-nio-8003-exec-1] DEBUG c.r.s.o.m.S.getByLikeName - <== Total: 0
最后解决方式:
修改skuName的value值,改成铅笔,不要加双引号,就可以了。
==> Preparing: SELECT s.id FROM sku s WHERE deleted = 0 AND (s.sku_name LIKE concat('%', ?, '%') OR s.sku_code LIKE concat('%', ?, '%') OR s.bar_code LIKE concat('%', ?, '%'))
2019-06-27 16:51:48 [http-nio-8003-exec-3] DEBUG c.r.s.o.m.S.getByLikeName - ==> Parameters: 铅笔(String), 铅笔(String), 铅笔(String)
2019-06-27 16:51:48 [http-nio-8003-exec-3] DEBUG c.r.s.o.m.S.getByLikeName - <== Total: 17
总结:
//参数不加双引号
Preparing: SELECT s.id FROM sku s WHERE s.sku_name LIKE ? OR s.sku_code LIKE ? OR s.bar_code LIKE ? AND deleted = '0'
2019-06-27 17:04:56 [http-nio-8003-exec-2] DEBUG c.r.s.o.m.S.getByLikeName - ==> Parameters: %铅笔%(String), %铅笔%(String), %铅笔%(String)
2019-06-27 17:04:56 [http-nio-8003-exec-2] DEBUG c.r.s.o.m.S.getByLikeName - <== Total: 17
//参数加双引号
Preparing: SELECT s.id FROM sku s WHERE s.sku_name LIKE ? OR s.sku_code LIKE ? OR s.bar_code LIKE ? AND deleted = '0'
2019-06-27 17:09:06 [http-nio-8003-exec-1] DEBUG c.r.s.o.m.S.getByLikeName - ==> Parameters: %"铅笔"%(String), %"铅笔"%(String), %"铅笔"%(String)
双引号也是字符啊,想想也是这个道理,这么简单,搞了好久。
注意controller层请求:
@PostMapping("queryList")
public BaseResultVo queryList(@ModelAttribute WarehouseInventoryReportDTO dto);
这里使用的是@ModelAttribute,postman参数放在params里,即跟在url后面。
如果前端请求把参数放在了response body中传过来,@ModelAttribute需要改成@RequestBody。