本文介绍 Spring Boot 2 集成 MariaDB Connector/J 驱动连接 MySQL 数据库的方法。
目录
- MariaDB Connector/J 简介
- 版本说明
- 代码示例
MariaDB Connector/J 简介
MariaDB 是一个开源关系型数据库,由 MySQL 的一些原始开发者领导,与 MySQL 保持高度兼容。
尽管 MySQL 官方提供了 MySQL Connector/J 驱动,但是因为使用了 GPL V2.0 协议,导致所有引用此驱动的软件都不得不开源,MariaDB Connector/J 驱动使用的是 LGPL V2.1 开源协议,对商业应用更加友好,因此可以使用 MariaDB Connector/J 替代 MySQL Connector/J 用于连接 MySQL 数据库。
版本说明
- JDK 8
- MySQL 8.x
- MariaDB Connector/J 2.6.0
代码示例
- 创建存储用户信息的数据表。
CREATE TABLE `user` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(16) NOT NULL COMMENT '姓名',
`age` INT UNSIGNED NOT NULL COMMENT '年龄',
`email` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '电子邮箱',
PRIMARY KEY (`id`),
UNIQUE INDEX `uk_name` (`name` ASC)
) COMMENT='用户信息';
创建 Spring Boot 工程,参考:IntelliJ IDEA 创建 Spring Boot 工程。
在生成的
pom
文件中添加spring-boot-starter-jdbc
和mariadb-java-client
依赖。
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.7.RELEASE</version>
<relativePath/>
</parent>
<groupId>tutorial.spring.boot</groupId>
<artifactId>spring-boot-mysql-mariadb-driver</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring-boot-mysql-mariadb-driver</name>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>2.6.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
- 在
application.yml
中添加数据源配置。
spring:
datasource:
driver-class-name: org.mariadb.jdbc.Driver
username: root
password: 123456
url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false
- 编写映射数据表的领域模型类。
package tutorial.spring.boot.mysql.domain;
import java.util.Objects;
public class User {
/**
* 数据库表 user 列名
*/
public static class ColumnConstant {
public static final String ID = "id";
public static final String NAME = "name";
public static final String AGE = "age";
public static final String EMAIL = "email";
}
private Long id;
private String name;
private Integer age;
private String email;
public User(String name, Integer age, String email) {
this.name = name;
this.age = age;
this.email = email;
}
// Getter and Setter 略
@Override
public boolean equals(Object o) {
if (this == o) {
return true;
}
if (o == null || getClass() != o.getClass()) {
return false;
}
User user = (User) o;
if (!Objects.equals(name, user.name)) {
return false;
}
if (!Objects.equals(age, user.age)) {
return false;
}
return Objects.equals(email, user.email);
}
@Override
public int hashCode() {
int result = name != null ? name.hashCode() : 0;
result = 31 * result + (age != null ? age.hashCode() : 0);
result = 31 * result + (email != null ? email.hashCode() : 0);
return result;
}
// toString 略
}
- 编写 DAO(Data Access Object)层接口。
package tutorial.spring.boot.mysql.dao;
import tutorial.spring.boot.mysql.domain.User;
import java.sql.SQLException;
import java.util.List;
public interface UserDao {
/**
* 查询用户总数
*
* @return 记录总数
*/
int count() throws SQLException;
/**
* 根据 ID 删除用户
*
* @param id 用户ID,对应主键
* @return 删除记录数目
*/
int delete(long id);
/**
* 新增用户
*
* @param user 封装用户信息的User对象
* @return 插入记录主键
*/
long insert(User user) throws SQLException;
/**
* 批量新增用户
*
* @return 新增记录总数
*/
int insertBatch(List<User> users);
/**
* 根据 ID 查询用户
*
* @param id 用户ID,对应主键
* @return 查询到的用户记录,如无对应记录则返回 null
*/
User select(long id);
/**
* 查询全部用户
*
* @return 封装用户信息的User对象列表
*/
List<User> selectAll();
/**
* 更新用户
*
* @param user 封装用户信息的User对象
* @return 更新记录数
*/
int update(User user);
}
- 编写 DAO(Data Access Object)层实现。
package tutorial.spring.boot.mysql.dao.impl;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import tutorial.spring.boot.mysql.dao.UserDao;
import tutorial.spring.boot.mysql.domain.User;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;
@Repository
public class UserDaoImpl implements UserDao {
private final JdbcOperations jdbcOperations;
public UserDaoImpl(JdbcOperations jdbcOperations) {
this.jdbcOperations = jdbcOperations;
}
@Override
public int count() throws SQLException {
String sql = "SELECT COUNT(*) FROM user";
Integer count = jdbcOperations.queryForObject(sql, Integer.class);
if (Objects.isNull(count)) {
throw new SQLException();
}
return count;
}
@Override
public int delete(long id) {
String sql = "DELETE FROM user WHERE id=?";
return jdbcOperations.update(sql, id);
}
@Override
public long insert(User user) throws SQLException {
if (Objects.isNull(user)) {
throw new IllegalArgumentException("Param[user] is null!");
}
final String sql = "INSERT INTO user ("
+ User.ColumnConstant.NAME + ", "
+ User.ColumnConstant.AGE + ", "
+ User.ColumnConstant.EMAIL + ") VALUES (?, ?, ?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcOperations.update(connection -> {
PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
preparedStatement.setString(1, Objects.isNull(user.getName()) ? "Anonymous User" : user.getName());
preparedStatement.setInt(2, Objects.isNull(user.getAge()) ? -1 : user.getAge());
preparedStatement.setString(3, Objects.isNull(user.getEmail()) ? "" : user.getEmail());
return preparedStatement;
}, keyHolder);
if (Objects.isNull(keyHolder.getKey())) {
throw new SQLException();
}
return keyHolder.getKey().longValue();
}
@Override
public int insertBatch(List<User> users) {
List<Object[]> params = users.stream().map(user -> new Object[]{
user.getName(),
user.getAge(),
user.getEmail()
}).collect(Collectors.toList());
final String sql = "INSERT INTO user ("
+ User.ColumnConstant.NAME + ", "
+ User.ColumnConstant.AGE + ", "
+ User.ColumnConstant.EMAIL + ") VALUES (?, ?, ?)";
int[] result = jdbcOperations.batchUpdate(sql, params);
return Arrays.stream(result).sum();
}
@Override
public User select(long id) {
String sql = "SELECT "
+ User.ColumnConstant.ID + ", "
+ User.ColumnConstant.NAME + ", "
+ User.ColumnConstant.AGE + ", "
+ User.ColumnConstant.EMAIL
+ " FROM user WHERE id=?";
List<User> users = jdbcOperations.query(sql, this::mapResultSetToUser, id);
return users.size() == 0 ? null : users.get(0);
}
private User mapResultSetToUser(ResultSet resultSet, int rowNum)
throws SQLException {
User user = new User(resultSet.getString(User.ColumnConstant.NAME),
resultSet.getInt(User.ColumnConstant.AGE),
resultSet.getString(User.ColumnConstant.EMAIL));
user.setId(resultSet.getLong(User.ColumnConstant.ID));
return user;
}
@Override
public List<User> selectAll() {
String sql = "SELECT "
+ User.ColumnConstant.ID + ", "
+ User.ColumnConstant.NAME + ", "
+ User.ColumnConstant.AGE + ", "
+ User.ColumnConstant.EMAIL
+ " FROM user";
return jdbcOperations.query(sql, this::mapResultSetToUser);
}
@Override
public int update(User user) {
if (Objects.isNull(user)) {
throw new IllegalArgumentException("Param[user] is null!");
}
if (Objects.isNull(user.getId())) {
throw new IllegalArgumentException("The [id] attribute in param[user] is null!");
}
String sql = "UPDATE user SET name=?, age=?, email=? WHERE id=?";
return jdbcOperations.update(sql,
Objects.isNull(user.getName()) ? "Anonymous User" : user.getName(),
Objects.isNull(user.getAge()) ? -1 : user.getAge(),
Objects.isNull(user.getEmail()) ? "" : user.getEmail(),
user.getId()
);
}
}
- 基于 JUnit 5 和 Spring Boot 测试框架编写单元测试。
package tutorial.spring.boot.mysql.dao;
import org.assertj.core.api.Assertions;
import org.junit.jupiter.api.Order;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.transaction.annotation.Transactional;
import tutorial.spring.boot.mysql.domain.User;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
@SpringBootTest
@Transactional(rollbackFor = Exception.class)
public class UserDaoTest {
@Autowired
private UserDao userDao;
@Test
@Order(1)
public void testNotNull() {
Assertions.assertThat(userDao).isNotNull();
}
@Test
@Order(2)
public void testCount() throws SQLException {
Assertions.assertThat(userDao.count()).isEqualTo(0);
userDao.insert(new User("Tom", 32, "tom@zzz.org"));
userDao.insert(new User("Lily", 16, "lily@zzz.org"));
userDao.insert(new User("James", 28, "james@zzz.org"));
Assertions.assertThat(userDao.count()).isEqualTo(3);
}
@Test
@Order(3)
public void testDelete() throws SQLException {
long id = userDao.insert(new User("Harry", 12, "harry@magic.com"));
Assertions.assertThat(userDao.delete(id)).isEqualTo(1);
}
@Test
@Order(4)
public void testInsert() throws SQLException {
int count = userDao.count();
User user = new User("Jack", 18, "jack@xxx.com");
long id = userDao.insert(user);
Assertions.assertThat(id).isGreaterThan(0);
Assertions.assertThat(userDao.count()).isEqualTo(count + 1);
User result = userDao.select(id);
Assertions.assertThat(result).isEqualTo(user);
}
@Test
@Order(5)
public void testInsertBatch() {
List<User> users = Arrays.asList(new User("Tom", 32, "tom@zzz.org"),
new User("Lily", 16, "lily@zzz.org"),
new User("James", 28, "james@zzz.org"),
new User("Jack", 18, "jack@xxx.com"));
Assertions.assertThat(userDao.insertBatch(users)).isEqualTo(4);
List<User> allUsers = userDao.selectAll();
users.forEach(user -> Assertions.assertThat(allUsers.contains(user)).isTrue());
}
@Test
@Order(6)
public void testSelect() throws SQLException {
User user = new User("Lily", 16, "lily@zzz.org");
long id = userDao.insert(user);
User result = userDao.select(id);
Assertions.assertThat(result).isNotNull();
Assertions.assertThat(result).isEqualTo(user);
}
@Test
@Order(7)
public void testSelectAll() throws SQLException {
int count = userDao.count();
List<User> users = Arrays.asList(new User("Tom", 32, "tom@zzz.org"),
new User("Lily", 16, "lily@zzz.org"),
new User("James", 28, "james@zzz.org"),
new User("Jack", 18, "jack@xxx.com"),
new User("Harry", 12, "harry@magic.com"));
Assertions.assertThat(userDao.insertBatch(users)).isEqualTo(5);
List<User> allUsers = userDao.selectAll();
Assertions.assertThat(allUsers.size()).isGreaterThan(0);
Assertions.assertThat(userDao.count()).isEqualTo(count + 5);
users.forEach(user -> Assertions.assertThat(allUsers.contains(user)).isTrue());
}
@Test
@Order(8)
public void testUpdate() throws SQLException {
User user = new User("Jack", 18, "jack@xxx.com");
long id = userDao.insert(user);
user.setId(id);
User result1 = userDao.select(id);
Assertions.assertThat(result1).isEqualTo(user);
user.setName("Changed");
user.setAge(19);
user.setEmail("unknown@yyy.com");
Assertions.assertThat(userDao.update(user)).isEqualTo(1);
User result2 = userDao.select(id);
Assertions.assertThat(result2).isNotEqualTo(result1);
Assertions.assertThat(result2).isEqualTo(user);
}
}
执行过程略。