MyBatisPlus实操Demo

工程展示
在这里插入图片描述

第1步:创建maven项目

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.lian</groupId>
    <artifactId>mbdemo01</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mbdemo01</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

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

        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!--lombok用来简化实体类-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

第2步:创建实体类User

package com.lian.mbdemo01.entity;

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

import java.io.Serializable;
import java.util.Date;

@Data
public class User implements Serializable {

    @TableId(type = IdType.ID_WORKER)  //设置主键自增策略
    private Long id;
    private String name;
    private Integer age;
    private String email;

    //记录的创建时间,更新时间 自动填充
    @TableField(fill = FieldFill.INSERT)
    private Date createTime;
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date updateTime;

    /**
     * 乐观锁
     * 1、数据库中添加version字段
     * 2、实体类添加version字段,并添加@Version 注解
     * 3、元对象处理器接口添加version的insert默认值
     * 4、配置类 在 MybatisPlusConfig 中注册 Bean
     * 5、测试
     */
    @Version
    @TableField(fill = FieldFill.INSERT)
    private Integer version;

    /**
     * 物理删除:真实删除,将对应数据从数据库中删除,之后查询不到此条被删除数据
     * 逻辑删除:假删除,将对应数据中代表是否被删除字段状态修改为“被删除状态”,之后在数据库中仍旧能看到此条数据记录
     *
     * 1、数据库添加字段 ALTER TABLE `user` ADD COLUMN `deleted` boolean
     * 2、实体类添加deleted 字段
     * 3、元对象处理器接口添加deleted的insert默认值
     * 4、application.properties 加入配置
     * 5、在 MybatisPlusConfig 中注册 Bean
     * 6、测试逻辑删除
     * 测试后发现,数据并没有被删除,deleted字段的值由0变成了1
     * 测试后分析打印的sql语句,是一条update
     * 注意:被删除数据的deleted 字段的值必须是 0,才能被选取出来执行逻辑删除的操作
     * UPDATE user SET deleted=1 WHERE id=? AND deleted=0
     * 7、测试逻辑删除后的查询
     * 测试后分析打印的sql语句,包含 WHERE deleted=0
     * SELECT id,name,age,email,create_time,update_time,deleted FROM user WHERE deleted=0
     */
    @TableLogic
    @TableField(fill = FieldFill.INSERT)
    private Integer deleted;

}

第3步:配置类MybatisPlusConfig

package com.lian.mbdemo01.config;

import com.baomidou.mybatisplus.core.injector.ISqlInjector;
import com.baomidou.mybatisplus.extension.injector.LogicSqlInjector;
import com.baomidou.mybatisplus.extension.plugins.OptimisticLockerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.PerformanceInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Profile;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@MapperScan("com.lian.mbdemo01.mapper")
public class MybatisPlusConfig {

    /**
     * 乐观锁插件
     */
    @Bean
    public OptimisticLockerInterceptor optimisticLockerInterceptor(){
        return new OptimisticLockerInterceptor();
    }

    /**
     * 分页插件
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }

    /**
     * 逻辑删除插件
     */
    @Bean
    public ISqlInjector sqlInjector() {
        return new LogicSqlInjector();
    }

    /**
     * SQL 执行性能分析插件
     * 参数:maxTime: SQL 执行最大时长,超过自动停止运行,有助于发现问题。
     * 参数:format: SQL是否格式化,默认false。
     * 开发环境使用,线上不推荐。 maxTime 指的是 sql 最大执行时长
     */
    @Bean
    @Profile({"dev","test"})// 设置 dev test 环境开启
    public PerformanceInterceptor performanceInterceptor() {
        PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();
        performanceInterceptor.setMaxTime(100);//ms,超过此处设置的ms则sql不执行
//        performanceInterceptor.setMaxTime(5);//ms,超过此处设置的ms不执行
        performanceInterceptor.setFormat(true);
        return performanceInterceptor;
    }

}

第4步:实现元对象处理器接口

package com.lian.mbdemo01.handler;

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.util.Date;

