Spring Boot-5.0条件构造器

最新推荐文章于 2023-08-14 16:39:02 发布

鹅纷斐，

最新推荐文章于 2023-08-14 16:39:02 发布

阅读量321

点赞数

分类专栏： Spring 文章标签： mybatis mysql 数据库 spring boot sql

本文链接：https://blog.csdn.net/weixin_42181264/article/details/113864990

版权

Spring 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

接口方法的参数中，会出现各种Wrapper，比如 queryWrapper、updateWrapper等。Wrapper的作用就是用于定义各种各样的条件（where）。所以不管是查询、更新、删除都会用到Wrapper。

比较大小

eq(R column, Object val); // 等价于 =
ne(R column, Object val); // 等价于 <>
gt(R column, Object val); // 等价于 >
ge(R column, Object val); // 等价于 >=
lt(R column, Object val); // 等价于 <
le(R column, Object val); // 等价于 <=

范围

between(R column, Object val1, Object val2);//等价于between a and b,例：between(“age”, 18, 30) ->age between 18 and 30
notBetween(R column, Object val1, Object val2); // 等价于 not between a and b, 例： notBetween(“age”, 18, 30) —> age not between 18 and 30
in(R column, Object… values); // 等价于字段 IN (v0, v1, …),例: in(“age”,{1,2,3}) —> age in (1,2,3)
notIn(R column, Object… values); // 等价于字段 NOT IN (v0, v1, …), 例: notIn(“age”,{1,2,3}) —> age not in (1,2,3)
inSql(R column, Object… values); // 等价于字段 IN (sql 语句), 例: inSql(“id”, “select id from table where id < 3”) —> id in (select id from table where id < 3)
notInSql(R column, Object… values); // 等价于字段 NOT IN (sql 语句)

模糊匹配

like(R column, Object val); // 等价于 LIKE ‘%值%’，例: like(“name”, “王”) —> name like ‘%王%’
notLike(R column, Object val); // 等价于 NOT LIKE ‘%值%’，例: notLike(“name”, “王”) —> name not like ‘%王%’
likeLeft(R column, Object val); // 等价于 LIKE ‘%值’，例: likeLeft(“name”, “王”) —> name like ‘%王’
likeRight(R column, Object val); // 等价于 LIKE ‘值%’，例: likeRight(“name”, “王”) —> name like ‘王%’

分组、排序

groupBy(R… columns); // 等价于 GROUP BY 字段, …，例: groupBy(“id”, “name”) —> group by id,name
orderByAsc(R… columns); // 等价于 ORDER BY 字段, … ASC，例: orderByAsc(“id”, “name”) —> order by id ASC,name ASC
orderByDesc(R… columns); // 等价于 ORDER BY 字段, … DESC，例: orderByDesc(“id”, “name”) —> order by id DESC,name DESC
having(String sqlHaving, Object… params); // 等价于 HAVING ( sql语句 )，例: having(“sum(age) > {0}”, 11) —> having sum(age) > 11

拼接、嵌套sql

or(); // 等价于 a or b，例：eq(“id”,1).or().eq(“name”,“老王”) —> id = 1 or name = ‘老王’
or(Consumer consumer); // 等价于 or(a or/and b)，or 嵌套。例: or(i -> i.eq(“name”, “李白”).ne(“status”, “活着”)) —> or (name = ‘李白’ and status <> ‘活着’)
and(Consumer consumer); // 等价于 and(a or/and b)，and 嵌套。例: and(i -> i.eq(“name”, “李白”).ne(“status”, “活着”)) —> and (name = ‘李白’ and status <> ‘活着’)
nested(Consumer consumer); // 等价于 (a or/and b)，普通嵌套。例: nested(i -> i.eq(“name”, “李白”).ne(“status”, “活着”)) —> (name = ‘李白’ and status <> ‘活着’)
apply(String applySql, Object… params); // 拼接sql（若不使用 params 参数，可能存在 sql 注入），例: apply(“date_format(dateColumn,’%Y-%m-%d’) = {0}”, “2020-08-08”) —> date_format(dateColumn,’%Y-%m-%d’) = ‘2020-08-08’")
last(String lastSql); // 无视优化规则直接拼接到 sql 的最后，可能存若在 sql 注入。
exists(String existsSql); // 拼接 exists 语句。例: exists(“select id from table where age = 1”) —> exists (select id from table where age = 1)

查询条件

select(String... sqlSelect); // 用于定义需要返回的字段。例： select("id", "name", "age") ---> select id, name, age
select(Predicate<TableFieldInfo> predicate); // Lambda 表达式，过滤需要的字段。

在StudentController中添加以下请求

@RequestMapping("/selectWrapper")
    public List<Student> selectWrapper(){
        QueryWrapper<Student> wrapper=new QueryWrapper<>();
        //构建查询条件
        wrapper.select("id","name","sex","age")
                .lt("age",22)
                .like("sex","men");
        //执行查询
        return studentService.list(wrapper);
    }

查询结果
在这里插入图片描述
更新条件

set(String column, Object val); // 用于设置 set 字段值。例: set("name", null) ---> set name = null
setSql(String sql); // 用于设置 set 字段值。例: setSql("name = '老李头'") ---> set name = '老李头'
lambda(); // 返回一个 LambdaUpdateWrapper

在controller中添加以下代码：

@RequestMapping("/updateWrapper")
    public List<Student> updateWrapper(){
        //更新用户数据的wrapper
        UpdateWrapper<Student> wrapper=new UpdateWrapper<Student>();
        //修改数据的语句
        wrapper.set("sex","men");//使sex字段的值更新为men
        wrapper.setSql("age=age-"+1);//自定义的sql语句
        //条件
        wrapper.le("id",2);
        studentService.update(wrapper);
        return studentService.listByIds(Arrays.asList(1,2));
    }

