Introduction to JDBC – JDBC简介

最后修改: 2017年 5月 4日

中文/混合/英文(键盘快捷键：t)

1. Overview

1.概述

In this article, we’re going to take a look at JDBC (Java Database Connectivity) which is an API for connecting and executing queries on a database.

在这篇文章中，我们要看一下JDBC（Java数据库连接），这是一个用于连接和执行数据库查询的API。

JDBC can work with any database as long as proper drivers are provided.

只要提供适当的驱动程序，JDBC可以与任何数据库一起工作。

2. JDBC Drivers

2.JDBC驱动程序

A JDBC driver is a JDBC API implementation used for connecting to a particular type of database. There are several types of JDBC drivers:

JDBC驱动是用于连接特定类型数据库的JDBC API实现。有几种类型的JDBC驱动程序。

  • Type 1 – contains a mapping to another data access API; an example of this is the JDBC-ODBC driver
  • Type 2 – is an implementation that uses client-side libraries of the target database; also called a native-API driver
  • Type 3 – uses middleware to convert JDBC calls into database-specific calls; also known as a network protocol driver
  • Type 4 – connect directly to a database by converting JDBC calls into database-specific calls; known as database protocol drivers or thin drivers,

The most commonly used type is type 4, as it has the advantage of being platform-independent. Connecting directly to a database server provides better performance compared to other types. The downside of this type of driver is that it’s database-specific – given each database has its own specific protocol.

最常用的类型是类型4，因为它的优点是平台独立。与其他类型相比，直接连接到数据库服务器可以提供更好的性能。这种类型的驱动程序的缺点是它是特定于数据库的–鉴于每个数据库都有自己的特定协议。

3. Connecting to a Database

3.连接到数据库

To connect to a database, we simply have to initialize the driver and open a database connection.

要连接到数据库，我们只需初始化驱动程序并打开一个数据库连接。

3.1. Registering the Driver

3.1.注册驱动程序

For our example, we will use a type 4 database protocol driver.

在我们的例子中，我们将使用一个4型数据库协议驱动程序。

Since we’re using a MySQL database, we need the mysql-connector-java dependency:

由于我们使用的是MySQL数据库，我们需要mysql-connector-javaa>依赖。

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.6</version>
</dependency>

Next, let’s register the driver using the Class.forName() method, which dynamically loads the driver class:

接下来，让我们使用Class.forName()方法来注册驱动程序，它可以动态地加载驱动程序类。

Class.forName("com.mysql.cj.jdbc.Driver");

In older versions of JDBC, before obtaining a connection, we first had to initialize the JDBC driver by calling the Class.forName method. As of JDBC 4.0, all drivers that are found in the classpath are automatically loaded. Therefore, we won’t need this Class.forName part in modern environments.

在旧版本的JDBC中，在获得连接之前，我们首先必须通过调用Class.forName方法来初始化JDBC驱动程序。从JDBC 4.0开始，所有在classpath中找到的驱动程序都被自动加载。因此，在现代环境中，我们将不需要这个Class.forName部分。

3.2. Creating the Connection

3.2.创建连接

To open a connection, we can use the getConnection() method of DriverManager class. This method requires a connection URL String parameter:

要打开一个连接，我们可以使用DriverManager类的getConnection()方法。这个方法需要一个连接URLString参数。

try (Connection con = DriverManager
  .getConnection("jdbc:mysql://localhost:3306/myDb", "user1", "pass")) {
    // use con here
}

Since the Connection is an AutoCloseable resource, we should use it inside a try-with-resources block.

由于Connection 是一个AutoCloseable资源，我们应该在try-with-resources块里面使用它。

The syntax of the connection URL depends on the type of database used. Let’s take a look at a few examples:

连接URL的语法取决于所用数据库的类型。让我们看一下几个例子。

jdbc:mysql://localhost:3306/myDb?user=user1&password=pass
jdbc:postgresql://localhost/myDb
jdbc:hsqldb:mem:myDb

To connect to the specified myDb database, we will have to create the database and a user, and add grant necessary access:

