springboot整合mybatis plus 常见增删改查方法的应用

项目完整图，源码下载地址
https://download.csdn.net/download/qq_41712271/16690360


1 pom.xml配置

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
</dependency>

<!--mybatis plus和springboot整合-->
<dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.1</version>
</dependency>

<dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
            <scope>provided</scope>
</dependency>

2 application.properties配置

server.port=8081
#==============================数据库相关配置========================================
spring.datasource.driver-class-name =com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/cloud_guanggao?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.datasource.username =root
spring.datasource.password =123456

#==============================mybatis plus 相关配置================================
#默认配置路径
mybatis-plus.mapper-locations=classpath*:/mapper/*Mapper.xml

#配置mybatis plus打印sql日志
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

#mybatis plus下划线转驼峰配置，默认就是true
mybatis-plus.configuration.map-underscore-to-camel-case=true

#配置文件配置 自定义sql的包扫描
mybatis-plus.type-aliases-package=cn.huawei.guanggao.pojo

#配置全局默认主键类型,实体类就不用加 @TableId(value = "id", type = IdType.AUTO)
mybatis-plus.global-config.db-config.id-type=auto

3 pojo类GuangGao.java编写 和 对应的表

package cn.huawei.guanggao.pojo;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;

//lombok 相关的注解，生成get,set,全参构造,无参构造
@Data
@AllArgsConstructor
@NoArgsConstructor

//mybatis plus表名映射
@TableName("guanggao")
public class GuangGao {
    //mybatis plus 定义表的主键
    @TableId(value = "id",type = IdType.AUTO)
    private Integer id;

    private String title;

    //mybatis plus 定义表的非主键字段,因为和数据据的名称不一样，所以才设置，一般不用配置
    @TableField(value = "cover_img")
    private String coverImg;
    private Integer price;

    @TableField(value = "create_time")
    private Date createTime;
    private Double point;
}

4 mybatis plus配置类 MybatisPlusPageConfig.java（分页用）

package cn.huawei.guanggao.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MybatisPlusPageConfig {

        /*  旧版本配置
      @Bean
      public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
      }*/

    /**
     * 新的分页插件 ,拦截器的原理感觉
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }

}

5 mapper层，GuangGaoMapper.java

package cn.huawei.guanggao.mapper;

import cn.huawei.guanggao.pojo.GuangGao;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import java.util.List;

/**
 * Mapper 继承该接口后，无需编写 mapper.xml 文件，即可获得CRUD功能
 */
public interface GuangGaoMapper extends BaseMapper<GuangGao> {

    //也可以 自定义sql脚本
    List<GuangGao> getAllGuangGaoList();
}

6 GuangGaoMapper.xml文件 (可省略，只是演示如何使用自定义的sql)

<?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接口的路径，记得修改-->
<mapper namespace="cn.huawei.guanggao.mapper.GuangGaoMapper">

    <select id="getAllGuangGaoList" resultType="GuangGao">
        select id,title,cover_img,price,create_time from guanggao
    </select>

</mapper>

7 启动类 GuanggaoApplication.java 编写

package cn.huawei.guanggao;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("cn.huawei.guanggao.mapper")
public class GuanggaoApplication {
    public static void main(String[] args) {
        SpringApplication.run(GuanggaoApplication.class, args);
    }
}

8 增删改查方法测试用例 GuanggaoApplicationTests.java

package cn.huawei.guanggao;

import cn.huawei.guanggao.mapper.GuangGaoMapper;
import cn.huawei.guanggao.pojo.GuangGao;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.*;
import java.util.function.Consumer;

@SpringBootTest
class GuanggaoApplicationTests {

    @Autowired
    private GuangGaoMapper guangGaoMapper;

    /*
    添加记录,并返回新增记录的id
     */
    @Test
    void test_2() {
        GuangGao a = new GuangGao(null, "a", "a.jpg", 150, new Date(), 155.5);
        guangGaoMapper.insert(a);
        System.out.println(a.getId());
    }


    /*
    根据id删除，删除 id=66
     */
    @Test
    void test_3() {
        guangGaoMapper.deleteById(66);
    }


    /*
    删除（根据ID 批量删除）
    DELETE FROM guanggao WHERE id IN (69,70)
     */
    @Test
    void test_15() {
        guangGaoMapper.deleteBatchIds(Arrays.asList(69, 70));
    }


    /*
    根据 wrapper 条件，删除记录
    queryWrapper 实体对象封装操作类（可以为 null,里面的 entity 用于生成 where 语句

     DELETE FROM guanggao WHERE (
        id = 68
        OR
        id IN (67,68))
     */
    @Test
    void test_14() {
        QueryWrapper<GuangGao> wrapper = new QueryWrapper<>();
        wrapper.eq("id", 68);
        wrapper.or();
        wrapper.in("id", 67, 68);

        guangGaoMapper.delete(wrapper);
    }