原始数据：
在这里插入图片描述
执行条件查询之后

基本更新

@RequestMapping("/update")
    public Student update(Student student){
        System.out.println(student);
        studentService.updateById(student);
        return studentService.getById(student.getId());
    }

项目结构：
在这里插入图片描述

StudentController：

package com.wxf.springbootmybatisplus.controller;


import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.wxf.springbootmybatisplus.entity.Student;
import com.wxf.springbootmybatisplus.service.IStudentService;
import org.apache.catalina.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import java.util.Arrays;
import java.util.List;

/**
 * <p>
 *  前端控制器
 * </p>
 *
 * @author wxf
 * @since 2021-02-19
 */
@RestController
@RequestMapping("/student")
public class StudentController {

    @Autowired
    IStudentService studentService;
    @RequestMapping("/list")
    public List<Student> list(){
        return studentService.list();
    }

    @RequestMapping("/page")
    public IPage<Student> selectPage(int current,int size){
        IPage<Student > studentIPage=studentService.selectPage(current,size);
        return studentIPage;
    }

    @RequestMapping("/selectWrapper")
    public List<Student> selectWrapper(){
        QueryWrapper<Student> wrapper=new QueryWrapper<>();
        //构建查询条件
        wrapper.select("id","name","sex","age")
                .lt("age",22)
                .like("sex","men");
        //执行查询
        return studentService.list(wrapper);
    }

    @RequestMapping("/updateWrapper")
    public List<Student> updateWrapper(){
        //更新用户数据的wrapper
        UpdateWrapper<Student> wrapper=new UpdateWrapper<Student>();
        //修改数据的语句
        wrapper.set("sex","men");//使sex字段的值更新为men
        wrapper.setSql("age=age-"+1);//自定义的sql语句
        //条件
        wrapper.le("id",2);
        studentService.update(wrapper);
        return studentService.listByIds(Arrays.asList(1,2));
    }

//    @RequestMapping("/update")
//    public Student update(Student student){
//        System.out.println(student);
//        studentService.updateById(student);
//        return studentService.getById(student.getId());
//    }

}

StudentServiceImpl：

package com.wxf.springbootmybatisplus.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.wxf.springbootmybatisplus.entity.Student;
import com.wxf.springbootmybatisplus.mapper.StudentMapper;
import com.wxf.springbootmybatisplus.service.IStudentService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author wxf
 * @since 2021-02-19
 */
@Service
//@MapperScan("com.wxf.springbootmybatisplus.mapper")
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements IStudentService {
    @Autowired
    StudentMapper studentMapper;

    @Override
    public IPage<Student> selectPage(int current, int size) {
        // 根据Wrapper 自定义条件查询
        QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
        Page<Student> studentPage = new Page<Student>(current, size);
        //userPage.setCurrent(2L);  //当前是第几页 默认为1
        //userPage.setSize(2);  //每页大小
        IPage<Student> studentIPage = studentMapper.selectPage(studentPage, queryWrapper);
        return studentIPage;
    }
}

StudentMapper：

package com.wxf.springbootmybatisplus.mapper;

import com.wxf.springbootmybatisplus.entity.Student;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author wxf
 * @since 2021-02-19
 */
@Mapper
public interface StudentMapper extends BaseMapper<Student> {

}

IStudentService：

package com.wxf.springbootmybatisplus.service;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.wxf.springbootmybatisplus.entity.Student;
import com.baomidou.mybatisplus.extension.service.IService;

/**
 * <p>
 *  服务类
 * </p>
 *
 * @author wxf
 * @since 2021-02-19
 */

public interface IStudentService extends IService<Student> {
    //添加方法
    IPage<Student> selectPage(int current,int size);

}

SpringbootMybatisPlusApplication：

package com.wxf.springbootmybatisplus;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

@SpringBootApplication
@MapperScan("com.wxf.springbootmybatisplus.mapper")
public class SpringbootMybatisPlusApplication {

    /*
    * 分页插件
    * */
    @Bean
    public PaginationInterceptor paginationInterceptor(){
        PaginationInterceptor paginationInterceptor=new PaginationInterceptor();
        // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false
        // paginationInterceptor.setOverflow(false);
        // 设置最大单页限制数量，默认 500 条，-1 不受限制
        // paginationInterceptor.setLimit(500);
        // 开启 count 的 join 优化,只针对部分 left join
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        return paginationInterceptor;
    }
    //好了吧！！
    public static void main(String[] args) {
        SpringApplication.run(SpringbootMybatisPlusApplication.class, args);
    }

}

Student：

package com.wxf.springbootmybatisplus.entity;

import java.io.Serializable;
import lombok.Data;
import lombok.EqualsAndHashCode;

/**
 * <p>
 * 
 * </p>
 *
 * @author wxf
 * @since 2021-02-19
 */
@Data
@EqualsAndHashCode(callSuper = false)
public class Student implements Serializable {

    private static final long serialVersionUID = 1L;

    private Integer id;

    private String name;

    private String sex;

    private Integer age;


}

鹅纷斐，

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Spring Boot-5.0条件构造器

接口方法的参数中，会出现各种Wrapper，比如 queryWrapper、updateWrapper等。Wrapper的作用就是用于定义各种各样的条件（where）。所以不管是查询、更新、删除都会用到Wrapper。比较大小eq(R column, Object val); // 等价于 =ne(R column, Object val); // 等价于 <>gt(R column, Object val); // 等价于 >ge(R column, Object val)
复制链接

扫一扫