为了连接到指定的myDb数据库，我们将不得不创建数据库和一个用户，并添加授予必要的权限。

CREATE DATABASE myDb;
CREATE USER 'user1' IDENTIFIED BY 'pass';
GRANT ALL on myDb.* TO 'user1';

4. Executing SQL Statements

4.执行SQL语句

The send SQL instructions to the database, we can use instances of type Statement, PreparedStatement, or CallableStatement, which we can obtain using the Connection object.

向数据库发送SQL指令，我们可以使用Statement、PreparedStatement或CallableStatement类型的实例，我们可以通过Connection对象获得这些实例。

4.1. Statement

4.1.声明

The Statement interface contains the essential functions for executing SQL commands.

Statement界面包含执行SQL命令的基本功能。

First, let’s create a Statement object:

首先，让我们创建一个Statement对象。

try (Statement stmt = con.createStatement()) {
    // use stmt here
}

Again, we should work with Statements inside a try-with-resources block for automatic resource management.

同样，我们应该在Statements内工作，以实现自动资源管理的try-with-resourcesblock。

Anyway, executing SQL instructions can be done through the use of three methods:

总之，执行SQL指令可以通过使用三种方法完成。

  • executeQuery() for SELECT instructions
  • executeUpdate() for updating the data or the database structure
  • execute() can be used for both cases above when the result is unknown

Let’s use the execute() method to add a students table to our database:

让我们使用execute()方法来向我们的数据库添加一个students表。

String tableSql = "CREATE TABLE IF NOT EXISTS employees" 
  + "(emp_id int PRIMARY KEY AUTO_INCREMENT, name varchar(30),"
  + "position varchar(30), salary double)";
stmt.execute(tableSql);

When using the execute() method to update the data, then the stmt.getUpdateCount() method returns the number of rows affected.

当使用execute()方法来更新数据时，那么stmt.getUpdateCount()方法将返回受影响的行数。

If the result is 0 then either no rows were affected, or it was a database structure update command.

如果结果是0，那么要么没有行受到影响，要么就是一个数据库结构更新命令。

If the value is -1, then the command was a SELECT query; we can then obtain the result using stmt.getResultSet().

如果值是-1，那么该命令是一个SELECT查询；然后我们可以使用stmt.getResultSet()获得结果。

Next, let’s add a record to our table using the executeUpdate() method:

接下来，让我们使用executeUpdate()方法向我们的表添加一条记录。

String insertSql = "INSERT INTO employees(name, position, salary)"
  + " VALUES('john', 'developer', 2000)";
stmt.executeUpdate(insertSql);

The method returns the number of affected rows for a command that updates rows or 0 for a command that updates the database structure.

对于更新行的命令，该方法返回受影响的行数，对于更新数据库结构的命令，则返回0。

We can retrieve the records from the table using the executeQuery() method which returns an object of type ResultSet:

我们可以使用executeQuery()方法从表中检索记录，该方法返回一个ResultSet类型的对象。

String selectSql = "SELECT * FROM employees"; 
try (ResultSet resultSet = stmt.executeQuery(selectSql)) {
    // use resultSet here
}

We should make sure to close the ResultSet instances after use. Otherwise, we may keep the underlying cursor open for a much longer period than expected. To do that, it’s recommended to use a try-with-resources block, as in our example above.

我们应该确保在使用后关闭ResultSet实例。否则，我们可能会让底层游标的开放时间比预期的长得多。要做到这一点，建议使用一个try-with-resources块，就像我们上面的例子一样。

4.2. PreparedStatement

4.2.PreparedStatement

PreparedStatement objects contain precompiled SQL sequences. They can have one or more parameters denoted by a question mark.

PreparedStatement对象包含预编译的SQL序列。它们可以有一个或多个参数，用问号表示。

Let’s create a PreparedStatement which updates records in the employees table based on given parameters:

让我们创建一个PreparedStatement，根据给定参数更新employees表中的记录。

String updatePositionSql = "UPDATE employees SET position=? WHERE emp_id=?";
try (PreparedStatement pstmt = con.prepareStatement(updatePositionSql)) {
    // use pstmt here
}