/**
 * 实现元对象处理器接口
 * 1、数据库在User表中添加datetime类型的新的字段 create_time、update_time,自动填充字段
 * 2、user字段加注解,@TableField(fill = FieldFill.INSERT)
 * 3、MyMetaObjectHandler类配置
 */
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

    private static final Logger LOGGER = LoggerFactory.getLogger(MyMetaObjectHandler.class);

    @Override
    public void insertFill(MetaObject metaObject) {
        LOGGER.info("start insert fill ....");
        this.setFieldValByName("createTime",new Date(),metaObject);
        this.setFieldValByName("updateTime",new Date(),metaObject);
        this.setFieldValByName("version",1,metaObject);
        this.setFieldValByName("deleted",0,metaObject);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        LOGGER.info("start update fill ....");
        this.setFieldValByName("updateTime",new Date(),metaObject);
    }
}

第5步:mapper层

package com.lian.mbdemo01.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.lian.mbdemo01.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

@Mapper
@Repository
public interface UserMapper extends BaseMapper<User> {
}

第6步:主启动类

package com.lian.mbdemo01;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@MapperScan("com.lian.mbdemo01.mapper")
@SpringBootApplication
public class Mbdemo01Application {

    public static void main(String[] args) {
        SpringApplication.run(Mbdemo01Application.class, args);
    }

}

第7步:配置application.properties

# 配置数据源
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mp?serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=root

#mybatis日志
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

#全局设置主键生成策略,影响所有实体的配置,可以设置全局主键配置,@TableId只适合单个实体的主键字段
mybatis-plus.global-config.db-config.id-type=auto

# 逻辑删除
mybatis-plus.global-config.db-config.logic-delete-value=1
mybatis-plus.global-config.db-config.logic-not-delete-value=0

#环境设置:dev、test、prod
spring.profiles.active=dev

第8步:测试类之mybatisPlus基础crud

package com.lian.mbdemo01;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.lian.mbdemo01.entity.User;
import com.lian.mbdemo01.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * mybatisPlus基础的增删改查操作
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class Mbdemo01ApplicationTests {

    @Autowired
    UserMapper userMapper;

    @Test
    public void testSelectList() {
        System.out.println(("----- selectAll method test ------"));
        //UserMapper 中的 selectList() 方法的参数为 MP 内置的条件封装器 Wrapper
        //所以不填写就是无任何条件
        List<User> users = userMapper.selectList(null);
        users.forEach(System.out::println);
    }

    @Test
    public void testInsert(){
        User user = new User();
        //user.setId("");
        user.setAge(10);
        user.setName("aipi");
        user.setEmail("376536577@qq.com");
        int result = userMapper.insert(user);
        System.out.println(result); //影响的行数
        System.out.println(user); //id自动回填
    }

    @Test
    public void testUpdate(){
        User user = new User();
        user.setId(1471716233916932097L);
        user.setAge(10);
        int result = userMapper.updateById(user);
        System.out.println(result);
    }

    /**
     * 测试乐观锁成功
     */
    @Test
    public void testVersion(){
        User user = userMapper.selectById(1471736174497984514L);
        user.setAge(12);
        user.setName("danpier");
        userMapper.updateById(user);
    }

    /**
     * 测试乐观锁成功
     */
    @Test
    public void testVersionFail(){
        User user = userMapper.selectById(1471728156284805122L);
        user.setAge(12);
        user.setName("dan");
        user.setVersion(user.getVersion()-1);
        userMapper.updateById(user);
    }

    /**
     * 测试查询
     */
    @Test
    public void testSelectById(){
        User user = userMapper.selectById(1471728156284805122L);
        System.out.println(user);
    }

    /**
     * 测试批量查询
     */
    @Test
    public void testBatchSelectById(){
        List<User> users = userMapper.selectBatchIds(Arrays.asList(1, 2, 3, 4));
        System.out.println(users);
    }

    /**
     * 测试用map封装
     */
    @Test
    public void testSelectByMap(){
        //User user = userMapper.selectById(1471728156284805122L);
        Map map = new HashMap();
        map.put("name","pi");
        map.put("age",10);
        List list = userMapper.selectByMap(map);
        System.out.println(list);
    }

    /**
     * 测试selectPage分页
     * 测试:最终通过page对象获取相关数据
     */
    @Test
    public void testPage(){
        Page<User> page = new Page<>(1,5);
        userMapper.selectPage(page,null);
        page.getRecords().forEach(System.out::println);
        System.out.println(page.getCurrent());
        System.out.println(page.getPages());
        System.out.println(page.getSize());
        System.out.println(page.getTotal());
        System.out.println(page.hasNext());
        System.out.println(page.hasPrevious());
    }

    //用map的形式查询分页
    @Test
    public void testSelectMapsPage() {
        Page<User> page = new Page<>(1, 5);
        IPage<Map<String, Object>> mapIPage = userMapper.selectMapsPage(page, null);
        //注意:此行必须使用 mapIPage 获取记录列表,否则会有数据类型转换错误
        mapIPage.getRecords().forEach(System.out::println);
        System.out.println(page.getCurrent());
        System.out.println(page.getPages());
        System.out.println(page.getSize());
        System.out.println(page.getTotal());
        System.out.println(page.hasNext());
        System.out.println(page.hasPrevious());
    }

    //删除单列
    @Test
    public void delete(){
        User user = userMapper.selectById(1471728156284805122L);
        userMapper.deleteById(user);
    }

    //批量删除用批量id
    @Test
    public void deleteBatch(){
        List<Long> list = Arrays.asList(1471716233916932097L, 1471724535568650241L);
        userMapper.deleteBatchIds(list);
    }

    //批量删除用map
    @Test
    public void deleteBatchMap(){
        //List<Long> list = Arrays.asList(1471716233916932097L, 1471724535568650241L);
        HashMap<String, Object> map = new HashMap<>();
        map.put("id",1471716233916932097L);
        map.put("id",1471724535568650241L);
        userMapper.deleteByMap(map);
    }

    /**
     * 测试逻辑删除
     * 测试后发现,数据并没有被删除,deleted字段的值由0变成了1
     * 测试后分析打印的sql语句,是一条update
     * 注意:被删除数据的deleted 字段的值必须是 0,才能被选取出来执行逻辑删除的操作
     */
    @Test
    public void testLogicDelete(){
        int result = userMapper.deleteById(1471736174497984514L);
        System.out.println(result);
    }

    /**
     * 测试 逻辑删除后的查询:
     * 不包括被逻辑删除的记录
     */
    @Test
    public void testLogicDeleteSelect() {
        User user = new User();
        List<User> users = userMapper.selectList(null);
        users.forEach(System.out::println);
    }

    /**
     * 测试 性能分析插件
     * Time:16 ms - ID:com.lian.mbdemo01.mapper.UserMapper.insert
     * Execute SQL:
     *     INSERT
     *     INTO
     *         user
     *         ( id, name, age, email, create_time, update_time, version, deleted )
     *     VALUES
     *         ( 1471787336014188545, 'jack', 20, 'test2@baomidou.com', '2021-12-17 18:20:20.051', '2021-12-17 18:20:20.051', 1, 0 )
     */
    @Test
    public void testPerformance() {
        User user = new User();
        user.setName("jack");
        user.setEmail("test2@baomidou.com");
        user.setAge(20);
        userMapper.insert(user);
    }

}

