Spring-Boot-2x-myBatis全注解实现CRUD及自动建表

本文主要介绍一个基于 Spring Boot 2.x，mySQL 和 myBatis 完成简单的用 Web 操作数据库的 demo 程序，然后采用的是全注解方式实现的，完全不需要 xml 配置（后续会在写一个全 xml 配置 demo）。主要支持以下功能：
（1）数据库自动建表，如本例中的 user 表。
（2）数据库 CRUD(create read update delete)操作。
（3）通过 http get 操作 user 表。

环境准备：
（1）IDEA(建议使用 Ultimate 版本，会自带通过 IDEA 操作 database 的功能)
（2）MySQL
（3）Maven + JDK8

项目目录结构：

\---main
    +---java
    |   \---hello
    |       |   MainApplication.java
    |       |   
    |       +---bean
    |       |       User.java
    |       |       
    |       +---config
    |       |       MyBatisMapperScannerConfig.java
    |       |       MybatisTableConfig.java
    |       |       
    |       +---controller
    |       |       UserController.java
    |       |       
    |       +---dao
    |       |       UserDao.java
    |       |       
    |       \---service
    |               UserService.java
    |               
    \---resources
            application.properties
            sql.txt
pom.xml

数据库和用户表：
默认的使用数据库是 MySQL 下的 sakila，这个可以通过修改 application.properties 里的配置更改本地数据库名。
user 表用的是类似下面的 SQL 语句创建的：

