How to fix the Hibernate “No Dialect mapping for JDBC type” issue

(Last Updated On: February 6, 2019)
Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

Introduction

Recently, stumbled on this question on the Hibernate forum, and since I’ve been seeing it before on StackOverflow and bumped into it myself while working with JPA and Hibernate, I decided to turn the answer into an article.

Therefore, in this article, you are going to find out how you can fix the “No Dialect mapping for JDBC type” Hibernate issue.

Domain Model

Considering we have a Book entity that defines a properties attribute which is associated with a JSON column in the database.

The Book entity can be mapped as follows:

@Entity(name = "Book")
@Table(name = "book")
@TypeDef(
    name = "jsonb-node", 
    typeClass = JsonNodeBinaryType.class
)
public class Book {

    @Id
    @GeneratedValue
    private Long id;

    @NaturalId
    private String isbn;

    @Type(type = "jsonb-node")
    @Column(columnDefinition = "jsonb")
    private JsonNode properties;

    //Getters and setters omitted for brevity
}

The JsonNodeBinaryType is provided by the hibernate-types project, so if you want to persist JSON properties, you don’t need to write your own Hibernate Types.
Just add the hibernate-types dependency to your project and map the JSON properties accordingly.

Persisting and fetching the Book entity

Now, let’s assume we have added the following Book entity in our database:

Book book = new Book();
book.setIsbn("978-9730228236");
book.setProperties(
    JacksonUtil.toJsonNode(
        "{" +
        "   \"title\": \"High-Performance Java Persistence\"," +
        "   \"author\": \"Vlad Mihalcea\"," +
        "   \"publisher\": \"Amazon\"," +
        "   \"price\": 44.99" +
        "}"
    )
);

entityManager.persist(book);

When persisting the Book entity, Hibernate will issue the proper SQL INSERT statement:

INSERT INTO book (
    isbn, 
    properties, 
    id
) 
VALUES (
    '978-9730228236', 
    {
        "title":"High-Performance Java Persistence",
        "author":
        "Vlad Mihalcea",
        "publisher":"Amazon",
        "price":44.99
    }, 
    1
)

Now, we fetching the Book entity by its natural identifier, we can see that the properties JSON attribute is fetched as a JsonNode:

Book book = entityManager
.unwrap(Session.class)
.bySimpleNaturalId(Book.class)
.load("978-9730228236");

assertEquals(
    "High-Performance Java Persistence", 
    book.getProperties().get("title").asText()
);

Fetching the JSON attribute using JPQL

Now, if we want to fetch the properties entity attribute using JPQL, we can execute the following query:

JsonNode properties = entityManager
.createQuery(
    "select b.properties " +
    "from Book b " +
    "where b.isbn = :isbn", JsonNode.class)
.setParameter("isbn", "978-9730228236")
.getSingleResult();

assertEquals(
    "High-Performance Java Persistence", 
    properties.get("title").asText()
);

And everything works properly since the JPQL query is parsed and the underlying Hibernate Type that handles the properties attribute is going to be known when building the result.

Fetching the JSON attribute using native SQL

However, if we try to do the same using a native SQL query:

JsonNode properties = (JsonNode) entityManager
.createNativeQuery(
    "SELECT properties " +
    "FROM book " +
    "WHERE isbn = :isbn")
.setParameter("isbn", "978-9730228236")
.getSingleResult();

assertEquals(
    "High-Performance Java Persistence",
    properties.get("title").asText()
);

Hibernate will throw the following MappingException:

javax.persistence.PersistenceException: org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

The 1111 JDBC type corresponds to Types.OTHER which is what the PostgreSQL JDBC Driver uses for jsonb column types.

Mapping Types.OTHER to JsonNodeBinaryType

There are several ways you can address this issue. You can register a Hibernate Type to handle the JDBC Types.OTHER either globally or on a per-query basis.

Mapping the JDBC Types.OTHER to JsonNodeBinaryType at the Dialect level

You can map a given JDBC Type code to a Hibernate Type using the database-specific Dialect.

Therefore, for PostgreSQL, we could define a PostgreSQL95JsonDialect that looks as follows:

public class PostgreSQL95JsonDialect 
        extends PostgreSQL95Dialect {

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

And if we provide the custom PostgreSQL95JsonDialect via the hibernate.dialect configuration property:

<property 
    name="hibernate.dialect"
    value="com.vladmihalcea.book.hpjp.hibernate.type.json.PostgreSQL95JsonDialect"
/>

The native SQL query will run just fine.

Mapping the JDBC Types.OTHER to JsonNodeBinaryType at the NativeQuery level

Another option is to provide Hibernate Type associated with the current JDBC ResultSet:

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

assertEquals(
    "High-Performance Java Persistence",
    properties.get("title").asText()
);

Notice the addScalar method call which provides the Hibernate Type to be used when handling the Types.Other JDBC column type.

That’s it!

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Handling the No Dialect mapping for JDBC type issue is not very complicated, and it can be done either globally or on a per-query basis.

Download free ebook sample

Newsletter logo
10 000 readers have found this blog worth following!

If you subscribe to my newsletter, you'll get:
  • A free sample of my Video Course about running Integration tests at warp-speed using Docker and tmpfs
  • 3 chapters from my book, High-Performance Java Persistence,
  • a 10% discount coupon for my book.

18 Comments on “How to fix the Hibernate “No Dialect mapping for JDBC type” issue

  1. I use Wildfly (Hibernate 5.3.x), PostegreSQL 10 and I have table columns with PostgreSQL-specific ‘uuid’ and ‘jsonb’ data types.
    Depending on the situation (query complexity, jsonb involved or not, etc.) I need to be able to use all kinds of JPA queries via the JEE EntityManager i.e. native queries, JPQL queries and (maybe) CriteriaAPI queries.
    Probably my question below is answered in various articles or SO answers but sincerely I am lost with it.
    Could you be so kind as to share which Hibernate types (either the Hibernate built-ins or yours from hibernte-types-52) should I register and which should be the corresponding Java data types on the @Entity class in order to have an as effecient as possible data transfer from Java to PostgreSQL

    Many thanks,
    Vagelis

    • For entity queries, Hibernate will use the entity mappings, meaning that @TypeDef will automatilaly do the trick. For native SQL, it’s not possible to know what Types.OTHER is supposed to mean, so use addScalar as explained in this article.

      • So the problem with registering the types with Hibernate is that PostgreSQL uses Types.OTHER and we can’t register uuid and jsonb at the same time?
        Could that be argued with the Postgres JDBC driver community because it seems like a shortcoming?

      • The problem is JDBC defines the Types.OTHER for everything else not supported natively. Now, if PostgreSQL JDBC Driver used different codes like other RDBMS do (e.g. OracleTypes), it would be easier to fix this issue indeed.

  2. Hi Vlad. Thanx for the article.
    Please, help. There is i have an error when i make nativeQuery. I have UUID id in my entity, when i create nativequery throws an errors like this:
    org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111.

    I’ve searched the solution everywhere, but couldn’t find

  3. Hi Vlad!

    I try to use json or array types of ‘hibernate-types’ in Spring Data projections but with no luck.
    If I use array type then I get an exception:

    org.springframework.orm.jpa.JpaSystemException: Could not instantiate Type: com.vladmihalcea.hibernate.type.array.StringArrayType

    Then I use json type I get another exception:

    java.lang.IllegalArgumentException: Projection type must be an interface!

    For array type I used StringArrayType in the custom PostgreSQL dialect class. For json type – JsonNodeStringType:

    public class PostgreSQLCustomDialect extends PostgreSQL95Dialect {
    
        public PostgreSQLCustomDialect() {
            super();
            registerHibernateType(Types.OTHER, JsonNodeStringType.class.getName());
            registerHibernateType(Types.ARRAY, StringArrayType.class.getName());
        }
    }
    

    Could you please help to solve this problem?
    My demo repo is here: https://github.com/Cepr0/hibernate-types-in-projection

    Just run Application class – then you can see those exceptions.
    Database settings are in resources/application.yml

    Thanks in advance!
    Sergei.

    • You can find lots of tests that work just fine in the project GitHub repository. In your case, the problem comes from the Spring configuration which is outside the scope of this project. Run a comparison debug between my tests and yours and see why mine work just fine and your application does not.

  4. Hi Vlad,

    I always get javax.persistence.NoResultException: No entity found for query, whe i execute the as mentioned above
    entityManager.createNativeQuery(“SELECT properties FROM public.customer WHERE properties ->> ‘name’ = :name”).setParameter(“name”, name).unwrap(org.hibernate.query.NativeQuery.class).addScalar(“properties”, JsonNodeBinaryType.INSTANCE);

    But it works fine if i remove setParameter i.e.

    entityManager.createNativeQuery(“SELECT properties FROM public.customer WHERE properties ->> ‘name’ = ‘Peter’ “).unwrap(org.hibernate.query.NativeQuery.class).addScalar(“properties”, JsonNodeBinaryType.INSTANCE);

    is this an issue with setParameter. Please clarify.

    regards
    Nagarjun

    • There is no issue. Most likely, there is no customer with the name you provided. To validate this, just take the SQL Hibernate generates for you and run it in the SQL console.

      • Hi Vlad,

        Thanks for the reply. This is strange behavior i am observing. The query getting generated is the same and the customer with specified name exists and query when executed in db console works fine.
        But when i do setParamater(“name”,”Kim”); log is as below TRACE 145412 — [nio-8080-exec-7] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] – [Kim]
        and query works.

        again when i do setParamater(“name”,name); log is as below is TRACE 145412 — [nio-8080-exec-7] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] – [“Kim”].

        query doesnt work in second case.

        If you see extra ” is generated in the second case. is this normal. Please clarify

        regards
        Nagarjun

      • Try to debug it and see why the outcome is different.

      • Hi Vlad,

        Thanks, This works. I am trying a different scenario. Is it possible to query key values in a json array.
        For example if the jsonb data stored is [{“key1″:”value1”},{“key2″:”value2”}].

      • Sure thing. Just use a native SQL query if the database supports the feature.

      • Hi Vlad,

        I used the query SELECT properties FROM public.customer WHERE properties @>[{“key1″:”value1”}]; This works fine. But I would like to parameterize like u provided in example in blog like 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();

        Can we do parameterized query for json array. could you please point me to example implementation.

        Regards
        Nagarjun

  5. I get that exception (“No Dialect mapping for JDBC type: 745962066”) when trying to save a java.time.YearMonth with an AttributeConverter. I would have expected that to work. Will that be fixed in the future?

    • The problem comes from the DB side which uses a column type whose JDBC Type code is 745962066. I have no idea what column would that be, but it’s not something standard. That being said, an AttributeConverter will not work since you need to get/set the column at the JDBC level based on what the Driver expects and then convert it to YearMonth. A Hibernate custom Type is more suitable for the task.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Hypersistence Optimizer can automatically detect if you are using JPA and Hibernate properly