mybatisplus 示例练习2 Wrapper

新建springboot工程

导入依赖

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

 配置

application.properties

#springboot默认数据源
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimeZone=UTC
spring.datasource.username=root
spring.datasource.password=mysql123
#默认日志
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
#数据库表前缀
mybatis-plus.global-config.db-config.table-prefix=p_
#主键自增策略，mysql自增
mybatis-plus.global-config.db-config.id-type=auto

数据库，建表

drop table if exists p_user;
create table p_user(
	uid bigint(20) not null auto_increment,
	user_name varchar(20) not null,
	age int(11),
	email varchar(50),
	is_deleted tinyint default 0 comment '逻辑删除,0未删除，1删除',
	primary key(uid)
);

insert into p_user(uid,user_name,age,email) values (null,'a1',22,'xx@xx.com');
insert into p_user(uid,user_name,age,email) values (null,'a2',20,'xx@xx.com');
insert into p_user(uid,user_name,age,email) values (null,'b1',23,'xx@xx.com');
insert into p_user(uid,user_name,age,email) values (null,'b2',10,'xx@xx.com');
insert into p_user(uid,user_name,age,email) values (null,'c3',30,'xx@xx.com');

实体类

User.java

package com.shrimpking.pojo;


import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/6/10 10:53
 */
@Data
@TableName("p_user")
public class User
{
    /**
     * @TableId注解，表示数据库主键字段的名称，
     * type表示主键采取mysql自增
     * IdType = ASSIGN_ID就是雪花算法
     */
    @TableId(value = "uid",type = IdType.AUTO)
    private Long id;

    /**
     * 指定对应数据库表的字段名称
     */
    @TableField("user_name")
    private String name;

    private Integer age;

    private String email;

    /**
     * 指定逻辑删除的字段
     */
    @TableLogic
    private Integer isDeleted;
}

Mapper接口

UserMapper.java

package com.shrimpking.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.shrimpking.pojo.User;
import org.springframework.stereotype.Repository;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/6/10 11:00
 */
@Repository
public interface UserMapper extends BaseMapper<User>
{

}

UserMapper.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.shrimpking.mapper.UserMapper">



</mapper>

UserService.java

package com.shrimpking.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.shrimpking.pojo.User;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/6/10 11:03
 */

public interface UserService extends IService<User>
{
}

UserServiceImpl.java

package com.shrimpking.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.shrimpking.mapper.UserMapper;
import com.shrimpking.pojo.User;
import com.shrimpking.service.UserService;
import org.springframework.stereotype.Service;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/6/10 11:04
 */
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService
{

}

测试

MapperTest.java

package com.shrimpking;

import com.shrimpking.mapper.UserMapper;
import com.shrimpking.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/6/10 11:29
 */
@SpringBootTest
public class MapperTest
{
    @Autowired
    private UserMapper userMapper;

    @Test
    public void testInsert()
    {
        //INSERT INTO p_user ( name, age ) VALUES ( ?, ? )
        //INSERT INTO p_user ( user_name, age ) VALUES ( ?, ? )
        User user = new User();
        user.setName("zz");
        user.setAge(33);
        int result = userMapper.insert(user);
        System.out.println("result:" + result);
        System.out.println(user.getId());
    }

    @Test
    public void testDelete()
    {
        //UPDATE p_user SET is_deleted=1 WHERE uid=? AND is_deleted=0
        //逻辑删除一个用户
        int result = userMapper.deleteById(1L);
        System.out.println("result:" + result);
    }

    @Test
    public void testSelect()
    {
        //SELECT uid AS id,user_name AS name,age,email,is_deleted FROM p_user WHERE is_deleted=0
        //开启逻辑删除后，查询用户自动屏蔽已删除的
        List<User> userList = userMapper.selectList(null);
        userList.forEach(System.out::println);
    }


}

ServiceTest.java

package com.shrimpking;

import com.shrimpking.service.UserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/6/10 11:05
 */
@SpringBootTest
public class ServiceTest
{

    @Autowired
    private UserService userService;

    @Test
    public void test()
    {
        //SELECT COUNT( * ) FROM p_user
        //查询总记录
        long count = userService.count();
        System.out.println(count);
    }
}

WrapperTest.java

package com.shrimpking;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.shrimpking.mapper.UserMapper;
import com.shrimpking.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/6/10 12:05
 */
@SpringBootTest
public class wrapperTest
{
    @Autowired
    private UserMapper userMapper;

    @Test
    public void test1()
    {
        //查询用户名中包含字母a，年龄在20到30岁之间，且邮箱不为空的用户
        //SELECT uid AS id,user_name AS name,age,email,is_deleted
        // FROM p_user WHERE is_deleted=0
        // AND (user_name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("user_name","a")
                .between("age",20,30)
                .isNotNull("email");
        List<User> userList = userMapper.selectList(queryWrapper);
        for (User user : userList)
        {
            System.out.println(user);
        }

    }

    @Test
    public void test2()
    {
        //查询用户，先按照年龄进行降序排序，在按照id升序排序
        //SELECT uid AS id,user_name AS name,age,email,is_deleted
        // FROM p_user WHERE is_deleted=0 ORDER BY age DESC,uid ASC
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByDesc("age")
                .orderByAsc("uid");
        List<User> userList = userMapper.selectList(queryWrapper);
        for (User user : userList)
        {
            System.out.println(user);
        }
    }

    @Test
    public void test3()
    {
        //删除年龄小于10岁的用户
        //UPDATE p_user SET is_deleted=1 WHERE is_deleted=0 AND (age <= ?)
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.le("age",10);
        int result = userMapper.delete(queryWrapper);
        System.out.println("result:" + result);

    }


