Jpa call store procedure, fail to convert timestamps within ref_cursor

Question

At the moment, our project using JPA to call a stored procedure like this:

  StoredProcedureQuery query = em.createStoredProcedureQuery("Package.Procedure")
            .registerStoredProcedureParameter(1, Long.class, ParameterMode.IN)
            .registerStoredProcedureParameter(2, String.class, ParameterMode.IN)
            .registerStoredProcedureParameter(3, String.class, ParameterMode.IN)
            .registerStoredProcedureParameter(4, Long.class, ParameterMode.IN)
            .registerStoredProcedureParameter(5, String.class, ParameterMode.IN)
            .registerStoredProcedureParameter(6, String.class, ParameterMode.IN)
            .registerStoredProcedureParameter(7, String.class, ParameterMode.IN)
            .registerStoredProcedureParameter(8, String.class, ParameterMode.IN)
            .registerStoredProcedureParameter(9, Long.class, ParameterMode.IN)
            .registerStoredProcedureParameter(10, Class.class, ParameterMode.REF_CURSOR)
            .setParameter(1, null)
            .setParameter(2, null)
            .setParameter(3, null)
            .setParameter(4, null)
            .setParameter(5, null)
            .setParameter(6, null)
            .setParameter(7, null)
            .setParameter(8, null)
            .setParameter(9, null);

    List<Object[]> result = query.getResultList();

The output is a table which have 2 timestamp column.

When i try to run this code, we got the exception :

Caused by: org.hibernate.MappingException: No Dialect mapping for JDBC type: -101

As i search around, -101 mean that the result contain timestamps and fail to convert it i think. We try to remove those 2 timestamps column from procedure and everything work.

I try to search for a solution and most answer tell me to cast the value before return or to "addScalar". But the one who create these procedure doesn't want to update it and ask us to find a solution for it.

So can anywant tell me what should i do in this case?

Search for Timestamp to Date conversions. if I understand you correctly you may find this link helpful: [link] (https://stackoverflow.com/questions/11839246/how-to-convert-timestamp-to-date-in-java) — Talgat, Jun 27 '18 at 11:52
@Talgat uhm not really, the problem is that the type -101 ( this is a timestamp somehow ) was not defined and map to any class. So i have to add my own mapping — mameo, Jun 28 '18 at 03:14

score 1 · Accepted Answer · answered Jun 28 '18 at 03:08

After more research, i found this page

I have to create my own customize Oracle dialect and register type -101 for it.

public class MyOracleDialect extends Oracle10gDialect {

    public MyOracleDialect() {
        super();
        registerHibernateType(-101, DateType.INSTANCE.getName());
    }
}

Since -101 type was not found in Types class, i have to use a fix number here.

After that, i change my "hibernate.dialect" config to this class.

Everything working now.

score 0 · Answer 2 · answered Jun 27 '18 at 13:39

0

if I understand you correctly you have error in converting timestamp. all of java Dates such as java.util.Date or java.sql.Date work with a long number and you can get this long number from your timestamp and use it to convert to your java Date

answered Jun 27 '18 at 13:39

Mahdiyar Zerehpoush

356
5
18

After some more research, this is the problem with the library default mapping class. I have to customize the mapping between db type and java type to make it work ( i answered my own question ) – mameo Jun 28 '18 at 03:16

Jpa call store procedure, fail to convert timestamps within ref_cursor

2 Answers2