Java : Connection (JDBC) - API使用例

Connection (Java SE 21 & JDK 21) の使い方まとめです。
だいたいのメソッドを網羅済みです。
API仕様書のおともにどうぞ。

概要

特定のデータベースとの接続(セッション)を表現します。接続のコンテキスト内でSQL文が実行され結果が返されます。

クラス構成

Connection インタフェースは、特定のデータベースとの接続(セッション) を表します。
このインタフェースをとおして、SQL文やストアド・プロシージャを実行できます。

public record Product(String name, int stock) {
    @Override
    public String toString() {
        return "%s(%d)".formatted(name, stock);
    }
}

final var products = List.of(
        new Product("Orange", 10),
        new Product("Banana", 20),
        new Product("Apple", 30)
);

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    System.out.println("-- create table --");
    try (final var statement = connection.createStatement()) {
        final var ret = statement.executeUpdate("""
                CREATE TABLE PRODUCTS (
                    NAME VARCHAR(20),
                    STOCK INTEGER
                )
                """);
        System.out.println("ret = " + ret);
    }

    System.out.println("-- insert --");
    try (final var statement = connection.prepareStatement("""
            INSERT INTO PRODUCTS VALUES( ?, ? )
            """)) {
        for (final var product : products) {
            statement.setString(1, product.name());
            statement.setInt(2, product.stock());
            final var ret = statement.executeUpdate();
            System.out.println("ret = " + ret);
        }
    }

    System.out.println("-- select --");
    try (final var statement = connection.createStatement()) {
        try (final var result = statement.executeQuery("""
                SELECT * FROM PRODUCTS
                """)) {
            while (result.next()) {
                final var name = result.getString("NAME");
                final var stock = result.getInt("STOCK");

                System.out.println(name + " : " + stock);
            }
        }
    }

    // 結果
    // ↓
    //-- create table --
    //ret = 0
    //-- insert --
    //ret = 1
    //ret = 1
    //ret = 1
    //-- select --
    //Orange : 10
    //Banana : 20
    //Apple : 30
}

本記事のコード例では、利便性のために下記の Product クラスと ProductTable クラスを使います。

public record Product(String name, int stock) {
    @Override
    public String toString() {
        return "%s(%d)".formatted(name, stock);
    }
}

public class ProductTable {

    public void create(Connection connection) throws SQLException {
        try (final var statement = connection.createStatement()) {
            statement.executeUpdate("""
                    CREATE TABLE PRODUCTS (
                        NAME VARCHAR(20),
                        STOCK INTEGER
                    )
                    """);
        }
    }

    public void insert(Connection connection, Product product) throws SQLException {
        insert(connection, List.of(product));
    }

    public void insert(Connection connection, List<Product> products) throws SQLException {
        try (final var statement = connection.prepareStatement("""
                INSERT INTO PRODUCTS VALUES( ?, ? )
                """)) {
            for (final var product : products) {
                statement.setString(1, product.name());
                statement.setInt(2, product.stock());
                statement.executeUpdate();
            }
        }
    }

    public void update(Connection connection, Product product) throws SQLException {
        try (final var statement = connection.prepareStatement("""
                UPDATE PRODUCTS SET STOCK = ? WHERE NAME = ?
                """)) {
            statement.setInt(1, product.stock());
            statement.setString(2, product.name());
            statement.executeUpdate();
        }
    }

    public List<Product> selectAll(Connection connection) throws SQLException {
        final var products = new ArrayList<Product>();
        try (final var statement = connection.createStatement()) {
            try (final var result = statement.executeQuery("""
                    SELECT * FROM PRODUCTS
                    """)) {
                while (result.next()) {
                    final var name = result.getString("NAME");
                    final var stock = result.getInt("STOCK");
                    products.add(new Product(name, stock));
                }
            }
        }

        return products;
    }
}

フィールド

static final int TRANSACTION_NONE

トランザクションがサポートされていないことを示す定数です。

System.out.println(Connection.TRANSACTION_NONE); // 0

※ Derby はこのフィールドをサポートしていません。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {
    connection.setTransactionIsolation(Connection.TRANSACTION_NONE);
} catch (SQLException e) {
    System.out.println(e);
}

// 結果
// ↓
//java.sql.SQLException: Invalid or (currently) unsupported isolation level, '0',
//  passed to Connection.setTransactionIsolation().
//  The currently supported values are java.sql.Connection.TRANSACTION_SERIALIZABLE,
//  java.sql.Connection.TRANSACTION_REPEATABLE_READ,
//  java.sql.Connection.TRANSACTION_READ_COMMITTED,
//  and java.sql.Connection.TRANSACTION_READ_UNCOMMITTED.

static final int TRANSACTION_READ_COMMITTED

ダーティ読込みは抑制され、繰返し不可の読み込みおよびファントム読込みが起こることを示す定数です。

System.out.println(Connection.TRANSACTION_READ_COMMITTED); // 2

// ノンリピータブルリードを発生させる例です。
final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

final var product1 = new Product("Orange", 10);
final var product2 = new Product("Orange", 9999);

// テーブルを準備します。
try (final var connection = DriverManager.getConnection(url)) {
    table.create(connection);
    table.insert(connection, product1);
}

try (final var executor = Executors.newFixedThreadPool(2)) {
    executor.submit(() -> {
        try (final var connectionA = DriverManager.getConnection(url)) {

            connectionA.setTransactionIsolation(
                    Connection.TRANSACTION_READ_COMMITTED);
            connectionA.setAutoCommit(false);

            System.out.println("A : select all : " + table.selectAll(connectionA));

            System.out.println("A : sleep ... (5 sec.)");
            TimeUnit.SECONDS.sleep(5);

            System.out.println("A : select all : " + table.selectAll(connectionA));
            System.out.println("A : ↑ Non-repeatable reads!");

            System.out.println("A : ** commit **");
            connectionA.commit();

        } catch (SQLException | InterruptedException e) {
            System.out.println(e);
        }
    });

    TimeUnit.SECONDS.sleep(1);

    executor.submit(() -> {
        try (final var connectionB = DriverManager.getConnection(url)) {

            connectionB.setTransactionIsolation(
                    Connection.TRANSACTION_READ_COMMITTED);
            connectionB.setAutoCommit(false);

            System.out.println("B : update : " + product2);
            table.update(connectionB, product2);

            System.out.println("B : ** commit **");
            connectionB.commit();

        } catch (SQLException e) {
            System.out.println(e);
        }
    });
}

// 結果
// ↓
//A : select all : [Orange(10)]
//A : sleep ... (5 sec.)
//B : update : Orange(9999)
//B : ** commit **
//A : select all : [Orange(9999)]
//A : ↑ Non-repeatable reads!
//A : ** commit **

static final int TRANSACTION_READ_UNCOMMITTED

ダーティ読み込み、繰返し不可の読み込み、およびファントム読込みが起こることを示す定数です。

System.out.println(Connection.TRANSACTION_READ_UNCOMMITTED); // 1

// ダーティリードを発生させる例です。
final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

final var product1 = new Product("Orange", 10);
final var product2 = new Product("Orange", 9999);

// テーブルを準備します。
try (final var connection = DriverManager.getConnection(url)) {
    table.create(connection);
    table.insert(connection, product1);
}

