mybatis-plus内置 CRUD

最新推荐文章于 2023-10-18 09:50:00 发布

@Autowire

最新推荐文章于 2023-10-18 09:50:00 发布

阅读量164

点赞数

本文链接：https://blog.csdn.net/zs18753479279/article/details/118572980

版权

在这里插入图片描述

DROP TABLE IF EXISTS user;

CREATE TABLE user
(
	id BIGINT(20) NOT NULL COMMENT '主键ID',
	name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
	age INT(11) NULL DEFAULT NULL COMMENT '年龄',
	email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (id)
);

DELETE FROM user;

INSERT INTO user (id, name, age, email) VALUES
(1, 'Jone', 18, 'test1@baomidou.com'),
(2, 'Jack', 20, 'test2@baomidou.com'),
(3, 'Tom', 28, 'test3@baomidou.com'),
(4, 'Sandy', 21, 'test4@baomidou.com'),
(5, 'Billie', 24, 'test5@baomidou.com');

1实体类

package com.zs.testmybatisplus.entity;

import com.baomidou.mybatisplus.annotation.TableField;

import lombok.Data;
import lombok.experimental.Accessors;

/**
 * <p>
 * 用户实体对应表 user
 * </p>
 *
 * @author hubin
 * @since 2018-08-11
 */
@Data
@Accessors(chain = true)
public class User {

    private Long id;
    private String name;
    private Integer age;
    private String email;
    @TableField(exist = false)
    private String ignoreColumn = "ignoreColumn";

    @TableField(exist = false)
    private Integer count;
}

2Mapper

package com.zs.testmybatisplus.mapper;


import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zs.testmybatisplus.entity.User;

/**
 * <p>
 * MP 支持不需要 UserMapper.xml 这个模块演示内置 CRUD 咱们就不要 XML 部分了
 * </p>
 *
 * @author hubin
 * @since 2018-08-11
 */
public interface UserMapper extends BaseMapper<User> {

}

3测试类

package com.zs.testmybatisplus;

import static org.assertj.core.api.Assertions.assertThat;

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

import javax.annotation.Resource;

import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zs.testmybatisplus.entity.User;
import com.zs.testmybatisplus.mapper.UserMapper;


