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依存関係が必要です。

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

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

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

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

3.2. 接続の作成

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

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

接続はAutoCloseableリソースであるため、try-with-resourcesブロック内で使用する必要があります。

接続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命令をデータベースに送信します。Statement PreparedStatement、または CallableStatement、のインスタンスを使用できます。これらは、Connection[を使用して取得できます。 X195X]オブジェクト。

4.1. ステートメント

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

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

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

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

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

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

execute()メソッドを使用して、studentテーブルをデータベースに追加しましょう。

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

ステートメントは、SQL Stringパラメーターを指定せずにexecuteQuery()、executeUpdate()、execute()の3つのメソッドのいずれかを使用して実行されます。

int rowsAffected = pstmt.executeUpdate();

4.3. CallableStatement

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

CallableStatement オブジェクトを作成するには、 ConnectionprepareCall()メソッドを使用できます。

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

ストアドプロシージャの入力パラメータ値の設定は、 PreparedStatement インターフェイスのように、 setX()メソッドを使用して行われます。

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

ストアドプロシージャに出力パラメータがある場合は、 registerOutParameter()メソッドを使用してそれらを追加する必要があります。

cstmt.registerOutParameter(1, Types.INTEGER);

次に、ステートメントを実行し、対応する getX()メソッドを使用して戻り値を取得しましょう。

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

たとえば、機能させるには、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 ;

上記のinsertEmployeeプロシージャは、指定されたパラメータを使用して employee テーブルに新しいレコードを挿入し、 emp_idoutパラメータで新しいレコードのIDを返します。

Javaからストアドプロシージャを実行できるようにするには、接続ユーザーがストアドプロシージャのメタデータにアクセスできる必要があります。 これは、すべてのデータベースのすべてのストアドプロシージャに対する権限をユーザーに付与することで実現できます。

GRANT ALL ON mysql.proc TO 'user1';

または、プロパティnoAccessToProcedureBodiestrueに設定して接続を開くこともできます。

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

これにより、JDBC APIに、ユーザーにはプロシージャメタデータを読み取る権限がないことが通知されるため、すべてのパラメータがINOUT Stringパラメータとして作成されます。

5. クエリ結果の解析

クエリを実行すると、結果は ResultSet オブジェクトで表されます。このオブジェクトは、テーブルに似た構造で、行と列があります。

5.1. ResultSetインターフェース

ResultSet は、 next()メソッドを使用して次の行に移動します。

まず、取得したレコードを保存するEmployeeクラスを作成しましょう。

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

次に、 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); 
    }
}

各テーブルセルの値の取得は、タイプ getX()のメソッドを使用して実行できます。ここで、Xはセルデータのタイプを表します。

getX()メソッドは、セルの順序を表す int パラメーター、または列の名前を表すStringパラメーターとともに使用できます。 クエリの列の順序を変更する場合は、後者のオプションをお勧めします。

5.2. 更新可能ResultSet

暗黙的に、 ResultSet オブジェクトは前方にのみトラバースでき、変更できません。

ResultSet を使用してデータを更新し、データを両方向にトラバースする場合は、追加のパラメーターを使用してStatementオブジェクトを作成する必要があります。

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

このタイプのResultSetをナビゲートするには、次のいずれかの方法を使用できます。

  • first()、last()、beforeFirst()、beforeLast()ResultSetの最初または最後の行またはこれらの前の行に移動します
  • next()、previous()ResultSetで前後に移動します
  • getRow()–現在の行番号を取得します
  • moveToInsertRow()、moveToCurrentRow() –新しい空の行に移動して挿入し、新しい行にある場合は現在の行に戻します
  • abstract(int row)–は指定された行に移動します
  • relative(int nrRows) –指定された行数でカーソルを移動します

ResultSet の更新は、 updateX()の形式のメソッドを使用して実行できます。Xはセルデータのタイプです。 これらのメソッドは、 ResultSet オブジェクトのみを更新し、データベーステーブルは更新しません。

ResultSet の変更をデータベースに永続化するには、次のいずれかの方法をさらに使用する必要があります。

  • updateRow() –現在の行への変更をデータベースに保持します
  • insertRow()、deleteRow() –新しい行を追加するか、データベースから現在の行を削除します
  • refreshRow() –データベースの変更でResultSetを更新します
  • cancelRowUpdates() –現在の行に加えられた変更をキャンセルします

従業員のテーブルのデータを更新して、これらのメソッドのいくつかを使用する例を見てみましょう。

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. メタデータの解析

JDBC APIを使用すると、メタデータと呼ばれるデータベースに関する情報を検索できます。

6.1. DatabaseMetadata

DatabaseMetadata インターフェイスを使用して、テーブル、ストアドプロシージャ、SQLダイアレクトなどのデータベースに関する一般的な情報を取得できます。

データベーステーブルの情報を取得する方法を簡単に見てみましょう。

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

6.2. ResultSetMetadata

このインターフェイスを使用して、特定の 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. トランザクションの処理

デフォルトでは、各SQLステートメントは完了直後にコミットされます。 ただし、プログラムでトランザクションを制御することも可能です。

これは、データの一貫性を維持したい場合、たとえば、前のトランザクションが正常に完了した場合にのみトランザクションをコミットしたい場合に必要になることがあります。

まず、ConnectionautoCommitプロパティをfalseに設定してから、 commit()および rollback( )トランザクションを制御するメソッド。

従業員のposition列の更新後に、 salary 列の2番目の更新ステートメントを追加し、両方をトランザクションでラップしてみましょう。 このように、給与は、ポジションが正常に更新された場合にのみ更新されます。

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

簡潔にするために、ここではtry-with-resourcesブロックを省略しています。

8. リソースを閉じる

使用しなくなったら、データベースリソースを解放するために接続を閉じる必要があります

これは、 close()APIを使用して実行できます。

con.close();

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

Statement PreparedStatement CallableStatement 、およびResultSetについても同じことが言えます。

9. 結論

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

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