第9步:测试类之条件构造器demo

package com.lian.mbdemo01;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.lian.mbdemo01.entity.User;
import com.lian.mbdemo01.mapper.UserMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

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

/**
 * mybatisPlus 条件构造器 wrapper 的复杂查询
 */
@RunWith(SpringRunner.class)
@SpringBootTest
public class WrapperTests {

    @Autowired
    UserMapper userMapper;

    /**
     * 1、ge、gt、le、lt、isNull、isNotNull
     */
    //UPDATE user SET deleted=1 WHERE deleted=0 AND name IS NULL AND age >= ? AND email IS NOT NULL
    @Test
    public void testDelete(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper
                .isNull("name")
                .ge("age", 12)
                .isNotNull("email");
        int result = userMapper.delete(queryWrapper);
        System.out.println("delete return count = " + result);
    }

    /**
     * 2、eq、ne
     * seletOne返回的是一条实体记录,当出现多条时会报错
     * SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND name = ?
     */
    @Test
    public void testSelectOne() {

        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("name", "Tom");
        User user = userMapper.selectOne(queryWrapper);
        System.out.println(user);
    }

    /**
     * 3、between、notBetween
     * SELECT COUNT(1) FROM user WHERE deleted=0 AND age BETWEEN ? AND ?
     */
    @Test
    public void testSelectCount() {

        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.between("age", 20, 30);
        Integer count = userMapper.selectCount(queryWrapper);
        System.out.println(count);
    }

