SpringBoot2.X (二十五)：SpringBoot整合 Mybatis + MySQL CURD 示例

古城微笑少年丶 2022-05-11 14:50 196阅读 0赞

### 文章目录 ###

*   *   *  工程结构图
         *   *  1、maven 依赖：
             *  2、yml 配置文件：
             *  3、核心代码：
             *   *  User.java
                 *  MyBatisConfig.java
                 *  UserController.java
                 *  UserService.java
                 *  UserServiceImpl.java
                 *  UserMapper.java
             *  4、测试
             *   *  1）查询
                 *  2）添加
                 *  3）移除
                 *  4）修改
             *  总结：

话不多数，直接开始撸代码…

### 工程结构图 ###

开始之前先放张工程结构图

![工程结构图][70]

#### 1、maven 依赖： ####

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>1.3.2</version>
            </dependency>
    
            
            <dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper-spring-boot-starter</artifactId>
                <version>1.2.9</version>
            </dependency>
    
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.1.9</version>
            </dependency>
    
            
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>

#### 2、yml 配置文件： ####

spring:
      application:
        name: mybatis-curd
    
      datasource:
        username: root
        password: root
        url: jdbc:mysql://localhost:3306/test?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&autoReconnect=true
        continue-on-error: true
        sql-script-encoding: UTF-8
        driver-class-name: com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        druid:
          initial-size: 5
          min-idle: 5
          max-active: 20
          max-wait: 60000
          # 间隔多久进行一次检测，检测需要关闭的空闲连接
          time-between-eviction-runs-millis: 60000
          # 一个连接在池中最小生存的时间
          min-evictable-idle-time-millis: 300000
          validation-query: SELECT 1 FROM DUAL
          test-while-idle: true
          test-on-borrow: false
          test-on-return: false
          pool-prepared-statements: true
          max-pool-prepared-statement-per-connection-size: 20
          connect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
          use-global-data-source-stat: true
          filters: stat,wall,log4j2
    
    mybatis:
      type-aliases-package: com.fxbin.mybaits.*
      configuration:
        map-underscore-to-camel-case: true
        # 打印sql, 方便调试
        log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
        use-generated-keys: true
        default-statement-timeout: 60
        default-fetch-size: 100

#### 3、核心代码： ####

##### User.java #####

package com.fxbin.mybatis.bean;
    
    import lombok.Data;
    
    import java.util.Date;
    
    /**
     * created with IntelliJ IDEA.
     * author: fxbin
     * date: 2018/10/21
     * time: 5:59
     * version: 1.0
     * description:
     */
    @Data
    public class User {
    
        /**
         * 主键ID
         */
        private Integer id;
    
        /**
         * 用户名
         */
        private String username;
    
        /**
         * 密码
         */
        private String password;
    
        /**
         * 创建时间
         */
        private Date gmtCreate;
    
        /**
         * 修改时间
         */
        private Date gmtModified;
    
    }

##### MyBatisConfig.java #####

package com.fxbin.mybatis.config;
    
    import com.github.pagehelper.PageHelper;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    import java.util.Properties;
    
    /**
     * created with IntelliJ IDEA.
     * author: fxbin
     * date: 2018/10/21
     * time: 11:33
     * version: 1.0
     * description: MyBatis 分页插件配置
     */
    @Configuration
    public class MyBatisConfig {
    
        @Bean
        public PageHelper pageHelper(){
            PageHelper pageHelper = new PageHelper();
            Properties p = new Properties();
    
            // 设置为true时，会将RowBounds第一个参数offset当成pageNum页码使用
            p.setProperty("offsetAsPageNum","true");
    
            //设置为true时，使用RowBounds分页会进行count查询
            p.setProperty("rowBoundsWithCount","true");
            p.setProperty("reasonable","true");
            pageHelper.setProperties(p);
            return pageHelper;
        }
    }

##### UserController.java #####

package com.fxbin.mybatis.controller;
    
    import com.fasterxml.jackson.databind.ObjectMapper;
    import com.fxbin.mybatis.bean.User;
    import com.fxbin.mybatis.service.UserService;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.RestController;
    
    import javax.annotation.Resource;
    
    /**
     * created with IntelliJ IDEA.
     * author: fxbin
     * date: 2018/10/21
     * time: 5:53
     * version: 1.0
     * description:
     */
    @RestController
    public class UserController {
    
        @Resource
        private UserService userService;
    
        /**
         * 查询全部
         * @param page
         * @param size
         * @return
         */
        @RequestMapping("/listAll")
        public Object listAll(@RequestParam(value = "page",defaultValue = "1")int page,
                              @RequestParam(value = "size",defaultValue = "10")int size){
            return userService.listAll(page, size);
        }
    
        /**
         * 添加数据
         * @param user
         * @return
         */
        @RequestMapping("/insert")
        public int insert (User user){
            return userService.insert(user);
        }
    
        /**
         * 删除
         * @param userId
         * @return
         */
        @RequestMapping("/remove")
        public int remove(Integer userId){
            return userService.remove(userId);
        }
    
        /**
         * 修改
         * @param user
         * @return
         */
        @RequestMapping("/update")
        public int update(User user){
            return userService.update(user);
        }
    }

