本文主要介绍一个基于 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 程序就已经完成啦~