0

Dear Openstack 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

masber
  • 2,875
  • 7
  • 29
  • 49
  • Possible duplicate of [No Dialect mapping for JDBC type: 1111](https://stackoverflow.com/questions/28192547/no-dialect-mapping-for-jdbc-type-1111) – Rann Lifshitz Apr 22 '19 at 12:28
  • Hi @RannLifshitz, thank you for your comment, unfortunately my issue is different than the one in the solution you recommended – masber Apr 22 '19 at 12:45
  • What data types are for pi.returnStartDate and pi.returnEndDate in database? You may need to cast them to DATETIME explicitly in the query. – PeterHe Apr 22 '19 at 13:07
  • @PeterHe they are DATE type, how should I do the casting? – masber Apr 22 '19 at 13:08
  • `1111` is SQL type `OTHER`, which means the field is not being returned as a date, or it would have been recognized. The problem may be in your JDBC driver. – coladict Apr 22 '19 at 14:42
  • And, sorry if I'm missing something, but can't you just use one select with `er.trackingNumber = :scanValue OR er.rma = :scanValue OR er.invoice = :scanValue` instead? It seems you're overcomplicaing your query by a lot. – coladict Apr 22 '19 at 14:44
  • @ManuelSopenaBallesteros Not sure which RDBMS you are using. If you are using Mysql, cast(pi.returnStartDate AS DATETIME) – PeterHe Apr 22 '19 at 18:08
  • @coladict the reason why I am using UNION ALL ... LIMIT 1 is because I only need the results from 1 of the queries only and not all of them. So if a record with er.trackingNumber exists, then I need that record, otherwise I am telling to the database to go and check if it can find records with er.rma and so on – masber Apr 22 '19 at 23:52
  • You could use an `order by` for that. Especially if your database supports the `nulls first/last` syntax. – coladict Apr 23 '19 at 06:17
  • @coladict not sure how would work. Would you mind giving a simple example? – masber Apr 23 '19 at 07:27
  • `er.trackingNumber = :scanValue OR er.rma = :scanValue OR er.invoice = :scanValue ORDER BY trackingNumber NULLS LAST, rma NULLS LAST, invoice NULLS LAST LIMIT 1` that is if it supports `nulls last`. You haven't mentioned what your database is. – coladict Apr 23 '19 at 07:58
  • @coladict sorry, I am using mysql 5.7 – masber Apr 23 '19 at 08:00
  • https://dev.mysql.com/doc/refman/5.7/en/working-with-null.html `When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC`. This should help make your query simpler. – coladict Apr 23 '19 at 08:09

0 Answers0