To add parameters to the PreparedStatement, we can use simple setters – setX() – where X is the type of the parameter, and the method arguments are the order and value of the parameter:

为了向PreparedStatement添加参数，我们可以使用简单的设置器–setX()–其中X是参数的类型，而方法参数是参数的顺序和值。

pstmt.setString(1, "lead developer");
pstmt.setInt(2, 1);

The statement is executed with one of the same three methods described before: executeQuery(), executeUpdate(), execute() without the SQL String parameter:

语句是用前面描述的三种方法之一执行的。executeQuery(), executeUpdate(), execute()没有SQLString参数。

int rowsAffected = pstmt.executeUpdate();

4.3. CallableStatement

4.3.CallableStatement

The CallableStatement interface allows calling stored procedures.

CallableStatement接口允许调用存储过程。

To create a CallableStatement object, we can use the prepareCall() method of Connection:

为了创建一个CallableStatement对象，我们可以使用Connection的prepareCall方法。

String preparedSql = "{call insertEmployee(?,?,?,?)}";
try (CallableStatement cstmt = con.prepareCall(preparedSql)) {
    // use cstmt here
}

Setting input parameter values for the stored procedure is done like in the PreparedStatement interface, using setX() methods:

为存储过程设置输入参数值，就像在PreparedStatement接口中一样，使用setX()方法进行。

cstmt.setString(2, "ana");
cstmt.setString(3, "tester");
cstmt.setDouble(4, 2000);

If the stored procedure has output parameters, we need to add them using the registerOutParameter() method:

如果存储过程有输出参数，我们需要使用registerOutParameter()方法添加它们。

cstmt.registerOutParameter(1, Types.INTEGER);

Then let’s execute the statement and retrieve the returned value using a corresponding getX() method:

然后让我们执行该语句，并使用相应的getX()方法检索返回值。

cstmt.execute();
int new_id = cstmt.getInt(1);

For example to work, we need to create the stored procedure in our MySql database:

例如，为了工作，我们需要在MySql数据库中创建存储过程。

delimiter //
CREATE PROCEDURE insertEmployee(OUT emp_id int, 
  IN emp_name varchar(30), IN position varchar(30), IN salary double) 
BEGIN
INSERT INTO employees(name, position,salary) VALUES (emp_name,position,salary);
SET emp_id = LAST_INSERT_ID();
END //
delimiter ;

The insertEmployee procedure above will insert a new record into the employees table using the given parameters and return the id of the new record in the emp_id out parameter.

上面的insertEmployee存储过程将使用给定的参数向employees表中插入一条新记录，并在emp_id输出参数中返回新记录的id。

To be able to run a stored procedure from Java, the connection user needs to have access to the stored procedure’s metadata. This can be achieved by granting rights to the user on all stored procedures in all databases:

为了能够从Java中运行一个存储过程，连接用户需要对存储过程的元数据有访问权。这可以通过授予该用户在所有数据库中的所有存储过程的权限来实现。

GRANT ALL ON mysql.proc TO 'user1';

Alternatively, we can open the connection with the property noAccessToProcedureBodies set to true:

另外，我们可以在打开连接时将属性noAccessToProcedureBodies设置为true。

con = DriverManager.getConnection(
  "jdbc:mysql://localhost:3306/myDb?noAccessToProcedureBodies=true", 
  "user1", "pass");

This will inform the JDBC API that the user does not have the rights to read the procedure metadata so that it will create all parameters as INOUT String parameters.

这将通知JDBC API，用户没有权利读取存储过程的元数据，因此它将把所有参数创建为INOUT String参数。

5. Parsing Query Results

5.解析查询结果

After executing a query, the result is represented by a ResultSet object, which has a structure similar to a table, with lines and columns.

执行查询后，结果由ResultSet对象表示，它的结构类似于表格，有行和列。

5.1. ResultSet Interface

5.1.ResultSet接口

The ResultSet uses the next() method to move to the next line.

ResultSet使用next()方法来移动到下一行。

Let’s first create an Employee class to store our retrieved records:

