【数据篇】SpringBoot 整合 MyBatis 操作 MySql

写在最前

• MyBatis 入门必读
• Docker安装Mysql

MySql 准备数据

创建表

CREATE TABLE `sys_user` (
                            `user_id` bigint NOT NULL,
                            `username` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户名',
                            PRIMARY KEY (`user_id`),
                            KEY `user_idx1_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='用户表';

初始化数据

INSERT INTO `mingyue_springboot`.`sys_user` (`user_id`, `username`) VALUES (1, 'mingyue');
INSERT INTO `mingyue_springboot`.`sys_user` (`user_id`, `username`) VALUES (2, 'Strive');

SpringBoot 整合 MyBatis

Demo 地址：mingyue-springboot-mybatis

1.引入依赖

<!--  mybatis  -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.2</version>
</dependency>
<!--  Mysql数据库驱动  -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

2.修改 SpringBoot 配置文件

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: 123456
    url: jdbc:mysql://127.0.0.1:3306/mingyue?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
    hikari:
      # 连接池最大连接数
      maximum-pool-size: 12
      # 空闲时保持最小连接数
      minimum-idle: 5
      # 空闲连接存活时间
      idle-timeout: 300000
      # 连接超时时间
      connection-timeout: 20000
      # 测试sql
      connection-test-query: select 1
      
# mybatis 配置
mybatis:
  mapper-locations:
    - classpath:mapper/*.xml
    - classpath*:com/**/mapper/*.xml

3.创建实体类

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

/** @author Strive */
@Data
@ToString
@Builder
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(value = "用户实体类", description = "用户信息描述类")
public class MingYueUser {

  @ApiModelProperty(value = "用户id")
  private Long userId;

  @ApiModelProperty(value = "用户名")
  private String username;
}

4.创建 Mapper 接口

import com.csp.mingyue.mybatis.model.MingYueUser;
import org.apache.ibatis.annotations.Mapper;

/**
 * @author Strive
 * @date 2022/4/20 15:25
 * @description
 */
@Mapper
public interface SysUserMapper {

  /**
   * 根据用户ID查询用户信息
   *
   * @param userId 用户ID
   * @return 用户信息
   */
  MingYueUser queryUserById(Long userId);
}

5.创建 Mapper 映射文件

根据 mybatis.mapper-locations 配置地址创建 Mapper 映射文件

<?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="com.csp.mingyue.mybatis.mapper.SysUserMapper">
  <!-- 通用查询映射结果 -->
  <resultMap id="baseResultMap" type="com.csp.mingyue.mybatis.model.MingYueUser">
    <id column="user_id" property="userId"/>
    <result column="username" property="username"/>
  </resultMap>

  <select id="queryUserById" resultMap="baseResultMap">
    select *
    from sys_user
    where user_id = #{userId}
  </select>
</mapper>

6.创建 Service 与接口

• Service

  import com.csp.mingyue.mybatis.mapper.SysUserMapper;
  import com.csp.mingyue.mybatis.model.MingYueUser;
  import lombok.RequiredArgsConstructor;
  import org.springframework.stereotype.Service;
  
  /** @author Strive */
  @Service
  @RequiredArgsConstructor
  public class MingYueUserService {
  
    private final SysUserMapper sysUserMapper;
  
    /**
     * 根据用户ID查询用户信息
     *
     * @param userId 用户ID
     * @return 用户信息
     */
    public MingYueUser queryUserById(Long userId) {
      return sysUserMapper.queryUserById(userId);
    }
  }
  

• 接口

  import com.csp.mingyue.mybatis.model.MingYueUser;
  import com.csp.mingyue.mybatis.service.MingYueUserService;
  import io.swagger.annotations.Api;
  import io.swagger.annotations.ApiOperation;
  import lombok.RequiredArgsConstructor;
  import org.springframework.http.ResponseEntity;
  import org.springframework.web.bind.annotation.GetMapping;
  import org.springframework.web.bind.annotation.PathVariable;
  import org.springframework.web.bind.annotation.RequestMapping;
  import org.springframework.web.bind.annotation.RestController;
  
  /** @author Strive */
  @Api(tags = "用户模块")
  @RestController
  @RequiredArgsConstructor
  @RequestMapping("/user")
  public class MingYueUserController {
  
    private final MingYueUserService mingYueUserService;
  
    @ApiOperation("根据用户ID查询用户信息")
    @GetMapping("/{userId}")
    public ResponseEntity<MingYueUser> queryUserById(@PathVariable Long userId) {
      return ResponseEntity.ok(mingYueUserService.queryUserById(userId));
    }
  }
  

7.测试接口

访问：http://127.0.0.1:8080/user/2

返回示例：

{
    "userId": 2,
    "username": "Strive"
}