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

Hibernate ORM
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:

Hibernate: SELECT rptmetricstx -> 'svp_name' as text FROM 
       		burmetrics where rpttypecd = 'onboarding'
2018-08-14 11:07:36.984 ERROR 31764 --- [nio-9001-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111] with root cause

org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111
	at org.hibernate.dialect.TypeNames.get(TypeNames.java:71) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.dialect.TypeNames.get(TypeNames.java:103) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:683) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.loader.custom.JdbcResultMetadata.getHibernateType(JdbcResultMetadata.java:77) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.loader.custom.ScalarResultColumnProcessor.performDiscovery(ScalarResultColumnProcessor.java:45) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:494) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.loader.Loader.processResultSet(Loader.java:2213) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.loader.Loader.getResultSet(Loader.java:2169) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1930) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1892) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.loader.Loader.doQuery(Loader.java:937) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:340) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.loader.Loader.doList(Loader.java:2689) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.loader.Loader.doList(Loader.java:2672) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2506) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.loader.Loader.list(Loader.java:2501) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2223) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1053) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:170) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1505) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at org.hibernate.query.Query.getResultList(Query.java:135) ~[hibernate-core-5.3.4.Final-atlassian-3.jar:5.3.4.Final]
	at com.amex.test.driver.service.TestServiceImpl.findAllByEpicId(TestServiceImpl.java:44) ~[classes/:na]
	at com.amex.test.driver.controller.TestController.testController3(TestController.java:36) ~[classes/:na]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_121]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_121]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_121]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_121]
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:877) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:783) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:974) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:877) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:661) ~[tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:851) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) ~[tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) ~[tomcat-embed-websocket-8.5.31.jar:8.5.31]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:109) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) ~[tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:496) [tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) [tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) [tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) [tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) [tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803) [tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790) [tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1468) [tomcat-embed-core-8.5.31.jar:8.5.31]
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-8.5.31.jar:8.5.31]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_121]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_121]
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-8.5.31.jar:8.5.31]
	at java.lang.Thread.run(Thread.java:745) [na:1.8.0_121]

JSON Data:

"{"svp_name": "asd", "record_id": 456, "director_name": "asdas"}"

Postgre Query working fine:

SELECT rptmetricstx -> 'svp_name' as text FROM burmetrics where rpttypecd = 'onboarding'

Code:

List result = em.createNativeQuery("SELECT rptmetricstx -> 'svp_name' as text FROM \n"
				+ "       \t\tburmetrics where rpttypecd = 'onboarding'").getResultList();

Tried using @Query annotation in spring JPA also but getting same error:

@Query(value = "SELECT rptmetricstx -> 'svp_name' as text FROM \n" +
          "       \t\tburmetrics where rpttypecd = 'onboarding'", nativeQuery = true)
    public ArrayList<String> findEpicId();
2

Hibernate does not support JSON natively, so you need to use something like the hibernate-types if you want to persist and fetch JSON properties using Hibernate.

In your example, the problem is that you need to map the Types.OTHER JDBC type to JsonNodeBinaryType Hibernate Type offered by the hibernate-types which can be done as follows.

At the Dialect level

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

And provide the new Dialect to Hibernate:

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

At the Query level

By using the addScalar Hibernate-specific method:

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

For more details, check out this article.

3

Hii,

I am unable to fix this issue in Spring Boot.

I am getting this log:
2019-03-22 13:54:50.205 DEBUG 12856 — [nio-8084-exec-1] org.hibernate.SQL : SELECT * FROM test_json_xml WHERE data @> ‘{“email”:“dhiraj”}’
2019-03-22 13:54:50.216 ERROR 12856 — [nio-8084-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: No Dialect mapping for JDBC type: 1111; nested exception is org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111] with root cause

org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111
at org.hibernate.dialect.TypeNames.get(TypeNames.java:71) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.dialect.TypeNames.get(TypeNames.java:103) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:683) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
at org.hibernate.loader.custom.JdbcResultMetadata.getHibernateType(JdbcResultMetadata.java:77) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]