    /**
     * 4、allEq
     * SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND name = ? AND id = ? AND age = ?
     */
    @Test
    public void testSelectList() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        Map<String, Object> map = new HashMap<>();
        map.put("id", 2);
        map.put("name", "Jack");
        map.put("age", 20);
        queryWrapper.allEq(map);
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    /**
     * 5、like、notLike、likeLeft、likeRight
     * selectMaps返回Map集合列表
     * SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND name NOT LIKE '%e%' AND email LIKE 't%'
     */
    @Test
    public void testSelectMaps() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper
                .notLike("name", "e")
                .likeRight("email", "t");
        List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);//返回值是Map列表
        maps.forEach(System.out::println);
    }

    /**
     * 6、in、notIn、inSql、notinSql、exists、notExists
     * in、notIn:
     * notIn("age",{1,2,3})--->age not in (1,2,3)
     * notIn("age", 1, 2, 3)--->age not in (1,2,3)
     * inSql、notinSql:可以实现子查询
     * 例: inSql("age", "1,2,3,4,5,6")--->age in (1,2,3,4,5,6)
     * 例: inSql("id", "select id from table where id < 3")--->id in (select id from table where id < 3)
     * SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND id IN (select id from user where id < 3)
     */
    @Test
    public void testSelectObjs() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        //queryWrapper.in("id", 1, 2, 3);
        queryWrapper.inSql("id", "select id from user where id < 3");
        List<Object> objects = userMapper.selectObjs(queryWrapper);//返回值是Object列表
        objects.forEach(System.out::println);
    }

    /**
     * 7、or、and
     * 注意:这里使用的是 UpdateWrapper
     * 不调用or则默认为使用 and 连
     * UPDATE user SET name=Andy, age=99, update_time=? WHERE deleted=0 AND name LIKE %h% OR age BETWEEN 20 AND 30
     */
    @Test
    public void testUpdate1() {
        //修改值
        User user = new User();
        user.setAge(99);
        user.setName("Andy");
        //修改条件
        UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
        userUpdateWrapper
                .like("name", "h")
                .or()
                .between("age", 20, 30);
        int result = userMapper.update(user, userUpdateWrapper);
        System.out.println(result);
    }

    /**
     * 8、嵌套or、嵌套and
     * 这里使用了lambda表达式,or中的表达式最后翻译成sql时会被加上圆括号
     * UPDATE user SET name=Andy, age=99, update_time=? WHERE deleted=0 AND name LIKE %h% OR ( name = 李白 AND age <> 20 )
     *
     * EQ 就是 EQUAL等于
     * NE 就是 NOT EQUAL不等于
     * GT 就是 GREATER THAN大于 
     * LT 就是 LESS THAN小于
     * GE 就是 GREATER THAN OR EQUAL 大于等于
     * LE 就是 LESS THAN OR EQUAL 小于等于
     */
    @Test
    public void testUpdate2() {
        //修改值
        User user = new User();
        user.setAge(99);
        user.setName("Andy");
        //修改条件
        UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
        userUpdateWrapper
                .like("name", "h")
                .or(i -> i.eq("name", "李白").ne("age", 20));
        int result = userMapper.update(user, userUpdateWrapper);
        System.out.println(result);
    }

    /**
     * 9、orderBy、orderByDesc、orderByAsc
     * SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 ORDER BY id DESC
     */
    @Test
    public void testSelectListOrderBy() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByDesc("id");
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    /**
     * 10、last
     * 直接拼接到 sql 的最后
     * 注意:只能调用一次,多次调用以最后一次为准 有sql注入的风险,请谨慎使用
     * select ... from ..  where ... group by ... having ... order by ... limit ...
     * SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 limit 1
     */
    @Test
    public void testSelectListLast() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.last("limit 1");
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    /**
     * 11、指定要查询的列
     * SELECT id,name,age FROM user WHERE deleted=0
     */
    @Test
    public void testSelectListColumn() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("id", "name", "age");
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    /**
     * 12、set、setSql
     * 最终的sql会合并 user.setAge(),以及 userUpdateWrapper.set()  和 setSql() 中 的字段
     * UPDATE user SET age=99, update_time=?, name=老李头, email = '123@qq.com' WHERE deleted=0 AND name LIKE %h%
     */
    @Test
    public void testUpdateSet() {
        //修改值
        User user = new User();
        user.setAge(99);
        //修改条件
        UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
        userUpdateWrapper
                .like("name", "h")
                .set("name", "老李头")//除了可以查询还可以使用set设置修改的字段
                .setSql(" email = '123@qq.com'");//可以有子查询
        int result = userMapper.update(user, userUpdateWrapper);
        System.out.println(result);
    }

}
  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值