try (final var executor = Executors.newFixedThreadPool(2)) {

    executor.submit(() -> {
        try (final var connectionA = DriverManager.getConnection(url)) {

            connectionA.setTransactionIsolation(
                    Connection.TRANSACTION_READ_UNCOMMITTED);
            connectionA.setAutoCommit(false);

            System.out.println("A : select all = " + table.selectAll(connectionA));

            System.out.println("A : update : " + product2);
            table.update(connectionA, product2);

            System.out.println("A : sleep ... (3 sec.)");
            TimeUnit.SECONDS.sleep(3);

            System.out.println("A : ** rollback **");
            connectionA.rollback();

        } catch (SQLException | InterruptedException e) {
            System.out.println(e);
        }
    });

    TimeUnit.SECONDS.sleep(1);

    executor.submit(() -> {
        try (final var connectionB = DriverManager.getConnection(url)) {

            connectionB.setTransactionIsolation(
                    Connection.TRANSACTION_READ_UNCOMMITTED);
            connectionB.setAutoCommit(false);

            System.out.println("B : select all = " + table.selectAll(connectionB));
            System.out.println("B : ↑ Dirty reads!");

            System.out.println("B : sleep ... (3 sec.)");
            TimeUnit.SECONDS.sleep(3);

            System.out.println("B : select all = " + table.selectAll(connectionB));

            System.out.println("B : ** commit **");
            connectionB.commit();

        } catch (SQLException | InterruptedException e) {
            System.out.println(e);
        }
    });
}

// 結果
// ↓
//A : select all = [Orange(10)]
//A : update : Orange(9999)
//A : sleep ... (3 sec.)
//B : select all = [Orange(9999)]
//B : ↑ Dirty reads!
//B : sleep ... (3 sec.)
//A : ** rollback **
//B : select all = [Orange(10)]
//B : ** commit **

static final int TRANSACTION_REPEATABLE_READ

ダーティ読み込みおよび繰返し不可の読込みは抑制され、ファントム読込みが起こることを示す定数です。

System.out.println(Connection.TRANSACTION_REPEATABLE_READ); // 4

// ファントムリードを発生させる例です。
final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

final var product1 = new Product("Orange", 10);
final var product2 = new Product("Banana", 20);
final var product3 = new Product("Apple", 30);

// テーブルを準備します。
try (final var connection = DriverManager.getConnection(url)) {
    table.create(connection);
    table.insert(connection, List.of(product1, product2));
}

try (final var executor = Executors.newFixedThreadPool(2)) {
    executor.submit(() -> {
        try (final var connectionA = DriverManager.getConnection(url)) {

            connectionA.setTransactionIsolation(
                    Connection.TRANSACTION_REPEATABLE_READ);
            connectionA.setAutoCommit(false);

            System.out.println("A : select : " + table.selectAll(connectionA));

            System.out.println("A : sleep ... (5 sec.)");
            TimeUnit.SECONDS.sleep(5);

            System.out.println("A : select : " + table.selectAll(connectionA));
            System.out.println("A : ↑ Phantom reads!");

            System.out.println("A : ** commit **");
            connectionA.commit();

        } catch (SQLException | InterruptedException e) {
            System.out.println(e);
        }
    });

    TimeUnit.SECONDS.sleep(1);

    executor.submit(() -> {
        try (final var connectionB = DriverManager.getConnection(url)) {

            connectionB.setTransactionIsolation(
                    Connection.TRANSACTION_REPEATABLE_READ);
            connectionB.setAutoCommit(false);

            System.out.println("B : insert : " + product3);
            table.insert(connectionB, product3);

            System.out.println("B : ** commit **");
            connectionB.commit();

        } catch (SQLException e) {
            System.out.println(e);
        }
    });
}

// 結果
// ↓
//A : select : [Orange(10), Banana(20)]
//A : sleep ... (5 sec.)
//B : insert : Apple(30)
//B : ** commit **
//A : select : [Orange(10), Banana(20), Apple(30)]
//A : ↑ Phantom reads!
//A : ** commit **

static final int TRANSACTION_SERIALIZABLE

ダーティ読み込み、繰返し不可の読み込み、およびファントム読込みが抑制されることを示す定数です。

System.out.println(Connection.TRANSACTION_SERIALIZABLE); // 8

// ファントムリードが発生しない例です。
final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

final var product1 = new Product("Orange", 10);
final var product2 = new Product("Banana", 20);
final var product3 = new Product("Apple", 30);

// テーブルを準備します。
try (final var connection = DriverManager.getConnection(url)) {
    table.create(connection);
    table.insert(connection, List.of(product1, product2));
}

try (final var executor = Executors.newFixedThreadPool(2)) {
    executor.submit(() -> {
        try (final var connectionA = DriverManager.getConnection(url)) {

            connectionA.setTransactionIsolation(
                    Connection.TRANSACTION_SERIALIZABLE);
            connectionA.setAutoCommit(false);

            System.out.println("A : select : " + table.selectAll(connectionA));

            System.out.println("A : sleep ... (5 sec.)");
            TimeUnit.SECONDS.sleep(5);

            System.out.println("A : select : " + table.selectAll(connectionA));

            System.out.println("A : ** commit **");
            connectionA.commit();

            System.out.println("A : select : " + table.selectAll(connectionA));

            System.out.println("A : ** commit **");
            connectionA.commit();

        } catch (SQLException | InterruptedException e) {
            System.out.println(e);
        }
    });

    TimeUnit.SECONDS.sleep(1);

    executor.submit(() -> {
        try (final var connectionB = DriverManager.getConnection(url)) {

            connectionB.setTransactionIsolation(
                    Connection.TRANSACTION_SERIALIZABLE);
            connectionB.setAutoCommit(false);

            System.out.println("B : insert : " + product3);
            table.insert(connectionB, product3);

            System.out.println("B : ** commit **");
            connectionB.commit();

        } catch (SQLException e) {
            System.out.println(e);
        }
    });
}

// 結果
// ↓
//A : select : [Orange(10), Banana(20)]
//A : sleep ... (5 sec.)
//B : insert : Apple(30)
//A : select : [Orange(10), Banana(20)]
//A : ** commit **
//B : ** commit **
//A : select : [Orange(10), Banana(20), Apple(30)]
//A : ** commit **

メソッド

void abort (Executor executor)

オープン接続を終了します。

try (final var executor = Executors.newSingleThreadExecutor()) {

    final var url = "jdbc:derby:memory:db;create=true";
    final var table = new ProductTable();

    try (final var connection = DriverManager.getConnection(url)) {
        connection.setAutoCommit(false);

        table.create(connection);
        table.insert(connection, new Product("Orange", 10));
        connection.commit();
        System.out.println(table.selectAll(connection)); // [Orange(10)]

        // コミットしません。
        table.update(connection, new Product("Orange", 9999));
        System.out.println(table.selectAll(connection)); // [Orange(9999)]

        System.out.println(connection.isClosed()); // false

        connection.abort(executor);
        System.out.println(connection.isClosed()); // true
    }

    try (final var connection = DriverManager.getConnection(url)) {
        System.out.println(table.selectAll(connection)); // [Orange(10)]
    }
}

default void beginRequest ()

リクエスト(独立した作業単位)がこの接続から始まっていることを示す、ドライバへのヒント。

このメソッドは、接続プール・マネージャで使用されます。
ユーザがこのメソッドを使うことは少ないと思いますので、コード例は割愛します。

void clearWarnings ()

このConnectionオブジェクトに関して報告されたすべての警告をクリアします。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {
    // null
    System.out.println(connection.getWarnings());
}

try (final var connection = DriverManager.getConnection(url)) {
    // java.sql.SQLWarning: Database 'memory:db' not created,
    //   connection made to existing database instead.
    System.out.println(connection.getWarnings());

    connection.clearWarnings();

    // null
    System.out.println(connection.getWarnings());
}

void close ()

自動的な解除を待たずに、ただちにこのConnectionオブジェクトのデータベースとJDBCリソースを解除します。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {
    System.out.println(connection.isClosed()); // false
}

// try-with-resources文を使わない例です。
final var url = "jdbc:derby:memory:db;create=true";

final var connection = DriverManager.getConnection(url);
try {
    System.out.println(connection.isClosed()); // false
} finally {
    connection.close();
}

System.out.println(connection.isClosed()); // true

void commit ()

