Win a copy of Node.js Design Patterns: Design and implement production-grade Node.js applications using proven patterns and techniques this week in the Server-Side JavaScript and NodeJS forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

Mapping Postgres JSONB data type with Hibernate: No Dialect mapping for JDBC type

 
Ranch Hand
Posts: 85
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have been consulting a number of approaches/posts/questions in order to deal with the following error (full stack trace) when running a Kotlin/SpringBoot application:



The issue lies in mapping the PostgreSQL’s JSONB data type with Hibernate.

The 2 approaches I have extensively tried and debugged are the following:

  • 1
  • Implementing a Custom Hibernate Mapping and creating a custom UserType for JSONB. References: https://vladmihalcea.com/how-to-map-json-collections-using-jpa-and-hibernate/, https://vladmihalcea.com/hibernate-no-dialect-mapping-for-jdbc-type/, https://github.com/vladmihalcea/high-performance-java-persistence
  • 2
  • Use Hibernate types. References are  https://www.vojtechruzicka.com/postgresqls-jsonb-type-mapping-using-hibernate/, https://thoughts-on-java.org/persist-postgresqls-jsonb-data-type-hibernate/, https://stackoverflow.com/questions/59967180/how-to-create-custom-jsonb-mapper-for-postgresql-and-hibernate-in-kotlin, https://github.com/thjanssen/HibernateJSONBSupport/tree/master/PostgresJSONB/src/main/java/org/thoughts/on/java/model

    I have attempted profusely at both, but with no luck whatsoever and I am keen to understand where I am going wrong and what I have been missing.

    Approach 1

    My Entity:



    My PoJO (or basically what it will need to be mapped):



    My custom type (abstract class)



    My concrete class:



    My application.yml jpa hibernate properties



    Approach 2

    Hibernate properties are exactly the same as well as the PoJo class, no custom mapper is included.

    Entity



    Custom Dialect (using hibernate types):



    Please note that I also tried using only:



    as well as having all this in my entity or the base entity it extended from (no change for that matter):



    Is there anything evidently wrong I am doing in both approaches? I cannot get it working and, not sure if in any way relevant, the numerical value after No Dialect mapping for JDBC type: is always different. I am adding this as I have seen some ids being relevant for certain categories of errors.

    Can you help? Do I need to add additional changes such as a scalar query?

    Thank you
     
    Saloon Keeper
    Posts: 24331
    167
    Android Eclipse IDE Tomcat Server Redhat Java Linux
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Well, that's way more than my bleary eyes can take in (at least when I'm not being paid for it), but if I'm not mistaken you're coming up against a situation where a database offers features above and beyond standard SQL and JDBC and you're hoping to get Hibernate to integrate tightly with it.

    While I'm certainly a PostgreSQL fan, I do tend to shy away from stuff that's overly vendor-dependent myself.

    In the general realm of JSON versus Hibernate, however, here's a thought. Hibernate JPA works with POJOs. Java also has the ability to serialize JavaScript into and out of POJOs. It's entirely permissible to annotate and/or use POJOs for more than one framework, and in fact I've done so - in my case with JAXB and Hibernate to provide XML support. Just remember to detach/re-attach from JPA when necessary.

    I apologize if I'm babbling or missing that you've already considered that, but, as I said, I'm doing a very superficial reading and wanted to make sure that you had considered that option.
     
    Geane Norm
    Ranch Hand
    Posts: 85
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Tim Holloway wrote:Well, that's way more than my bleary eyes can take in (at least when I'm not being paid for it), but if I'm not mistaken you're coming up against a situation where a database offers features above and beyond standard SQL and JDBC and you're hoping to get Hibernate to integrate tightly with it.

    While I'm certainly a PostgreSQL fan, I do tend to shy away from stuff that's overly vendor-dependent myself.

    In the general realm of JSON versus Hibernate, however, here's a thought. Hibernate JPA works with POJOs. Java also has the ability to serialize JavaScript into and out of POJOs. It's entirely permissible to annotate and/or use POJOs for more than one framework, and in fact I've done so - in my case with JAXB and Hibernate to provide XML support. Just remember to detach/re-attach from JPA when necessary.

    I apologize if I'm babbling or missing that you've already considered that, but, as I said, I'm doing a very superficial reading and wanted to make sure that you had considered that option.



    Hi, thank you for your reply and no need to apologise. Apologies for the verbose post, but I tend to be very specific when I go out and ask the community, so I have provided as much details as possible. I am not sure why I should be considering JAXB, the issue is with mapping from Pojo to Jonsb data type (in Postgres) when using Hibernate + Spring JPA (Jackson is already used for serialising and deserialising). There are 2 main approaches (as i have documented) and still both give me the same error. I simply wonder whether I am missing something very crucial out.

    Thanks
     
    Tim Holloway
    Saloon Keeper
    Posts: 24331
    167
    Android Eclipse IDE Tomcat Server Redhat Java Linux
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Well, you wouldn't use JAXB. I used it because in my particular app I was working with XML.

    As for the rest, a second glance seems to indicate that you're extending the PostgreSQL Dialect module but possibly not connecting to the extended version. What I'd do in a case like this is set a debugger breakpoint on the "get" method of org.hibernate.dialect.TypeNames and see what it's actually looking at to get the type names. You should be able to pull the Hibernate source and connect it to your debugger with only a little amount of effort - just make sure that the version of the source you pull matches the version of Hibernate that you are using!
     
    Geane Norm
    Ranch Hand
    Posts: 85
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Tim Holloway wrote:Well, you wouldn't use JAXB. I used it because in my particular app I was working with XML.

    As for the rest, a second glance seems to indicate that you're extending the PostgreSQL Dialect module but possibly not connecting to the extended version. What I'd do in a case like this is set a debugger breakpoint on the "get" method of org.hibernate.dialect.TypeNames and see what it's actually looking at to get the type names. You should be able to pull the Hibernate source and connect it to your debugger with only a little amount of effort - just make sure that the version of the source you pull matches the version of Hibernate that you are using!



    Thank you for this, I had a good dig about it. First of all, here are some relevant info regarding postgres, hibernate and postgres JDBC, etc:

    postgres:10-alpine
    PostgreSQL JDBC Driver JDBC 4.2 » 42.2.8
    org.springframework.boot:spring-boot-starter-data-jpa:2.2.1.RELEASE
    org.hibernate:hibernate-core:5.4.8.Final


    I am going to try to have the hibernate type approach working (namely Approach 2). I have re-aligned the custom Dialect class to my postgres version:




    In my entity I have:




    with this too:



    I then embarked in debugging as per your advice and targeting this from TypeNames.java:



    Needless to say that I am still getting the same error, but this is what I can see:



    I cannot see jsonb type, but then I want to find out exactly what is the mapping coming from my custom dialect. This is what I can see:



    Not sure where this is going all wrong. It looks very straightforward from and from the posts by the hibernate-types' author.

    If you have any idea, please let me know.
     
    Blood pressure normal? What do I change to get "magnificent"? Maybe this tiny ad?
    Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
    https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    reply
      Bookmark Topic Watch Topic
    • New Topic