MyBatis 复习文档

MyBatis 复习文档：结合自定义扩展（假设为MyBatis-Flex）

1. 引言
   MyBatis 是一个优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis-Flex（假设的扩展）可能提供了额外的功能，如更便捷的CRUD操作、分页插件、条件构造器等，但本文档将重点放在MyBatis的核心概念上，并假设MyBatis-Flex提供了类似MyBatis Plus的增强功能。

1.1数据库搭建
mysql> CREATE TABLE IF NOT EXISTS tb_userinfo (
id INT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
cat_name VARCHAR(20) NOT NULL COMMENT ‘类别名称’,
parent_id INT(11) COMMENT ‘父级ID’,
show_in_nav TINYINT(1) COMMENT ‘是否导航显示’,
sort_order SMALLINT(6) COMMENT ‘排序’,
create_time DATETIME NOT NULL,
update_time DATETIME NOT NULL,
deleted INT(1) NOT NULL DEFAULT 0 COMMENT ‘逻辑删除’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=‘分类表’;
1.2数据库表实现
INSERT INTO tb_userinfo (id, cat_name, parent_id, show_in_nav, sort_order, create_time, update_time, deleted)
VALUES
(1, ‘前端’, 50, TRUE, NULL, ‘2023-07-04 17:01:00’, ‘2023-07-04 17:01:00’, FALSE),
(2, ‘Java’, 50, TRUE, NULL, ‘2023-07-04 17:01:00’, ‘2023-07-04 17:01:00’, FALSE),
(3, ‘Python’, 50, TRUE, NULL, ‘2023-07-04 17:01:00’, ‘2023-07-04 17:01:00’, FALSE),
(4, ‘人工智能’, 50, TRUE, NULL, ‘2023-07-04 17:01:00’, ‘2023-07-04 17:01:00’, FALSE),
(5, ‘AIGC’, 50, TRUE, NULL, ‘2023-07-04 17:01:00’, ‘2023-07-04 17:01:00’, FALSE),
(6, ‘算法’, 50, TRUE, NULL, ‘2023-07-04 17:01:00’, ‘2023-07-04 17:01:00’, FALSE),
(7, ‘移动开发’, 50, TRUE, NULL, ‘2023-07-04 17:01:00’, ‘2023-07-04 17:01:00’, FALSE),
(8, ‘HarmonyOS’, 50, TRUE, NULL, ‘2023-07-04 17:02:00’, ‘2023-07-04 17:02:00’, FALSE),
(9, ‘推荐’, 50, TRUE, NULL, ‘2023-07-04 17:02:00’, ‘2023-07-04 17:02:00’, FALSE),
(10, ‘关注’, 50, TRUE, NULL, ‘2023-07-04 17:02:00’, ‘2023-07-04 17:02:00’, FALSE),
(11, ‘运维’, 50, TRUE, NULL, ‘2023-07-04 17:02:00’, ‘2023-07-04 17:02:00’, FALSE);

2. 环境搭建
   2.1 依赖配置
   在 Maven 项目中，首先需要在 pom.xml 文件中添加 MyBatis 和数据库驱动的依赖。如果 MyBatis-Flex 是一个 Maven 依赖，也需要添加它。
   
   org.springframework.boot
   spring-boot-starter-web
   
   
   com.mybatis-flex
   mybatis-flex-spring-boot-starter
   1.8.1
   
   
   com.mybatis-flex
   mybatis-flex-processor
   1.8.1
   provided
   
   
   com.alibaba
   druid-spring-boot-starter
   1.2.6
   
   
   mysql
   mysql-connector-java
   runtime
   
   
   org.projectlombok
   lombok
   
   
   org.springframework.boot
   spring-boot-starter-test
   test
   
   2.2 MyBatis 配置
   在 src/main/resources 目录下创建 MyBatis 的配置文件yml，并配置数据源、事务管理器以及映射文件的位置。
   spring：
   datasource:
   username: root password:
   driver-class-name: com.mysql.cj.jdbc.Driver
   url: jdbc:mysql://localhost:3306/school?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
   druid:
   initial-size: 5
   min-idle: 5
   max-active: 20
   max-wait: 60000
   time-between-eviction-runs-millis: 60000
   min-evictable-idle-time-millis: 100000
   max-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
   aop-patterns: com.example.demo.*
   stat-view-servlet:
   enabled: true
   login-username: root
   login-password: 123456
   web-stat-filter:
   enabled: true
   url-pattern: /*
   exclusions: ‘.js,.gif,.jpg,.png,.css,.ico,/druid/*’
   filters: stat,wall
   filter:
   stat:
   slow-sql-millis: 1000
   log-slow-sql: true
   enabled: true
   wall
   enabled: true
   config:
   drop-table-allow: false

3. Mapper 接口
   3.1 Mapper 接口
   定义一个 Mapper 接口，用于声明数据库操作方法。
   package com.example.spring1.demos.web.mapper;

import com.example.spring1.demos.web.pojo.ArticleCat;
import com.mybatisflex.core.BaseMapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;
@Mapper
public interface ArticleCatMapper extends BaseMapper {

// 通过ID查询课程信息
@Select("SELECT * FROM tb_userinfo WHERE id = #{id}")
ArticleCat selectCourseById(@Param("id") Integer id);


@Select("SELECT create_time FROM tb_userinfo WHERE cat_name = #{cat_name}")
Date selectCourseCreatedAtByName(@Param("cat_name") String name);

}
3.2servlet层
package com.example.spring1.demos.web.Service;

import com.example.spring1.demos.web.mapper.ArticleCatMapper;
import com.example.spring1.demos.web.pojo.ArticleCat;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.Date;
import java.util.List;

@Service
public class ArticleCatService {

private ArticleCatMapper articleCatMapper;

@Autowired
public ArticleCatService(ArticleCatMapper articleCatMapper) {
    this.articleCatMapper = articleCatMapper;
}

public ArticleCat getCourseById(Integer id) {
    return articleCatMapper.selectCourseById(id);
}

public Date getCourseCreatedAtByName(String name) {
    return articleCatMapper.selectCourseCreatedAtByName(name);
}

}
3.3控制层
package com.example.spring1.demos.web.Controller;

import com.example.spring1.demos.web.Service.ArticleCatService;
import com.example.spring1.demos.web.pojo.ArticleCat;

import org.springframework.beans.factory.annotation.Autowired;
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;

import java.util.Date;
import java.util.List;

@RestController
@RequestMapping(“/api/articleCats”)
public class ArticleCatController {

private ArticleCatService articleCatService;

@Autowired
public ArticleCatController(ArticleCatService courseService) {
    this.articleCatService = courseService;
}

@GetMapping("/{id}")
public ResponseEntity<ArticleCat> getCourseById(@PathVariable Integer id) {
    ArticleCat course = articleCatService.getCourseById(id);
    return ResponseEntity.ok(course);
}

@GetMapping("/name/{name}")
public ResponseEntity<Date> getCourseCreatedAtByName(@PathVariable String name) {
    Date createdAt = articleCatService.getCourseCreatedAtByName(name);
    return ResponseEntity.ok(createdAt);
}

}
3.4实体类
package com.example.spring1.demos.web.pojo;

import com.mybatisflex.annotation.Column;
import com.mybatisflex.annotation.Id;
import com.mybatisflex.annotation.KeyType;
import com.mybatisflex.annotation.Table;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.validation.constraints.NotBlank;
import java.io.Serializable;
import java.util.Date;

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(“文章分类”)
@Table(value = “article_cat”)
public class ArticleCat implements Serializable {
@Id(keyType = KeyType.Auto)
@ApiModelProperty(“编号”)
private Integer id;

@ApiModelProperty("分类")
@NotBlank(message = "分类不能为空")
private String cat_name;

@ApiModelProperty("父编号")
private Integer parentId;

@ApiModelProperty("排序")
private Integer sortOrder;

@ApiModelProperty("更新时间")
@Column(onInsertValue = "now()", onUpdateValue = "now()")
private Date updateTime;

@ApiModelProperty("创建时间")
@Column(onInsertValue = "now()")
private Date createTime;

@ApiModelProperty("模拟删除标记")
private Integer deleted;

@ApiModelProperty("显示在导航")
private ArticleCat show;

}
实现功能一截图：

通过查询课程名字来查询课程创建时间
功能二截图：

通过id来查询课程名称