背景
在做小项目的运营后台时,需要制作多条件搜索的表单,要求是:支持任一字段搜索或几个字段的搜索,当无字段搜索时返回所有数据。视图如下
在收集表单的数据之后,使用axios发起post请求,代码如下:
onSubmit() {
let url = this.url.query;
let data = new FormData();
data.append('oldFileName',this.form.oldFileName);
data.append('newFileName',this.form.newFileName);
data.append('type',this.form.type);
if(this.form.startTime !== null){
data.append('startTime',this.form.startTime.toString());
}
if(this.form.endTime !== null){
data.append('endTime',this.form.endTime.toString());
}
axios.post(url,data).then((response)=>{
console.log(response);
this.dataSource = response.data;
}).catch((err)=>{
console.log(err);
}).finally(()=>{
});
},
后端的mapper的语句:
<select id="query" parameterType="com.peko.filemanager.dto.QueryForm" resultType="com.peko.filemanager.entity.MyFile">
select *
from t_file
where 1=1
<trim prefix="and" suffixOverrides="and">
<if test="oldFileName != null and oldFileName != ''">old_file_name = #{oldFileName,jdbcType=VARCHAR} and</if>
<if test="newFileName != null and newFileName != ''">new_file_name = #{newFileName,jdbcType=VARCHAR} and</if>
<if test="type != null and type != ''">type = #{type,jdbcType=VARCHAR} and</if>
<if test="startTime != null">upload_time >= #{startTime} and</if>
<if test="endTime != null">upload_time <= #{endTime} and</if>
</trim>
</select>
写完之后,就尝试了无条件搜索,却发现并没有查询到所有的数据,后端打印的日志:
2021-03-17 14:03:48.214 INFO 6772 --- [nio-9090-exec-2] c.p.f.service.impl.FileServiceImpl : QueryForm(endTime=null, newFileName=null, oldFileName=null, startTime=null, type=null)
2021-03-17 14:03:48.216 DEBUG 6772 --- [nio-9090-exec-2] c.peko.filemanager.dao.FileMapper.query : ==> Preparing: select id,old_file_name,new_file_name,ext,path,size,type,download_counts,upload_time,won_img from t_file where 1=1 and old_file_name = ? and new_file_name = ? and type = ?
2021-03-17 14:03:48.216 DEBUG 6772 --- [nio-9090-exec-2] c.peko.filemanager.dao.FileMapper.query : ==> Parameters: null(String), null(String), null(String)
2021-03-17 14:03:48.217 DEBUG 6772 --- [nio-9090-exec-2] c.peko.filemanager.dao.FileMapper.query : <== Total: 0
2021-03-17 14:03:48.217 INFO 6772 --- [nio-9090-exec-2] c.p.f.service.impl.FileServiceImpl : []
可以看到前端传到后端的数据中,都是 null 的值的,理应来说,在mybatis的查询语句中,这五行语句应该不会被执行才对
<if test="oldFileName != null and oldFileName != ''">old_file_name = #{oldFileName,jdbcType=VARCHAR} and</if>
<if test="newFileName != null and newFileName != ''">new_file_name = #{newFileName,jdbcType=VARCHAR} and</if>
<if test="type != null and type != ''">type = #{type,jdbcType=VARCHAR} and</if>
<if test="startTime != null">upload_time >= #{startTime} and</if>
<if test="endTime != null">upload_time <= #{endTime} and</if>
但是在打印的执行sql中看到这三句被执行了,也就时说 oldFileName、newFileName 、type 这几个数据它们不为null,也不为"",那到底是什么呢??
<if test="oldFileName != null and oldFileName != ''">old_file_name = #{oldFileName,jdbcType=VARCHAR} and</if>
<if test="newFileName != null and newFileName != ''">new_file_name = #{newFileName,jdbcType=VARCHAR} and</if>
<if test="type != null and type != ''">type = #{type,jdbcType=VARCHAR} and</if>
解决
前端在处理表单数据时,如果为空就不要放进data的数据体中,这样数据就完全为空了
if(this.form.oldFileName !== null && this.form.oldFileName !== ""){
data.append('oldFileName',this.form.oldFileName);
}
if(this.form.newFileName !== null && this.form.newFileName !== ""){
data.append('newFileName',this.form.newFileName);
}
if(this.form.type !== null && this.form.type !== ""){
data.append('type',this.form.type);
}
日志:
2021-03-17 14:21:33.746 INFO 7344 --- [nio-9090-exec-4] c.p.f.service.impl.FileServiceImpl : QueryForm(endTime=null, newFileName=null, oldFileName=null, startTime=null, type=null)
2021-03-17 14:21:33.747 DEBUG 7344 --- [nio-9090-exec-4] c.peko.filemanager.dao.FileMapper.query : ==> Preparing: select id,old_file_name,new_file_name,ext,path,size,type,download_counts,upload_time,won_img from t_file where 1=1
2021-03-17 14:21:33.748 DEBUG 7344 --- [nio-9090-exec-4] c.peko.filemanager.dao.FileMapper.query : ==> Parameters:
2021-03-17 14:21:33.750 DEBUG 7344 --- [nio-9090-exec-4] c.peko.filemanager.dao.FileMapper.query : <== Total: 27
遗留问题
oldFileName、newFileName 、type 这几个数据它们不为null,也不为"",那到底是什么呢??