JDBCの概要

Javaトップ

Spring5とSpringBoot2の基礎に焦点を当てた新しいLearnSpringコースを発表しました。

>>コースをチェックしてください

1。概要

この記事では、データベースに接続してクエリを実行するためのAPIであるJDBC(Java Database Connectivity)について説明します。

JDBCは、適切なドライバーが提供されている限り、任意のデータベースで機能します。

2.JDBCドライバー

JDBCドライバーは、特定のタイプのデータベースに接続するために使用されるJDBCAPI実装です。JDBCドライバーにはいくつかのタイプがあります。

  • タイプ1–別のデータアクセスAPIへのマッピングが含まれています。この例は、JDBC-ODBCドライバーです。
  • タイプ2–ターゲットデータベースのクライアント側ライブラリを使用する実装です。ネイティブAPIドライバーとも呼ばれます
  • タイプ3–ミドルウェアを使用してJDBC呼び出しをデータベース固有の呼び出しに変換します。ネットワークプロトコルドライバーとも呼ばれます
  • タイプ4– JDBC呼び出しをデータベース固有の呼び出しに変換することにより、データベースに直接接続します。データベースプロトコルドライバーまたはシンドライバーとして知られています。

最も一般的に使用されるタイプはタイプ4です。これは、プラットフォームに依存しないという利点があります。データベースサーバーに直接接続すると、他のタイプに比べてパフォーマンスが向上します。このタイプのドライバーの欠点は、データベース固有であるということです。各データベースには固有のプロトコルがあるためです。

3.データベースへの接続

データベースに接続するには、ドライバーを初期化してデータベース接続を開くだけです。

3.1。ドライバーの登録

この例では、タイプ4データベースプロトコルドライバーを使用します。

MySQLデータベースを使用しているため、mysql-connector-java依存関係が必要です。

 mysql mysql-connector-java 6.0.6 

次に、Class.forName()メソッドを使用してドライバーを登録します。このメソッドは、ドライバークラスを動的にロードします。

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

古いバージョンのJDBCでは、接続を取得する前に、Class.forNameメソッドを呼び出してJDBCドライバーを初期化する必要がありました。JDBC 4.0以降、クラスパスで見つかったすべてのドライバーが自動的にロードされます。したがって、最近の環境では、このClass.forName部分は必要ありません。

3.2。接続の作成

接続を開くには、DriverManagerクラスのgetConnection()メソッドを使用できます。このメソッドには、接続URL文字列パラメーターが必要です。

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

以来接続があるAutoCloseableリソース、我々は内部でそれを使用する必要がありますのtry-と資源ブロック

接続URLの構文は、使用するデータベースのタイプによって異なります。いくつかの例を見てみましょう。

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

指定されたmyDbデータベースに接続するには、データベースとユーザーを作成し、必要なアクセス許可を追加する必要があります。

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

4.SQLステートメントの実行

SQL命令をデータベースに送信します。タイプStatementPreparedStatement、またはCallableStatementのインスタンスを使用できます。これらは、Connectionオブジェクトを使用して取得できます。

4.1。ステートメント

文のインタフェースは、SQLコマンドを実行するための基本的な機能が含まれています。

まず、Statementオブジェクトを作成しましょう。

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

繰り返しになりますが、自動リソース管理のために、try-with-resourcesブロック内のStatementを操作する必要があります。

とにかく、SQL命令の実行は、次の3つの方法を使用して実行できます。

  • SELECT命令のexecuteQuery()
  • データまたはデータベース構造を更新するためのexecuteUpdate()
  • execute()は、結果が不明な上記の両方の場合に使用できます

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);

execute()メソッドを使用してデータを更新する場合、stmt.getUpdateCount()メソッドは影響を受ける行数を返します。

結果が0の場合、影響を受けた行がないか、データベース構造の更新コマンドでした。

値が-1の場合、コマンドはSELECTクエリでした。次に、stmt.getResultSet()を使用して結果を取得できます。

次に、executeUpdate()メソッドを使用してテーブルにレコードを追加しましょう。

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

このメソッドは、行を更新するコマンドの場合は影響を受ける行の数を返し、データベース構造を更新するコマンドの場合は0を返します。

ResultSet型のオブジェクトを返すexecuteQuery()メソッドを使用して、テーブルからレコードを取得できます。

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

使用後は必ずResultSetインスタンスを閉じる必要があります。そうしないと、基になるカーソルが予想よりもはるかに長い期間開いたままになる可能性があります。これを行うには、上記の例のように、try-with-resourcesブロックを使用することをお勧めします。

4.2。PreparedStatement

PreparedStatementオブジェクトには、プリコンパイルされたSQLシーケンスが含まれています。それらは、疑問符で示される1つ以上のパラメーターを持つことができます。

指定されたパラメーターに基づいてemployeesテーブルのレコードを更新するPreparedStatementを作成しましょう。

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

PreparedStatementにパラメーターを追加するには、単純なセッター– setX() –を使用できます。ここで、Xはパラメーターのタイプであり、メソッド引数はパラメーターの順序と値です。

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

ステートメントは、前述と同じ3つのメソッドのいずれかを使用して実行されます:executeQuery()、executeUpdate()、execute()、SQL文字列パラメーターなし:

int rowsAffected = pstmt.executeUpdate();

4.3。CallableStatement

CallableStatementインターフェースは、ストアドプロシージャを呼び出すことができます。

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

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:

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:

cstmt.registerOutParameter(1, Types.INTEGER);

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

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

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

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.

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:

GRANT ALL ON mysql.proc TO 'user1';

Alternatively, we can open the connection with the property noAccessToProcedureBodies set to 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.

5. Parsing Query Results

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

5.1. ResultSet Interface

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

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

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:

String selectSql = "SELECT * FROM employees"; try (ResultSet resultSet = stmt.executeQuery(selectSql)) { List 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.

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.

5.2. Updatable ResultSet

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

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:

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

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

  • 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.

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

  • 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:

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

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

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.

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

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

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

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

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.

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.

8. Closing the Resources

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:

con.close();

ただし、try-with-resourcesブロックでリソースを使用している場合は、try-with-resourcesブロックが自動的に呼び出すため、close()メソッドを明示的に呼び出す必要はありません。

同じことが、StatementPreparedStatementCallableStatement、およびResultSetにも当てはまります。

9.結論

このチュートリアルでは、JDBCAPIの操作の基本について説明しました。

いつものように、例の完全なソースコードはGitHubにあります。

Javaの底

Spring5とSpringBoot2の基礎に焦点を当てた新しいLearnSpringコースを発表しました。

>>コースをチェックしてください