Spring Boot整合MyBatis Plus连接数据库

GitHub:SpringBootDemo
Gitee:SpringBootDemo
微信公众号:在这里插入图片描述

Spring Boot整合MyBatis连接数据库 这篇文章中,我们已经可以使用Spring Boot整合MyBatis来连接数据库,但随着使用,我们发现,MyBatis还是稍微有点复杂,那有没有更加简单的方式来操作数据库呢,我们惊奇的发现了MyBatis Plus。

0 开发环境

  • JDK:1.8
  • Spring Boot:2.1.1.RELEASE
  • MySQL:5.7.13

1 引入依赖

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

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

<!--lombok-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.30</version>
    <scope>provided</scope>
</dependency>

2 引入数据源

application.yml 增加如下配置信息

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&serverTimezone=GMT%2B8
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
#
mybatis-plus:
  mapper-locations: classpath:mapper/*.xml                #对应mapper映射xml文件所在路径
  type-aliases-package: cn.wbnull.springbootdemo.entity   #对应实体类路径

3 测试

3.1 新建数据库表

CREATE SCHEMA `test` DEFAULT CHARACTER SET utf8mb4 ;

CREATE TABLE `test`.`user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`));

INSERT INTO `test`.`user` (`name`) VALUES ('张三');
INSERT INTO `test`.`user` (`name`) VALUES ('李四');
INSERT INTO `test`.`user` (`name`) VALUES ('王五');
INSERT INTO `test`.`user` (`name`) VALUES ('周六');

3.2 创建实体类

package cn.wbnull.springbootdemo.entity;

import lombok.Data;

@Data
public class User {

    private int id;
    private String name;
}

3.3 创建Mapper

package cn.wbnull.springbootdemo.mapper;

import cn.wbnull.springbootdemo.entity.User;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.springframework.stereotype.Repository;

@Repository
public interface UserMapper extends BaseMapper<User> {

}

3.4 创建映射文件

resources 目录下新建 mapper 文件夹,用于存放MyBatis Plus映射文件

<?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="cn.wbnull.springbootdemo.mapper.UserMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.wbnull.springbootdemo.entity.User">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, name
    </sql>
</mapper>

3.5 创建Service

package cn.wbnull.springbootdemo.service;

import cn.wbnull.springbootdemo.entity.User;
import cn.wbnull.springbootdemo.mapper.UserMapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    public String add(String name) {
        User user = new User();
        user.setName(name);

        userMapper.insert(user);

        return "操作成功";
    }

    public List<User> query() {
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();

        return userMapper.selectList(queryWrapper);
    }

    public String update(int id, String name) {
        LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper.set(User::getName, name);
        updateWrapper.eq(User::getId, id);

        userMapper.update(updateWrapper);

        return "操作成功";
    }

    public String delete(int id) {
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(User::getId, id);

        userMapper.delete(queryWrapper);

        return "操作成功";
    }
}

3.6 创建Controller

package cn.wbnull.springbootdemo.controller;

import cn.wbnull.springbootdemo.entity.User;
import cn.wbnull.springbootdemo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("user")
public class UserController {

    @Autowired
    public UserService userService;

    @PostMapping(value = "add")
    public String add(@RequestParam(value = "name") String name) {
        return userService.add(name);
    }

    @PostMapping(value = "query")
    public List<User> query() {
        return userService.query();
    }

    @PostMapping(value = "update")
    public String update(@RequestParam(value = "id") int id, @RequestParam(value = "name") String name) {
        return userService.update(id, name);
    }

    @PostMapping(value = "delete")
    public String delete(@RequestParam(value = "id") int id) {
        return userService.delete(id);
    }
}

3.7 项目启动类

增加@MapperScan,扫描mapper

package cn.wbnull.springbootdemo;

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

@SpringBootApplication
@MapperScan("cn.wbnull.springbootdemo.mapper")
public class MybatisPlusApplication {

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

3.8 测试

使用Postman进行测试,输出结果如下

3.8.1 select

在这里插入图片描述

3.8.2 insert

在这里插入图片描述

我们查下数据库,并再用postman请求
在这里插入图片描述
在这里插入图片描述

3.8.3 update

在这里插入图片描述

数据库中成功更新
在这里插入图片描述

3.8.4 delete

在这里插入图片描述

数据库中成功删除
在这里插入图片描述
截至这里,Spring Boot已经成功整合MyBatis Plus并连接上了数据库,且测试正常。

对比发现,我们使用LambdaQueryWrapper来操作数据库会特别方便,

并且,在我们实际开发中,如果存在大量数据库表,我们依旧可以使用Generator来自动生成代码

4 条件构造器 QueryWrapper

上面 3.5 中,我们创建的Service类中,使用了QueryWrapper简化SQL,其基本用法可参考官方文档:https://baomidou.com/pages/10c804/

以下简单整理可供参考

函数名说明例子
allEq全部eq(或个别isNull)allEq({id:1,name:“老王”,age:null})--->id = 1 and name = ‘老王’ and age is null
allEq({id:1,name:“老王”,age:null}, false)--->id = 1 and name = ‘老王’
eq等于 =eq(“name”, “老王”)--->name = ‘老王’
ne不等于 <>ne(“name”, “老王”)--->name <> ‘老王’
gt大于 >gt(“age”, 18)--->age > 18
ge大于等于 >=ge(“age”, 18)--->age >= 18
lt小于 <lt(“age”, 18)--->age < 18
le小于等于 <=le(“age”, 18)--->age <= 18
betweenBETWEEN 值1 AND 值2between(“age”, 18, 30)--->age between 18 and 30
notBetweenNOT BETWEEN 值1 AND 值2notBetween(“age”, 18, 30)--->age not between 18 and 30
likeLIKE ‘%值%’like(“name”, “王”)--->name like ‘%王%’
notLikeNOT LIKE ‘%值%’notLike(“name”, “王”)--->name not like ‘%王%’
likeLeftLIKE ‘%值’likeLeft(“name”, “王”)--->name like ‘%王’
likeRightLIKE ‘值%’likeRight(“name”, “王”)--->name like ‘王%’
notLikeLeftNOT LIKE ‘%值’notLikeLeft(“name”, “王”)--->name not like ‘%王’
notLikeRightNOT LIKE ‘值%’notLikeRight(“name”, “王”)--->name not like ‘王%’
isNull字段 IS NULLisNull(“name”)--->name is null
isNotNull字段 IS NOT NULLisNotNull(“name”)--->name is not null
in字段 IN (value.get(0), value.get(1), …)
字段 IN (v0, v1, …)
in(“age”,{1,2,3})--->age in (1,2,3)
in(“age”, 1, 2, 3)--->age in (1,2,3)
notIn字段 NOT IN (value.get(0), value.get(1), …)
NOT IN (v0, v1, …)
notIn(“age”,{1,2,3})--->age not in (1,2,3)
notIn(“age”, 1, 2, 3)--->age not in (1,2,3)
inSql字段 IN ( sql语句 )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)
notInSql字段 NOT IN ( sql语句 )notInSql(“age”, “1,2,3,4,5,6”)--->age not in (1,2,3,4,5,6)
notInSql(“id”, “select id from table where id < 3”)--->id not in (select id from table where id < 3)
groupBy分组:GROUP BY 字段, …groupBy(“id”, “name”)--->group by id,name
orderByAsc排序:ORDER BY 字段, … ASCorderByAsc(“id”, “name”)--->order by id ASC,name ASC
orderByDesc排序:ORDER BY 字段, … DESCorderByDesc(“id”, “name”)--->order by id DESC,name DESC
orderBy排序:ORDER BY 字段, …orderBy(true, true, “id”, “name”)--->order by id ASC,name ASC
havingHAVING ( sql语句 )having(“sum(age) > 10”)--->having sum(age) > 10
having(“sum(age) > {0}”, 11)--->having sum(age) > 11
funcfunc 方法(主要方便在出现if…else下调用不同方法能不断链)func(i -> if(true) {i.eq(“id”, 1)} else {i.ne(“id”, 1)})
or拼接 OReq(“id”,1).or().eq(“name”,“老王”)--->id = 1 or name = ‘老王’
or(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))--->or (name = ‘李白’ and status <> ‘活着’)
andAND 嵌套and(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))--->and (name = ‘李白’ and status <> ‘活着’)
nested正常嵌套 不带 AND 或者 ORnested(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))--->(name = ‘李白’ and status <> ‘活着’)
apply拼接 sqlapply(“id = 1”)--->id = 1
apply(“date_format(dateColumn,‘%Y-%m-%d’) = ‘2008-08-08’”)--->date_format(dateColumn,‘%Y-%m-%d’) = ‘2008-08-08’“)
apply(“date_format(dateColumn,‘%Y-%m-%d’) = {0}”, “2008-08-08”)--->date_format(dateColumn,‘%Y-%m-%d’) = ‘2008-08-08’”)
last无视优化规则直接拼接到 sql 的最后last(“limit 1”)
exists拼接 EXISTS ( sql语句 )exists(“select id from table where age = 1”)--->exists (select id from table where age = 1)
notExists拼接 NOT EXISTS ( sql语句 )notExists(“select id from table where age = 1”)--->not exists (select id from table where age = 1)
QueryWrapper
select设置查询字段select(“id”, “name”, “age”)
select(i -> i.getProperty().startsWith(“test”))
UpdateWrapper
setSQL SET 字段set(“name”, “老李头”)
set(“name”, “”)--->数据库字段值变为空字符串
set(“name”, null)--->数据库字段值变为null
setSql设置 SET 部分 SQLsetSql(“name = ‘老李头’”)

5 代码生成器

5.1 引入依赖

<!-- 代码生成器 -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-generator</artifactId>
    <version>3.5.4</version>
</dependency>

<!-- 模板引擎 -->
<dependency>
    <groupId>org.apache.velocity</groupId>
    <artifactId>velocity-engine-core</artifactId>
    <version>2.3</version>
</dependency>

5.2 新建代码生成器类

代码生成器所有配置可参考官方文档:https://baomidou.com/pages/981406/

package cn.wbnull.springbootdemo.mybatis;

import com.baomidou.mybatisplus.core.exceptions.MybatisPlusException;
import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.config.*;
import com.baomidou.mybatisplus.generator.config.rules.DateType;

import java.util.Collections;
import java.util.Scanner;

public class MybatisPlusGenerator {

    private static final String URL = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&serverTimezone=GMT%2B8";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "root";

    private static final String PACKAGE_PATH = System.getProperty("user.dir") + "/spring-boot-mybatis-plus/src/main/java";
    private static final String RESOURCES_MAPPER_PATH = System.getProperty("user.dir") + "/spring-boot-mybatis-plus/src/main/resources/mapper/";

    public static void main(String[] args) {
        DataSourceConfig dataSourceConfig = new DataSourceConfig.Builder(URL, USERNAME, PASSWORD)
                .build();

        AutoGenerator autoGenerator = new AutoGenerator(dataSourceConfig);
        autoGenerator.global(globalConfig());
        autoGenerator.packageInfo(packageConfig());
        autoGenerator.strategy(strategyConfig());

        autoGenerator.execute();
    }

    private static GlobalConfig globalConfig() {
        return new GlobalConfig.Builder()
                .outputDir(PACKAGE_PATH)
                .author("null")
                .dateType(DateType.TIME_PACK)
                .commentDate("yyyy-MM-dd")
                .disableOpenDir()
                .build();
    }

    private static PackageConfig packageConfig() {
        return new PackageConfig.Builder()
                .parent("cn.wbnull.springbootdemo")
                .pathInfo(Collections.singletonMap(OutputFile.xml, RESOURCES_MAPPER_PATH))
                .build();
    }

    private static StrategyConfig strategyConfig() {
        return new StrategyConfig.Builder()
                .addInclude(scanner().split(","))
                .mapperBuilder()
                .enableBaseResultMap()
                .enableBaseColumnList()
                .entityBuilder()
                .enableLombok()
                .enableTableFieldAnnotation()
                .build();
    }

    public static String scanner() {
        Scanner scanner = new Scanner(System.in);
        String hint = "请输入数据库表名,多个表名使用英文逗号分隔:";
        System.out.println(hint);
        if (scanner.hasNext()) {
            String ipt = scanner.next();
            if (ipt != null && ipt.length() > 0) {
                return ipt;
            }
        }

        throw new MybatisPlusException("请输入正确的数据库表名");
    }
}

5.3 测试

5.3.1 新建数据库表

我们先新建一个数据库表,便于一会测试自动生成代码

CREATE TABLE `test`.`user_info` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `userCode` VARCHAR(20) NOT NULL,
  `userName` VARCHAR(45) NULL,
  `password` VARCHAR(40) NOT NULL,
  PRIMARY KEY (`id`));

5.3.2 测试

运行MybatisPlusGenerator,输入需要生成的表名
在这里插入图片描述
文件生成完成
在这里插入图片描述

生成文件如下
在这里插入图片描述
这样,对于大量的数据库表,我们就可以使用Generator来生成基本的代码,然后自己再添加其他所需要的代码即可。


CSDN:https://blog.csdn.net/dkbnull/article/details/136331111
微信:https://mp.weixin.qq.com/s/ZJTKX_gmn6ffsY7hNrspHQ
知乎:https://zhuanlan.zhihu.com/p/684251625


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值