JDBC编程
数据库操作总结起来就四个字：增删改查，行话叫CRUD：Create，Retrieve，Update和Delete。
什么是JDBC？JDBC是Java DataBase Connectivity的缩写，它是Java程序访问数据库的标准接口。
┌ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┐
│ ┌───────────────┐ │
│ Java App │
│ └───────────────┘ │
│
│ ▼ │
┌───────────────┐
│ │JDBC Interface │◀─┼─── JDK
└───────────────┘
│ │ │
▼
│ ┌───────────────┐ │
│ JDBC Driver │◀───── Vendor
│ └───────────────┘ │
│
└ ─ ─ ─ ─ ─│─ ─ ─ ─ ─ ┘
▼
┌───────────────┐
│ Database │
└───────────────┘
从代码来看，Java标准库自带的JDBC接口其实就是定义了一组接口，而某个具体的JDBC驱动其实就是实现了这些接口的类：
┌ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┐
│ ┌───────────────┐ │
│ Java App │
│ └───────────────┘ │
│
│ ▼ │
┌───────────────┐
│ │JDBC Interface │◀─┼─── JDK
└───────────────┘
│ │ │
▼
│ ┌───────────────┐ │
│ MySQL Driver │◀───── Oracle
│ └───────────────┘ │
│
└ ─ ─ ─ ─ ─│─ ─ ─ ─ ─ ┘
▼
┌───────────────┐
│ MySQL │
└───────────────┘
实际上，一个MySQL的JDBC的驱动就是一个jar包，它本身也是纯Java编写的。我们自己编写的代码只需要引用Java标准库提供的java.sql包下面的相关接口，由此再间接地通过MySQL驱动的jar包通过网络访问MySQL服务器，所有复杂的网络通讯都被封装到JDBC驱动中，因此，Java程序本身只需要引入一个MySQL驱动的jar包就可以正常访问MySQL服务器：
┌ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┐
┌───────────────┐
│ │ App.class │ │
└───────────────┘
│ │ │
▼
│ ┌───────────────┐ │
│ java.sql.* │
│ └───────────────┘ │
│
│ ▼ │
┌───────────────┐ TCP ┌───────────────┐
│ │ mysql-xxx.jar │──┼────────▶│ MySQL │
└───────────────┘ └───────────────┘
└ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┘
JVM
小结
使用JDBC的好处是：
- 各数据库厂商使用相同的接口，Java代码不需要针对不同数据库分别开发；
- Java程序编译期仅依赖java.sql包，不依赖具体数据库的jar包；
- 可随时替换底层数据库，访问数据库的Java代码基本不变。
JDBC查询
PreparedStatement.executeQuery()
连接
Connection代表一个JDBC连接
URL是由数据库厂商指定的格式，例如，MySQL的URL是：
jdbc:mysql://<hostname>:<port>/<db>?key1=value1&key2=value2
假设数据库运行在本机localhost，端口使用标准的3306，数据库名称是learnjdbc，那么URL如下：
jdbc:mysql://localhost:3306/learnjdbc?useSSL=false&characterEncoding=utf8
后面的两个参数表示不使用SSL加密，使用UTF-8作为字符编码（注意MySQL的UTF-8是utf8）
要获取数据库连接，使用如下代码：
// JDBC连接的URL, 不同数据库有不同的格式:
String JDBC_URL = "jdbc:mysql://localhost:3306/test";
String JDBC_USER = "root";
String JDBC_PASSWORD = "password";
// 获取连接:
Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
// TODO: 访问数据库...
// 关闭连接:
conn.close();
核心代码是DriverManager提供的静态方法getConnection()。DriverManager会自动扫描classpath，找到所有的JDBC驱动，然后根据我们传入的URL自动挑选一个合适的驱动
查询
Statement执行查询对象ResultSet结果集对象
- 通过
Connection提供的createStatement()方法创建一个Statement对象，用于执行一个查询；、 - 执行
Statement对象提供的executeQuery("SELECT * FROM students")并传入SQL语句，执行查询并获得返回的结果集，使用ResultSet来引用这个结果集； - 反复调用
ResultSet的next()方法并读取每一行结果。
完整查询代码如下：
try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
try (Statement stmt = conn.createStatement()) {
try (ResultSet rs = stmt.executeQuery("SELECT id, grade, name, gender FROM students WHERE gender=1")) {
while (rs.next()) {
long id = rs.getLong(1); // 注意：索引从1开始
long grade = rs.getLong(2);
String name = rs.getString(3);
int gender = rs.getInt(4);
}
}
}
}
Statement和ResultSet都是需要关闭的资源，因此嵌套使用try (resource)确保及时关闭；
rs.next()用于判断是否有下一行记录，如果有，将自动把当前行移动到下一行（一开始获得ResultSet时当前行不是第一行）；
ResultSet获取列时，索引从1开始而不是0；
必须根据SELECT的列的对应位置来调用getLong(1)，getString(2)这些方法，否则对应位置的数据类型不对，将报错。
SQL注入
举例
User login(String name, String pass) {
...
stmt.executeQuery("SELECT * FROM user WHERE login='" + name + "' AND pass='" + pass + "'");
...
}
如果用户的输入是程序期待的值，就可以拼出正确的SQL。例如：name = "bob"，pass = "1234"：
SELECT * FROM user WHERE login='bob' AND pass='1234'
但是，如果用户的输入是一个精心构造的字符串，就可以拼出意想不到的SQL，这个SQL也是正确的，但它查询的条件不是程序设计的意图。例如：name = "bob' OR pass=", pass = " OR pass='"：
SELECT * FROM user WHERE login='bob' OR pass=' AND pass=' OR pass=''
这个SQL语句执行的时候，根本不用判断口令是否正确，这样一来，登录就形同虚设
应对方法
PreparedStatement- 因为
PreparedStatement始终使用?作为占位符，并且把数据连同SQL本身传给数据库，这样可以保证每次传给数据库的SQL语句是相同的，只是占位符的数据不同，还能高效利用数据库本身对查询的缓存
- 因为
User login(String name, String pass) {
...
String sql = "SELECT * FROM user WHERE login=? AND pass=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, name);
ps.setObject(2, pass);
...
}
数据类型
| 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代表一个JDBC连接；
使用JDBC查询时，总是使用PreparedStatement进行查询而不是Statement；
查询结果总是ResultSet，即使使用聚合查询也不例外。
JDBC更新
插入
PreparedStatement.executeUpdate()
示例
try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
try (PreparedStatement ps = conn.prepareStatement(
"INSERT INTO students (id, grade, name, gender) VALUES (?,?,?,?)")) {
ps.setObject(1, 999); // 注意：索引从1开始
ps.setObject(2, 1); // grade
ps.setObject(3, "Bob"); // name
ps.setObject(4, "M"); // gender
int n = ps.executeUpdate(); // 1
}
}
插入并获取主键
获取自增主键的正确写法是在创建PreparedStatement的时候，指定一个RETURN_GENERATED_KEYS标志位，表示JDBC驱动必须返回插入的自增主键。示例代码如下：
try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
try (PreparedStatement ps = conn.prepareStatement(
"INSERT INTO students (grade, name, gender) VALUES (?,?,?)",
Statement.RETURN_GENERATED_KEYS)) {
ps.setObject(1, 1); // grade
ps.setObject(2, "Bob"); // name
ps.setObject(3, "M"); // gender
int n = ps.executeUpdate(); // 1
try (ResultSet rs = ps.getGeneratedKeys()) {
if (rs.next()) {
long id = rs.getLong(1); // 注意：索引从1开始
}
}
}
}
更新
更新操作是UPDATE语句，它可以一次更新若干列的记录。更新操作和插入操作在JDBC代码的层面上实际上没有区别，除了SQL语句不同：
try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
try (PreparedStatement ps = conn.prepareStatement("UPDATE students SET name=? WHERE id=?")) {
ps.setObject(1, "Bob"); // 注意：索引从1开始
ps.setObject(2, 999);
int n = ps.executeUpdate(); // 返回更新的行数
}
}
executeUpdate()返回数据库实际更新的行数。返回结果可能是正数，也可能是0（表示没有任何记录更新）。
删除
删除操作是DELETE语句，它可以一次删除若干行。和更新一样，除了SQL语句不同外，JDBC代码都是相同的：
try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
try (PreparedStatement ps = conn.prepareStatement("DELETE FROM students WHERE id=?")) {
ps.setObject(1, 999); // 注意：索引从1开始
int n = ps.executeUpdate(); // 删除的行数
}
}
小结
使用JDBC执行INSERT、UPDATE和DELETE都可视为更新操作；
更新操作使用PreparedStatement的executeUpdate()进行，返回受影响的行数。
JDBC事务
数据库事务（Transaction）是由若干个SQL语句构成的一个操作序列，有点类似于Java的synchronized同步。数据库系统保证在一个事务中的所有SQL要么全部执行成功，要么全部不执行，即数据库事务具有ACID特性：
- Atomicity：原子性
- Consistency：一致性
- Isolation：隔离性
- Durability：持久性
数据库事务可以并发执行，而数据库系统从效率考虑，对事务定义了不同的隔离级别。SQL标准定义了4种隔离级别，分别对应可能出现的数据不一致的情况：
| Isolation Level | 脏读（Dirty Read） | 不可重复读（Non Repeatable Read） | 幻读（Phantom Read） |
|---|---|---|---|
| Read Uncommitted | Yes | Yes | Yes |
| Read Committed | - | Yes | Yes |
| Repeatable Read | - | - | Yes |
| Serializable | - | - | - |
JDBC的事务代码：
Connection conn = openConnection();
try {
// 关闭自动提交:
conn.setAutoCommit(false);
// 执行多条SQL语句:
insert(); update(); delete();
// 提交事务:
conn.commit();
} catch (SQLException e) {
// 回滚事务:
conn.rollback();
} finally {
conn.setAutoCommit(true);
conn.close();
}
JDBC批处理
PreparedStatement.addBatch()添加参数到batchPreparedStatement.executeBatch()一次性提交存在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的batch操作会大大提高执行效率，对内容相同，参数不同的SQL，要优先考虑batch操作。
JDBC连接池
在执行JDBC的增删改查的操作时，如果每一次操作都来一次打开连接，操作，关闭连接，那么创建和销毁JDBC连接的开销就太大了。为了避免频繁地创建和销毁JDBC连接，我们可以通过连接池（Connection Pool）复用已经创建好的连接。
JDBC连接池有一个标准的接口javax.sql.DataSource，注意这个类位于Java标准库中，但仅仅是接口。要使用JDBC连接池，我们必须选择一个JDBC连接池的实现。常用的JDBC连接池有：
- HikariCP
- C3P0
- BoneCP
- Druid
例：
添加HikariCP的依赖如下：
- com.zaxxer:HikariCP:2.7.1
创建一个DataSource实例，这个实例就是连接池：
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/test");
config.setUsername("root");
config.setPassword("password");
config.addDataSourceProperty("connectionTimeout", "1000"); // 连接超时：1秒
config.addDataSourceProperty("idleTimeout", "60000"); // 空闲超时：60秒
config.addDataSourceProperty("maximumPoolSize", "10"); // 最大连接数：10
DataSource ds = new HikariDataSource(config);
小结
数据库连接池是一种复用Connection的组件，它可以避免反复创建新连接，提高JDBC代码的运行效率；
可以配置连接池的详细参数并监控连接池。
扫一扫
1139
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