    /*
    通用删除操作 deleteByMap  map要写列名条件 不能是实体属性名
    删除 where id=67 and title=a
     */
    @Test
    void test_4() {
        HashMap<String, Object> map = new HashMap<>();
        map.put("id", 67);
        map.put("title", "a");
        guangGaoMapper.deleteByMap(map);
    }


    /*
    修改操作，根据id
    UPDATE guanggao SET title=?, cover_img=? WHERE id=71
     */
    @Test
    void test_16() {
        GuangGao guangGao = new GuangGao();
        guangGao.setTitle("光华大道");
        guangGao.setCoverImg("http://2.jpg");
        guangGao.setId(71);
        guangGaoMapper.updateById(guangGao);
    }


    /*
    queryWrapper更新操作
    修改 UPDATE guanggao SET title=?, cover_img=? WHERE (id = 65)
     */
    @Test
    void test_5() {
        GuangGao guangGao = new GuangGao();
        guangGao.setTitle("森岛帆高");
        guangGao.setCoverImg("http://www.baidu.com/img/mv1.jpg");

        QueryWrapper<GuangGao> wrapper = new QueryWrapper<>();
        wrapper.eq("id", 65);
        guangGaoMapper.update(guangGao, wrapper);
    }


    /*
    updateWrapper更新操作
    UPDATE guanggao SET title=?,cover_img=? WHERE (id = ?)
     */
    @Test
    void test_6() {
        UpdateWrapper<GuangGao> wrapper = new UpdateWrapper<>();
        wrapper.set("title", "大规模");
        wrapper.set("cover_img", "meilv_1.jpg");
        wrapper.eq("id", 64);

        guangGaoMapper.update(null, wrapper);
    }


    /*
        根据id查询 1个或多个
        FROM guanggao WHERE id=65
        FROM guanggao WHERE id IN ( 45, 46 )
    */
    @Test
    void test_17() {
        GuangGao guangGao = guangGaoMapper.selectById(65);

        List<GuangGao> guangGaos = guangGaoMapper.selectBatchIds(Arrays.asList(45, 46));
    }


    /*
    查询操作 QueryWrapper
     */
    @Test
    void test_1() {
        QueryWrapper<GuangGao> guangGaoQueryWrapper = new QueryWrapper<>();
        List<GuangGao> guangGaos =
                guangGaoMapper.selectList(guangGaoQueryWrapper);
    }


    /*
  QueryWrapper查询 基本操作,注意select的使用
  SELECT id,title FROM guanggao WHERE (id > 20)
   */
    @Test
    void test_12() {
        QueryWrapper<GuangGao> wrapper = new QueryWrapper<>();
        wrapper.select("id", "title");
        wrapper.gt("id", 20);

        guangGaoMapper.selectList(wrapper);
    }


    /*
    查询操作 QueryWrapper ,并排序
    WHERE (id IN (60,61,62) AND price > 150)
        ORDER BY id DESC,title DESC
     */
    @Test
    void test_10() {
        QueryWrapper<GuangGao> wrapper = new QueryWrapper<>();
        wrapper.in("id", Arrays.asList(60, 61, 62));
        wrapper.gt("price", 150);
        wrapper.orderByDesc("id", "title");
        List<GuangGao> guangGaos = guangGaoMapper.selectList(wrapper);
    }


    /*
    查询操作 QueryWrapper ，演示 apply,lick
    WHERE DATE_FORMAT(create_time,'%Y-%m-%d')>='2021-01-10' AND DATE_FORMAT(create_time,'%Y-%m-%d')<='2021-01-18' AND title LIKE '玩转%'
     */
    @Test
    void test_7() {
        QueryWrapper<GuangGao> wrapper = new QueryWrapper<>();
        wrapper.apply("DATE_FORMAT(create_time,'%Y-%m-%d')>={0} and DATE_FORMAT(create_time,'%Y-%m-%d')<={1}", "2021-01-10", "2021-01-18");

        wrapper.likeRight("title", "玩转");

        List<GuangGao> guangGaos = guangGaoMapper.selectList(wrapper);
    }


