[TOC]
> JDBC是Java DataBase Connectivity的缩写,它是Java程序访问数据库的标准接口。
使用Java程序访问数据库时,Java代码并不是直接通过TCP连接去访问数据库,而是通过JDBC接口来访问,而JDBC接口则通过JDBC驱动来实现真正对数据库的访问。
# 一. 基本概述
我们在Java代码中如果要访问MySQL,必须编写代码操作JDBC接口。注意到JDBC接口是Java标准库自带的,所以可以直接编译。而具体的JDBC驱动是由数据库厂商提供的。因此,访问某个具体的数据库,我们只需要引入该厂商提供的JDBC驱动,就可以通过JDBC接口来访问,这样保证了Java程序编写的是一套数据库访问代码,却可以访问各种不同的数据库,因为他们都提供了标准的JDBC驱动。实际上,一个MySQL的JDBC的驱动就是一个jar包。
# 二. 准备依赖
- 这里添加依赖的scope是runtime,因为编译Java程序并不需要MySQL的这个jar包,只有在运行期才需要使用。如果把runtime改成compile,虽然也能正常编译,但是在IDE里写程序的时候,会多出来一大堆类似com.mysql.jdbc.Connection这样的类,非常容易与Java标准库的JDBC接口混淆,所以坚决不要设置为compile。
```
mysql
mysql-connector-java
5.1.49
runtime
```
- 测试数据
```
-- 创建数据库learjdbc:
DROP DATABASE IF EXISTS learnjdbc;
CREATE DATABASE learnjdbc;
-- 创建登录用户learn/口令learnpassword
CREATE USER IF NOT EXISTS learn@'%' IDENTIFIED BY 'learnpassword';
GRANT ALL PRIVILEGES ON learnjdbc.* TO learn@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
-- 创建表students:
USE learnjdbc;
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);
```
# 三. MySQL示例
mysql连接的格式
```
jdbc:mysql://:/?key1=value1&key2=value2
```
```
package com;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MySQLHello {
public static void main(String[] args) throws SQLException {
Hello hello = new Hello();
hello.select();
hello.insert();
hello.update();
hello.delete();
hello.connection.close();
}
}
class Hello {
String JDBC_URL;
String JDBC_USER;
String JDBC_PASSWORD;
Connection connection;
public Hello() throws SQLException {
this.JDBC_URL = "jdbc:mysql://127.0.0.1:3306/learnjdbc?useSSL=false&characterEncoding=utf8";
this.JDBC_USER = "learn";
this.JDBC_PASSWORD = "learnpassword";
this.connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
}
// 查询
public void select() throws SQLException {
String sql1 = "SELECT id, grade, name, gender FROM students WHERE gender=? AND grade=?";
try (PreparedStatement ps = connection.prepareStatement(sql1)) {
ps.setObject(1, 1); // 注意:索引从1开始
ps.setObject(2, 3);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
long id = rs.getLong(1);
long grade = rs.getLong("grade");
String name = rs.getString("name");
int gender = rs.getInt("gender");
System.out.printf("id=%s, grade=%s, name=%s, gender=%s\n",
id, grade, name, gender);
}
}
}
}
// 插入
public void insert() throws SQLException {
String sql2 = "INSERT INTO students (name, gender, grade, score) VALUES (?,?,?,?)";
try (PreparedStatement ps = connection.prepareStatement(sql2, Statement.RETURN_GENERATED_KEYS)) {
ps.setObject(1, "Bob"); // 注意:索引从1开始
ps.setObject(2, 1); // grade
ps.setObject(3, 3); // name
ps.setObject(4, 100); // gender
int n = ps.executeUpdate(); // 1, 返回表示插入的记录数量
// 获取自增的键值(这里是主键,也可以不是主键)
try (ResultSet rs = ps.getGeneratedKeys()) {
if (rs.next()) {
long id = rs.getLong(1); // 注意:索引从1开始
System.out.println("id = " + id);
}
}
}
}
// 更新
public void update() throws SQLException {
try (PreparedStatement ps = connection.prepareStatement("UPDATE students SET name=? WHERE id=?")) {
ps.setObject(1, "Bob"); // 注意:索引从1开始
ps.setObject(2, 12);
int n = ps.executeUpdate(); // 返回更新的行数
}
}
// 删除
public void delete() throws SQLException {
try (PreparedStatement ps = connection.prepareStatement("DELETE FROM students WHERE id=?")) {
ps.setObject(1, 12); // 注意:索引从1开始
int n = ps.executeUpdate(); // 删除的行数
}
}
}
```
| **SQL数据类型** | **Java数据类型** |
|---------------|--------------------------|
| BIT, BOOL | boolean |
| INTEGER | int |
| BIGINT | long |
| REAL | float |
| FLOAT, DOUBLE | double |
| CHAR, VARCHAR | String |
| DECIMAL | BigDecimal |
| DATE | java.sql.Date, LocalDate |
| TIME | java.sql.Time, LocalTime |
# 四. JDBC事务
- 默认情况下,我们获取到Connection连接后,总是处于“自动提交”模式,也就是每执行一条SQL都是作为事务自动执行的,这也是为什么前面几节我们的更新操作总能成功的原因:因为默认有这种“隐式事务”。只要关闭了Connection的autoCommit,那么就可以在一个事务中执行多条语句,事务以commit()方法结束。
```
Connection conn = openConnection();
try {
// 关闭自动提交:
conn.setAutoCommit(false);
// 执行多条SQL语句:
insert(); update(); delete();
// 提交事务:
conn.commit();
} catch (SQLException e) {
// 回滚事务:
conn.rollback();
} finally {
conn.setAutoCommit(true);
conn.close();
}
```
- 如果要设定事务的隔离级别,可以使用如下代码:
```
// 设定隔离级别为READ COMMITTED:
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
```
- 如果没有调用上述方法,那么会使用数据库的默认隔离级别。MySQL的默认隔离级别是REPEATABLE READ。
# 五. JDBC之Batch
- 在JDBC代码中,我们可以利用SQL数据库的这一特性,把同一个SQL但参数不同的若干次操作合并为一个batch执行。我们以批量插入为例
```
try (PreparedStatement ps = conn.prepareStatement("INSERT INTO students (name, gender, grade, score) VALUES (?, ?, ?, ?)")) {
// 对同一个PreparedStatement反复设置参数并调用addBatch():
for (Student s : students) {
ps.setString(1, s.name);
ps.setBoolean(2, s.gender);
ps.setInt(3, s.grade);
ps.setInt(4, s.score);
ps.addBatch(); // 添加到batch
}
// 执行batch:
int[] ns = ps.executeBatch();
for (int n : ns) {
System.out.println(n + " inserted."); // batch中每个SQL执行的结果数量
}
}
```
# 六. JDBC连接池
- 目前使用最广泛的JDBC连接池是HikariCP
```
com.zaxxer
HikariCP
2.7.1
```
- 我们需要创建一个DataSource实例,这个实例就是连接池,注意创建DataSource也是一个非常昂贵的操作,所以通常DataSource实例总是作为一个全局变量存储,并贯穿整个应用程序的生命周期。
- 当我们调用conn.close()方法时(在try(resource){...}结束处),不是真正“关闭”连接,而是释放到连接池中,以便下次获取连接时能直接返回。
- 因此,连接池内部维护了若干个Connection实例,如果调用ds.getConnection(),就选择一个空闲连接,并标记它为“正在使用”然后返回,如果对Connection调用close(),那么就把连接再次标记为“空闲”从而等待下次调用。这样一来,我们就通过连接池维护了少量连接,但可以频繁地执行大量的SQL语句。
```
package com;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class MySQLHello {
public static void main(String[] args) throws SQLException {
Hello hello = new Hello();
hello.select();
}
}
class Hello {
String JDBC_URL;
String JDBC_USER;
String JDBC_PASSWORD;
DataSource ds;
public Hello() throws SQLException {
this.JDBC_URL = "jdbc:mysql://127.0.0.1:3306/learnjdbc?useSSL=false&characterEncoding=utf8";
this.JDBC_USER = "learn";
this.JDBC_PASSWORD = "learnpassword";
HikariConfig config = new HikariConfig();
config.setJdbcUrl(this.JDBC_URL);
config.setUsername(this.JDBC_USER);
config.setPassword(this.JDBC_PASSWORD);
config.addDataSourceProperty("connectionTimeout", "1000"); // 连接超时:1秒
config.addDataSourceProperty("idleTimeout", "60000"); // 空闲超时:60秒
config.addDataSourceProperty("maximumPoolSize", "50"); // 最大连接数:50
ds = new HikariDataSource(config);
}
public void select() throws SQLException {
String sql1 = "SELECT id, grade, name, gender FROM students WHERE name = ?";
try (Connection connection = ds.getConnection()) {
try (PreparedStatement ps = connection.prepareStatement(sql1)) {
ps.setObject(1, "Bob"); // 注意:索引从1开始
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
long id = rs.getLong(1);
long grade = rs.getLong("grade");
String name = rs.getString("name");
int gender = rs.getInt("gender");
System.out.printf("id=%s, grade=%s, name=%s, gender=%s\n",
id, grade, name, gender);
}
}
}
}
}
}
```
- 参考资料
[JDBC编程](https://www.liaoxuefeng.com/wiki/1252599548343744/1255943820274272)