##### UserService.java #####

package com.fxbin.mybatis.service;
    
    import com.fxbin.mybatis.bean.User;
    
    import java.util.List;
    
    /**
     * created with IntelliJ IDEA.
     * author: fxbin
     * date: 2018/10/21
     * time: 5:54
     * version: 1.0
     * description:
     */
    public interface UserService {
    
        Object listAll(int page, int size);
    
        int insert(User user);
    
        int remove(Integer userId);
    
        int update(User user);
    }

##### UserServiceImpl.java #####

package com.fxbin.mybatis.service.impl;
    
    import com.fxbin.mybatis.bean.User;
    import com.fxbin.mybatis.mapper.UserMapper;
    import com.fxbin.mybatis.service.UserService;
    import com.github.pagehelper.PageHelper;
    import com.github.pagehelper.PageInfo;
    import org.springframework.stereotype.Service;
    
    import javax.annotation.Resource;
    import java.util.List;
    
    /**
     * created with IntelliJ IDEA.
     * author: fxbin
     * date: 2018/10/21
     * time: 5:54
     * version: 1.0
     * description:
     */
    @Service
    public class UserServiceImpl implements UserService {
    
        @Resource
        private UserMapper userMapper;
    
        @Override
        public Object listAll(int page, int size) {
            PageHelper.startPage(page, size);
            List<User> userList = userMapper.listAll();
            PageInfo<User> pageInfo = new PageInfo<>(userList);
            return pageInfo;
        }
    
        @Override
        public int insert(User user) {
            return userMapper.insert(user);
        }
    
        @Override
        public int remove(Integer userId) {
            return userMapper.remove(userId);
        }
    
        @Override
        public int update(User user) {
            return userMapper.update(user);
        }
    }

##### UserMapper.java #####

package com.fxbin.mybatis.mapper;
    
    import com.fxbin.mybatis.bean.User;
    import org.apache.ibatis.annotations.*;
    
    import java.util.List;
    
    /**
     * created with IntelliJ IDEA.
     * author: fxbin
     * date: 2018/10/21
     * time: 5:55
     * version: 1.0
     * description:
     */
    @Mapper
    public interface UserMapper {
    
        @Select({
                "select * from user"
        })
        List<User> listAll();
    
        @Insert({
                "insert into user(`username`, `password`) values(#{username}, #{password})"
        })
        int insert(User user);
    
        @Delete({
                "delete from user where id = #{userId}"
        })
        int remove(Integer userId);
    
        @Update({
                "update user set username = #{username}, password = #{password} where id = #{id}"
        })
        int update(User user);
    }

#### 4、测试 ####

##### 1）查询 #####

![在这里插入图片描述][70 1]

##### 2）添加 #####

![在这里插入图片描述][70 2]

查看数据库记录，进一步验证，确实已经新增了一条记录  
![在这里插入图片描述][70 3]

##### 3）移除 #####

![在这里插入图片描述][70 4]

查看数据库，id 为1 的记录已经成功移除

![在这里插入图片描述][70 5]

##### 4）修改 #####

我们修改之前新增的id为13 的记录，username 修改为 “修改”，password 修改为 “222”  
![在这里插入图片描述][70 6]

查看数据库修改记录，已成功

![在这里插入图片描述][70 7]

#### 总结： ####

以上就是有关 SpringBoot2.X 整合 Mysql + Mybatis 的 CURD 的简单案例，这里我没有对返回结果进行封装，各位可根据自行需要，进行返回结果的封装…

示例代码：https://gitee.com/fxbin/SpringBoot2Example

— end —

*`如有问题，请及时联系，谢谢`*

[70]: /images/20220505/4e963cc78a6d4b3b824d45655dc7b5f8.png
[70 1]: /images/20220505/bba63eb9e47446e491ea35fef573f671.png
[70 2]: /images/20220505/2580a01f9efc422884510ff35d012cca.png
[70 3]: /images/20220505/e0ff7b59117c4d469ac6a1e05a2de4d9.png
[70 4]: /images/20220505/f28e0bb7689248a5a788428c6ca97de4.png
[70 5]: /images/20220505/23dcb196e64c46e7ab01888fc3a782ee.png
[70 6]: /images/20220505/34de665d316b4f65b28f848c320ac24f.png
[70 7]: /images/20220505/89f281a560b14b74bd44ed4749085418.png