直前のコミット/ロールバック以降に行われた変更をすべて永続的なものにし、このConnectionオブジェクトが現在保持するデータベース・ロックをすべて解除します。

final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

try (final var connection = DriverManager.getConnection(url)) {
    connection.setAutoCommit(false);

    table.create(connection);
    table.insert(connection, new Product("Orange", 10));
    connection.commit();
    System.out.println(table.selectAll(connection)); // [Orange(10)]

    // コミットしません。
    table.update(connection, new Product("Orange", 9999));
    System.out.println(table.selectAll(connection)); // [Orange(9999)]

    connection.rollback();

    System.out.println(table.selectAll(connection)); // [Orange(10)]

    connection.commit();
}

Array createArrayOf (String typeName, Object[] elements)

Arrayオブジェクトを生成するファクトリ・メソッドです。

※ Derby はこのメソッドをサポートしていません。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {
    final var ret = connection.createArrayOf("xxx", new String[]{"a", "b", "c"});
} catch (SQLException e) {
    System.out.println(e);
}

// 結果
// ↓
//java.sql.SQLFeatureNotSupportedException: Feature not implemented: No details.

Blob createBlob ()

Blobインタフェースを実装しているオブジェクトを構築します。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    try (final var statement = connection.createStatement()) {
        statement.executeUpdate("""
                CREATE TABLE TESTS (
                    VALUE BLOB(1K)
                )
                """);
    }

    try (final var statement = connection.prepareStatement("""
            INSERT INTO TESTS VALUES( ? )
            """)) {

        final var blob = connection.createBlob();
        blob.setBytes(1, new byte[]{10, 20, 30});

        statement.setBlob(1, blob);
        statement.executeUpdate();
    }

    try (final var statement = connection.createStatement()) {
        try (final var result = statement.executeQuery("""
                SELECT * FROM TESTS
                """)) {
            while (result.next()) {
                final var blob = result.getBlob(1);
                final var bytes = blob.getBytes(1, Math.toIntExact(blob.length()));
                System.out.println("value = " + Arrays.toString(bytes));
            }
        }
    }

    // 結果
    // ↓
    //value = [10, 20, 30]
}

Clob createClob ()

Clobインタフェースを実装しているオブジェクトを構築します。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    try (final var statement = connection.createStatement()) {
        statement.executeUpdate("""
                CREATE TABLE TESTS (
                    VALUE CLOB(1K)
                )
                """);
    }

    try (final var statement = connection.prepareStatement("""
            INSERT INTO TESTS VALUES( ? )
            """)) {

        final var clob = connection.createClob();
        clob.setString(1, "XYZ");

        statement.setClob(1, clob);
        statement.executeUpdate();
    }

    try (final var statement = connection.createStatement()) {
        try (final var result = statement.executeQuery("""
                SELECT * FROM TESTS
                """)) {
            while (result.next()) {
                final var clob = result.getClob(1);
                final var str = clob.getSubString(1, Math.toIntExact(clob.length()));
                System.out.println("value = " + str);
            }
        }
    }

    // 結果
    // ↓
    //value = XYZ
}

NClob createNClob ()

NClobインタフェースを実装しているオブジェクトを構築します。

※ Derby はこのメソッドをサポートしていません。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {
    final var ret = connection.createNClob();
} catch (SQLException e) {
    System.out.println(e);
}

// 結果
// ↓
//java.sql.SQLFeatureNotSupportedException: Feature not implemented: No details.

SQLXML createSQLXML ()

SQLXMLインタフェースを実装しているオブジェクトを構築します。

※ Derby はこのメソッドをサポートしていません。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {
    final var ret = connection.createSQLXML();
} catch (SQLException e) {
    System.out.println(e);
}

// 結果
// ↓
//java.sql.SQLFeatureNotSupportedException: Feature not implemented: No details.

Statement createStatement ()

SQL文をデータベースに送るためのStatementオブジェクトを生成します。

final var products = List.of(
        new Product("Orange", 10),
        new Product("Banana", 20),
        new Product("Apple", 30)
);

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    System.out.println("-- create table --");
    try (final var statement = connection.createStatement()) {
        final var ret = statement.executeUpdate("""
                CREATE TABLE PRODUCTS (
                    NAME VARCHAR(20),
                    STOCK INTEGER
                )
                """);
        System.out.println("ret = " + ret);
    }

    System.out.println("-- insert --");
    try (final var statement = connection.prepareStatement("""
            INSERT INTO PRODUCTS VALUES( ?, ? )
            """)) {
        for (final var product : products) {
            statement.setString(1, product.name());
            statement.setInt(2, product.stock());
            final var ret = statement.executeUpdate();
            System.out.println("ret = " + ret);
        }
    }

    System.out.println("-- select --");
    try (final var statement = connection.createStatement()) {
        try (final var result = statement.executeQuery("""
                SELECT * FROM PRODUCTS
                """)) {
            while (result.next()) {
                final var name = result.getString("NAME");
                final var stock = result.getInt("STOCK");

                System.out.println(name + " : " + stock);
            }
        }
    }

    // 結果
    // ↓
    //-- create table --
    //ret = 0
    //-- insert --
    //ret = 1
    //ret = 1
    //ret = 1
    //-- select --
    //Orange : 10
    //Banana : 20
    //Apple : 30
}

Statement createStatement (int resultSetType, int resultSetConcurrency)

指定された型と並行処理でResultSetオブジェクトを生成するStatementオブジェクトを生成します。

final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

final var products = List.of(
        new Product("Orange", 10),
        new Product("Banana", 20),
        new Product("Apple", 30)
);

try (final var connection = DriverManager.getConnection(url)) {

    table.create(connection);
    table.insert(connection, products);

    System.out.println("-- select --");
    try (final var statement = connection.createStatement(
            ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)) {

        try (final var result = statement.executeQuery("""
                SELECT * FROM PRODUCTS
                """)) {

            while (result.next()) {
                final var name = result.getString("NAME");
                final var stock = result.getInt("STOCK");

                System.out.println(name + " : " + stock);

                result.updateInt("STOCK", stock / 2);
                result.updateRow();
            }
        }
    }

    // 結果
    // ↓
    //-- select --
    //Orange : 10
    //Banana : 20
    //Apple : 30

    System.out.println("-- select --");
    try (final var statement = connection.createStatement(
            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {

        try (final var result = statement.executeQuery("""
                SELECT * FROM PRODUCTS
                """)) {

            result.afterLast();
            while (result.previous()) {
                final var name = result.getString("NAME");
                final var stock = result.getInt("STOCK");

                System.out.println(name + " : " + stock);
            }
        }
    }

    // 結果
    // ↓
    //-- select --
    //Apple : 15
    //Banana : 10
    //Orange : 5
}

Statement createStatement (int resultSetType, int resultSetConcurrency, int resultSetHoldability)

指定された型、並行処理、および保持機能でResultSetオブジェクトを生成するStatementオブジェクトを生成します。

final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

try (final var connection = DriverManager.getConnection(url)) {

    table.create(connection);
    table.insert(connection, new Product("Orange", 10));

    connection.setAutoCommit(false);

    try (final var statement = connection.createStatement(
            ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
            ResultSet.CLOSE_CURSORS_AT_COMMIT)) {

        try (final var result = statement.executeQuery("""
                SELECT * FROM PRODUCTS
                """)) {

            System.out.println(result.isClosed()); // false

            connection.commit();
            System.out.println(result.isClosed()); // true
        }
    }
}

Struct createStruct (String typeName, Object[] attributes)

Structオブジェクトを生成するファクトリ・メソッドです。

※ Derby はこのメソッドをサポートしていません。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {
    final var ret = connection.createStruct("xxx", new String[]{"a", "b", "c"});
} catch (SQLException e) {
    System.out.println(e);
}

// 結果
// ↓
//java.sql.SQLFeatureNotSupportedException: Feature not implemented: No details.

default void endRequest ()

独立した作業単位であるリクエストが完了したドライバへのヒント。

このメソッドは、接続プール・マネージャで使用されます。
ユーザがこのメソッドを使うことは少ないと思いますので、コード例は割愛します。

boolean getAutoCommit ()

このConnectionオブジェクトの現在の自動コミット・モードを取得します。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    final var ret1 = connection.getAutoCommit();
    System.out.println(ret1); // true

    connection.setAutoCommit(false);

    final var ret2 = connection.getAutoCommit();
    System.out.println(ret2); // false
}

final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

try (final var connection = DriverManager.getConnection(url)) {
    connection.setAutoCommit(false);

    table.create(connection);
    table.insert(connection, new Product("Orange", 10));
    connection.commit();
    System.out.println(table.selectAll(connection)); // [Orange(10)]

    // コミットしません。
    table.update(connection, new Product("Orange", 9999));
    System.out.println(table.selectAll(connection)); // [Orange(9999)]

    connection.rollback();

    System.out.println(table.selectAll(connection)); // [Orange(10)]

    connection.commit();
}

String getCatalog ()

このConnectionオブジェクトの現在のカタログ名を取得します。

※ Derby はこのメソッドをサポートしていません。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    final var meta = connection.getMetaData();
    System.out.println(meta.supportsCatalogsInDataManipulation()); // false

    System.out.println(connection.getCatalog()); // null

    connection.setCatalog("XYZ");
    System.out.println(connection.getCatalog()); // null
}

