Springboot + MySql + MyBatis 以注解形式实现多条件动态模糊查询并分页

2022-07-01 10:18:51

服务端:
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">
  • 作者:卑微猿同学
  • 原文链接:https://blog.csdn.net/weixin_42547014/article/details/99436597
    更新时间:2022-07-01 10:18:51