SpringBoot使用mybatis的最佳实践

基于SpringBoot 2.4.0, 使用mybatis, 数据库mysql
github: https://github.com/varyuan/awesome-java

增加如下依赖

        <!-- 默认JDBC连接池为HikariCP, https://github.com/brettwooldridge/HikariCP-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>
        <!-- mysql数据库驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.20</version>
        </dependency>

1. 配置数据源和mybatis, application.yaml

spring:
  datasource:
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/yuan?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
    # 池配置
    hikari:
      maximum-pool-size: 2
      pool-name: yuan-dev
# mybatis配置
mybatis:
  type-aliases-package: com.varyuan.awesome.po
  mapper-locations: classpath:mapper/*.xml
  configuration:
    # 开启数据表字段自动下划线转驼峰
    map-underscore-to-camel-case: true
# 打印sql
logging:
  level:
    com.varyuan.awesome.dao: debug

2. 建表造数sql

CREATE TABLE `dict`  (
    `id` int UNSIGNED AUTO_INCREMENT COMMENT '主键',
	`kind` varchar(255) NOT NULL COMMENT '种类',
	`code` varchar(255) NOT NULL COMMENT '键',
	`val` varchar(255) DEFAULT NULL COMMENT '值',
	`note` varchar(255) DEFAULT NULL COMMENT '注释',
	`is_del` tinyint DEFAULT 0 COMMENT '逻辑删除标志',
    `insert_time` datetime  DEFAULT CURRENT_TIMESTAMP(0) COMMENT '添加时间',
    `update_time` datetime  DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB  CHARACTER SET = utf8mb4 COMMENT '字典表';
INSERT INTO `dict`(`kind`, `code`, `val`, `note`) VALUES ('召唤师技能', 'flash', '闪现', NULL);

3. 新建实体类

package com.varyuan.awesome.po;

import lombok.Data;

@Data
public class Dict {
    private int id;
    // 种类
    private String kind;
    // 键
    private String code;
    // 值
    private String val;
    // 注释
    private String note;
}

4. 新建DictDao接口, 并在App上添加注解 @MapperScan(basePackages = "com.varyuan.awesome.dao")

package com.varyuan.awesome.dao;

import com.varyuan.awesome.po.Dict;

public interface DictDao {
    Dict selectById(int id);

    int insert(Dict dict);

    int delete(int id);
}

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

@SpringBootApplication
@MapperScan(basePackages = "com.varyuan.awesome.dao")
public class App {

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

4. 新建resources/mapper/DictDao.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.varyuan.awesome.dao.DictDao">

    <select id="selectById" parameterType="int" resultType="dict">
        select *
        from dict
        where id = #{id}
          and is_del = 0
    </select>

    <insert id="insert" parameterType="dict" useGeneratedKeys="true" keyProperty="id">
        insert into dict(kind, code, val, note)
        values (#{kind}, #{code}, #{val}, #{note})
    </insert>

    <delete id="delete" parameterType="int">
        update dict
        set is_del=1
        where id = #{id}
    </delete>
</mapper>

5. 测试代码

import com.varyuan.awesome.App;
import com.varyuan.awesome.dao.DictDao;
import com.varyuan.awesome.po.Dict;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;

@SpringBootTest(classes = App.class)
@Slf4j
@Transactional
public class DictDaoTest {
    @Resource
    private DictDao dictDao;

    @Test
    public void insert() {
        Dict dict = new Dict();
        dict.setKind("召唤师技能");
        dict.setCode("TP");
        dict.setVal("传送");
        int succ = dictDao.insert(dict);
        if (succ != 1) {
            log.error("fail to insert record");
        } else {
            log.info("insert success,new record id is: {}", dict.getId());
        }
    }

    @Test
    public void selectById() {
        Dict dict = dictDao.selectById(1);
        log.info("dict :{}", dict);
    }

    @Test
    public void delete() {
        int succ = dictDao.delete(1);
        if (succ != 1) {
            log.error("fail to delete record");
        } else {
            log.info("delete success");
        }
    }
}