MyBatisPlus之高级查询用法及说明

例： 查询所有学生信息，返回name age 列。
    querywrapper<user> wrapper = new querywrapper<>();

	1.wrapper.select("name","age");//---------->查询列集合，将查询数据作为两例。
	
	2.wrapper.select("name,age");//---------->查询列集合，将查询数据作为一例。

    usermapper.selectlist(wrapper)
          

orderbyasc： 正序排序
   等价sql: select ..from table   order by 字段, ... asc

orderbydesc ：倒序排序
        
wrapper方法：
orderbyasc(r... columns)
orderbyasc(boolean condition, r... columns)
    
效果： 
orderbyasc("id", "name")--->order by id asc,name asc
       
列：查询所有员工信息按age正序排， 如果age一样， 按id正序排
    @test
    public void test0(){
        querywrapper<employee> wrapper = new querywrapper<>();
        wrapper.orderbyasc("age", "id");
        employeemapper.selectlist(wrapper);
    }

//对应sql语句：
    select id,name,password,email,age,admin,dept_id from employee order by age asc,id asc
        orderbydesc 跟 orderbyasc用法一样， 不过是倒序排

orderby：定制排序
等价sql: select ..from table   order by 字段,

//参数1：布尔表达式：控制是否进行排序操作
//参数2：控制是不是正序
orderby(boolean condition, boolean isasc, r... columns)

 排序：order by 字段, ...
 &效果：  
 orderby(true, true, "id", "name")--->order by id asc,name asc
    
例:查询所有员工信息按age正序排， 如果age一样， 按id正序排
    @test
    public void test1(){
        querywrapper<employee> wrapper = new querywrapper<>();
        //apper.orderbyasc("age", "id");
        //等价于：
        wrapper.orderby(true, true, "age", "id");
        employeemapper.selectlist(wrapper);
    }
//sql语句
select id,name,password,email,age,admin,dept_id from employee order by age asc,id asc

列：查询所有员工信息按age正序排， 如果age一样， 按id倒序排
    @test
    public void test2(){
        querywrapper<employee> wrapper = new querywrapper<>();
        wrapper.orderbyasc("age");
        wrapper.orderbydesc("id");
        employeemapper.selectlist(wrapper);
    }

//对应sql语句
select id,name,password,email,age,admin,dept_id from employee order by age asc,id desc

方法：
(1)：groupby ： 分组：group by 字段, ...
(2)：groupby(r... columns)
(3)：groupby(boolean condition, r... columns)
    
&效果：
 groupby("id", "name")--->group by id,name
 
例: 以部门id进行分组查询，查每个部门员工个数
    @test
    public void test3(){
        querywrapper<employee> wrapper = new querywrapper<>();
        wrapper.groupby("dept_id");
        wrapper.select("dept_id", "count(id) count");
        employeemapper.selectmaps(wrapper);
    }
//对应sql语句
select dept_id,count(id) count from employee group by dept_id 

方法：
(1): having ： having ( sql语句 ) 
(2): having(string sqlhaving, object... params)
   
   //参数1：布尔表达式：控制是否进行过滤操作
   //参数2：以哪个字段为过滤
   //参数3：以哪个条件作为过滤条件
(3): having(boolean condition, string sqlhaving, object... params)
   
  例: having("sum(age) > 10")--->having sum(age) > 10
  例: having("sum(age) > {0}", 11)--->having sum(age) > 11

   需求： 以部门id进行分组查询，查每个部门员工个数， 将大于3人的部门过滤出来
   @test
   public void test5(){
       querywrapper<employee> wrapper = new querywrapper<>();
       wrapper.groupby("dept_id")
               .select("dept_id", "count(id) count")
               //.having("count > {0}", 3)
               .having("count >3");
       employeemapper.selectmaps(wrapper);
   }
//对应sql语句：
 select dept_id,count(id) count from employee group by dept_id having count >3

1)：alleq:全能匹配;即：所有条件都得等；

  例 查询 name='张三‘ age='18‘的学生信息

	步骤：
  	（1）： map（string ，object）mp = new hashmap();
          		mp.put("name","张三");
	      		mp.put("age","18");

  	(2):  querywrapper<employee> wrapper = new querywrapper<>();
		 		wrapper.alleq(mp);

  	(3):usermapper.selelist(wrapper);



