2

I am using an Oracle 11.2 database with column types "TIMESTAMP WITH TIMEZONE" to store time relevant information. I can't change it because it is used by an existing system.

The Java application uses ojdbc7 (v12.1.0.2) + hibernate as JPA provider.

I simplified the following example for easer understanding!

There is an entity "Product" which maps "TIMESTAMP WITH TIMEZONE" columns to java.util.Calendar

@Entity
@Table(name = "PRODUCT")
public class Product {

@Id
@Column(name = "ID")
private String id;

@Column(name = "START_DATE", columnDefinition = "TIMESTAMP WITH TIME ZONE")
private Calendar startDate;

....
}

If I load it through a HQL query it works fine.

1. Issue

If I use a raw sql query:

SELECT * FROM PRODUCT where id='123456';

I get the following exception:

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

The sql query is a lot more complex I just used a very simple example here. So I can not simply convert it to an HQL query.

2. Issue

If I persist a new product with a start date having a time zone like AEDT. E.g.

2014-01-01 12:00:00 AEDT

it get saved in the database as

01-JAN-14 01.00.00.000000000 UTC

If I query it again from the Java application the Calendar object has the time in UTC as shown above and not the time in AEDT. It is not just a display issue.

The conversion from AEDT to UTC is correct, but the problem is that the information is lost that it was inserted with timezone AEDT.

Is there a way to prevent this? Why is this happening anyway? I searched a lot today, but could not find a good hint.

d1rk
  • 1,956
  • 2
  • 14
  • 19
  • Not flagging this as a dup, partly because a lot may have changed in 5 years, but: http://stackoverflow.com/questions/1289492/hibernate-timestamp-with-timezone – Jon Skeet Feb 26 '15 at 17:56
  • I checked that question already, but this is different. The mapping to the jpa entity itself works fine, from DB column "TIMESTAMP WITH TIMEZONE" to a java.util.Calendar, as well as the other way round. – d1rk Feb 26 '15 at 18:05
  • Well it doesn't sound like all is well with mappings, given the MappingException you've received... – Jon Skeet Feb 26 '15 at 18:09
  • This happens if I use a raw sql query without direct mapping to a jpa entity. The return type is simply List. – d1rk Feb 26 '15 at 18:10
  • My point is that if you try to fix that part - for example using one of the aproaches listed in that answer - then it may solve the rest too. Have you tried using a custom Hibernate type? – Jon Skeet Feb 26 '15 at 18:14

1 Answers1

0

You can solve the first issue the following way:

First, you need to unwrap the Query implementation. You said that you were using JPA + Hibernate, so:

Query jpaQuery = entityManager.createNativeQuery("select, t.int_c, t.str_c, t.timestamp_c from table t");
SQLQuery hibernateQuery = jpaQuery.unwrap(SQLQuery.class);

(org.hibernate.SQLQuery.class is the wrapped class)

Second, you have to set the type for ALL the columns that you want to retrieve:

hibernateQuery.addScalar("int_c", IntegerType.INSTANCE);
hibernateQuery.addScalar("str_c", StringType.INSTANCE);
hibernateQuery.addScalar("timestamp_c", TimestampType.INSTANCE);

Third, just make the query using the original JPA Query instance

List<Object[]> results = jpaQuery.getResultList();
Sergio
  • 179
  • 1
  • 7