#Mybatis-plus示例和常见问题

# Mybatis-plus基础示例

# 数据库脚本

-- 建表语句
CREATE TABLE user1
(
    id INT PRIMARY KEY,
    name VARCHAR(30) NULL
);
-- 预置5条数据
INSERT INTO USER1 (id, name) VALUES
     (1, 'Jone'),
     (2, 'Jack'),
     (3, 'Tom'),
     (4, 'Sandy'),
     (5, 'Billie');

# 创建entity

创建User用户的entity，并使其与Table对应。

import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;

@Data
@TableName("user1")
public class User {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String name;
}

# 创建User的Mapper文件

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.samples.quickstart.entity.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface UserMapper extends BaseMapper<User> {
    List<User> selectUsers();
}

# 创建单元测试类

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.samples.quickstart.entity.User;
import com.baomidou.mybatisplus.samples.quickstart.mapper.UserMapper;
import com.zaxxer.hikari.HikariDataSource;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

@SpringBootTest
public class QuickStartTest {
    @Resource
    private UserMapper userMapper;

    @Test
    public void testSelect() {
        System.out.println(("----- selectAll method test ------"));
        List<User> userList = userMapper.selectList(null);
        userList.forEach(System.out::println);
    }

    @Test
    public void testSelect2(){
        User user = userMapper.selectById(1L);
        System.out.println(user);
    }
    
    @Test
    public void testInsertUser() {
        userMapper.insert(new User(1001L,"aaaa"));
        User user = userMapper.selectById(1001L);
        System.out.println(user);
    }

    @Test
    public void testSelect4(){
        QueryWrapper<User>  wrapper = new QueryWrapper<>();
        QueryWrapper<User> allEq = wrapper.allEq(new HashMap<String, Object>() {{
            put("id", 1);
            put("name", "Jone");
        }}, true);

        List<User> users = userMapper.selectList(allEq);
        users.forEach(System.out::println);
    }

    @Test
    public void testSelect5(){
        QueryWrapper<User>  wrapper = new QueryWrapper<>();
        QueryWrapper<User> eq = wrapper.eq("name", "Jone");
        List<User> users = userMapper.selectList(eq);
        users.forEach(System.out::println);
    }

    @Test
    public void testSelect6(){
        QueryWrapper<User>  wrapper = new QueryWrapper<>();
        QueryWrapper<User> ne = wrapper.ne("id", 1L);
        List<User> users = userMapper.selectList(ne);
        users.forEach(System.out::println);
    }

    @Test
    public void testSelect7(){
        QueryWrapper<User>  wrapper = new QueryWrapper<>();
        QueryWrapper<User> gt = wrapper.gt("id", 2L);
        List<User> users = userMapper.selectList(gt);
        users.forEach(System.out::println);
    }

    @Test
    public void testSelect8(){
        QueryWrapper<User>  wrapper = new QueryWrapper<>();
        QueryWrapper<User> ge = wrapper.ge("id", 2L);
        List<User> users = userMapper.selectList(ge);
        users.forEach(System.out::println);
    }

    @Test
    public void testSelect9(){
        QueryWrapper<User>  wrapper = new QueryWrapper<>();
        QueryWrapper<User> between = wrapper.between("id", 2L, 4L);
        List<User> users = userMapper.selectList(between);
        users.forEach(System.out::println);
    }

    @Test
    public void testSelect10(){
        QueryWrapper<User>  wrapper = new QueryWrapper<>();
        QueryWrapper<User> between = wrapper.notBetween("id", 3L, 4L);
        List<User> users = userMapper.selectList(between);
        users.forEach(System.out::println);
    }

    @Test
    public void testSelect11(){
        QueryWrapper<User>  wrapper = new QueryWrapper<>();
        QueryWrapper<User> like = wrapper.like("name", "J");
        List<User> users = userMapper.selectList(like);
        users.forEach(System.out::println);
    }

    @Test
    public void testSelect12(){
        QueryWrapper<User>  wrapper = new QueryWrapper<>();
        QueryWrapper<User> like = wrapper.notLike("name", "J");
        List<User> users = userMapper.selectList(like);
        users.forEach(System.out::println);
    }

    @Test
    public void testSelect13(){
        QueryWrapper<User>  wrapper = new QueryWrapper<>();
        QueryWrapper<User> like = wrapper.likeLeft("name", "J");
        List<User> users = userMapper.selectList(like);
        users.forEach(System.out::println);
    }