2): eq:当个参数判断是否相等。---------->对应sql中 “等于=”

	列：eq("name","zhnagsan")----------->name="zhangsane"


3): ne: 不等于---------->对应sql中 “不等于< >”
      
   列：ne("name","张三")------------->name< >'张三嗯'

(1):get :大于 >
    列：get("age",18)----------->age>18  

(2):ge :大于等于 >=
    列：ge("age",18)----------->age>=18 

(3):lt :小于 <
    列：lt("age",18)----------->age<18 

(4):le :小于等于 <=
    列：le("age",18)----------->age<=18 
    

@mapper
public interface hhchaincustomerinfomapper extends basemapper<hhchaincustomerinfo> {
    
 
    /**
    * 使用mybatis-plus方式调用saveorupdatebatch不需要写这个接口
    boolean saveorupdatebatch(@param("entities") collection<hhchaincustomerinfo> hhchaincustomerinfos);
    */
 
}

/**
 * 链路客户信息service接口
 *
 * @author js
 * @date 2023-09-10
 */
public interface ihhchaincustomerinfoservice extends iservice<hhchaincustomerinfo> {
    

}

@service
public class hhchaincustomerinfoserviceimpl extends serviceimpl<hhchaincustomerinfomapper, hhchaincustomerinfo> implements ihhchaincustomerinfoservice {
 
 
    /*@override
    public boolean saveorupdatebatch(collection<hhchaincustomerinfo> entitylist) {
        return hhchaincustomerinfomapper.saveorupdatebatch(entitylist);
    }*/
 
}

@setter
@getter
public class queryobject {
    private int currentpage=1;
    private int pagesize=1;
    
}

@setter
@getter
public class employeequery extends queryobject{
}

步骤：配置分页插件
//分页
    @bean
    public mybatisplusinterceptor mybatisplusinterceptor() {
        mybatisplusinterceptor interceptor = new mybatisplusinterceptor();
        paginationinnerinterceptor paginationinnerinterceptor = new paginationinnerinterceptor(dbtype.mysql);
        paginationinnerinterceptor.setoverflow(true); //合理化
        interceptor.addinnerinterceptor(paginationinnerinterceptor);
       return interceptor;
    }

public interface  iemployeeservice extends iservice<employee>{//----------->泛型里为实体类
    //------->必须继承 iservice<t>
	//分页方法：
    public ipage<employee> query(employeequery qo);
} 

public class employeeserviceimpl extends service<employeemapper,employee> implements iemployeeservice{
    //------------>service<employeemapper,employee>:实体类mapper接口；实体类
    //------->必须继承 service<tmapper,t>
   @override
    public ipage<employee> query(employeequery qo) {
        ipage<employee> page = new page<>(qo.getcurrentpage(), qo.getpagesize());  //设置分页信息
        querywrapper<employee> wrapper = wrappers.<employee>query();  //拼接条件
      
        //有条件约束写在这里：
    	
        return super.page(page,wrapper);
    }
   }


列：查询第2页员工信息， 每页显示3条， 按id排序
    @test
    public void testpage(){
        employeequery qo = new employeequery();
        qo.setpagesize(3);
        qo.setcurrentpage(2);
        ipage<employee> page = employeeservice.query(qo);
        system.out.println("当前页：" + page.getcurrent());
        system.out.println("总页数：" + page.getpages());
        system.out.println("每页显示条数：" + page.getsize());
        system.out.println("总记录数：" + page.gettotal());
        system.out.println("当前页显示记录：" + page.getrecords());
    }
//sql语句
select id,name,password,email,age,admin,dept_id from employee where (name like ?) limit ?,?

import lombok.data;

@data
public class pagequery  {
    /**
     * 当前页
     */
    private integer curpage;

    /**
     * 页大小
     */
    private integer pagesize;

}

import com.baomidou.mybatisplus.annotation.tableid;
import com.zhecre.jx.business.model.index.request.pagequery;
import lombok.data;

import java.util.date;
@data
public class compensateinfo extends pagequery {

    /**
     * 方案状态(1：未提交|2：已提交|3：已驳回|4：已审核)
     */
    private string status;

    /**
     * 方案编号
     */
    @tableid
    private string caseid;


