服务端:
1.创建SqlMapper.java
package com.symapgis.lycp.mapper;import com.symapgis.lycp.entity.*;publicclassSqlMapper{/**
* 变更表多条件模糊查询
*
* @param chan 传入的对象
* @param strat 分页从第几页开始
* @param size 分页,每页多少条
* @return
*/public StringselectByChangeIF(Change chan,int strat,int size){
StringBuffer sql=newStringBuffer("select * from changetable where 1=1 ");if(chan.getChanApplication()!= null&&!("".equals(chan.getChanApplication()))){//LIKE '%******%'
sql.append(" AND chan_application LIKE '%"+ chan.getChanApplication()+"%'");}if(chan.getChanAffiliate()!= null&&!("".equals(chan.getChanAffiliate()))){
sql.append(" AND chan_affiliate LIKE '%"+ chan.getChanAffiliate()+"%'");}if(chan.getChanWeizhichange()!= null&&!("".equals(chan.getChanWeizhichange()))){
sql.append(" AND chan_weizhichange='"+ chan.getChanWeizhichange()+"'");}if(chan.getChanCqsm()!= null&&!("".equals(chan.getChanCqsm()))){
sql.append(" AND chan_cqsm='"+ chan.getChanCqsm()+"'");}//日期格式, 以具体到某一天为例 LIKE '2019-08-13%'if(chan.getChanApplicationTime()!= null&&!("".equals(chan.getChanApplicationTime()))){
sql.append(" AND chan_application_time LIKE '"+ chan.getChanApplicationTime()+"%'");}if(chan.getChanYsResult()!= null&&!("".equals(chan.getChanYsResult()))){
sql.append(" AND chan_ys_result='"+ chan.getChanYsResult()+"'");}//分页语句
sql.append(" ORDER BY chan_application_time DESC LIMIT "+ strat+","+ size);return sql.toString();}/**
* 变更表配合模糊查询返回数量
*
* @param chan
* @return
*/public StringselectByChangeIFCount(Change chan){
StringBuffer sql=newStringBuffer("SELECT COUNT(*) from (SELECT * from changetable where 1=1 ");if(chan.getChanApplication()!= null&&!("".equals(chan.getChanApplication()))){
sql.append(" AND chan_application LIKE '%"+ chan.getChanApplication()+"%'");}if(chan.getChanAffiliate()!= null&&!("".equals(chan.getChanAffiliate()))){
sql.append(" AND chan_affiliate LIKE '%"+ chan.getChanAffiliate()+"%'");}if(chan.getChanWeizhichange()!= null&&!("".equals(chan.getChanWeizhichange()))){
sql.append(" AND chan_weizhichange='"+ chan.getChanWeizhichange()+"'");}if(chan.getChanCqsm()!= null&&!("".equals(chan.getChanCqsm()))){
sql.append(" AND chan_cqsm='"+ chan.getChanCqsm()+"'");}if(chan.getChanApplicationTime()!= null&&!("".equals(chan.getChanApplicationTime()))){
sql.append(" AND chan_application_time LIKE '"+ chan.getChanApplicationTime()+"%'");}if(chan.getChanYsResult()!= null&&!("".equals(chan.getChanYsResult()))){
sql.append(" AND chan_ys_result='"+ chan.getChanYsResult()+"'");}
sql.append(" ORDER BY chan_application_time DESC ) as a");return sql.toString();}}
拼接后为:
//分页模糊查询语句select*from changetablewhere1=1AND chan_applicationLIKE'%2019%'AND chan_affiliateLIKE'%19%'AND chan_weizhichange='是'AND chan_cqsm='买断'AND chan_application_timeLIKE'2019-08-06%'AND chan_ys_result='1'ORDERBY chan_application_timeDESCLIMIT0,10//返回模糊查询数量语句SELECTCOUNT(*)from(SELECT*from changetablewhere1=1AND chan_applicationLIKE'%2019%'AND chan_affiliateLIKE'%19%'AND chan_weizhichange='是'AND chan_cqsm='买断'AND chan_application_timeLIKE'2019-08-06%'AND chan_ys_result='1'ORDERBY chan_application_timeDESC)as a
2.Mapper层
创建接口
@MapperpublicinterfaceChangeMapper{/**
* 多条件模糊查询
* @param chan
* @param start
* @param size
* @return
* 注意 type 为创建的类名 method 为方法名
*/@SelectProvider(type=SqlMapper.class,method="selectByChangeIF")
List<Change>selectByChangeIF(Change chan,@Param("strat")int start,@Param("size")int size);/**
* 多条件模糊查询,返回数量
* @param chan
* @param start
* @param size
* @return
*/@SelectProvider(type=SqlMapper.class,method="selectByChangeIFCount")intselectByChangeIFCount(Change chan);}
3.Controller层
import net.sf.json.JSONObject;import java.util.HashMap;import java.util.Map;@RestController@RequestMapping("/Change")publicclassChangeController{@Autowiredprivate ChangeService chanser;@ResponseBody@RequestMapping("/selectByChangeIf")public ObjectselectByIF(@RequestParam("chanObj") String chanObj){
Map<String, Object> map=newHashMap<>();//接收前端发送来的JSON,转换为对象
JSONObject jsonobj= JSONObject.fromObject(chanObj);
Change chan=newChange();
chan.setChanApplication((String) jsonobj.get("chanApplication"));
chan.setChanAffiliate((String) jsonobj.get("chanAffiliate"));
chan.setChanWeizhichange((String) jsonobj.get("chanWeizhichange"));
chan.setChanCqsm((String) jsonobj.get("chanCqsm"));
chan.setChanApplicationTime((String) jsonobj.get("chanApplicationTime"));
chan.setChanYsResult((String) jsonobj.get("chanYsResult"));int start=(int) jsonobj.get("start");int size=(int) jsonobj.get("size");
map.put("ChangePageList", chanser.selectByChangeIF(chan, start, size));
map.put("ChangePageListCount", chanser.selectByChangeIFCount(chan, start, size));return map;}}
前端发送的JSON:
{
chanApplication:"2019"
chanAffiliate:"19"
chanApplication:"2019"
chanApplicationTime:"2019-08-06"
chanCqsm:"买断"
chanWeizhichange:"是"
chanYsResult:"1"
size:10
start:0}
注意引用的JSON
import net.sf.json.JSONObject;
//在pom.xml引入jar包<dependency><groupId>net.sf.json-lib</groupId><artifactId>json-lib</artifactId><version>2.2.3</version><classifier>jdk15</classifier></dependency>
前端:
利用JQuery发送请求
$('#submit').click(function(){//获取form表单中的内容var d={};var t=$('form').serializeArray();
$.each(t,function(){
d[this.name]=this.value;});var chanObj= d;
chanObj.start=0;
chanObj.size=10;
console.log(chanObj);
$.ajax({
url:"/Change/selectByChangeIf",
type:"post",
contentType:"application/json",
dataType:"json",
data:{moveObj:JSON.stringify(chanObj)},
success:function(res){
console.log(res);}})})
2.form表单
<formclass="form-inline"id="formList"><divclass="form-group"><labelfor="chanApplication"class="sr-only">Name</label><inputtype="text"class="form-control"id="chanApplication"placeholder="申请序号"name="chanApplication"></div><divclass="form-group"><labelfor="chanAffiliate"class="sr-only">Name</label><inputtype="text"class="form-control"id="chanAffiliate"placeholder="代销点编号"name="chanAffiliate"></div><divclass="form-group">
·<labelclass="sr-only"for="chanWeizhichange">Email</label><selectclass="form-control"id="chanWeizhichange"name="chanWeizhichange"><optionvalue="">-- 是否变更 --</option><optionvalue="是">是</option><optionvalue="否">否</option></select></div><divclass="form-group"><labelclass="sr-only"for="chanCqsm">Email</label><selectclass="form-control"id="chanCqsm"name="chanCqsm"><optionvalue="">-- 产权说明 --</option><optionvalue="国有">国有</option><optionvalue="买断">买断</option></select></div><divclass="form-group"><labelfor="chanApplicationTime"class="sr-only">申请时间:</label><inputtype="text"class="demo-input"placeholder="申请日期"id="chanApplicationTime"name="chanApplicationTime"></div><divclass="form-group"><labelclass="sr-only"for="chanYsResult">Email</label><selectclass="form-control"id="chanYsResult"name="chanYsResult"><optionvalue="">-- 申请状态 --</option><optionvalue="0">未审核</option><optionvalue="1">通过</option><optionvalue="2">未通过</option></select></div><divclass="form-group">