PostgreSQLのJSON型、JSONB型をJDBCで少し扱ってみる

# What's？

PostgreSQLの`json`型、`jsonb`型をJDBC経由で扱っておきたいなということで。

# PostgreSQLのJDBCドライバーとjson、jsonb型

PostgreSQLのJDBCドライバーのドキュメントには、`json`型や`jsonb`型に関する記述はなさそうです。

https://jdbc.postgresql.org/documentation/

# 環境

今回の環境は、こちら。

```shell
$ java --version
openjdk 17.0.4 2022-07-19
OpenJDK Runtime Environment (build 17.0.4+8-Ubuntu-120.04)
OpenJDK 64-Bit Server VM (build 17.0.4+8-Ubuntu-120.04, mixed mode, sharing)


$ mvn --version
Apache Maven 3.8.6 (84538c9988a25aec085021c365c560670ad80f63)
Maven home: /home/charon/.sdkman/candidates/maven/current
Java version: 17.0.4, vendor: Private Build, runtime: /usr/lib/jvm/java-17-openjdk-amd64
Default locale: ja_JP, platform encoding: UTF-8
OS name: "linux", version: "5.4.0-128-generic", arch: "amd64", family: "unix"
```

PostgreSQLは、Dockerコンテナで用意します。

```shell
$ docker container run -it --rm -p 5432:5432 \
  -e POSTGRES_DB=example \
  -e POSTGRES_USER=charon \
  -e POSTGRES_PASSWORD=password \
  --name postgres \
  postgres:14.5
```

次のテーブルを作成済みとします。

```sql
create table t(
  id integer,
  json_column json,
  jsonb_column jsonb,
  primary key(id)
);
```

`pom.xml`は、以下のように用意しました。

```xml: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>org.example</groupId>
    <artifactId>postgres-jdbc-json-jsonb</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    </properties>

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

        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>5.9.1</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.assertj</groupId>
            <artifactId>assertj-core</artifactId>
            <version>3.23.1</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>2.22.2</version>
            </plugin>
        </plugins>
    </build>
</project>
```

確認は、テストコードで行いたいと思います。

# PostgreSQLのJDBCドライバーで、json、jsonb型を扱う

テストコードのクラス定義は、以下のようにします。

```java:src/test/java/com/example/PostgreSqlJsonTest.java
package com.example;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import org.junit.jupiter.api.Test;

import static org.assertj.core.api.Assertions.assertThat;
import static org.assertj.core.api.Assertions.assertThatThrownBy;

public class PostgreSqlJsonTest {

    // ここにテストを書く！
}
```

この中を埋めていきましょう。

まず、なにも考えずにデータを登録しようとしてすると、例外がスローされました。

```java
    @Test
    public void testInsertFailure() throws SQLException {
        try (Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/example", "charon", "password");
             PreparedStatement ps =
                     connection.prepareStatement("insert into t(id, json_column, jsonb_column) values(?, ?, ?)")) {
            assertThatThrownBy(() -> {
                ps.setInt(1, 1);
                ps.setString(2, "{\"foo\": \"bar\"}");
                ps.setString(3, "{\"foo\": \"bar\"}");

                ps.executeUpdate();
            })
                    .isInstanceOf(SQLException.class)
                    .hasMessage("ERROR: column \"json_column\" is of type json but expression is of type character varying\n" +
                            "  ヒント: You will need to rewrite or cast the expression.\n" +
                            "  位置: 57");
        }
    }
```

JSON型に文字列データを与えるな、と言われますね。

```shell
org.postgresql.util.PSQLException: ERROR: column "json_column" is of type json but expression is of type character varying
  ヒント: You will need to rewrite or cast the expression.
  位置: 57
```

`jsonb`型でも同じです。

これを回避するには、キャストすると良さそうです。