Properties getClientInfo ()

ドライバでサポートされる各クライアント情報プロパティの名前と現在の値が含まれるリストを返します。

※ Derby はこのメソッドをサポートしていません。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    System.out.println(connection.getClientInfo()); // {}
    System.out.println(connection.getClientInfo("aaa")); // null

    try {
        connection.setClientInfo("aaa", "XXX");
    } catch (SQLClientInfoException e) {
        System.out.println(e);
    }

    // 結果
    // ↓
    //java.sql.SQLClientInfoException:
    //  The requested property change is not supported 'aaa'='XXX'.

    try {
        final var properties = new Properties();
        properties.setProperty("aaa", "XXX");
        connection.setClientInfo(properties);
    } catch (SQLClientInfoException e) {
        System.out.println(e);
    }

    // 結果
    // ↓
    //java.sql.SQLClientInfoException:
    //  The requested property change is not supported 'aaa'='XXX'.
}

String getClientInfo (String name)

名前で指定されたをクライアント情報プロパティの値を返します。

※ Derby はこのメソッドをサポートしていません。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    System.out.println(connection.getClientInfo()); // {}
    System.out.println(connection.getClientInfo("aaa")); // null

    try {
        connection.setClientInfo("aaa", "XXX");
    } catch (SQLClientInfoException e) {
        System.out.println(e);
    }

    // 結果
    // ↓
    //java.sql.SQLClientInfoException:
    //  The requested property change is not supported 'aaa'='XXX'.

    try {
        final var properties = new Properties();
        properties.setProperty("aaa", "XXX");
        connection.setClientInfo(properties);
    } catch (SQLClientInfoException e) {
        System.out.println(e);
    }

    // 結果
    // ↓
    //java.sql.SQLClientInfoException:
    //  The requested property change is not supported 'aaa'='XXX'.
}

int getHoldability ()

このConnectionオブジェクトを使用して生成されたResultSetオブジェクトの現在の保持機能を取得します。

final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

try (final var connection = DriverManager.getConnection(url)) {

    table.create(connection);
    table.insert(connection, new Product("Orange", 10));

    connection.setAutoCommit(false);

    final var ret1 = connection.getHoldability();
    System.out.println(ret1 == ResultSet.HOLD_CURSORS_OVER_COMMIT); // true

    try (final var statement = connection.createStatement()) {

        try (final var result = statement.executeQuery("""
                SELECT * FROM PRODUCTS
                """)) {

            System.out.println(result.isClosed()); // false

            connection.commit();
            System.out.println(result.isClosed()); // false
        }
    }

    connection.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);

    final var ret2 = connection.getHoldability();
    System.out.println(ret2 == ResultSet.CLOSE_CURSORS_AT_COMMIT); // true

    try (final var statement = connection.createStatement()) {

        try (final var result = statement.executeQuery("""
                SELECT * FROM PRODUCTS
                """)) {

            System.out.println(result.isClosed()); // false

            connection.commit();
            System.out.println(result.isClosed()); // true
        }
    }
}

DatabaseMetaData getMetaData ()

このConnectionオブジェクトが接続を表すデータベースに関するメタデータを格納するDatabaseMetaDataオブジェクトを取得します。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    final var meta = connection.getMetaData();
    System.out.println(meta.getDatabaseProductName()); // Apache Derby
    System.out.println(meta.getUserName()); // APP
}

int getNetworkTimeout ()

ドライバがデータベース要求の完了を待つミリ秒数を取得します。

※ Derby はこのメソッドをサポートしていません。

final var url = "jdbc:derby://localhost:1527/db;create=true";
try (final var connection = DriverManager.getConnection(url)) {
    final var ret = connection.getNetworkTimeout();
} catch (SQLFeatureNotSupportedException e) {
    System.out.println("SQLFeatureNotSupportedException!");
}

// 結果
// ↓
//SQLFeatureNotSupportedException!

String getSchema ()

このConnectionオブジェクトの現在のスキーマ名を取得します。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    final var ret1 = connection.getSchema();
    System.out.println(ret1); // APP

    try (final var statement = connection.createStatement()) {
        statement.executeUpdate("""
                CREATE SCHEMA TEST
                """);
    }

    connection.setSchema("TEST");

    final var ret2 = connection.getSchema();
    System.out.println(ret2); // TEST
}

int getTransactionIsolation ()

このConnectionオブジェクトの現在のトランザクション遮断レベルを取得します。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    final var ret1 = connection.getTransactionIsolation();
    System.out.println(ret1 == Connection.TRANSACTION_READ_COMMITTED); // true

    connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

    final var ret2 = connection.getTransactionIsolation();
    System.out.println(ret2 == Connection.TRANSACTION_SERIALIZABLE); // true
}

Map<String,Class<?>> getTypeMap ()

このConnectionオブジェクトに関連付けられたMapオブジェクトを取得します。

※ Derby はこのメソッドをサポートしていません。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    System.out.println(connection.getTypeMap()); // {}

    try {
        connection.setTypeMap(Map.of("dummy", Object.class));
    } catch (SQLFeatureNotSupportedException e) {
        System.out.println(e);
    }

    // 結果
    // ↓
    //java.sql.SQLFeatureNotSupportedException: Feature not implemented: No details.
}

SQLWarning getWarnings ()

このConnectionオブジェクトに関する呼出しによって報告される最初の警告を取得します。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {
    // null
    System.out.println(connection.getWarnings());
}

try (final var connection = DriverManager.getConnection(url)) {
    // java.sql.SQLWarning: Database 'memory:db' not created,
    //   connection made to existing database instead.
    System.out.println(connection.getWarnings());

    connection.clearWarnings();

    // null
    System.out.println(connection.getWarnings());
}

boolean isClosed ()

このConnectionオブジェクトがクローズされているかどうかを取得します。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {
    System.out.println(connection.isClosed()); // false
}

// try-with-resources文を使わない例です。
final var url = "jdbc:derby:memory:db;create=true";

final var connection = DriverManager.getConnection(url);
try {
    System.out.println(connection.isClosed()); // false
} finally {
    connection.close();
}

System.out.println(connection.isClosed()); // true

boolean isReadOnly ()

このConnectionオブジェクトが読込み専用モードかどうかを取得します。

final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