/**
 * <p>
 * 内置 CRUD 演示
 * </p>
 *
 * @author hubin
 * @since 2018-08-11
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class SampleTest {

    @Resource
    private UserMapper mapper;

    @Test
    public void aInsert() {
        User user = new User();
        user.setName("小羊");
        user.setAge(3);
        user.setEmail("abc@mp.com");
        /**
         * 成功直接拿回写的 ID
         * ==>  Preparing: INSERT INTO user ( id, name, age, email ) VALUES ( ?, ?, ?, ? )
         * ==> Parameters: 1412944721606373377(Long), 小羊(String), 3(Integer), abc@mp.com(String)
         */
        assertThat(mapper.insert(user)).isGreaterThan(0);
        assertThat(user.getId()).isNotNull();
    }


    @Test
    public void bDelete() {
        /**
         * ==>  Preparing: DELETE FROM user WHERE id=?
         * ==> Parameters: 3(Long)
         */
       mapper.deleteById(3L);
        /**
         * ==>  Preparing: DELETE FROM user WHERE (name = ?)
         * ==> Parameters: Sandy(String)
         */
        mapper.delete(new QueryWrapper<User>().lambda()
                .eq(User::getName, "Sandy"));
    }


    @Test
    public void cUpdate() {

        /**
         * ==>  Preparing: UPDATE user SET email=? WHERE id=?
         * ==> Parameters: ab@c.c(String), 1(Long)
         */
        mapper.updateById(new User()
                .setId(1L)
                .setEmail("ab@c.c"));
        /**
         * ==>  Preparing: UPDATE user SET name=?, age=? WHERE (id = ?)
         * ==> Parameters: mp(String), 3(Integer), 2(Integer)
         */
        mapper.update(
                new User().setName("mp"),
                Wrappers.<User> lambdaUpdate()
                        .set(User::getAge, 3)
                        .eq(User::getId, 2)
        );
        /**
         * ==>  Preparing: SELECT id,name,age,email FROM user WHERE id=?
         * ==> Parameters: 2(Integer)
         */
        User user = mapper.selectById(2);
        assertThat(user.getAge()).isEqualTo(3);
        assertThat(user.getName()).isEqualTo("mp");
        /**
         ==>  Preparing: UPDATE user SET email=? WHERE (id = ?)
         ==> Parameters: null, 2(Integer)
         */
        mapper.update(
                null,
                Wrappers.<User>lambdaUpdate()
                        .set(User::getEmail, null)
                        .eq(User::getId, 2)
        );
        /**
         * ==>  Preparing: SELECT id,name,age,email FROM user WHERE id=?
         * ==> Parameters: 1(Integer)
         */
        assertThat(mapper.selectById(1).getEmail()).isEqualTo("ab@c.c");
        user = mapper.selectById(2);
        assertThat(user.getEmail()).isNull();
        assertThat(user.getName()).isEqualTo("mp");

        /**
         * ==>  Preparing: UPDATE user SET email=? WHERE (id = ?)
         * ==> Parameters: miemie@baomidou.com(String), 2(Integer)
         */
        mapper.update(
                new User().setEmail("miemie@baomidou.com"),
                new QueryWrapper<User>().lambda()
                        .eq(User::getId, 2)
        );
        user = mapper.selectById(2);
        assertThat(user.getEmail()).isEqualTo("miemie@baomidou.com");

        mapper.update(
                new User().setEmail("miemie2@baomidou.com"),
                Wrappers.<User>lambdaUpdate()
                        .set(User::getAge, null)
                        .eq(User::getId, 2)
        );
        user = mapper.selectById(2);
        assertThat(user.getEmail()).isEqualTo("miemie2@baomidou.com");
        assertThat(user.getAge()).isNull();
    }


    @Test
    public void dSelect() {
        /**
         * ==>  Preparing: INSERT INTO user ( id, name, age, email ) VALUES ( ?, ?, ?, ? )
         * ==> Parameters: 10086(Long), miemie(String), 3(Integer), miemie@baomidou.com(String)
         */
        mapper.insert(
                new User().setId(10087L)
                        .setName("miemie")
                        .setEmail("miemie@baomidou.com")
                        .setAge(3));
        /**
         * ==>  Preparing: SELECT id,name,age,email FROM user WHERE id=?
         * ==> Parameters: 10086(Long)
         */
        assertThat(mapper.selectById(10086L)
                .getEmail()).isEqualTo("miemie@baomidou.com");
        /**
         * ==>  Preparing: SELECT id,name,age,email FROM user WHERE (id = ?)
         * ==> Parameters: 10086(Integer)
         */
        User user = mapper.selectOne(new QueryWrapper<User>().lambda()
                .eq(User::getId, 10086));

        assertThat(user.getName()).isEqualTo("miemie");
        assertThat(user.getAge()).isEqualTo(3);
        /**
         * ==>  Preparing: SELECT id FROM user
         */
        mapper.selectList(Wrappers.<User>lambdaQuery()
                .select(User::getId)).forEach(x -> {
                    assertThat(x.getId()).isNotNull();
                    assertThat(x.getEmail()).isNull();
                    assertThat(x.getName()).isNull();
                    assertThat(x.getAge()).isNull();
                });
        /**
         * ==>  Preparing: SELECT id,name FROM user
         */
        mapper.selectList(new QueryWrapper<User>()
                .select("id", "name")).forEach(x -> {
                    assertThat(x.getId()).isNotNull();
                    assertThat(x.getEmail()).isNull();
                    assertThat(x.getName()).isNotNull();
                    assertThat(x.getAge()).isNull();
                });
    }

    @Test
    public void orderBy() {
        /**
         * ==>  Preparing: SELECT id,name,age,email FROM user ORDER BY age ASC
         */
        List<User> users = mapper.selectList(Wrappers.<User>query()
                .orderByAsc("age"));
        assertThat(users).isNotEmpty();
        //多字段排序
        /**
         * ==>  Preparing: SELECT id,name,age,email FROM user ORDER BY age ASC,name ASC
         */
        List<User> users2 = mapper.selectList(Wrappers.<User>query()
                .orderByAsc("age","name"));
        assertThat(users2).isNotEmpty();
        //先按age升序排列，age相同再按name降序排列
        /**
         * ==>  Preparing: SELECT id,name,age,email FROM user ORDER BY age ASC,name DESC
         */
        List<User> users3 = mapper.selectList(Wrappers.<User>query()
                .orderByAsc("age")
                .orderByDesc("name"));
        assertThat(users3).isNotEmpty();
    }

    @Test
    public void selectMaps() {
        /**
         * ==>  Preparing: SELECT id,name,age,email FROM user ORDER BY age ASC
         */
        List<Map<String, Object>> mapList = mapper.selectMaps(Wrappers.<User>query()
                .orderByAsc("age"));
        assertThat(mapList).isNotEmpty();
        assertThat(mapList.get(0)).isNotEmpty();
        System.out.println(mapList.get(0));
    }

    @Test
    public void selectMapsPage() {
        /**
         * ==>  Preparing: SELECT id,name,age,email FROM user ORDER BY age ASC
         */
        IPage<Map<String, Object>> page = mapper.selectMapsPage(new Page<>(1, 5),
                Wrappers.<User>query()
                        .orderByAsc("age"));
        assertThat(page).isNotNull();
        assertThat(page.getRecords()).isNotEmpty();
        assertThat(page.getRecords().get(0)).isNotEmpty();
        System.out.println(page.getRecords().get(0));
    }

    @Test
    public void orderByLambda() {
        /**
         * ==>  Preparing: SELECT id,name,age,email FROM user ORDER BY age ASC
         */
        List<User> users = mapper.selectList(Wrappers.<User>lambdaQuery()
                .orderByAsc(User::getAge));
        assertThat(users).isNotEmpty();
        //多字段排序
        List<User> users2 = mapper.selectList(Wrappers.<User>lambdaQuery()
                .orderByAsc(User::getAge,User::getName));
        assertThat(users2).isNotEmpty();
        //先按age升序排列，age相同再按name降序排列
        List<User> users3 = mapper.selectList(Wrappers.<User>lambdaQuery()
                .orderByAsc(User::getAge)
                .orderByDesc(User::getName));
        assertThat(users3).isNotEmpty();
    }

    @Test
    public void testSelectMaxId() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        /**
         * ==>  Preparing: SELECT max(id) as id FROM user
         */
        wrapper.select("max(id) as id");
        User user = mapper.selectOne(wrapper);
        System.out.println("maxId=" + user.getId());
        List<User> users = mapper.selectList(Wrappers.<User>lambdaQuery().orderByDesc(User::getId));
        Assert.assertEquals(user.getId().longValue(), users.get(0).getId().longValue());
    }

    @Test
    public void testGroup() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        /**
         * ==>  Preparing: SELECT age, count(*) FROM user GROUP BY age
         */
        wrapper.select("age, count(*)")
                .groupBy("age");
        /**
         * ==>  Preparing: SELECT age FROM user GROUP BY age ORDER BY age ASC
         */
        List<Map<String, Object>> maplist = mapper.selectMaps(wrapper);
        for (Map<String, Object> mp : maplist) {
            System.out.println(mp);
        }
        /**
         * lambdaQueryWrapper groupBy orderBy
         */
        /**
         * ==>  Preparing: SELECT age FROM user GROUP BY age ORDER BY age ASC
         */
        LambdaQueryWrapper<User> lambdaQueryWrapper = new QueryWrapper<User>().lambda()
                .select(User::getAge)
                .groupBy(User::getAge)
                .orderByAsc(User::getAge);
        for (User user : mapper.selectList(lambdaQueryWrapper)) {
            System.out.println(user);
        }
    }

    @Test
    public void testTableFieldExistFalse() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        /**
         * @TableField(exist = false)
         *     private Integer count;
         *
         * ==>  Preparing: SELECT age, count(age) as count FROM user GROUP BY age
         *
         * User(id=null, name=null, age=null, email=null, ignoreColumn=ignoreColumn, count=0)
         * User(id=null, name=null, age=3, email=null, ignoreColumn=ignoreColumn, count=4)
         * User(id=null, name=null, age=18, email=null, ignoreColumn=ignoreColumn, count=1)
         * User(id=null, name=null, age=24, email=null, ignoreColumn=ignoreColumn, count=1)
         */
        wrapper.select("age, count(age) as count")
                .groupBy("age");
        List<User> list = mapper.selectList(wrapper);
        list.forEach(System.out::println);
        /**
         ==>  Preparing: SELECT id,name,age,email FROM user WHERE id=?
         ==> Parameters: 100899(Long)

         User(id=100899, name=miemie, age=3, email=miemie@baomidou.com, ignoreColumn=ignoreColumn, count=null)
         */
        mapper.insert(
                new User().setId(1008919L)
                        .setName("miemie")
                        .setEmail("miemie@baomidou.com")
                        .setAge(3));
        User miemie = mapper.selectById(1008919L);
        System.out.println(miemie);
        Assert.assertNotNull(miemie);

    }

}