Hibernate PostgreSQL JSONB issue: No Dialect mapping for JDBC type: 1111


#1

Hi, I have jsonb data type stored in postgres. I want to retrieve a field from jsonb data using hibernate. My hibernate query is correctly formed and when I execute it directly on postgre, it runs correctly and gives me the result. But when I execute through HIbernate, I get bind error.
Hibernate 5.2
Postgre 9.4

Stack Trace:


#2

There’s no stack-trace attached, so I assume you got the No Dialect mapping for JDBC type: 1111 error.

First, Hibernate does not support JSON natively, so you might want to check out the hibernate-types project which offers this functionality.

Second, the 1111 code stands for Types.OTHER, so you might want to register that to the jsonb type.

For that, you can either use a custom Dialect:

public class PostgreSQL95JsonDialect 
        extends PostgreSQL95Dialect {
 
    public PostgreSQL95JsonDialect() {
        super();
        this.registerHibernateType(
            Types.OTHER, JsonNodeBinaryType.class.getName()
        );
    }
}

Or, at native SQL query level:

JsonNode properties = (JsonNode) entityManager
.createNativeQuery(
    "SELECT properties " +
    "FROM book " +
    "WHERE isbn = :isbn")
.setParameter("isbn", "978-9730228236")
.unwrap(org.hibernate.query.NativeQuery.class)
.addScalar("properties", JsonNodeBinaryType.INSTANCE)
.getSingleResult();

Check out this article for more details about how you can register a custom JDBC Type to a Hibernate Type.