try (final var connection = DriverManager.getConnection(url)) {

    System.out.println(connection.isReadOnly()); // false

    table.create(connection);
    table.insert(connection, new Product("Orange", 10));
    System.out.println(table.selectAll(connection)); // [Orange(10)]

    table.update(connection, new Product("Orange", 20));
    System.out.println(table.selectAll(connection)); // [Orange(20)]

    connection.setReadOnly(true);
    System.out.println(connection.isReadOnly()); // true

    try {
        table.update(connection, new Product("Orange", 30));
    } catch (SQLException e) {
        System.out.println(e);
    }

    // 結果
    // ↓
    //java.sql.SQLException: An SQL data change is not permitted
    //  for a read-only connection, user or database.
}

boolean isValid (int timeout)

接続がクローズされておらず、まだ有効である場合は、trueを返します。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    System.out.println(connection.isValid(1)); // true

    connection.close();
    System.out.println(connection.isValid(1)); // false
}

String nativeSQL (String sql)

指定されたSQL文をシステムの本来のSQL文法に変換します。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    final var ret = connection.nativeSQL("SELECT * FROM PRODUCTS");
    System.out.println(ret); // SELECT * FROM PRODUCTS
}

CallableStatement prepareCall (String sql)

データベースのストアド・プロシージャを呼び出すためのCallableStatementオブジェクトを生成します。

package com.example.sql.connection;

public class Procedure {
    public static void test(int value, int[] result) {
        result[0] = value * 5;
    }
}

final var url = "jdbc:derby:memory:db;create=true";

try (final var connection = DriverManager.getConnection(url)) {
    try (final var statement = connection.createStatement()) {
        statement.executeUpdate("""
                CREATE PROCEDURE TEST(
                    IN VALUE INTEGER,
                    OUT RESULT INTEGER
                )
                LANGUAGE JAVA
                PARAMETER STYLE JAVA
                NO SQL
                EXTERNAL NAME 'com.example.sql.connection.Procedure.test'
                """);
    }

    try (final var statement = connection.prepareCall("""
            CALL TEST( ?, ? )
            """)) {
        statement.setInt(1, 100);
        statement.registerOutParameter(2, Types.INTEGER);
        statement.execute();

        System.out.println(statement.getInt(2)); // 500
    }
}

CallableStatement prepareCall (String sql, int resultSetType, int resultSetConcurrency)

指定された型と並行処理でResultSetオブジェクトを生成するCallableStatementオブジェクトを生成します。

package com.example.sql.connection;

public class Procedure {
    public static void test(int value, int[] result) {
        result[0] = value * 5;
    }
}

final var url = "jdbc:derby:memory:db;create=true";

try (final var connection = DriverManager.getConnection(url)) {
    try (final var statement = connection.createStatement()) {
        statement.executeUpdate("""
                CREATE PROCEDURE TEST(
                    IN VALUE INTEGER,
                    OUT RESULT INTEGER
                )
                LANGUAGE JAVA
                PARAMETER STYLE JAVA
                NO SQL
                EXTERNAL NAME 'com.example.sql.connection.Procedure.test'
                """);
    }

    try (final var statement = connection.prepareCall("""
            CALL TEST( ?, ? )
            """, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
        statement.setInt(1, 100);
        statement.registerOutParameter(2, Types.INTEGER);
        statement.execute();

        System.out.println(statement.getInt(2)); // 500
    }
}

CallableStatement prepareCall (String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability)

指定された型と並行処理でResultSetオブジェクトを生成するCallableStatementオブジェクトを生成します。

package com.example.sql.connection;

public class Procedure {
    public static void test(int value, int[] result) {
        result[0] = value * 5;
    }
}

final var url = "jdbc:derby:memory:db;create=true";

try (final var connection = DriverManager.getConnection(url)) {
    try (final var statement = connection.createStatement()) {
        statement.executeUpdate("""
                CREATE PROCEDURE TEST(
                    IN VALUE INTEGER,
                    OUT RESULT INTEGER
                )
                LANGUAGE JAVA
                PARAMETER STYLE JAVA
                NO SQL
                EXTERNAL NAME 'com.example.sql.connection.Procedure.test'
                """);
    }

    try (final var statement = connection.prepareCall("""
                    CALL TEST( ?, ? )
                    """, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
            ResultSet.HOLD_CURSORS_OVER_COMMIT)) {
        statement.setInt(1, 100);
        statement.registerOutParameter(2, Types.INTEGER);
        statement.execute();

        System.out.println(statement.getInt(2)); // 500
    }
}

PreparedStatement prepareStatement (String sql)

パラメータ付きSQL文をデータベースに送るためのPreparedStatementオブジェクトを生成します。

final var products = List.of(
        new Product("Orange", 10),
        new Product("Banana", 20),
        new Product("Apple", 30)
);

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    System.out.println("-- create table --");
    try (final var statement = connection.createStatement()) {
        final var ret = statement.executeUpdate("""
                CREATE TABLE PRODUCTS (
                    NAME VARCHAR(20),
                    STOCK INTEGER
                )
                """);
        System.out.println("ret = " + ret);
    }

    System.out.println("-- insert --");
    try (final var statement = connection.prepareStatement("""
            INSERT INTO PRODUCTS VALUES( ?, ? )
            """)) {
        for (final var product : products) {
            statement.setString(1, product.name());
            statement.setInt(2, product.stock());
            final var ret = statement.executeUpdate();
            System.out.println("ret = " + ret);
        }
    }

    System.out.println("-- select --");
    try (final var statement = connection.createStatement()) {
        try (final var result = statement.executeQuery("""
                SELECT * FROM PRODUCTS
                """)) {
            while (result.next()) {
                final var name = result.getString("NAME");
                final var stock = result.getInt("STOCK");

                System.out.println(name + " : " + stock);
            }
        }
    }

    // 結果
    // ↓
    //-- create table --
    //ret = 0
    //-- insert --
    //ret = 1
    //ret = 1
    //ret = 1
    //-- select --
    //Orange : 10
    //Banana : 20
    //Apple : 30
}

PreparedStatement prepareStatement (String sql, int autoGeneratedKeys)

自動生成キーを取得する機能を持つデフォルトのPreparedStatementオブジェクトを生成します。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    System.out.println("-- create table --");
    try (final var statement = connection.createStatement()) {
        statement.executeUpdate("""
                CREATE TABLE TESTS (
                    ID INTEGER GENERATED ALWAYS AS IDENTITY,
                    NAME VARCHAR(20)
                )
                """);
    }

    System.out.println("-- insert --");
    try (final var statement = connection.prepareStatement("""
            INSERT INTO TESTS( NAME ) VALUES ( ? )
            """, Statement.RETURN_GENERATED_KEYS)) {
        for (final var name : List.of("a", "b", "c")) {
            statement.setString(1, name);
            statement.executeUpdate();

            try (final var keys = statement.getGeneratedKeys()) {
                while (keys.next()) {
                    final var id = keys.getInt(1);
                    System.out.printf("id = %d, name = %s%n", id, name);
                }
            }
        }
    }

    System.out.println("-- select --");
    try (final var statement = connection.createStatement()) {
        try (final var result = statement.executeQuery("""
                SELECT * FROM TESTS
                """)) {
            while (result.next()) {
                final var id = result.getInt("ID");
                final var name = result.getString("NAME");

                System.out.printf("id = %d, name = %s%n", id, name);
            }
        }
    }

    // 結果
    // ↓
    //-- create table --
    //-- insert --
    //id = 1, name = a
    //id = 2, name = b
    //id = 3, name = c
    //-- select --
    //id = 1, name = a
    //id = 2, name = b
    //id = 3, name = c
}

PreparedStatement prepareStatement (String sql, int[] columnIndexes)