让我们首先创建一个Employee类来存储我们检索的记录。

public class Employee {
    private int id;
    private String name;
    private String position;
    private double salary;
 
    // standard constructor, getters, setters
}

Next, let’s traverse the ResultSet and create an Employee object for each record:

接下来，让我们遍历ResultSet，为每条记录创建一个Employee对象。

String selectSql = "SELECT * FROM employees"; 
try (ResultSet resultSet = stmt.executeQuery(selectSql)) {
    List<Employee> employees = new ArrayList<>(); 
    while (resultSet.next()) { 
        Employee emp = new Employee(); 
        emp.setId(resultSet.getInt("emp_id")); 
        emp.setName(resultSet.getString("name")); 
        emp.setPosition(resultSet.getString("position")); 
        emp.setSalary(resultSet.getDouble("salary")); 
        employees.add(emp); 
    }
}

Retrieving the value for each table cell can be done using methods of type getX() where X represents the type of the cell data.

检索每个表格单元的值可以使用getX()类型的方法，其中X代表单元数据的类型。

The getX() methods can be used with an int parameter representing the order of the cell, or a String parameter representing the name of the column. The latter option is preferable in case we change the order of the columns in the query.

getX()方法可以使用一个代表单元格顺序的int参数，或者一个代表列名的String参数。在我们改变查询中的列的顺序时，后一种选择是比较好的。

5.2. Updatable ResultSet

5.2.可更新的ResultSet

Implicitly, a ResultSet object can only be traversed forward and cannot be modified.

隐含地，ResultSet对象只能被向前遍历，不能被修改。

If we want to use the ResultSet to update data and traverse it in both directions, we need to create the Statement object with additional parameters:

如果我们想使用ResultSet来更新数据并双向遍历，我们需要创建带有额外参数的Statement对象。

stmt = con.createStatement(
  ResultSet.TYPE_SCROLL_INSENSITIVE, 
  ResultSet.CONCUR_UPDATABLE
);

To navigate this type of ResultSet, we can use one of the methods:

要浏览这种类型的ResultSet，我们可以使用其中一个方法。

  • first(), last(), beforeFirst(), beforeLast() – to move to the first or last line of a ResultSet or to the line before these
  • next(), previous() – to navigate forward and backward in the ResultSet
  • getRow() – to obtain the current row number
  • moveToInsertRow(), moveToCurrentRow() – to move to a new empty row to insert and back to the current one if on a new row
  • absolute(int row) – to move to the specified row
  • relative(int nrRows) – to move the cursor the given number of rows

Updating the ResultSet can be done using methods with the format updateX() where X is the type of cell data. These methods only update the ResultSet object and not the database tables.

更新ResultSet可以使用格式为updateX()的方法进行，其中X是单元格数据的类型。这些方法只更新ResultSet对象，而不是数据库表。

To persist the ResultSet changes to the database, we must further use one of the methods:

为了将ResultSet的变化持久化到数据库，我们必须进一步使用其中一个方法。

  • updateRow() – to persist the changes to the current row to the database
  • insertRow(), deleteRow() – to add a new row or delete the current one from the database
  • refreshRow() – to refresh the ResultSet with any changes in the database
  • cancelRowUpdates() – to cancel changes made to the current row

Let’s take a look at an example of using some of these methods by updating data in the employee’s table:

让我们看看通过更新employee的表中的数据来使用其中一些方法的例子。

try (Statement updatableStmt = con.createStatement(
  ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)) {
    try (ResultSet updatableResultSet = updatableStmt.executeQuery(selectSql)) {
        updatableResultSet.moveToInsertRow();
        updatableResultSet.updateString("name", "mark");
        updatableResultSet.updateString("position", "analyst");
        updatableResultSet.updateDouble("salary", 2000);
        updatableResultSet.insertRow();
    }
}

6. Parsing Metadata

6.解析元数据[/strong]

The JDBC API allows looking up information about the database, called metadata.

JDBC API允许查询关于数据库的信息，称为元数据。

6.1. DatabaseMetadata

6.1.DatabaseMetadata