CREATE TABLE `user` (`id` int(13) NOT NULL AUTO_INCREMENT,
    `name` varchar(33) DEFAULT NULL,
    `age` int(3) DEFAULT NULL,
    `money` double DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

本 demo 采用了自动建表的方式，即在 Spring Boot 启动的时候会自动根据
（1）application.properties 里的配置
（2）config/MyBatisMapperScannerConfig.java 和 config/ MybatisTableConfig.java
（3）bean/user.json 里面设置的注解
通过第三方框架 mybatis.actable 完成表的自动创建功能。
具体内容可以参考 https://segmentfault.com/a/11…

需要注意的是，使用第三方框架 mybatis.actable 的时候以下四个依赖项都需要引入：

        <dependency>
            <groupId>com.gitee.sunchenbin.mybatis.actable</groupId>
            <artifactId>mybatis-enhance-actable</artifactId>
            <version>1.0.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.4</version>
        </dependency>
        <dependency>
            <groupId>net.sf.json-lib</groupId>
            <artifactId>json-lib</artifactId>
            <version>2.4</version>
            <classifier>jdk15</classifier>
            <exclusions>
                <exclusion>
                    <artifactId>commons-logging</artifactId>
                    <groupId>commons-logging</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.18</version>
        </dependency>

pom.xml pom 中需要添加 boot-starter-web 的依赖，MySQL 连接的依赖，myBatis 的依赖，以及第三方框架 mybatis.actable 需要的四个依赖。

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>MybatisDemo</groupId>
    <artifactId>MybatisDemo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.6.RELEASE</version>
    </parent>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <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>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.gitee.sunchenbin.mybatis.actable</groupId>
            <artifactId>mybatis-enhance-actable</artifactId>
            <version>1.0.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.4</version>
        </dependency>
        <dependency>
            <groupId>net.sf.json-lib</groupId>
            <artifactId>json-lib</artifactId>
            <version>2.4</version>
            <classifier>jdk15</classifier>
            <exclusions>
                <exclusion>
                    <artifactId>commons-logging</artifactId>
                    <groupId>commons-logging</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.18</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

配置 application.properties，连接本地 MySQL 数据库以及自动建表配置

server.port=8333
# 数据库为 sakila
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/sakila?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# 自动建表的配置，结合 hello.config 可以自动创建 user 的表
mybatis.table.auto=create
mybatis.model.pack=hello.bean
mybatis.database.type=mysql

MyBatisMapperScannerConfig.java 自动建表配置类

package hello.config;

import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;


@Configuration
@AutoConfigureAfter(MybatisTableConfig.class)
public class MyBatisMapperScannerConfig {

    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer() throws Exception{MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setBasePackage("com.example.mapper.*;com.gitee.sunchenbin.mybatis.actable.dao.*");
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
        return mapperScannerConfigurer;
    }

}

MybatisTableConfig.java 自动建表配置类，需要配置自动建表的 User 类路径 hello.bean.*

package hello.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.beans.factory.config.PropertiesFactoryBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

@Configuration
@ComponentScan(basePackages = {"com.gitee.sunchenbin.mybatis.actable.manager.*"})
public class MybatisTableConfig {@Value("${spring.datasource.driver-class-name}")
    private String driver;

    @Value("${spring.datasource.url}")
    private String url;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Bean
    public PropertiesFactoryBean configProperties() throws Exception{PropertiesFactoryBean propertiesFactoryBean = new PropertiesFactoryBean();
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        propertiesFactoryBean.setLocations(resolver.getResources("classpath*:application.properties"));
        return propertiesFactoryBean;
    }

    @Bean
    public DruidDataSource dataSource() {DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driver);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setMaxActive(30);
        dataSource.setInitialSize(10);
        dataSource.setValidationQuery("SELECT 1");
        dataSource.setTestOnBorrow(true);
        return dataSource;
    }

    @Bean
    public DataSourceTransactionManager dataSourceTransactionManager() {DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource());
        return dataSourceTransactionManager;
    }

    @Bean
    public SqlSessionFactoryBean sqlSessionFactory() throws Exception{SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource());
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:com/gitee/sunchenbin/mybatis/actable/mapping/*/*.xml"));
        sqlSessionFactoryBean.setTypeAliasesPackage("hello.bean.*");
        return sqlSessionFactoryBean;
    }

}

User.java
用户类 User，基于全注解方式以实现自动建表

package hello.bean;

import com.gitee.sunchenbin.mybatis.actable.annotation.Column;
import com.gitee.sunchenbin.mybatis.actable.annotation.Table;
import com.gitee.sunchenbin.mybatis.actable.command.BaseModel;
import com.gitee.sunchenbin.mybatis.actable.constants.MySqlTypeConstant;

@Table(name = "user")
public class User extends BaseModel {
    private static final long serialVersionUID = 5199200306752426433L;

    @Column(name = "id", type = MySqlTypeConstant.INT, isAutoIncrement = true, length = 13, isKey = true)
    private int id;

    @Column(name = "name", type = MySqlTypeConstant.VARCHAR , length = 33, isNull = false)
    private String name;

    @Column(name = "age", type = MySqlTypeConstant.INT, length = 3, isNull = false)
    private int age;

    @Column(name = "money", type = MySqlTypeConstant.DOUBLE, isNull = false)
    private double money;

    public int getId() {return id;}

    public void setId(int id) {this.id = id;}

    public String getName() {return name;}

    public void setName(String name) {this.name = name;}

    public int getAge() {return age;}

    public void setAge(int age) {this.age = age;}

    public double getMoney() {return money;}

    public void setMoney(double money) {this.money = money;}
}

UserDao.java
Dao 层开发基于全注解实现数据库 CRUD 操作

package hello.dao;

import hello.bean.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * 基于注解实现数据库 CRUD(create read update delete)
 */
@Mapper
public interface UserDao {

    /**
     * 插入用户信息
     */
    @Insert("INSERT INTO user(name, age, money) VALUES(#{name}, #{age}, #{money})")
    void insertUser(@Param("name") String name, @Param("age") Integer age, @Param("money") Double money);

    /**
     * 通过名字查询用户信息
     */
    @Select("SELECT * FROM user WHERE name = #{name}")
    List<User> findUserByName(@Param("name") String name);

    /**
     * 查询所有用户信息
     */
    @Select("SELECT * FROM user")
    List<User> findAllUser();

    /**
     * 根据 id 更新用户信息
     */
    @Update("UPDATE user SET name = #{name},age = #{age},money= #{money} WHERE id = #{id}")
    void updateUser(@Param("name") String name, @Param("age") Integer age, @Param("money") Double money,
                    @Param("id") int id);

    /**
     * 根据 id 删除用户信息
     */
    @Delete("DELETE from user WHERE name = #{name}")
    void deleteUser(@Param("name") String name);

    /**
     * 删除 user 表里面的所有数据
     */
    @Delete("DELETE from user WHERE 1 = 1")
    void deleteAllUserData();}

UserController.java
Controller 层实现 http get 的 insert，query，update，delete，clear 等操作。

package hello.controller;

