Use PostgreSQL data type (jsonb) from Java

I investigated how to use the data side peculiar to PostgreSQL from Python, so I will record it.

This is about the jsonb type.

This content is an excerpt of the content presented at the 51st Nagaoka IT Developer Study Group. The slide is here.

The Python version is here.

Use PostgreSQL from Java

When using PostgreSQL from an application Use via JDBC API. The JDBC API is a specification with only an interface, and there is a JDBC Driver for PostgreSQL as an implementation. This is the official PostgreSQL JDBC driver.

There are various DB access libraries via JDBC, but this time we will consider an example of using JPA as the Java standard persistence specification. JPA is also a specification and an interface is prepared. One of the implementations is EclipseLink.

Source code

https://github.com/civic/postgresql-java-json

Use jsonb

CREATE TABLE json_test(
  id integer,
  info jsonb
)

Using jsonb from low-level API (JDBC)

SELECT

resultSet.getString("info");
// {"a": 100, "b": "Hello"}

You can get the JSON string by getting the json column and gettingString.

INSERT (1)

import org.postgresql.util.PGobject;

PGobject pgobj =new PGobject();
pgobj.setValue(json_string); //json string
pgobj.setType("jsonb");

preparedStatement.setObject(1, pgobj);

There is no JSON type in the JDBC API. It is usually used via the interface provided by JDBC, but there is no choice but to use the driver function provided by PostgreSQL. Should be used in limited areas such as common libraries.

INSERT (2)

PreparedStatement ps =conn.prepareStatement(
 "INSERT INTO json_test(info) VALUES(jsonb(?))"
);

ps.setString(1, json_string);  //json string

A pattern that sets parameters as a JSON string and casts them to jsonb in an SQL statement. Since it is setString as a character string, it is possible only with the standard API.

Using jsonb from high level API (JPA)

SELECT / INSERT

@Entity
@Table(name = "json_test")
public class JsonTest implements Serializable {
    // ...Omission...

    @Column(name = "info")
    @Convert(converter = JsonbConverter.class)
    private String info;
...

JPA, an OR mapper-like persistence function. A mechanism called Converter is prepared in the JPA specifications. If you create a Converter and define a mutual conversion of Java type <=> SQL type, you can use it for column definition with annotation.

Implementation of Jsonb Converter

/**
@Converter
public class JsonbConverter implements AttributeConverter<String, PGobject>{

    /**
     * String-Conversion to Pgobject
     * @param x
     * @return 
     */
    @Override
    public PGobject convertToDatabaseColumn(String x) {
        PGobject pgobject =new PGobject();
        pgobject.setType("jsonb");
        try {
            pgobject.setValue(x);
        } catch (SQLException ex) {
            Logger.getLogger(JsonbConverter.class.getName()).log(Level.SEVERE, null, ex);
            throw new RuntimeException(ex);
        }
        return pgobject;
    }

    @Override
    /**
     * Pgobject-Convert to String
     * @param y
     * @return 
     */
    public String convertToEntityAttribute(PGobject y) {
        return y.getValue();
    }
    
}