    /*
    查询操作 QueryWrapper,演示多条件 is not null , between
    *** 注意：只要是用匿名内部类，生成的sql 是个组合，要用括号()起来的 ***
      WHERE (create_time IS NOT NULL AND title = ?
        OR
      (id BETWEEN ? AND ?))
     */
    @Test
    void test_8() {

        QueryWrapper<GuangGao> wrapper = new QueryWrapper<>();
        wrapper.isNotNull("create_time");
        wrapper.eq("title", "森岛帆高");

        wrapper.or(new Consumer<QueryWrapper<GuangGao>>() {
            @Override
            public void accept(QueryWrapper<GuangGao> queryWrapper) {
                queryWrapper.between("id", 60, 63);
            }
        });

        List<GuangGao> guangGaos = guangGaoMapper.selectList(wrapper);
    }


    /*
    查询操作 QueryWrapper
    *** 注意：只要是用匿名内部类，生成的sql 是个组合，要用括号()起来的 ***
    WHERE title LIKE ?
         AND
            (create_time IS NOT NULL
            OR
            price = ?)
     */
    @Test
    void test_9() {
        QueryWrapper<GuangGao> wrapper = new QueryWrapper<>();
        wrapper.likeRight("title", "玩转");
        wrapper.and(new Consumer<QueryWrapper<GuangGao>>() {
            @Override
            public void accept(QueryWrapper<GuangGao> queryWrapper) {
                queryWrapper.isNotNull("create_time");
                queryWrapper.or();
                queryWrapper.eq("price", 150);
            }
        });

        List<GuangGao> guangGaos = guangGaoMapper.selectList(wrapper);
    }


    /*
    wrapper 分页查询
    WHERE (id > 20) LIMIT (2-1)*5 ,5
     */
    @Test
    void test_11() {
        QueryWrapper<GuangGao> wrapper = new QueryWrapper<>();
        wrapper.gt("id", 20);
        //第1页，每页2条
        Page<GuangGao> page = new Page<>(2, 5);
        IPage<GuangGao> bannerDOIPage = guangGaoMapper.selectPage(page, wrapper);
        System.out.println("总条数" + bannerDOIPage.getTotal());
        System.out.println("总页数" + bannerDOIPage.getPages());
        //获取当前数据
        System.out.println(bannerDOIPage.getRecords());
    }


    /*
    查询（根据 columnMap 条件）
    FROM guanggao WHERE id = 67 AND title = 'a'
     */
    @Test
    void test_18() {
        HashMap<String, Object> map = new HashMap<>();
        map.put("id", 67);
        map.put("title", "a");

        List<GuangGao> guangGaos = guangGaoMapper.selectByMap(map);
    }


    /*
    根据 Wrapper 条件，结果只能有一条，否则会报错
    FROM guanggao WHERE (id = 63)
     */
    @Test
    void test_19() {
        QueryWrapper<GuangGao> wrapper = new QueryWrapper<>();
        wrapper.eq("id", 63);
        GuangGao guangGao = guangGaoMapper.selectOne(wrapper);
    }


    /*
    根据 Wrapper 条件，查询总记录数
    SELECT COUNT( * ) FROM guanggao WHERE (id > 25)
     */
    @Test
    void test_20() {
        QueryWrapper<GuangGao> wrapper = new QueryWrapper<>();
        wrapper.gt("id", 25);

        Integer integer = guangGaoMapper.selectCount(wrapper);
        System.out.println(integer);
    }


    /*
    根据 Wrapper 条件，查询全部记录
    SELECT price,count(price) as priceCount FROM guanggao GROUP BY price
    [{price=55, priceCount=3}, {price=66, priceCount=3}, {price=77, priceCount=3}]
     */
    @Test
    void test_21() {
        QueryWrapper<GuangGao> wrapper = new QueryWrapper<>();
        wrapper.select("price", "count(price) as priceCount");
        wrapper.groupBy("price");

        List<Map<String, Object>> maps = guangGaoMapper.selectMaps(wrapper);
        System.out.println(maps);
    }


    /*
    根据 Wrapper 条件，查询全部记录
    <p>注意： 只返回第一个字段的值</p>

    SELECT title,cover_img,price FROM guanggao WHERE (id = 45 OR id IN (46, 47))
    [玩Docker实战, 新版javase, Nodejs教程]
     */
    @Test
    void test_22() {
        QueryWrapper<GuangGao> wrapper = new QueryWrapper<>();
        wrapper.select("title", "cover_img", "price");
        wrapper.eq("id", 45);
        wrapper.or();
        wrapper.in("id", 46, 47);

        List<Object> objects = guangGaoMapper.selectObjs(wrapper);
        System.out.println(objects);
    }


    /*
    自定义sql脚本,在xml文件中编写，像mybatis那样
     */
    @Test
    void test_13() {
        List<GuangGao> allGuangGaoList = guangGaoMapper.getAllGuangGaoList();
        System.out.println(allGuangGaoList);
    }
}