LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@charoninTIS株式会社

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

Posted at

What's？

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

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

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

環境

今回の環境は、こちら。

$ 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コンテナで用意します。

$ 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

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

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

pom.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型を扱う

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

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 {

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

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

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

    @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型に文字列データを与えるな、と言われますね。

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型でも同じです。

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

    @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\"}");
                }
            }
        }
    }

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

            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として扱って良さそうです。

                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としてはどのような扱いになるのでしょう？

    @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型を扱えそうですね。

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

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?