指定された配列によって指定された自動生成キーを返す機能を持つデフォルトのPreparedStatementオブジェクトを生成します。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    System.out.println("-- create table --");
    try (final var statement = connection.createStatement()) {
        statement.executeUpdate("""
                CREATE TABLE TESTS (
                    ID INTEGER GENERATED ALWAYS AS IDENTITY,
                    NAME VARCHAR(20)
                )
                """);
    }

    System.out.println("-- insert --");
    final int[] columnIndexes = {1};
    try (final var statement = connection.prepareStatement("""
            INSERT INTO TESTS( NAME ) VALUES( ? )
            """, columnIndexes)) {
        for (final var name : List.of("a", "b", "c")) {
            statement.setString(1, name);
            statement.executeUpdate();

            try (final var keys = statement.getGeneratedKeys()) {
                while (keys.next()) {
                    final var id = keys.getInt(1);
                    System.out.printf("id = %d, name = %s%n", id, name);
                }
            }
        }
    }

    System.out.println("-- select --");
    try (final var statement = connection.createStatement()) {
        try (final var result = statement.executeQuery("""
                SELECT * FROM TESTS
                """)) {
            while (result.next()) {
                final var id = result.getInt("ID");
                final var name = result.getString("NAME");

                System.out.printf("id = %d, name = %s%n", id, name);
            }
        }
    }

    // 結果
    // ↓
    //-- create table --
    //-- insert --
    //id = 1, name = a
    //id = 2, name = b
    //id = 3, name = c
    //-- select --
    //id = 1, name = a
    //id = 2, name = b
    //id = 3, name = c
}

PreparedStatement prepareStatement (String sql, int resultSetType, int resultSetConcurrency)

指定された型と並行処理でResultSetオブジェクトを生成するPreparedStatementオブジェクトを生成します。

final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

final var products = List.of(
        new Product("Orange", 10),
        new Product("Banana", 20),
        new Product("Apple", 30)
);

try (final var connection = DriverManager.getConnection(url)) {

    table.create(connection);
    table.insert(connection, products);

    System.out.println("-- select --");
    try (final var statement = connection.prepareStatement("""
            SELECT * FROM PRODUCTS
            """, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)) {

        try (final var result = statement.executeQuery()) {
            while (result.next()) {
                final var name = result.getString("NAME");
                final var stock = result.getInt("STOCK");

                System.out.println(name + " : " + stock);

                result.updateInt("STOCK", stock / 2);
                result.updateRow();
            }
        }
    }

    // 結果
    // ↓
    //-- select --
    //Orange : 10
    //Banana : 20
    //Apple : 30

    System.out.println("-- select --");
    try (final var statement = connection.prepareStatement("""
            SELECT * FROM PRODUCTS
            """, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {

        try (final var result = statement.executeQuery()) {

            result.afterLast();
            while (result.previous()) {
                final var name = result.getString("NAME");
                final var stock = result.getInt("STOCK");

                System.out.println(name + " : " + stock);
            }
        }
    }

    // 結果
    // ↓
    //-- select --
    //Apple : 15
    //Banana : 10
    //Orange : 5
}

PreparedStatement prepareStatement (String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability)

指定された型、並行処理、および保持機能でResultSetオブジェクトを生成するPreparedStatementオブジェクトを生成します。

final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

try (final var connection = DriverManager.getConnection(url)) {

    table.create(connection);
    table.insert(connection, new Product("Orange", 10));

    connection.setAutoCommit(false);

    try (final var statement = connection.prepareStatement("""
                    SELECT * FROM PRODUCTS
                    """,
            ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
            ResultSet.CLOSE_CURSORS_AT_COMMIT)) {

        try (final var result = statement.executeQuery()) {

            System.out.println(result.isClosed()); // false

            connection.commit();
            System.out.println(result.isClosed()); // true
        }
    }
}

PreparedStatement prepareStatement (String sql, String[] columnNames)

指定された配列によって指定された自動生成キーを返す機能を持つデフォルトのPreparedStatementオブジェクトを生成します。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    System.out.println("-- create table --");
    try (final var statement = connection.createStatement()) {
        statement.executeUpdate("""
                CREATE TABLE TESTS (
                    ID INTEGER GENERATED ALWAYS AS IDENTITY,
                    NAME VARCHAR(20)
                )
                """);
    }

    System.out.println("-- insert --");
    final String[] columnNames = {"ID"};
    try (final var statement = connection.prepareStatement("""
            INSERT INTO TESTS( NAME ) VALUES( ? )
            """, columnNames)) {
        for (final var name : List.of("a", "b", "c")) {
            statement.setString(1, name);
            statement.executeUpdate();

            try (final var keys = statement.getGeneratedKeys()) {
                while (keys.next()) {
                    final var id = keys.getInt(1);
                    System.out.printf("id = %d, name = %s%n", id, name);
                }
            }
        }
    }

    System.out.println("-- select --");
    try (final var statement = connection.createStatement()) {
        try (final var result = statement.executeQuery("""
                SELECT * FROM TESTS
                """)) {
            while (result.next()) {
                final var id = result.getInt("ID");
                final var name = result.getString("NAME");

                System.out.printf("id = %d, name = %s%n", id, name);
            }
        }
    }

    // 結果
    // ↓
    //-- create table --
    //-- insert --
    //id = 1, name = a
    //id = 2, name = b
    //id = 3, name = c
    //-- select --
    //id = 1, name = a
    //id = 2, name = b
    //id = 3, name = c
}

void releaseSavepoint (Savepoint savepoint)

現在のトランザクションから指定されたSavepointオブジェクトと以降のSavepointオブジェクトを削除します。

final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

try (final var connection = DriverManager.getConnection(url)) {
    connection.setAutoCommit(false);

    table.create(connection);
    table.insert(connection, new Product("Orange", 10));

    connection.commit();
    System.out.println(table.selectAll(connection)); // [Orange(10)]

    // コミットしません。
    table.update(connection, new Product("Orange", 20));
    System.out.println(table.selectAll(connection)); // [Orange(20)]

    final var savepoint = connection.setSavepoint();
    System.out.println(savepoint.getSavepointId()); // 1

    table.update(connection, new Product("Orange", 30));
    System.out.println(table.selectAll(connection)); // [Orange(30)]

    connection.releaseSavepoint(savepoint);

    try {
        connection.rollback(savepoint);
    } catch (SQLException e) {
        System.out.println(e);
        connection.rollback();
    }

    // 結果
    // ↓
    //java.sql.SQLException: SAVEPOINT,
    //  SAVEPT1 does not  exist or is not active in the current transaction.
}

void rollback ()

現在のトランザクションにおけるすべての変更を取り消し、現在このConnectionオブジェクトが保持しているすべてのデータベース・ロックを解除します。

final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

try (final var connection = DriverManager.getConnection(url)) {
    connection.setAutoCommit(false);

    table.create(connection);
    table.insert(connection, new Product("Orange", 10));
    connection.commit();
    System.out.println(table.selectAll(connection)); // [Orange(10)]

    // コミットしません。
    table.update(connection, new Product("Orange", 9999));
    System.out.println(table.selectAll(connection)); // [Orange(9999)]

    connection.rollback();

    System.out.println(table.selectAll(connection)); // [Orange(10)]

    connection.commit();
}

void rollback (Savepoint savepoint)

指定されたSavepointオブジェクトが設定されたあとに行われたすべての変更を元に戻します。

final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

try (final var connection = DriverManager.getConnection(url)) {
    connection.setAutoCommit(false);

    table.create(connection);
    table.insert(connection, new Product("Orange", 10));

    connection.commit();
    System.out.println(table.selectAll(connection)); // [Orange(10)]

    // コミットしません。
    table.update(connection, new Product("Orange", 20));
    System.out.println(table.selectAll(connection)); // [Orange(20)]

    final var savepoint1 = connection.setSavepoint();
    System.out.println(savepoint1.getSavepointId()); // 1

    table.update(connection, new Product("Orange", 30));
    System.out.println(table.selectAll(connection)); // [Orange(30)]

    final var savepoint2 = connection.setSavepoint("XYZ");
    System.out.println(savepoint2.getSavepointName()); // XYZ

    table.update(connection, new Product("Orange", 40));
    System.out.println(table.selectAll(connection)); // [Orange(40)]

    connection.rollback(savepoint2);
    System.out.println(table.selectAll(connection)); // [Orange(30)]

    connection.rollback(savepoint1);
    System.out.println(table.selectAll(connection)); // [Orange(20)]

    connection.commit();
}

void setAutoCommit (boolean autoCommit)

この接続の自動コミット・モードを指定された状態に設定します。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    final var ret1 = connection.getAutoCommit();
    System.out.println(ret1); // true

    connection.setAutoCommit(false);

    final var ret2 = connection.getAutoCommit();
    System.out.println(ret2); // false
}

