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

Hibernate ORM
1

Dear Hibernate community,

I have a native query like the one below:

		List<Tuple> tuples = em.createNativeQuery("(SELECT"
				+ " er.id er_id, er.globalId glboalId, er.rma rma, er.trackingNumber trackingNumber, er.returnMethod returnMethod, er.invoice invoice, er.carrier_shipment_id carrier_shipment_id, er.carrier_label_request_id carrier_label_request_id, er.notes er_notes, er.pickupDateTime er_pickupDateTime"
				+ ", ca.id carrier_id, ca.firstName carrier_fn, ca.email carrier_email"
				+ ", sh.id shipper_id, sh.firstName shipper_fn, sh.email shipper_email"
				+ ", co.id consignee_id, co.firstName consignee_fn, co.email consignee_email"
				+ ", dest.id destination_id, dest.firstName destination_fn, dest.email destinaton_email"
				+ ", pi.id pi_id, pi.price price, pi.scanDateTime scanDateTime, pi.returnAction returnAction, pi.globalCondition globalCondition, pi.reasonToReturn reasonToReturn, pi.rule rule, pi.refundAction refundAction, pi.newSku newSku, pi.wrongSku wrongSku, pi.returnStartDate returnStartDate, pi.returnEndDate returnEndDate, pi.weight weight, pi.notes pi_notes"
				+ " FROM ereturn er"
				+ " JOIN product_item pi ON pi.ereturn = er.id"
				+ " JOIN user sh ON er.shipper = sh.id"
				+ " JOIN user ca ON er.carrier = ca.id"
				+ " JOIN user co ON er.consignee = co.id"
				+ " JOIN user dest ON er.destination = dest.id"
				+ " WHERE"
				+ " er.trackingNumber = :scanValue)"
				+ " UNION ALL"
				+ " (SELECT"
				+ " er.id er_id, er.globalId glboalId, er.rma rma, er.trackingNumber trackingNumber, er.returnMethod returnMethod, er.invoice invoice, er.carrier_shipment_id carrier_shipment_id, er.carrier_label_request_id carrier_label_request_id, er.notes er_notes, er.pickupDateTime er_pickupDateTime"
				+ ", ca.id carrier_id, ca.firstName carrier_fn, ca.email carrier_email"
				+ ", sh.id shipper_id, sh.firstName shipper_fn, sh.email shipper_email"
				+ ", co.id consignee_id, co.firstName consignee_fn, co.email consignee_email"
				+ ", dest.id destination_id, dest.firstName destination_fn, dest.email destinaton_email"
				+ ", pi.id pi_id, pi.price price, pi.scanDateTime scanDateTime, pi.returnAction returnAction, pi.globalCondition globalCondition, pi.reasonToReturn reasonToReturn, pi.rule rule, pi.refundAction refundAction, pi.newSku newSku, pi.wrongSku wrongSku, pi.returnStartDate returnStartDate, pi.returnEndDate returnEndDate, pi.weight weight, pi.notes pi_notes"
				+ " FROM ereturn er"
				+ " JOIN product_item pi ON pi.ereturn = er.id"
				+ " JOIN user sh ON er.shipper = sh.id"
				+ " JOIN user ca ON er.carrier = ca.id"
				+ " JOIN user co ON er.consignee = co.id"
				+ " JOIN user dest ON er.destination = dest.id"
				+ " WHERE"
				+ " er.rma = :scanValue)"
				+ " UNION ALL"
				+ " (SELECT"
				+ " er.id er_id, er.globalId glboalId, er.rma rma, er.trackingNumber trackingNumber, er.returnMethod returnMethod, er.invoice invoice, er.carrier_shipment_id carrier_shipment_id, er.carrier_label_request_id carrier_label_request_id, er.notes er_notes, er.pickupDateTime er_pickupDateTime"
				+ ", ca.id carrier_id, ca.firstName carrier_fn, ca.email carrier_email"
				+ ", sh.id shipper_id, sh.firstName shipper_fn, sh.email shipper_email"
				+ ", co.id consignee_id, co.firstName consignee_fn, co.email consignee_email"
				+ ", dest.id destination_id, dest.firstName destination_fn, dest.email destinaton_email"
				+ ", pi.id pi_id, pi.price price, pi.scanDateTime scanDateTime, pi.returnAction returnAction, pi.globalCondition globalCondition, pi.reasonToReturn reasonToReturn, pi.rule rule, pi.refundAction refundAction, pi.newSku newSku, pi.wrongSku wrongSku, pi.returnStartDate returnStartDate, pi.returnEndDate returnEndDate, pi.weight weight, pi.notes pi_notes"
				+ " FROM ereturn er"
				+ " JOIN product_item pi ON pi.ereturn = er.id"
				+ " JOIN user sh ON er.shipper = sh.id"
				+ " JOIN user ca ON er.carrier = ca.id"
				+ " JOIN user co ON er.consignee = co.id"
				+ " JOIN user dest ON er.destination = dest.id"
				+ " WHERE"
				+ " er.invoice = :scanValue)", Tuple.class)
				.setParameter("scanValue", scanValue)
				.getResultList();

While running this query I am getting the following error:

SEVERE: Servlet.service() for servlet [jersey-servlet] in context with path [/returnitRest] threw exception [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:70)
at org.hibernate.dialect.TypeNames.get(TypeNames.java:101)
at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:683)
at org.hibernate.loader.custom.JdbcResultMetadata.getHibernateType(JdbcResultMetadata.java:77)
at org.hibernate.loader.custom.ScalarResultColumnProcessor.performDiscovery(ScalarResultColumnProcessor.java:45)
at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:482)
at org.hibernate.loader.Loader.processResultSet(Loader.java:2214)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2170)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1931)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893)
at org.hibernate.loader.Loader.doQuery(Loader.java:938)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
at org.hibernate.loader.Loader.doList(Loader.java:2692)
at org.hibernate.loader.Loader.doList(Loader.java:2675)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507)
at org.hibernate.loader.Loader.list(Loader.java:2502)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2200)
at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1016)
at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:152)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414)
at org.hibernate.query.Query.getResultList(Query.java:146)
at returnitRest.EreturnDAO.fetchBySalesOrderOrRmaOrTrackingNumber(EreturnDAO.java:1088)
at endpoints.EreturnResource.getByGlobalId(EreturnResource.java:466)
...

If the UNION ALL and/or fields pi.returnStartDate and pi.returnEndDate are removed, then the query runs fine.

Please note I am using nativeQuery and not JPA/JPQL

Could someone please help me to understand what am I doing wrong?

thank you very much

Manuel

2

Try to implement custom dialect for this problem:https://vladmihalcea.com/hibernate-no-dialect-mapping-for-jdbc-type/

like:

package com.utp.conf;

import java.sql.Types;

import org.hibernate.dialect.PostgreSQL95Dialect;

import com.vladmihalcea.hibernate.type.json.JsonNodeBinaryType;
public class PostgreSQL95JsonDialect extends PostgreSQL95Dialect {

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

}

in application.properties file:
spring.jpa.properties.hibernate.dialect=com.utp.conf.PostgreSQL95JsonDialect