The DatabaseMetadata interface can be used to obtain general information about the database such as the tables, stored procedures, or SQL dialect.

DatabaseMetadata接口可用于获取有关数据库的一般信息，如表、存储过程或SQL方言。

Let’s have a quick look at how we can retrieve information on the database tables:

让我们快速看一下我们如何检索数据库表的信息。

DatabaseMetaData dbmd = con.getMetaData();
ResultSet tablesResultSet = dbmd.getTables(null, null, "%", null);
while (tablesResultSet.next()) {
    LOG.info(tablesResultSet.getString("TABLE_NAME"));
}

6.2. ResultSetMetadata

6.2.ResultSetMetadata

This interface can be used to find information about a certain ResultSet, such as the number and name of its columns:

该接口可用于查找关于某个ResultSet的信息，例如其列的数量和名称。

ResultSetMetaData rsmd = rs.getMetaData();
int nrColumns = rsmd.getColumnCount();

IntStream.range(1, nrColumns).forEach(i -> {
    try {
        LOG.info(rsmd.getColumnName(i));
    } catch (SQLException e) {
        e.printStackTrace();
    }
});

7. Handling Transactions

7.处理事务

By default, each SQL statement is committed right after it is completed. However, it’s also possible to control transactions programmatically.

默认情况下，每条SQL语句在完成后会立即提交。然而，也可以以编程方式控制事务。

This may be necessary in cases when we want to preserve data consistency, for example when we only want to commit a transaction if a previous one has completed successfully.

当我们想保持数据的一致性时，这可能是必要的，例如，当我们只想在前一个事务成功完成后提交一个事务。

First, we need to set the autoCommit property of Connection to false, then use the commit() and rollback() methods to control the transaction.

首先，我们需要将Connection的autoCommit属性设置为false，然后使用 commit()和rollback()方法来控制事务。

Let’s add a second update statement for the salary column after the employee position column update and wrap them both in a transaction. This way, the salary will be updated only if the position was successfully updated:

让我们在雇员职位列更新之后，为工资列添加第二个更新语句，并将它们都包在一个事务中。这样一来，只有当职位被成功更新时，工资才会被更新。

String updatePositionSql = "UPDATE employees SET position=? WHERE emp_id=?";
PreparedStatement pstmt = con.prepareStatement(updatePositionSql);
pstmt.setString(1, "lead developer");
pstmt.setInt(2, 1);

String updateSalarySql = "UPDATE employees SET salary=? WHERE emp_id=?";
PreparedStatement pstmt2 = con.prepareStatement(updateSalarySql);
pstmt.setDouble(1, 3000);
pstmt.setInt(2, 1);

boolean autoCommit = con.getAutoCommit();
try {
    con.setAutoCommit(false);
    pstmt.executeUpdate();
    pstmt2.executeUpdate();
    con.commit();
} catch (SQLException exc) {
    con.rollback();
} finally {
    con.setAutoCommit(autoCommit);
}

For the sake of brevity, we omit the try-with-resources blocks here.

为了简洁起见，我们在这里省略了try-with-resources块。

8. Closing the Resources

8.关闭资源

When we’re no longer using it, we need to close the connection to release database resources.

当我们不再使用它时，我们需要关闭连接以释放数据库资源。

We can do this using the close() API:

我们可以使用close() API来做到这一点。

con.close();

However, if we’re using the resource in a try-with-resources block, we don’t need to call the close() method explicitly, as the try-with-resources block does that for us automatically.

然而，如果我们在try-with-resources块中使用该资源，我们不需要明确地调用close()方法，因为try-with-resources块会自动为我们这样做。

The same is true for the Statements, PreparedStatements, CallableStatements, and ResultSets.

对于Statements、PreparedStatements、CallableStatements和ResultSets也是如此。

9. Conclusion

9.结论

In this tutorial, we had a look at the basics of working with the JDBC API.

在本教程中，我们看了一下使用JDBC API的基本知识。

As always, the full source code of the examples can be found over on GitHub.

一如既往，可以在GitHub上找到这些例子的完整源代码。