final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

try (final var connection = DriverManager.getConnection(url)) {
    connection.setAutoCommit(false);

    table.create(connection);
    table.insert(connection, new Product("Orange", 10));
    connection.commit();
    System.out.println(table.selectAll(connection)); // [Orange(10)]

    // コミットしません。
    table.update(connection, new Product("Orange", 9999));
    System.out.println(table.selectAll(connection)); // [Orange(9999)]

    connection.rollback();

    System.out.println(table.selectAll(connection)); // [Orange(10)]

    connection.commit();
}

void setCatalog (String catalog)

このConnectionオブジェクトのデータベースに作業のためのサブスペースを選択するために、カタログ名を設定します。

※ Derby はこのメソッドをサポートしていません。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    final var meta = connection.getMetaData();
    System.out.println(meta.supportsCatalogsInDataManipulation()); // false

    System.out.println(connection.getCatalog()); // null

    connection.setCatalog("XYZ");
    System.out.println(connection.getCatalog()); // null
}

void setClientInfo (String name, String value)

名前で指定されたクライアント情報プロパティの値を、値で指定された値に設定します。

※ Derby はこのメソッドをサポートしていません。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    System.out.println(connection.getClientInfo()); // {}
    System.out.println(connection.getClientInfo("aaa")); // null

    try {
        connection.setClientInfo("aaa", "XXX");
    } catch (SQLClientInfoException e) {
        System.out.println(e);
    }

    // 結果
    // ↓
    //java.sql.SQLClientInfoException:
    //  The requested property change is not supported 'aaa'='XXX'.

    try {
        final var properties = new Properties();
        properties.setProperty("aaa", "XXX");
        connection.setClientInfo(properties);
    } catch (SQLClientInfoException e) {
        System.out.println(e);
    }

    // 結果
    // ↓
    //java.sql.SQLClientInfoException:
    //  The requested property change is not supported 'aaa'='XXX'.
}

void setClientInfo (Properties properties)

接続のクライアント情報プロパティの値を設定します。

※ Derby はこのメソッドをサポートしていません。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    System.out.println(connection.getClientInfo()); // {}
    System.out.println(connection.getClientInfo("aaa")); // null

    try {
        connection.setClientInfo("aaa", "XXX");
    } catch (SQLClientInfoException e) {
        System.out.println(e);
    }

    // 結果
    // ↓
    //java.sql.SQLClientInfoException:
    //  The requested property change is not supported 'aaa'='XXX'.

    try {
        final var properties = new Properties();
        properties.setProperty("aaa", "XXX");
        connection.setClientInfo(properties);
    } catch (SQLClientInfoException e) {
        System.out.println(e);
    }

    // 結果
    // ↓
    //java.sql.SQLClientInfoException:
    //  The requested property change is not supported 'aaa'='XXX'.
}

void setHoldability (int holdability)

このConnectionオブジェクトを使用して生成されたResultSetオブジェクトのデフォルトの保持機能を指定された保持機能へ変更します。

final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

try (final var connection = DriverManager.getConnection(url)) {

    table.create(connection);
    table.insert(connection, new Product("Orange", 10));

    connection.setAutoCommit(false);

    final var ret1 = connection.getHoldability();
    System.out.println(ret1 == ResultSet.HOLD_CURSORS_OVER_COMMIT); // true

    try (final var statement = connection.createStatement()) {

        try (final var result = statement.executeQuery("""
                SELECT * FROM PRODUCTS
                """)) {

            System.out.println(result.isClosed()); // false

            connection.commit();
            System.out.println(result.isClosed()); // false
        }
    }

    connection.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);

    final var ret2 = connection.getHoldability();
    System.out.println(ret2 == ResultSet.CLOSE_CURSORS_AT_COMMIT); // true

    try (final var statement = connection.createStatement()) {

        try (final var result = statement.executeQuery("""
                SELECT * FROM PRODUCTS
                """)) {

            System.out.println(result.isClosed()); // false

            connection.commit();
            System.out.println(result.isClosed()); // true
        }
    }
}

void setNetworkTimeout (Executor executor, int milliseconds)

ConnectionまたはConnectionから作成されたオブジェクトが、データベースが任意の1つの要求に応答するのを待つ最大期間を設定します。

※ Derby はこのメソッドをサポートしていません。

try (final var executor = Executors.newSingleThreadExecutor()) {

    final var url = "jdbc:derby://localhost:1527/db;create=true";
    try (final var connection = DriverManager.getConnection(url)) {
        connection.setNetworkTimeout(executor, 120);
    } catch (SQLFeatureNotSupportedException e) {
        System.out.println("SQLFeatureNotSupportedException!");
    }

    // 結果
    // ↓
    //SQLFeatureNotSupportedException!
}

void setReadOnly (boolean readOnly)

ドライバがデータベースの最適化を有効にできるように、ヒントとして、この接続を読込み専用モードに設定します。

final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

try (final var connection = DriverManager.getConnection(url)) {

    System.out.println(connection.isReadOnly()); // false

    table.create(connection);
    table.insert(connection, new Product("Orange", 10));
    System.out.println(table.selectAll(connection)); // [Orange(10)]

    table.update(connection, new Product("Orange", 20));
    System.out.println(table.selectAll(connection)); // [Orange(20)]

    connection.setReadOnly(true);
    System.out.println(connection.isReadOnly()); // true

    try {
        table.update(connection, new Product("Orange", 30));
    } catch (SQLException e) {
        System.out.println(e);
    }

    // 結果
    // ↓
    //java.sql.SQLException: An SQL data change is not permitted
    //  for a read-only connection, user or database.
}

Savepoint setSavepoint ()

現在のトランザクションで名前のないセーブポイントを作成し、それを表す新しいSavepointオブジェクトを返します。

final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

try (final var connection = DriverManager.getConnection(url)) {
    connection.setAutoCommit(false);

    table.create(connection);
    table.insert(connection, new Product("Orange", 10));

    connection.commit();
    System.out.println(table.selectAll(connection)); // [Orange(10)]

    // コミットしません。
    table.update(connection, new Product("Orange", 20));
    System.out.println(table.selectAll(connection)); // [Orange(20)]

    final var savepoint1 = connection.setSavepoint();
    System.out.println(savepoint1.getSavepointId()); // 1

    table.update(connection, new Product("Orange", 30));
    System.out.println(table.selectAll(connection)); // [Orange(30)]

    final var savepoint2 = connection.setSavepoint("XYZ");
    System.out.println(savepoint2.getSavepointName()); // XYZ

    table.update(connection, new Product("Orange", 40));
    System.out.println(table.selectAll(connection)); // [Orange(40)]

    connection.rollback(savepoint2);
    System.out.println(table.selectAll(connection)); // [Orange(30)]

    connection.rollback(savepoint1);
    System.out.println(table.selectAll(connection)); // [Orange(20)]

    connection.commit();
}