import hello.bean.User;
import hello.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;


/**
 * 实现 CRUD http 请求对应 controller 接口
 */
@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserService userService;

    // http://localhost:8333/user/insert?name=ace&age=18&money=0
    @GetMapping("/insert")
    public List<User> insert(@RequestParam(value = "name", required = true) String name,
                             @RequestParam(value = "age", required = true) int age,
                             @RequestParam(value = "money", required = true) double money) {userService.insertOneService(name, age, money);
        return userService.selectAllUser();}

    // http://localhost:8333/user/query?name=ace
    @GetMapping("/query")
    public List<User> queryByName(@RequestParam(value = "name", required = false) String name) {if (name == null) {return userService.selectAllUser();
        }
        return userService.selectUserByName(name);
    }

    @GetMapping("/update")
    public List<User> update(@RequestParam(value = "name", required = true) String name,
                             @RequestParam(value = "age", required = true) int age,
                             @RequestParam(value = "money", required = true) double money) {userService.updateService(name, age, money);
        return userService.selectUserByName(name);
    }

    @GetMapping("/delete")
    public String delete(@RequestParam(value = "name", required = true) String name) {userService.deleteService(name);
        return "OK";
    }

    @GetMapping("/clear")
    public List<User> testClear() {userService.clearService();
        return userService.selectAllUser();}

    @GetMapping("/changemoney")
    public List<User> testchangemoney() {userService.insertService();
        userService.changemoney();
        return userService.selectAllUser();}

}

UserService.java
Service 层

package hello.service;

import hello.bean.User;
import hello.dao.UserDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.stream.Collectors;

@Service
public class UserService {
    @Autowired
    private UserDao userDao;


    /**
     * 根据名字查找用户
     */
    public List<User> selectUserByName(String name) {return userDao.findUserByName(name);
    }

    /**
     * 查找所有用户
     */
    public List<User> selectAllUser() {return userDao.findAllUser();
    }

    /**
     * 插入两个用户
     */
    public void insertService() {userDao.insertUser("Ace", 22, 3000.0);
        userDao.insertUser("Blink", 19, 3000.0);
    }

    /**
     * 插入某个指定用户
     */
    public void insertOneService(String name, int age, double money) {userDao.insertUser(name, age, money);
    }

    /**
     * 通过名字更新用户信息
     */
    @Transactional
    public void updateService(String name, int age, double money) {List<User> users = userDao.findUserByName(name);
        if (users.isEmpty()) {return;}
        List<Integer> ids = users.stream().map(User::getId).collect(Collectors.toList());
        ids.forEach(id -> userDao.updateUser(name, age, money, id));
    }

    /**
     * 根据 id 删除用户
     */
    public void deleteService(String name) {userDao.deleteUser(name);
    }

    /**
     * 清除表内所有数据
     */
    public void clearService() {userDao.deleteAllUserData();
    }

    /**
     * 模拟事务。由于加上了 @Transactional 注解，如果转账中途出了意外 Ace 和 Blink 的钱都不会改变。*/
    @Transactional
    public void changemoney() {userDao.updateUser("Ace", 22, 2000.0, 3);
        // 模拟转账过程中可能遇到的意外状况
        int temp = 1 / 0;
        userDao.updateUser("Blink", 19, 4000.0, 4);
    }
}

MainApplication.java
Spring Boot 启动类，通过继承 CommandLineRunner 在 Spring Boot 启动的时候，在表自动创建完后会在表中插入一些数据。

package hello;

import hello.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * 这份 demo 需要本地安装 mySQL, 并会在 Spring Boot 启动的时候自动在 sakila 数据库下按照 sql.txt 里面的语句新建一个 user 的表
 */
@SpringBootApplication
public class MainApplication implements CommandLineRunner {public static void main(String[] args) {SpringApplication.run(MainApplication.class, args);
    }

    @Autowired
    UserService userService;

    @Override
    public void run(String... args) throws Exception {userService.insertService();
    }
}

功能演示：
（1）数据库表自动创建，可以通过 console 看到 user 表的创建

（2）查询 query

（3）insert 插入数据

（4）update 更新数据

（5）delete 删除数据

截止目前，一个基于 Spring Boot 2.x，mySQL 和 myBatis 完成简单的用 Web 操作数据库的全注解实现 demo 程序就已经完成啦~