    /**
     * 方案名称
     */
    private string casename;

    /**
     * 医院名称
     */
    private string hosname;

    private string casetype;

    private string remark;
    
    private date commitdate;
    
    private string type;

}

import com.baomidou.mybatisplus.core.metadata.ipage;
import com.zhecre.jx.business.model.index.response.compensateinfo;
import com.zhecre.jx.business.service.cases.icompensateinfoservice;
import lombok.requiredargsconstructor;
import org.springframework.beans.factory.annotation.autowired;
import org.springframework.web.bind.annotation.*;


@restcontroller
@requiredargsconstructor
@requestmapping("/compensation")
public class compensationschemescontroller {
    @autowired
    private final icompensateinfoservice compensateinfoservice;

    @getmapping("/index/query")
    public ipage<compensateinfo> compensatepage( compensateinfo info){
        
		//调用service层
        ipage<compensateinfo> query = compensateinfoservice.query(info,info.getcasename(),info.getcasetype());
        return query;
    }

}

public interface icompensateinfoservice extends iservice<compensateinfo> {

    //分页方法：
    ipage<compensateinfo> query(compensateinfo info, string casename, string casetype);
}

import com.baomidou.mybatisplus.core.metadata.ipage;
import com.baomidou.mybatisplus.extension.plugins.pagination.page;
import com.baomidou.mybatisplus.extension.service.impl.serviceimpl;
import com.zhecre.jx.business.mapper.cases.compensateinfomapper;
import com.zhecre.jx.business.model.index.response.compensateinfo;
import com.zhecre.jx.business.service.cases.icompensateinfoservice;
import org.springframework.beans.factory.annotation.autowired;
import org.springframework.stereotype.service;

@service
public class compensateinfoserviceimpl extends serviceimpl<compensateinfomapper, compensateinfo> implements icompensateinfoservice {
    @autowired
    private compensateinfomapper compensateinfomapper;
    
    @override
    public ipage<compensateinfo> query(compensateinfo qo, string casename, string casetype) {
        
        page<compensateinfo> page = new page<>(qo.getcurpage(), qo.getpagesize());//调用mybatisplus分页插件
        
        ipage<compensateinfo> queryvopage = compensateinfomapper.findbypage(page, qo,casename,casetype);//定义分页方法

        return queryvopage;
    }
}

public interface compensateinfomapper extends basemapper<compensateinfo> {
	//自定义分页方法
    ipage<compensateinfo> findbypage(page<compensateinfo> page, compensateinfo qo,
                                     @param("casename") string casename,@param("casetype") string casetype);//这是条件查询参数
}

<?xml version="1.0" encoding="utf-8"?>
<!doctype mapper
        public "-//mybatis.org//dtd mapper 3.0//en"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zhecre.jx.business.mapper.cases.compensateinfomapper">
    //将查寻结果对象字段属性和数据库字段对应
    <resultmap id="compensateinfomapper" type="com.zhecre.jx.business.model.index.response.compensateinfo">
        <id property="caseid" column="caseid"></id>
        <result property="status" column="status"></result>
        <result property="casename" column="casename"></result>
        <result property="casetype" column="casetype"></result>
        <result property="remark" column="remark"></result>
        <result property="hosname" column="hosname"></result>
        <result property="commitdate" column="commitdate"></result>
        <result property="type" column="type"></result>
    </resultmap>
    //查询语句:得到结果集
    <select id="findbypage" parametertype="com.zhecre.jx.business.model.index.response.compensateinfo" resultmap="compensateinfomapper">
            select
            c.status,
            c.caseid,
            c.casename,
            c.casetype,
            p.hosname,
            c.remark,
            c.commitdate,
            c.type
            from
            pas_case c,
            pas_case_hospital p
        <where>
         and c.caseid = p.caseid
        <if test="casename !=null and casename != ''">
            and c.casename = #{casename}//传入的条件查数；如果传入的参数是对象的话要取对象中的某个属性,这个地方参数直接写成属性名即可，但是对象所属的类                                            必须提供getter方法；如果是普通变量字段，条件直接写成传入得字段
        </if>
        </if>
        <if test="casetype !=null and casetype != ''">
            and c.casetype = #{casetype}
        </if>
        </where>
        </select>
</mapper>