MyabtisPlus(高级查询)

Myabtis-Plus(高级查询)

在这里插入图片描述

函数名说明例子
eq等于 =例:eq(“name”,“张三”) :name = ‘张三’
ne不等于<>例: eq(“name”,“老王”)—>name = ‘老王’
gt大于>例:gt(“age”,18)—>age > 18
ge大于等于>=例:ge(“age”,18)—>age >= 18
lt小于<例:lt(“age”,18)—>age < 18
le小于<=例:le(“age”,18)—>age <= 18
betweenBETWEEN值1 AND值2例:between(“age”,18,30)—>age between 18 and 30
notBetweenNOT BETWEEN值1 AND值2例: notBetween(“age”,18,30)—>age not between 18 and 30
likeLIKE ‘%值%’例: like(“name”,“王”)—–>name like '%王%’
notLikeNOT LIKE ‘%值%’例: notLike (“name”,“王”)—>name not like '%王%’
likeLeftLIKE '%值’例:likeLeft (“name”,“王”)—–>name like '%王’
likeRightLIKE’值%’例: likeRight(“name”,“王”)—>name like ‘王%’
isNull字段IS NULL例: isNul1 (“name”)—>name is null
isNotNull字段IS NOT NULL例: isNotNull(“name”)—>name is not null
in字段IN (v0, v1,…)例: in(“age”,{1,2,3} )—–>age in (1,2,3)
notIn字段NOT IN (v0, v1,…)例: notIn(“age”,1,2,3)—>age not in (1,2,3)
inSql字段IN ( sql语句)inSql(“id”, “select id from table where id < 3”) —–>id in (select id from table where id < 3)
notInSql字段NOT IN ( sql语句)notInSql(“id”, “select id from table where id < 3”) —–>age not in (select id from table where id < 3)
groupBy分组:GROUP BY 字段,…例: groupBy(“id”, “name”)—>group by id, name
orderByAsc排序:ORDER BY字段,… ASC例: orderByAsc(“id”, “name”)—>order by id ASC, name ASC
orderByDesc排序:ORDER BY 字段,…DESC例: orderByDesc(“id”, “name”)—>order by id DESC, name DESC
orderBy排序:ORDER BY字段,…例: orderBy(true,true,“id”,“name”)—–>order by id ASC, name ASC
havingHAVING ( sql语句)having(“sum(age) >{0}”,11)—>having sum(age) > 11
or拼接OR主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)例:eq(“id”,1).or().eq(“name”,“老王”)—>id = 1 or name = '老王
andAND嵌套例: and(i -> i.eq(“name”,“李白”).ne(“status”,“活着”))—>and (name ='李白’ and status ’活着’)
apply拼接sql该方法可用于数据库函数动态入参的params对应前面sqlHaving内部的{index}部分.这样是不会有sql注入风险的,反之会有! 例: apply(“date_format(dateColumn, ‘%Y一%m-%d’) ={0}”, “2023-08-08”)—>date_format(dateColumn,’%Y一%m-%d’) = ‘2023-08-08’")
last无视优化规则直接拼接到sql 的最后无视优化规则直接拼接到sql 的最后只能调用一次,多次调用以最后一次为准有sql注入的风险,请谨慎使用例: last(“limit 1”)
exists拼接EXISTS ( sql语句)exists (select id from table where age = 1) 例: notExists(“select id from table where age = 1”) —>exists (select id from table where age = 1)
notExists拼接NOT EXISTS ( sql语句)例: notExists(“select id from table where age = 1”)—–>not exists (select id from table where age = 1)
nested正常嵌套不带AND或者 OR正常嵌套不带AND或者OR例: nested(i -> i.eq(“name”,“李白”).ne(“status”,“高兴”))—>(name = '李白’and status 高兴’)

一、列投影(select):

​ 只想看到查询到的数据中部分字段数据。

例: 查询所有学生信息,返回name age 列。
    QueryWrapper<USer> wrapper = new QueryWrapper<>();

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

    usermapper.selectlist(wrapper)
          

二、排序与分组:

1.1、排序(orderByAsc/orderByDesc):

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用法一样, 不过是倒序排

1.2、定制排序(orderBy):

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

三、分组查询(groupBy):

方法:
(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 

四、过滤条件(having):

方法:
(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. 等于 不等于

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< >'张三嗯'

2.大于 等于 小于

(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 
    

六、批量插入或更新数据

方式一:mybatis-plus 的saveOrUpdateBatch方法

继承BaseMapper即可,泛型使用当前要操作类
@Mapper
public interface HhChainCustomerInfoMapper extends BaseMapper<HhChainCustomerInfo> {
    
 
    /**
    * 使用mybatis-plus方式调用saveOrUpdateBatch不需要写这个接口
    boolean saveOrUpdateBatch(@Param("entities") Collection<HhChainCustomerInfo> hhChainCustomerInfos);
    */
 
}
Service 层继承 IService即可,泛型使用当前要操作类
/**
 * 链路客户信息Service接口
 *
 * @author js
 * @date 2023-09-10
 */
public interface IHhChainCustomerInfoService extends IService<HhChainCustomerInfo> {
    

}
Service 实现类 层继承 ServiceImpl即可,泛型使用当前要操作类
@Service
public class HhChainCustomerInfoServiceImpl extends ServiceImpl<HhChainCustomerInfoMapper, HhChainCustomerInfo> implements IHhChainCustomerInfoService {
 
 
    /*@Override
    public boolean saveOrUpdateBatch(Collection<HhChainCustomerInfo> entityList) {
        return hhChainCustomerInfoMapper.saveOrUpdateBatch(entityList);
    }*/
 
}

七、(单表)分页+高级查询:

步骤:

第一步:定义一个QueryObject类:
@Setter
@Getter
public class QueryObject {
    private int CurrentPage=1;
    private int PageSize=1;
    
}
第二步:定义一个QueryObject类:
@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;
    }
第四步:在Service接口层中定义分页方法:
public interface  IEmployeeService extends IService<Employee>{//----------->泛型里为实体类
    //------->必须继承 IService<T>
	//分页方法:
    public IPage<Employee> query(EmployeeQuery qo);
} 
第五步:在Service接口实现类中实现分页方法:
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 ?,?

八、联表分页查询+条件查询:

对于一对一,一对多,多对一,还是两表之间通过主外键关联查询,都可以;

总结:
     第一步:不管是那种情况,首先将响应给前端的数据封装成一个类并继承(PageQuery)
     第二步:按以下步骤做

步骤:
1.编写:请求Dto层:
import lombok.Data;

@Data
public class PageQuery  {
    /**
     * 当前页
     */
    private Integer curPage;

    /**
     * 页大小
     */
    private Integer pageSize;

}
2.编写封装响应给前端数据to
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;

}

3.定义controller层:
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;
    }

}

4.编写service 层:
public interface ICompensateInfoService extends IService<CompensateInfo> {

    //分页方法:
    IPage<CompensateInfo> query(CompensateInfo info, String casename, String casetype);
}

5.实现service层:
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;
    }
}

6.定义Mapper层:
public interface CompensateInfoMapper extends BaseMapper<CompensateInfo> {
	//自定义分页方法
    IPage<CompensateInfo> findByPage(Page<CompensateInfo> page, CompensateInfo qo,
                                     @Param("casename") String casename,@Param("casetype") String casetype);//这是条件查询参数
}
7.编写Mapper.xml:
<?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>

十四、事务操作:

在service层实现类上面加一个@Thansaction注解。

  • 6
    点赞
  • 39
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值