java代码中进行数据库相关操作

一、引入依赖

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.4</version>
</dependency>

二、创建LambdaQueryWrapper对象

LambdaQueryWrapper是MyBatis-Plus提供的一种查询构建器；

LambdaQueryWrapper<TestVo> queryWrapper = new LambdaQueryWrapper<>();

其中一些常用方法：

三、一些示例

正常情况默认and连接：

//Student是表的实体类
LambdaQueryWrapper<Student> queryWrapper = new LambdaQueryWrapper<Student>()
                .eq(ObjectUtils.isNotEmpty(testDto.getType()), Student::getType, testDto.getType())
                .eq(ObjectUtils.isNotEmpty(testDto.getId()), Student::getId, testDto.getId())
                .like(ObjectUtils.isNotEmpty(testDto.getTitle()), Student::getTitle, testDto.getTitle())
                .between(ObjectUtils.isNotEmpty(testDto.getTimeBegin()) && ObjectUtils.isNotEmpty(testDto.getTimeEnd()) , Student::getTime, testDto.getTimeBegin(), testDto.getTimeEnd());
        
        
String existsSql = "EXISTS (SELECT 1 FROM student stu WHERE stu.name='" + testDto.getName() + "')";
queryWrapper = queryWrapper.apply(existsSql);

studentMapper.selectList(queryWrapper)

使用or连接的话：

LambdaQueryWrapper<Student> wrapper = Wrappers.lambdaQuery();
 
// SELECT * FROM Student WHERE (name = "小明" AND id = 3)
wrapper.eq(Student::getName, "小明").and().eq(Student::getId , 3);
 
// SELECT * FROM Student WHERE (name = "小明" OR id = 3)       
wrapper.eq(Student::getName, "小明").or().eq(Student::getId , 3);


//多个or和and一起用时
//select * from WHERE id = '3' AND (name LIKE '%明%' OR score LIKE '%83' OR phone LIKE '%3%')
LambdaQueryWrapper<Student> wrapper = new LambdaQueryWrapper<>();
    wrapper.eq(Student::getId , 3);
    wrapper.and(wrapper->wrapper.like(Student::getName,"明")
                                .or()
                                .likeLift(Student::getScore,"83")
                                .or()
                                .like(Student::getPhone , 3));

计数

//统计数量
LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.eq(User::getUsername, "小明");
int count = userMapper.selectCount(queryWrapper);

 

四、创建LambdaUpdateWrapper对象

LambdaUpdateWrapper<Student> updateWrapper = new LambdaUpdateWrapper<>();

例如： 

studentMapper.update(new LambdaUpdateWrapper<Student>()
                .set(Student::getId, "1")
                .eq(Student::getName, "小明"));

五、UpdateWrapper和LambdaUpdateWrapper

UpdateWrapper和LambdaUpdateWrapper都是MyBatis-Plus提供的封装SQL语法的工具类

UpdateWrapper使用传统的getter/setter的方式构造查询条件，需要手动指定字段名；

LambdaUpdateWrapper使用Lambda表达式构造查询条件，并可以使用setSql进行自定义sql语句

UpdateWrapper<Student> updateWrapper = new UpdateWrapper<>();
updateWrapper.eq("name", "小明")
             .set("age", 18);


LambdaUpdateWrapper<Student> lambdaUpdateWrapper = new LambdaUpdateWrapper<>();
lambdaUpdateWrapper.eq(Student::getName, "小明")
                   .set(Student::getAge, 18);
                   .setSql("status = 1");