使用Spring Boot整合Mybatis-Plus实现数据库的增删查改

  Java   24分钟   864浏览   2评论

1、准备数据库中的表及表中的数据

/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.6.11 : Database - mp

*********************************************************************

*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`mp` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `mp`;

/*Table structure for table `tbl_employee` */

DROP TABLE IF EXISTS `tbl_employee`;

CREATE TABLE `tbl_employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(50) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `version` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;

/*Data for the table `tbl_employee` */

insert  into `tbl_employee`(`id`,`last_name`,`email`,`gender`,`age`,`version`) values (1,'Tom','tom@zou.com','1',22,1),(2,'Jerry','jerry@zou.com','0',25,1),(3,'Black','black@zou.com','1',30,1),(4,'White','white@zou.com','0',35,1),(14,'小邹','com@hq.cn','0',19,1),(18,'jack','mp@zou.com','1',22,1),(22,'abc','123@qq.com','1',23,2);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

2、初始化一个springboot项目

如何初始化一个springboot项目?
点击https://blog.csdn.net/Zou_05/article/details/115869941

注:勾选Lombok插件,简化开发

在这里插入图片描述

3、在pom.xml中加入mybatis-plus及mysql数据库的依赖

<!--mybatis-plus相关依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.5</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.10</version>
        </dependency>

4、将application.properties文件修改为application.yml,并添加mysql数据库的连接信息(mp为我的数据库名)

server:
  port: 8081
  servlet:
    context-path: /
spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/mp?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver

5、编写实体类(使用lombok简化代码)

@TableName指定数据库的表名

@TableId(type = IdType.AUTO)指定id为自增

package com.zou.demo1.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.Version;
import lombok.Data;

/**
 * @author: 邹祥发
 * @date: 2021/4/21 08:49
 */
@Data
@TableName("tbl_employee")
public class User {

 @TableId(type = IdType.AUTO)
 private Integer id;

 private String lastName;
 private String email;
 private Integer gender;
 private Integer age;

 @Version
 private Integer version;
}

6、编写Mapper类

package com.zou.demo1.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zou.demo1.entity.User;

/**
 * @author: 邹祥发
 * @date: 2021/4/21 08:55
 */
public interface UserMapper extends BaseMapper<User> {
}

7、因测试需要加入一些依赖,完整的pom.xml如下:

<?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.4.5</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.zou</groupId>
    <artifactId>demo1</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo1</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.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--mybatis-plus相关依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.5</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-test</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

8、进行功能测试

注:@MapperScan("com.zou.demo1.mapper")指定扫描的mapper包

(1)查询测试:

package com.zou.demo1.test;

import com.zou.demo1.entity.User;
import com.zou.demo1.mapper.UserMapper;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;

/**
 * @author: 邹祥发
 * @date: 2021/4/21 08:57
 */
@RunWith(SpringRunner.class)
@SpringBootTest
@MapperScan("com.zou.demo1.mapper")
public class SampleTest {

    @Autowired
    private UserMapper userMapper;

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

测试结果:

在这里插入图片描述

(2)插入测试:

@Test
public void insertTest(){
    System.out.println(("----- insertOne method test ------"));
    User user = new User();
    user.setLastName("uzi");
    user.setEmail("uzi@qq.com");
    user.setGender(0);
    user.setAge(24);
    user.setVersion(1);
    int i = userMapper.insert(user);
    System.out.println(i);
}

测试结果:

在这里插入图片描述

(3)更新测试:

注:不能设置version的值(因为没有引入乐观锁的插件),否则会抛出mybatis-plus乐观锁异常

 <!--乐观锁插件-->
 <bean class="com.baomidou.mybatisplus.plugins.OptimisticLockerInterceptor"/>

具体配置请点击:
https://mp.baomidou.com/guide/interceptor-optimistic-locker.html#optimisticlockerinnerinterceptor

@Test
public void updateTest(){
    System.out.println(("----- updateOneById method test ------"));
    User user = new User();
    user.setId(24);
    user.setLastName("mlxg");
    user.setEmail("mlxg@qq.com");
    user.setGender(1);
    user.setAge(23);
    int i = userMapper.updateById(user);
    System.out.println(i);
}

测试结果:

在这里插入图片描述

(4)删除测试:

@Test
public void deleteTest(){
    System.out.println(("----- deleteOneById method test ------"));
    int i = userMapper.deleteById(24);
    System.out.println(i);
}

测试结果:

在这里插入图片描述

如果你觉得文章对你有帮助,那就请作者喝杯咖啡吧☕
微信
支付宝
😀
😃
😄
😁
😆
😅
🤣
😂
🙂
🙃
😉
😊
😇
🥰
😍
🤩
😘
😗
☺️
😚
😙
🥲
😋
😛
😜
🤪
😝
🤑
🤗
🤭
🫢
🫣
🤫
🤔
🤨
😐
😑
😶
😏
😒
🙄
😬
😮‍💨
🤤
😪
😴
😷
🤒
🤕
🤢
🤮
🤧
🥵
🥶
🥴
😵
😵‍💫
🤯
🥳
🥺
😠
😡
🤬
🤯
😈
👿
💀
☠️
💩
👻
👽
👾
🤖
😺
😸
😹
😻
😼
😽
🙀
😿
😾
👋
🤚
🖐️
✋️
🖖
🫱
🫲
🫳
🫴
🫷
🫸
👌
🤌
🤏
✌️
🤞
🫰
🤟
🤘
🤙
👈️
👉️
👆️
🖕
👇️
☝️
🫵
👍️
👎️
✊️
👊
🤛
🤜
👏
🙌
👐
🤲
🤝
🙏
✍️
💅
🤳
💪
🦾
🦿
🦵
🦶
👂
🦻
👃
👶
👧
🧒
👦
👩
🧑
👨
👩‍🦱
👨‍🦱
👩‍🦰
👨‍🦰
👱‍♀️
👱‍♂️
👩‍🦳
👨‍🦳
👩‍🦲
👨‍🦲
🧔‍♀️
🧔‍♂️
👵
🧓
👴
👲
👳‍♀️
👳‍♂️
🧕
👮‍♀️
👮‍♂️
👷‍♀️
👷‍♂️
💂‍♀️
💂‍♂️
🕵️‍♀️
🕵️‍♂️
👩‍⚕️
👨‍⚕️
👩‍🌾
👨‍🌾
👩‍🍳
👨‍🍳
🐶
🐱
🐭
🐹
🐰
🦊
🐻
🐼
🐨
🐯
🦁
🐮
🐷
🐸
🐵
🐔
🐧
🐦
🦅
🦉
🐴
🦄
🐝
🪲
🐞
🦋
🐢
🐍
🦖
🦕
🐬
🦭
🐳
🐋
🦈
🐙
🦑
🦀
🦞
🦐
🐚
🐌
🦋
🐛
🦟
🪰
🪱
🦗
🕷️
🕸️
🦂
🐢
🐍
🦎
🦖
🦕
🐊
🐢
🐉
🦕
🦖
🐘
🦏
🦛
🐪
🐫
🦒
🦘
🦬
🐃
🐂
🐄
🐎
🐖
🐏
🐑
🐐
🦌
🐕
🐩
🦮
🐕‍🦺
🐈
🐈‍⬛
🐓
🦃
🦚
🦜
🦢
🦩
🕊️
🐇
🦝
🦨
🦡
🦫
🦦
🦥
🐁
🐀
🐿️
🦔
🌵
🎄
🌲
🌳
🌴
🌱
🌿
☘️
🍀
🎍
🎋
🍃
🍂
🍁
🍄
🌾
💐
🌷
🌹
🥀
🌺
🌸
🌼
🌻
🌞
🌝
🌛
🌜
🌚
🌕
🌖
🌗
🌘
🌑
🌒
🌓
🌔
🌙
🌎
🌍
🌏
🪐
💫
🌟
🔥
💥
☄️
☀️
🌤️
🌥️
🌦️
🌧️
⛈️
🌩️
🌨️
❄️
☃️
🌬️
💨
💧
💦
🌊
🍇
🍈
🍉
🍊
🍋
🍌
🍍
🥭
🍎
🍏
🍐
🍑
🍒
🍓
🥝
🍅
🥥
🥑
🍆
🥔
🥕
🌽
🌶️
🥒
🥬
🥦
🧄
🧅
🍄
🥜
🍞
🥐
🥖
🥨
🥯
🥞
🧇
🧀
🍖
🍗
🥩
🥓
🍔
🍟
🍕
🌭
🥪
🌮
🌯
🥙
🧆
🥚
🍳
🥘
🍲
🥣
🥗
🍿
🧈
🧂
🥫
🍱
🍘
🍙
🍚
🍛
🍜
🍝
🍠
🍢
🍣
🍤
🍥
🥮
🍡
🥟
🥠
🥡
🦪
🍦
🍧
🍨
🍩
🍪
🎂
🍰
🧁
🥧
🍫
🍬
🍭
🍮
🍯
🍼
🥛
🍵
🍶
🍾
🍷
🍸
🍹
🍺
🍻
🥂
🥃
🥤
🧃
🧉
🧊
🗺️
🏔️
⛰️
🌋
🏕️
🏖️
🏜️
🏝️
🏞️
🏟️
🏛️
🏗️
🏘️
🏙️
🏚️
🏠
🏡
🏢
🏣
🏤
🏥
🏦
🏨
🏩
🏪
🏫
🏬
🏭
🏯
🏰
💒
🗼
🗽
🕌
🛕
🕍
⛩️
🕋
🌁
🌃
🏙️
🌄
🌅
🌆
🌇
🌉
🎠
🎡
🎢
💈
🎪
🚂
🚃
🚄
🚅
🚆
🚇
🚈
🚉
🚊
🚝
🚞
🚋
🚌
🚍
🚎
🚐
🚑
🚒
🚓
🚔
🚕
🚖
🚗
🚘
🚙
🚚
🚛
🚜
🏎️
🏍️
🛵
🦽
🦼
🛺
🚲
🛴
🛹
🚏
🛣️
🛤️
🛢️
🚨
🚥
🚦
🚧
🛶
🚤
🛳️
⛴️
🛥️
🚢
✈️
🛩️
🛫
🛬
🪂
💺
🚁
🚟
🚠
🚡
🛰️
🚀
🛸
🧳
📱
💻
⌨️
🖥️
🖨️
🖱️
🖲️
💽
💾
📀
📼
🔍
🔎
💡
🔦
🏮
📔
📕
📖
📗
📘
📙
📚
📓
📒
📃
📜
📄
📰
🗞️
📑
🔖
🏷️
💰
💴
💵
💶
💷
💸
💳
🧾
✉️
📧
📨
📩
📤
📥
📦
📫
📪
📬
📭
📮
🗳️
✏️
✒️
🖋️
🖊️
🖌️
🖍️
📝
📁
📂
🗂️
📅
📆
🗒️
🗓️
📇
📈
📉
📊
📋
📌
📍
📎
🖇️
📏
📐
✂️
🗃️
🗄️
🗑️
🔒
🔓
🔏
🔐
🔑
🗝️
🔨
🪓
⛏️
⚒️
🛠️
🗡️
⚔️
🔫
🏹
🛡️
🔧
🔩
⚙️
🗜️
⚗️
🧪
🧫
🧬
🔬
🔭
📡
💉
🩸
💊
🩹
🩺
🚪
🛏️
🛋️
🪑
🚽
🚿
🛁
🧴
🧷
🧹
🧺
🧻
🧼
🧽
🧯
🛒
🚬
⚰️
⚱️
🗿
🏧
🚮
🚰
🚹
🚺
🚻
🚼
🚾
🛂
🛃
🛄
🛅
⚠️
🚸
🚫
🚳
🚭
🚯
🚱
🚷
📵
🔞
☢️
☣️
❤️
🧡
💛
💚
💙
💜
🖤
💔
❣️
💕
💞
💓
💗
💖
💘
💝
💟
☮️
✝️
☪️
🕉️
☸️
✡️
🔯
🕎
☯️
☦️
🛐
🆔
⚛️
🉑
☢️
☣️
📴
📳
🈶
🈚
🈸
🈺
🈷️
✴️
🆚
💮
🉐
㊙️
㊗️
🈴
🈵
🈹
🈲
🅰️
🅱️
🆎
🆑
🅾️
🆘
🛑
💢
💯
💠
♨️
🚷
🚯
🚳
🚱
🔞
📵
🚭
‼️
⁉️
🔅
🔆
🔱
⚜️
〽️
⚠️
🚸
🔰
♻️
🈯
💹
❇️
✳️
🌐
💠
Ⓜ️
🌀
💤
🏧
🚾
🅿️
🈳
🈂️
🛂
🛃
🛄
🛅
  2 条评论
召田最帅boy 博主   广东省深圳市

写博客真是一件记录自己成长的事情👍