[Java][JDBC]PostgreSQLのserial型、MS SQL ServerのIdentity型で生成された値を取得する

PostgreSQL の serial 型や SQL Server の identity 型で採番された値を取得するサンプルです。

PostgreSQL の serial 型での例

テスト用データベース

create table users (
   id serial primary key,
   name varchar(32) not null
);

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>example</artifactId>
    <version>1.0.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>example</name>

    <properties>
        <java.version>1.8</java.version>
        <maven.compiler.target>${java.version}</maven.compiler.target>
        <maven.compiler.source>${java.version}</maven.compiler.source>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.16</version>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

</project>

src/main/java/com/example/Example.java

1. PreparedStatementを作る時に RETURN_GENERATED_KEYS を指定します。
2. PreparedStatement#getGeneratedKeys で採番されたキーを取得します。

package com.example;

import java.sql.*;

public class Example {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.postgresql.Driver");

        String url = "jdbc:postgresql://127.0.0.1/my_database";
        String username = "my_username";
        String password = "my_secretpassword";

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            String insertQuery = "insert into users(name) values (?)";
            PreparedStatement stmt = connection.prepareStatement(insertQuery, Statement.RETURN_GENERATED_KEYS);
            stmt.setString(1, "John");
            stmt.executeUpdate();

            ResultSet rs = stmt.getGeneratedKeys();
            while (rs.next()) {
                int generatedKey = rs.getInt(1);
                System.out.println(String.format("generatedKey: %s", generatedKey));
            }
        }
    }

}

実行結果

$ mvn -q compile exec:java -Dexec.mainClass=com.example.Example
generatedKey: 1
$ mvn -q compile exec:java -Dexec.mainClass=com.example.Example
generatedKey: 2
$ mvn -q compile exec:java -Dexec.mainClass=com.example.Example
generatedKey: 3

Microsoft SQL Server での例

テスト用データベース

create table users (
    id bigint identity primary key,
    name varchar(32) not null,
)
go

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>example</artifactId>
    <version>1.0.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>example</name>

    <properties>
        <java.version>1.8</java.version>
        <maven.compiler.target>${java.version}</maven.compiler.target>
        <maven.compiler.source>${java.version}</maven.compiler.source>
    </properties>

    <dependencies>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>7.2.0.jre8</version>
            <scope>runtime</scope>
        </dependency>
    </dependencies>
</project>

src/main/java/com/example/Example.java

基本的には PostgreSQL の場合と同じです。

package com.example;

import java.sql.*;

public class Example {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        String databaseName = "my_database";
        String username = "my_username";
        String password = "my_secretpassword";

        String url = String.format("jdbc:sqlserver://localhost;databaseName=%s;user=%s;password=%s", databaseName, username, password);
        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            String insertQuery = "insert into users(name) values (?)";
            PreparedStatement stmt = connection.prepareStatement(insertQuery, Statement.RETURN_GENERATED_KEYS);
            stmt.setString(1, "John");
            stmt.executeUpdate();

            ResultSet rs = stmt.getGeneratedKeys();
            while (rs.next()) {
                int generatedKey = rs.getInt(1);
                System.out.println(String.format("generatedKey: %s", generatedKey));
            }
        }
    }

}

実行結果

$ mvn -q compile exec:java -Dexec.mainClass=com.example.Example
generatedKey: 1
$ mvn -q compile exec:java -Dexec.mainClass=com.example.Example
generatedKey: 2
$ mvn -q compile exec:java -Dexec.mainClass=com.example.Example
generatedKey: 3