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

最新推荐文章于 2024-04-19 11:37:46 发布

卑微猿同学

最新推荐文章于 2024-04-19 11:37:46 发布

阅读量3.6k

点赞数 1

分类专栏： Spring boot 项目

本文链接：https://blog.csdn.net/weixin_42547014/article/details/99436597

版权

Spring boot 项目专栏收录该内容

13 篇文章 2 订阅

订阅专栏

服务端：
1.创建SqlMapper.java

package com.symapgis.lycp.mapper;

import com.symapgis.lycp.entity.*;

public class SqlMapper {
 /**
     * 变更表多条件模糊查询
     *
     * @param chan  传入的对象
     * @param strat  分页从第几页开始
     * @param size  分页，每页多少条
     * @return
     */
    public String selectByChangeIF(Change chan, int strat, int size) {
        StringBuffer sql = new StringBuffer("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 String selectByChangeIFCount(Change chan) {
        StringBuffer sql = new StringBuffer("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 changetable where 1=1 AND chan_application LIKE '%2019%' AND chan_affiliate LIKE '%19%' AND chan_weizhichange='是' AND chan_cqsm='买断' AND chan_application_time LIKE '2019-08-06%' AND chan_ys_result='1' ORDER BY chan_application_time DESC LIMIT 0,10

//返回模糊查询数量语句
SELECT COUNT(*) from (SELECT * from changetable where 1=1 AND chan_application LIKE '%2019%' AND chan_affiliate LIKE '%19%' AND chan_weizhichange='是' AND chan_cqsm='买断' AND chan_application_time LIKE '2019-08-06%' AND chan_ys_result='1' ORDER BY chan_application_time DESC ) as a

2.Mapper层

创建接口

@Mapper
public interface ChangeMapper {
/**
     * 多条件模糊查询
     * @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")
    int selectByChangeIFCount(Change chan);
}

3.Controller层

import net.sf.json.JSONObject;
import java.util.HashMap;
import java.util.Map;

@RestController
@RequestMapping("/Change")
public class ChangeController {
	@Autowired
    private ChangeService chanser;
    
    @ResponseBody
    @RequestMapping("/selectByChangeIf")
    public Object selectByIF(@RequestParam("chanObj") String chanObj) {
        Map<String, Object> map = new HashMap<>();
	
		//接收前端发送来的JSON，转换为对象	

        JSONObject jsonobj = JSONObject.fromObject(chanObj);

        Change chan = new Change();

        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表单

<form class="form-inline" id="formList">
	<div class="form-group">
		<label for="chanApplication" class="sr-only">Name</label>
        <input type="text" class="form-control" id="chanApplication" placeholder="申请序号" name="chanApplication">
    </div>
    <div class="form-group">
    	<label for="chanAffiliate" class="sr-only">Name</label>
        <input type="text" class="form-control" id="chanAffiliate" placeholder="代销点编号" name="chanAffiliate">
     </div>

     <div class="form-group">
     ·	<label class="sr-only" for="chanWeizhichange">Email</label>
        <select class="form-control" id="chanWeizhichange" name="chanWeizhichange">
        	<option value="">-- 是否变更 --</option>
            <option value="是">是</option>
            <option value="否">否</option>
         </select>
      </div>

      <div class="form-group">
      	 <label class="sr-only" for="chanCqsm">Email</label>
      	 <select class="form-control" id="chanCqsm" name="chanCqsm">
      	 	 <option value="">-- 产权说明 --</option>
             <option value="国有">国有</option>
             <option value="买断">买断</option>
          </select>
       </div>
       <div class="form-group">
      	   <label for="chanApplicationTime" class="sr-only">申请时间：</label>
           <input type="text" class="demo-input" placeholder="申请日期" id="chanApplicationTime"  name="chanApplicationTime">
       </div>
       <div class="form-group">
            <label class="sr-only" for="chanYsResult">Email</label>
            <select class="form-control" id="chanYsResult" name="chanYsResult">
            	 <option value="">-- 申请状态 --</option>
                 <option value="0">未审核</option>
                 <option value="1">通过</option>
                 <option value="2">未通过</option>
             </select>
        </div>
        <div class="form-group">
            <a class="btn btn-primary" id="submit">查询</a>
            <a class="btn btn-danger" id="replace">重置</a>
        </div>
  </form>

卑微猿同学

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
Springboot + MySql + MyBatis 以注解形式实现多条件动态模糊查询并分页

服务端：1.创建SqlMapper.classpackage com.symapgis.lycp.mapper;import com.symapgis.lycp.entity.*;public class SqlMapper { /** * 变更表多条件模糊查询 * * @param chan 传入的对象 * @param strat 分页...
复制链接

扫一扫