JAVA数据库的标准接口JDBC总结

[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。

```
<dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <version>5.1.49</version>
 <scope>runtime</scope>
</dependency>

```

- 测试数据

```
-- 创建数据库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://<hostname>:<port>/<db>?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 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

```
<dependency>
 <groupId>com.zaxxer</groupId>
 <artifactId>HikariCP</artifactId>
 <version>2.7.1</version>
</dependency>
```

- 我们需要创建一个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)

余子越的博客

最近发表

友情链接