```java
    @Test
    public void testJsonAndJsonb() throws SQLException {
        try (Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/example", "charon", "password")) {
            try (PreparedStatement ps = connection.prepareStatement("truncate table t")) {
                ps.executeUpdate();
            }

            connection.setAutoCommit(false);

            try (PreparedStatement ps =
                         connection.prepareStatement("insert into t(id, json_column, jsonb_column) values(?, ?::json, cast(? as jsonb))")) {
                ps.setInt(1, 1);
                ps.setString(2, "{\"foo\": \"bar\"}");
                ps.setString(3, "{\"foo\": \"bar\"}");

                ps.executeUpdate();
            }

            try (PreparedStatement ps =
                         connection.prepareStatement("insert into t(id, json_column, jsonb_column) values(?, json(?), jsonb(?))")) {
                ps.setInt(1, 2);
                ps.setString(2, "{\"foo\": \"bar\"}");
                ps.setString(3, "{\"foo\": \"bar\"}");

                ps.executeUpdate();
            }

            connection.commit();

            try (PreparedStatement ps =
                         connection.prepareStatement("select id, json_column, jsonb_column from t where id = ?")) {
                ps.setInt(1, 1);

                try (ResultSet rs = ps.executeQuery()) {
                    rs.next();

                    assertThat(rs.getInt("id")).isEqualTo(1);
                    assertThat(rs.getString("json_column")).isEqualTo("{\"foo\": \"bar\"}");
                    assertThat(rs.getString("jsonb_column")).isEqualTo("{\"foo\": \"bar\"}");
                }
            }
        }
    }
```

いくつか方法がありますが、このあたりで。

```java
            try (PreparedStatement ps =
                         connection.prepareStatement("insert into t(id, json_column, jsonb_column) values(?, ?::json, cast(? as jsonb))")) {


            try (PreparedStatement ps =
                         connection.prepareStatement("insert into t(id, json_column, jsonb_column) values(?, json(?), jsonb(?))")) {
```

取得は、`String`として扱って良さそうです。

```java
                try (ResultSet rs = ps.executeQuery()) {
                    rs.next();

                    assertThat(rs.getInt("id")).isEqualTo(1);
                    assertThat(rs.getString("json_column")).isEqualTo("{\"foo\": \"bar\"}");
                    assertThat(rs.getString("jsonb_column")).isEqualTo("{\"foo\": \"bar\"}");
                }
```

# java.sql.Typesでの扱いを見る

`json`型、`jsonb`型は、`java.sql.Types`としてはどのような扱いになるのでしょう？

```java
    @Test
    public void testJsonAndJsonbTypeMapping() throws SQLException {
        try (Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/example", "charon", "password")) {
            DatabaseMetaData databaseMetaData = connection.getMetaData();
            try (ResultSet rs = databaseMetaData.getColumns(null, null, "t", "%")) {
                rs.next();
                assertThat(rs.getString("column_name")).isEqualTo("id");
                assertThat(rs.getInt("data_type")).isEqualTo(Types.INTEGER);
                assertThat(rs.getString("type_name")).isEqualTo("int4");

                rs.next();
                assertThat(rs.getString("column_name")).isEqualTo("json_column");
                assertThat(rs.getInt("data_type")).isEqualTo(Types.OTHER);
                assertThat(rs.getString("type_name")).isEqualTo("json");

                rs.next();
                assertThat(rs.getString("column_name")).isEqualTo("jsonb_column");
                assertThat(rs.getInt("data_type")).isEqualTo(Types.OTHER);
                assertThat(rs.getString("type_name")).isEqualTo("jsonb");
            }
        }
    }
```

`java.sql.Types#OTHER`となるようです。

# 別解

JDBC APIを外れていいのなら、`org.postgresql.util.PGobject`というクラスを使えば直接`json`型を扱えそうですね。

```java
    PreparedStatement pstmt = conn.prepareStatement("SELECT ?::json[]");
    PGobject p1 = new PGobject();
    p1.setType("json");
    p1.setValue("{\"x\": 10}");

    PGobject p2 = new PGobject();
    p2.setType("json");
    p2.setValue("{\"x\": 20}");
    PGobject[] in = new PGobject[] { p1, p2 };
    pstmt.setArray(1, conn.createArrayOf("json", in));
```

https://github.com/pgjdbc/pgjdbc/blob/REL42.5.0/pgjdbc/src/test/java/org/postgresql/test/jdbc4/ArrayTest.java#L229-L238