Savepoint setSavepoint (String name)

現在のトランザクションで指定された名前のセーブポイントを作成し、それを表す新しいSavepointオブジェクトを返します。

final var url = "jdbc:derby:memory:db;create=true";
final var table = new ProductTable();

try (final var connection = DriverManager.getConnection(url)) {
    connection.setAutoCommit(false);

    table.create(connection);
    table.insert(connection, new Product("Orange", 10));

    connection.commit();
    System.out.println(table.selectAll(connection)); // [Orange(10)]

    // コミットしません。
    table.update(connection, new Product("Orange", 20));
    System.out.println(table.selectAll(connection)); // [Orange(20)]

    final var savepoint1 = connection.setSavepoint();
    System.out.println(savepoint1.getSavepointId()); // 1

    table.update(connection, new Product("Orange", 30));
    System.out.println(table.selectAll(connection)); // [Orange(30)]

    final var savepoint2 = connection.setSavepoint("XYZ");
    System.out.println(savepoint2.getSavepointName()); // XYZ

    table.update(connection, new Product("Orange", 40));
    System.out.println(table.selectAll(connection)); // [Orange(40)]

    connection.rollback(savepoint2);
    System.out.println(table.selectAll(connection)); // [Orange(30)]

    connection.rollback(savepoint1);
    System.out.println(table.selectAll(connection)); // [Orange(20)]

    connection.commit();
}

void setSchema (String schema)

アクセスするスキーマ名を設定します。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    final var ret1 = connection.getSchema();
    System.out.println(ret1); // APP

    try (final var statement = connection.createStatement()) {
        statement.executeUpdate("""
                CREATE SCHEMA TEST
                """);
    }

    connection.setSchema("TEST");

    final var ret2 = connection.getSchema();
    System.out.println(ret2); // TEST
}

default void setShardingKey (ShardingKey shardingKey)

このConnectionで使用するshardingKeyを指定

※ Derby はこのメソッドをサポートしていません。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {
    connection.setShardingKey(null);
} catch (SQLFeatureNotSupportedException e) {
    System.out.println(e);
}

// 結果
// ↓
//java.sql.SQLFeatureNotSupportedException: setShardingKey not implemented

default void setShardingKey (ShardingKey shardingKey, ShardingKey superShardingKey)

このConnectionで使用するshardingKeyおよびsuperShardingKeyを指定

※ Derby はこのメソッドをサポートしていません。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {
    connection.setShardingKey(null, null);
} catch (SQLFeatureNotSupportedException e) {
    System.out.println(e);
}

// 結果
// ↓
//java.sql.SQLFeatureNotSupportedException: setShardingKey not implemented

default boolean setShardingKeyIfValid (ShardingKey shardingKey, int timeout)

この接続のシャーディング・キーを設定および検証します。

※ Derby はこのメソッドをサポートしていません。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {
    connection.setShardingKeyIfValid(null, 0);
} catch (SQLFeatureNotSupportedException e) {
    System.out.println(e);
}

// 結果
// ↓
//java.sql.SQLFeatureNotSupportedException: setShardingKeyIfValid not implemented

default boolean setShardingKeyIfValid (ShardingKey shardingKey, ShardingKey superShardingKey, int timeout)

この接続のシャーディング・キーを設定および検証します。

※ Derby はこのメソッドをサポートしていません。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {
    connection.setShardingKeyIfValid(null, null, 0);
} catch (SQLFeatureNotSupportedException e) {
    System.out.println(e);
}

// 結果
// ↓
//java.sql.SQLFeatureNotSupportedException: setShardingKeyIfValid not implemented

void setTransactionIsolation (int level)

このConnectionオブジェクトのトランザクション遮断レベルを指定されたものに変更することを試みます。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    final var ret1 = connection.getTransactionIsolation();
    System.out.println(ret1 == Connection.TRANSACTION_READ_COMMITTED); // true

    connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

    final var ret2 = connection.getTransactionIsolation();
    System.out.println(ret2 == Connection.TRANSACTION_SERIALIZABLE); // true
}

void setTypeMap (Map<String,Class<?>> map)

このConnectionオブジェクトの型マップとして、指定されたTypeMapオブジェクトをインストールします。

※ Derby はこのメソッドをサポートしていません。

final var url = "jdbc:derby:memory:db;create=true";
try (final var connection = DriverManager.getConnection(url)) {

    System.out.println(connection.getTypeMap()); // {}

    try {
        connection.setTypeMap(Map.of("dummy", Object.class));
    } catch (SQLFeatureNotSupportedException e) {
        System.out.println(e);
    }

    // 結果
    // ↓
    //java.sql.SQLFeatureNotSupportedException: Feature not implemented: No details.
}

Wrapperで宣言されたメソッド

isWrapperFor, unwrap

「Java API 使用例 : Wrapper」をご参照ください。

API 使用例

Java : Connection (JDBC) - API使用例

概要

フィールド

static final int TRANSACTION_NONE

static final int TRANSACTION_READ_COMMITTED

static final int TRANSACTION_READ_UNCOMMITTED

static final int TRANSACTION_REPEATABLE_READ

static final int TRANSACTION_SERIALIZABLE

メソッド

void abort (Executor executor)

default void beginRequest ()

void clearWarnings ()

void close ()

void commit ()

Array createArrayOf (String typeName, Object[] elements)

Blob createBlob ()

Clob createClob ()

NClob createNClob ()

SQLXML createSQLXML ()

Statement createStatement ()

Statement createStatement (int resultSetType, int resultSetConcurrency)

Statement createStatement (int resultSetType, int resultSetConcurrency, int resultSetHoldability)

Struct createStruct (String typeName, Object[] attributes)

default void endRequest ()

boolean getAutoCommit ()

String getCatalog ()

Properties getClientInfo ()

String getClientInfo (String name)

int getHoldability ()

DatabaseMetaData getMetaData ()

int getNetworkTimeout ()

String getSchema ()

int getTransactionIsolation ()

Map<String,Class<?>> getTypeMap ()

SQLWarning getWarnings ()

boolean isClosed ()

boolean isReadOnly ()

boolean isValid (int timeout)

String nativeSQL (String sql)

CallableStatement prepareCall (String sql)

CallableStatement prepareCall (String sql, int resultSetType, int resultSetConcurrency)

CallableStatement prepareCall (String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability)

PreparedStatement prepareStatement (String sql)

PreparedStatement prepareStatement (String sql, int autoGeneratedKeys)

PreparedStatement prepareStatement (String sql, int[] columnIndexes)

PreparedStatement prepareStatement (String sql, int resultSetType, int resultSetConcurrency)

PreparedStatement prepareStatement (String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability)

PreparedStatement prepareStatement (String sql, String[] columnNames)

void releaseSavepoint (Savepoint savepoint)

void rollback ()

void rollback (Savepoint savepoint)

void setAutoCommit (boolean autoCommit)

void setCatalog (String catalog)

void setClientInfo (String name, String value)

void setClientInfo (Properties properties)

void setHoldability (int holdability)

void setNetworkTimeout (Executor executor, int milliseconds)

void setReadOnly (boolean readOnly)

Savepoint setSavepoint ()

Savepoint setSavepoint (String name)

void setSchema (String schema)

default void setShardingKey (ShardingKey shardingKey)

default void setShardingKey (ShardingKey shardingKey, ShardingKey superShardingKey)

default boolean setShardingKeyIfValid (ShardingKey shardingKey, int timeout)

default boolean setShardingKeyIfValid (ShardingKey shardingKey, ShardingKey superShardingKey, int timeout)

void setTransactionIsolation (int level)

void setTypeMap (Map<String,Class<?>> map)

Wrapperで宣言されたメソッド

isWrapperFor, unwrap

関連記事