1. 开发环境
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.19</version>
</dependency>
2.2 启动 MySQL 服务
net start mysql
2.3 进行连接
import java.sql.*;
public class Main {
public static void main(String argv[]) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
// JDBC 连接基本格式
// jdbc:mysql://<hostname>:<port>/<db>?key1=value1&key2=value2
String JDBC_URL = "jdbc:mysql://localhost:3306/learn_jdbc?useSSL=false&serverTimezone=UTC";
String JDBC_USER = "root";
String JDBC_PASSWORD = "root";
// 使用try来确保及时释放资源
// 连接数据库
try (Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
}
}
}
没有报错就说明连接成功。
3. 初始数据准备
-- 创建数据库learn_jdbc:
DROP DATABASE IF EXISTS learn_jdbc;
CREATE DATABASE learn_jdbc;
-- 创建表students:
USE learn_jdbc;
CREATE TABLE students (
id BIGINT AUTO_INCREMENT NOT NULL,
name VARCHAR(50) NOT NULL,
gender TINYINT(1) NOT NULL,
grade INT NOT NULL,
score INT NOT NULL,
PRIMARY KEY(id)
) Engine=INNODB DEFAULT CHARSET=UTF8;
-- 插入初始数据:
INSERT INTO students (name, gender, grade, score) VALUES ('小明', 1, 1, 88);
INSERT INTO students (name, gender, grade, score) VALUES ('小红', 1, 1, 95);
INSERT INTO students (name, gender, grade, score) VALUES ('小军', 0, 1, 93);
INSERT INTO students (name, gender, grade, score) VALUES ('小白', 0, 1, 100);
INSERT INTO students (name, gender, grade, score) VALUES ('小牛', 1, 2, 96);
INSERT INTO students (name, gender, grade, score) VALUES ('小兵', 1, 2, 99);
INSERT INTO students (name, gender, grade, score) VALUES ('小强', 0, 2, 86);
INSERT INTO students (name, gender, grade, score) VALUES ('小乔', 0, 2, 79);
INSERT INTO students (name, gender, grade, score) VALUES ('小青', 1, 3, 85);
INSERT INTO students (name, gender, grade, score) VALUES ('小王', 1, 3, 90);
INSERT INTO students (name, gender, grade, score) VALUES ('小林', 0, 3, 91);
INSERT INTO students (name, gender, grade, score) VALUES ('小贝', 0, 3, 77);
添加完查看一下。
4. 查询
注意使用 PreparedStatement
以防止SQl注入问题。后续其他操作也都是如此。
嵌套使用try
语句以保证及时释放资源。
String sql = "SELECT id, grade,name,gender FROM students WHERE gender=? AND grade=?";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
// 设置每个占位符的值, 下标从1开始
ps.setObject(1, 1);
ps.setObject(2, 1);
// 传入SQL语句并获取返回的结果集
try (ResultSet results = ps.executeQuery()) {
// 迭代获取结果
while (results.next()) {
// 按类型获取各个属性的值, 索引下标从1开始
long id = results.getLong("id");
int gender = results.getInt("gender");
String name = results.getString("name");
int grade = results.getInt("grade");
System.out.println(id+" "+name+" "+grade+" "+gender);
}
}
}
查询结果符合预期。
1 小明 1 1
2 小红 1 1
5. 插入
本质上也是用PreparedStatement
执行一条SQL语句,不过最后执行的是 executeUpdate()
String sql = "INSERT INTO students (id, grade, name, gender, score) VALUES (?, ?, ?, ?, ?)";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
// 设置每个占位符的值, 下标从1开始
ps.setObject(1, 999);
ps.setObject(2, 1);
ps.setObject(3, "Bob");
ps.setObject(4, 1);
ps.setObject(5, 88);
// 表示插入的记录数量
int n = ps.executeUpdate();
System.out.println(n); // 1
}
成功插入后,可以发现多了一条关于“Bob”的记录。
插入后的结果
此时再次执行程序,就会因为插入相同主键的记录而报错
Exception in thread "main" java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '999' for key 'students.PRIMARY'
6. 插入并获取主键
在准备数据的时候可以发现,我们的字段 id
是一个自增主键,在执行 INSERT
语句时,并不需要指定主键,数据库会自动分配主键。对于使用自增主键的程序,有个额外的步骤,就是如何获取插入后的自增主键的值。
要获取自增主键,不能先插入,再查询。因为两条SQL执行期间可能有别的程序也插入了同一个表。获取自增主键的正确写法是在创建 PreparedStatement
的时候,指定一个 RETURN_GENERATED_KEYS
标志位,表示JDBC驱动必须返回插入的自增主键。
String sql = "INSERT INTO students (grade, name, gender, score) VALUES (?, ?, ?, ?)";
// 指定标志位, 表示返回插入的自增主键
try (PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
ps.setObject(1, "1");
ps.setObject(2, "Jack");
ps.setObject(3, 2);
ps.setObject(4, 67);
int n = ps.executeUpdate();
// 获取自增主键的值
try (ResultSet results = ps.getGeneratedKeys()) {
while (results.next()) {
long id = results.getLong(1);
System.out.println(id); // 1000
}
}
}
查看新增记录的主键 id
的值,确实是1000。
7. 更新
更新操作,除了 SQL 语句不同,没有什么特别。
String sql = "UPDATE students SET score=? WHERE name=?";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setObject(1, 999);
ps.setObject(2, "Bob");
int n = ps.executeUpdate();
System.out.println(n); // 1
}
执行更新操作后,name="Bob"
的记录的 score
属性被成功修改为 999。
8. 删除
修改 SQL 语句,其余无特别。
String sql = "DELETE FROM students WHERE name=?";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setObject(1, "Bob");
int n = ps.executeUpdate();
System.out.println(n); // 1
}
name="Bob"
的记录被成功删除了。
9. 事务
9.1 事务的概念
数据库事务(Transaction)是由若干个SQL语句构成的一个操作序列。
数据库系统保证在一个事务中的所有SQL要么全部执行成功,要么全部不执行,即数据库事务具有ACID特性:
- 事务的原子性( Atomicity):一组事务,要么成功;要么撤回。
- 一致性 (Consistency):事务执行后,数据库状态与其他业务规则保持一致。如转账业务,无论事务执行成功否,参与转账的两个账号余额之和应该是不变的。
- 隔离性(Isolation):事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。
- 持久性(Durability):软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit 选项 决定什么时候吧事务保存到日志里。
9.2 MySQ 中的事务
在默认情况下,MySQL每执行一条SQL语句,都是一个单独的事务。如果需要在一个事务中包含多条SQL语句,那么需要开启事务(start transaction)和结束事务(commit或rollback)。
在执行SQL语句之前,先执行start
transaction,这就开启了一个事务(事务的起点),然后可以去执行多条SQL语句,最后要结束事务,commit表示提交,即事务中的多条SQL语句所作出的影响会持久到数据库中,或者rollback,表示回滚到事务的起点,之前做的所有操作都被撤销了。
9.3 JDBC 中的事务
在JDBC中处理事务,都是通过Connection完成的。同一事务中所有的操作,都在使用同一个Connection对象。
Connection的三个方法与事务有关:
- setAutoCommit(boolean)
设置是否为自动提交事务,如果true(默认值为true)表示自动提交,也就是每条执行的SQL语句都是一个单独的事务,如果设置为false,那么相当于开启了事务了。
- commit()
提交结束事务。
- rollback()
回滚结束事务。
基本形式
try{
// 开启事务
connection.setAutoCommit(false);
/*
一系列数据库操作
*/
// 提交事务
connection.commit();
} catch() {
// 回滚事务
connection.rollback();
} finally {
// 恢复自动提交
connection.setAutoCommit(true);
// 释放资源
connection.close();
}
9.4 具体实现
本节数据库中的初始状态和上一节结束时一致。、
Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
try {
// 关闭自动提交
connection.setAutoCommit(false);
// 执行多条 SQL 语句
insert(connection); // 1
update(connection); // 1
// delete(connection); // 1
// 手动提交事务
connection.commit();
} catch (Exception e) {
// 事务执行可能失败, 失败需要回滚
connection.rollback();
} finally {
// 恢复自动提交
connection.setAutoCommit(true);
// 释放资源
connection.close();
}
为了能更明显地看出变化,这里只执行插入和更新两个操作。可以看到数据表 students 中多出来了一条name="Bob"
且 score=999
的记录,说明事务执行成功。
10. Batch
SQL数据库对SQL语句相同,但只有参数不同的若干语句可以作为batch执行,即批量执行,这种操作有特别优化,速度远远快于循环执行每个SQL。
在JDBC代码中,我们可以利用SQL数据库的这一特性,把同一个SQL但参数不同的若干次操作合并为一个batch执行。
执行batch和执行一个SQL不同点在于,需要对同一个PreparedStatement
反复设置参数并调用addBatch()
,这样就相当于给一个SQL加上了多组参数,相当于变成了“多行”SQL。
第二个不同点是调用的不是executeUpdate()
,而是executeBatch()
,因为我们设置了多组参数,相应地,返回结果也是多个int
值,因此返回类型是int[]
,循环int[]
数组即可获取每组参数执行后影响的结果数量。
String sql = "INSERT INTO students (name, gender, grade, score) VALUES (?, ?, ?, ?)";
String[] names = {"A", "B", "C"};
try (PreparedStatement ps = connection.prepareStatement(sql)) {
// 反复设置参数并添加到 batch
for (String name : names) {
ps.setObject(1, name);
ps.setObject(2, 1);
ps.setObject(3, 2);
ps.setObject(4, 60);
ps.addBatch();
}
// 执行 batch
int[] ns = ps.executeBatch();
for (int n : ns) {
// 每个 SQL 语句的执行效果
System.out.println(n + "inserted");
}
}
可以看到三条只有 name
属性不同的记录被成功添加了。
11. 连接池
在执行JDBC的增删改查的操作时,如果每一次操作都来一次打开连接,操作,关闭连接,那么创建和销毁JDBC连接的开销就太大了。为了避免频繁地创建和销毁JDBC连接,我们可以通过连接池(Connection Pool)复用已经创建好的连接。
数据库连接池是一种复用Connection
的组件,它可以避免反复创建新连接,提高JDBC代码的运行效率。
JDBC连接池有一个标准的接口javax.sql.DataSource
,注意这个类位于Java标准库中,但仅仅是接口。要使用JDBC连接池,我们必须选择一个JDBC连接池的实现。常用的JDBC连接池有:
- HikariCP
- C3P0
- BoneCP
- Druid
目前使用最广泛的是HikariCP。我们以HikariCP为例,要使用JDBC连接池,先添加HikariCP的依赖如下:
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>2.7.1</version>
</dependency>
HikariConfig config=new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/learn_jdbc?useSSL=false&serverTimezone=UTC");
config.setUsername("root");
config.setPassword("root");
// 连接超时, 1000ms
config.addDataSourceProperty("connectionTimeout", "1000");
// 空闲超时, 60000ms
config.addDataSourceProperty("idleTimeout", "60000");
// 最大连接数
config.addDataSourceProperty("maximumPoolSize", "10");
// 创建连接池实例
DataSource ds=new HikariDataSource(config);
// 从连接池获取连接, 并及时关闭
try (Connection connection=ds.getConnection()){
// 在此处执行操作
delete(connection);
}
成功执行删除操作,name="Bob"
的记录被删除了。
通过连接池获取连接时,并不需要指定JDBC的相关URL、用户名、口令等信息,因为这些信息已经存储在连接池内部了(创建HikariDataSource
时传入的HikariConfig
持有这些信息)。一开始,连接池内部并没有连接,所以,第一次调用ds.getConnection()
,会迫使连接池内部先创建一个Connection
,再返回给客户端使用。当我们调用conn.close()
方法时(在try(resource){...}
结束处),不是真正“关闭”连接,而是释放到连接池中,以便下次获取连接时能直接返回。
因此,连接池内部维护了若干个Connection
实例,如果调用ds.getConnection()
,就选择一个空闲连接,并标记它为“正在使用”然后返回,如果对Connection
调用close()
,那么就把连接再次标记为“空闲”从而等待下次调用。这样一来,我们就通过连接池维护了少量连接,但可以频繁地执行大量的SQL语句。
通常连接池提供了大量的参数可以配置,例如,维护的最小、最大活动连接数,指定一个连接在空闲一段时间后自动关闭等,需要根据应用程序的负载合理地配置这些参数。此外,大多数连接池都提供了详细的实时状态以便进行监控。
12. 参考链接
https://www.liaoxuefeng.com/wiki/1252599548343744/1255943820274272
https://www.cnblogs.com/gdwkong/p/7633016.html