Win a copy of Transfer Learning for Natural Language Processing (MEAP) this week in the Artificial Intelligence and Machine Learning 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Paul Clapham
  • Devaka Cooray
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Knute Snortum
  • Liutauras Vilda
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Piet Souris
Bartenders:
  • salvin francis
  • Carey Brown
  • Frits Walraven

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

 
Ranch Hand
Posts: 60
  • Mark post as helpful
  • send pies
  • 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: 21960
    149
    Android Eclipse IDE Tomcat Server Redhat Java Linux
    • Mark post as helpful
    • send pies
    • 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: 60
    • Mark post as helpful
    • send pies
    • 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: 21960
    149
    Android Eclipse IDE Tomcat Server Redhat Java Linux
    • Mark post as helpful
    • send pies
    • 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: 60
    • Mark post as helpful
    • send pies
    • 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.
     
    My name is Inigo Montoya, you killed my father, prepare to read a tiny ad:
    Two software engineers solve most of the world's problems in one K&R sized book
    https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
      Bookmark Topic Watch Topic
    • New Topic