    @Test
    public void test4()
    {
        //修改年龄大于等于20，用户名包含字母a，或邮箱为空的用户
        //UPDATE p_user SET user_name=?, email=? WHERE is_deleted=0
        // AND (age >= ? AND user_name LIKE ? OR email IS NULL)
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.ge("age",20)
                .like("user_name","a")
                .or()
                .isNull("email");
        User user = new User();
        user.setName("修改");
        user.setEmail("zhang@163.com");
        int result = userMapper.update(user, queryWrapper);
        System.out.println("result:" + result);

    }

    @Test
    public void test5()
    {
        //修改用户名中包含字母b且（年龄大于20或邮箱为空）的用户
        //UPDATE p_user SET user_name=?, email=? WHERE is_deleted=0
        // AND (user_name LIKE ? AND (age >= ? OR email IS NULL))
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("user_name","b")
                .and(
                        param->param.ge("age",20)
                                .or()
                                .isNull("email")
                );
        User user = new User();
        user.setName("李四");
        user.setEmail("lisi@qq.com");
        int result = userMapper.update(user, queryWrapper);
        System.out.println("result:" + result);

    }

    @Test
    public void test6()
    {
        //只查询某些字段
        //SELECT user_name,age,email FROM p_user WHERE is_deleted=0
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("user_name","age","email");
        List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
        maps.forEach(System.out::println);
    }

    @Test
    public void test7()
    {
        //如何子查询
        //查询用户id小于100 的用户
        //SELECT uid AS id,user_name AS name,age,email,is_deleted
        // FROM p_user WHERE is_deleted=0
        // AND (uid IN (select uid from p_user where uid<=100))
        QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
        queryWrapper.inSql("uid","select uid from p_user where uid<=100");
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);

    }

    @Test
    public void test8()
    {
        //如何使用udateWrapper
        //修改用户名包含c ，(年龄大于20或邮箱为空)的用户
        //UPDATE p_user SET user_name=?,email=? WHERE is_deleted=0
        // AND (user_name LIKE ? AND (age > ? OR email IS NULL))
        UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper.like("user_name","c")
                .and(
                        param->param.gt("age",20)
                        .or()
                        .isNull("email")
                );
        updateWrapper.set("user_name","曹操").set("email","cc@cc.com");
        int result = userMapper.update(null, updateWrapper);
        System.out.println("result:" + result);

    }

    @Test
    public void test9()
    {
        //模拟浏览器传值
        String username = "";
        Integer ageBegin = 20;
        Integer ageEnd = 30;

        //SELECT uid AS id,user_name AS name,age,email,is_deleted
        // FROM p_user WHERE is_deleted=0 AND (age >= ? AND age <= ?)
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        if(StringUtils.isNotBlank(username))
        {
            //判断某个字符串，是否不为空字符串，不为null，不为空白符
            queryWrapper.like("user_name",username);
        }
        if(ageBegin != null)
        {
            queryWrapper.ge("age",ageBegin);
        }
        if(ageEnd != null)
        {
            queryWrapper.le("age",ageEnd);
        }
        List<User> userList = userMapper.selectList(queryWrapper);
        userList.forEach(System.out::println);


    }

    @Test
    public void test10()
    {
        //模拟浏览器传值
        //优化上面的方法
        String username = "";
        Integer ageBegin = 20;
        Integer ageEnd = 30;

        //SELECT uid AS id,user_name AS name,age,email,is_deleted
        // FROM p_user WHERE is_deleted=0 AND (age >= ? AND age <= ?)
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like(StringUtils.isNotBlank(username),"user_name",username)
                .ge(ageBegin != null,"age",ageBegin)
                .le(ageEnd != null,"age",ageEnd);
        List<User> userList = userMapper.selectList(queryWrapper);
        for (User user : userList)
        {
            System.out.println(user);
        }
    }

    @Test
    public void test11()
    {
        //模拟浏览器传值
        String username = "";
        Integer ageBegin = 20;
        Integer ageEnd = 30;

        //使用拉姆达查询构造器，字段名称容易写错，改用拉姆达获取属性的方式
        //SELECT uid AS id,user_name AS name,age,email,is_deleted
        // FROM p_user WHERE is_deleted=0 AND (age >= ? AND age <= ?)
        LambdaQueryWrapper<User> lambdaQueryWrapper
                = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.like(StringUtils.isNotBlank(username),User::getName,username)
                .ge(ageBegin != null,User::getAge,ageBegin)
                .le(ageEnd != null,User::getAge,ageEnd);
        List<User> userList = userMapper.selectList(lambdaQueryWrapper);
        userList.forEach(System.out::println);

    }

    @Test
    public void test12()
    {
        //使用拉姆达构造器，如何修改用户
        //修改年龄大于20，用户名包含修，或者邮箱为空的用户
        //UPDATE p_user SET user_name=?,email=? WHERE is_deleted=0
        // AND (user_name LIKE ? AND age >= ? OR email IS NULL)
        LambdaUpdateWrapper<User> lambdaUpdateWrapper
                = new LambdaUpdateWrapper<>();
        lambdaUpdateWrapper.like(User::getName,"修")
                .ge(User::getAge,20)
                .or()
                .isNull(User::getEmail);
        lambdaUpdateWrapper.set(User::getName,"再改")
                .set(User::getEmail,"zai@zai.com");

        int result = userMapper.update(null, lambdaUpdateWrapper);
        System.out.println("result:" + result);

    }

}