mysql拦截器实现crud_Spring Boot 2.x + 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的时候以下四个依赖项都需要引入：

com.gitee.sunchenbin.mybatis.actable

mybatis-enhance-actable

1.0.3

org.apache.commons

commons-lang3

3.4

net.sf.json-lib

json-lib

2.4

jdk15

commons-logging

commons-logging

com.alibaba

druid

1.0.18

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

4.0.0

MybatisDemo

MybatisDemo

1.0-SNAPSHOT

org.springframework.boot

spring-boot-starter-parent

2.1.6.RELEASE

1.8

org.springframework.boot

spring-boot-maven-plugin

org.springframework.boot

spring-boot-starter-web

org.mybatis.spring.boot

mybatis-spring-boot-starter

1.3.2

mysql

mysql-connector-java

com.gitee.sunchenbin.mybatis.actable

mybatis-enhance-actable

1.0.3

org.apache.commons

commons-lang3

3.4

net.sf.json-lib

json-lib

2.4

jdk15

commons-logging

commons-logging

com.alibaba

druid

1.0.18

org.springframework.boot

spring-boot-starter-test

test

配置 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}")

ListfindUserByName(@Param("name") String name);

/**

* 查询所有用户信息

*/

@Select("SELECT * FROM user")

ListfindAllUser();

/**

* 根据 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 Listinsert(@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 ListqueryByName(@RequestParam(value = "name", required = false) String name) {

if (name == null) {

return userService.selectAllUser();

}

return userService.selectUserByName(name);

}

@GetMapping("/update")

public Listupdate(@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 ListtestClear() {

userService.clearService();

return userService.selectAllUser();

}

@GetMapping("/changemoney")

public Listtestchangemoney() {

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 ListselectUserByName(String name) {

return userDao.findUserByName(name);

}

/**

* 查找所有用户

*/

public ListselectAllUser() {

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) {

Listusers = userDao.findUserByName(name);

if (users.isEmpty()) {

return;

}

Listids = 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程序就已经完成啦~