    @Test
    public void testSelect14(){
        QueryWrapper<User>  wrapper = new QueryWrapper<>();
        QueryWrapper<User> like = wrapper.likeRight("name", "J");
        List<User> users = userMapper.selectList(like);
        users.forEach(System.out::println);
    }

    @Test
    public void testSelect15(){
        QueryWrapper<User>  wrapper = new QueryWrapper<>();
        QueryWrapper<User> id = wrapper.isNotNull("id");
        List<User> users = userMapper.selectList(id);
        users.forEach(System.out::println);
    }

    @Test
    public void testSelect16(){
        QueryWrapper<User>  wrapper = new QueryWrapper<>();
        QueryWrapper<User> id = wrapper.isNull("id");
        List<User> users = userMapper.selectList(id);
        users.forEach(System.out::println);
    }

    @Test
    public void testSelect17(){
        QueryWrapper<User>  wrapper = new QueryWrapper<>();
        QueryWrapper<User> in = wrapper.in("id", new ArrayList<Long>(){{
            add(1L);
            add(2L);
        }});
        List<User> users = userMapper.selectList(in);
        users.forEach(System.out::println);
    }

    @Test
    public void testSelect18(){
        QueryWrapper<User>  wrapper = new QueryWrapper<>();
        QueryWrapper<User> inSql = wrapper.inSql("id", "select id from user1 where id < 4");
        List<User> users = userMapper.selectList(inSql);
        users.forEach(System.out::println);
    }

    @Test
    public void testSelect21(){
        QueryWrapper<User>  wrapper = new QueryWrapper<>();
        wrapper.func(i -> {
            if (false) {
                i.eq("id", 1);
            }else {
                i.eq("id", 2);
            }
        });

        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }

    @Test
    public void testSelect22(){
        QueryWrapper<User>  wrapper = new QueryWrapper<>();
        wrapper.eq("id", 1).or().likeRight("name", "J");
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }

    @Test
    public void testSelect23(){
        QueryWrapper<User>  wrapper = new QueryWrapper<>();
        wrapper.and(i -> i.eq("id", 1).likeRight("name", "J"));
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.out::println);
    }
}

# 常见问题

# 自增主键的使用

Mybatis-plus的Entity必须有主键，用@TableId注解来标识主键，其中id的type字段有五种取值（根据Mybatis-plus版本略有不同，具体请参考Mybatis-plus的官方文档，本文只作简单介绍）。

参数	含义
AUTO	自动主键，在没给具体值的情况下使用数据库的自增
NONE	无
INPUT	必须手动设置值，否则不能执行插入，更新等操作
ASSIGN_ID	不设置情况下默认为此类型，使用雪花算法自动生成long或者String类型的主键
ASSIGN_UUID	生成不带中划线的UUID

AUTO是最推荐的写法，比较简单灵活，只要表结构里设置了自增都可以用，而且使用数据库的自增不容易产生主键冲突。

@Data
@TableName("user1")
public class User {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String name;
}

Note:

如需使用自增主键，不建议使用INPUT，然后自定义主键生成器，再从数据库中查自增序列给id赋值，这样的写法麻烦且效率低，而且还容易出错。

ASSIGN_ID和ASSIGN_UUID都是Java里自动生成主键，和数据库的自增主键无关。

# 批量插入

Mybatis-plus提供了ServiceImpl类，只要我们自己的Service继承ServiceImpl就可以使用里面预置的常用方法，例如saveBatch等。

UserService的定义：

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.dao.UserMapper;
import com.example.pojo.User;

import org.springframework.stereotype.Service;

@Service
@EnableCaching(proxyTargetClass = true)
public class UserServiceImpl extends ServiceImpl<UserMapper,User> {

}

test写法：


    @Autowired
    private UserService userService;
    
    @Test
    public void testAddUserList() {
        List<User> list = new ArrayList<>();
        User user1 = new User();
        user1.setName("aaa");
        list.add(user1);
        User user2 = new User();
        user2.setName("aaa1");
        list.add(user2);
        userService.saveBatch(list); // 不需要我们在UserServiceImpl里面定义saveBatch方法就可以使用
        List<User> list1 = userService.getUserList();
        list1.forEach(System.out::println);
    }

# SQL语句结束符

SQL语句的结束符并不固定，具体取决于使用的数据库管理系统和执行环境。通常情况下，SQL语法规则要求SQL语句必须采用分号 (;) 作为结束符，但在Mybatis-plus环境中SQL语句无需结束符。