Category Archives: hibernate

[Example] Hibernate One-To-Many bidirectional relation

Let us consider the relation between Semester and Subjects. A Semester has a collection of Subjects. Each Subject, in turn, has a reference to the Semester.

This is illustrated as below:

This is how I convert it to Hibernate entities:

Further, I should be able to insert it with the following code:

The SQL script is:

The sources can be found https://github.com/paawak/blog/tree/master/code/OneToManyExample.

[How To] Handle Oracle TimeStamp with TimeZone from Java

Oracle TimeStamp with TimeZone: A Critical Analysis

For storing the TimeZone information in TimeStamp, Oracle (10g) has two options:

  1. TIMESTAMP WITH TIME ZONE data type:  As the name indicates, this stores the TimeZone data along with the TimeStamp. The TimeZone can be either stored in the UTC format (eaxmple: +05:30) or the TimeZoneRegion format (example: Asia/Calcutta or Indian Standard Time).
  2. TIMESTAMP WITH LOCAL TIME ZONE data type: This does not store the TimeZone information per se. While inserting data, it adjusts for the offset of the client’s TimeZone and stores it according to the db server’s local TimeZone, without the actual TimeZone information. Similarly, while querying, the TimeStamp will be offseted against the client’s TimeZone, without returning any explicit TimeZone information. This data type is very confusing and is to be avoided at all costs.

As far as JDBC is concerned, things are plain and simple when we talk about Statement. The trouble starts the moment we think about PreparedStatement. Both these data types map to the java.sql.Timestamp in JDBC. But java.sql.Timestamp does not have any TimeZone information. In this scenario, we should be using the PreparedStatement.setTimestamp(int parameterIndex, Timestamp x, Calendar cal) to pass the TimeZone information to the db driver. There is another twist to this tale. The TIMESTAMP WITH LOCAL TIME ZONE data type needs to adjust the time against the client’s TimeZone. When we open a Oracle client session, the TimeZone is already set. But that is not the case with JDBC Connection. The TimeZone info is not set by the driver, for reasons best known to themselves. So a simple ResultSet.getTimestamp(int columnIndex) will not work. Instead, we have to use the ResultSet.getTimestamp(int columnIndex, Calendar cal) to pass the TimeZone to the db driver.

Simple JDBC example

Consider the following table having both types of TimeStamp with TimeZone:

This is the SQL statement for inserting data:

INSERT INTO TIMESTAMP_DEMO (ID, NAME, TIME_WITH_ZONE, TIME_WITH_ZONE_LOCAL) VALUES (0, ‘manualInsert’, TO_TIMESTAMP_TZ(‘2010-09-26 11:30:00 Australia/Adelaide’,’YYYY-MM-DD HH24:MI:SS TZR’), TO_TIMESTAMP_TZ(‘2010-09-26 11:30:00 Australia/Adelaide’,’YYYY-MM-DD HH24:MI:SS TZR’));

We will try to insert data from plain JDBC and then read it back to ensure that the information persisted is correct.

INSERT Approach 1

Consider the following code snippet:

Now run the following query from any Oracle SQL Client:

SELECT TO_CHAR(TIME_WITH_ZONE, ‘YYYY-MM-DD HH24:MI:SS:FF TZR’) AS TIME_WITH_ZONE FROM TIMESTAMP_DEMO;

You will find that the returned value is:

2010-11-02 11:30:00:000000000 +05:30

Note that the TimeZone info is wrongly stored. The only conclusion that we can have is that the JDBC driver is buggy. How do we get around this problem?

INSERT Approach 2

On running the select statement, the data got back is:

2010-11-02 11:30:00:000000000 AUSTRALIA/ADELAIDE

This is precisely the data which was inserted. The trcik here is to bypass the JDBC data and use Oracle function TO_TIMESTAMP_TZ(timeString, format).

SELECT Approach 1

On running the above, we get the following exception:

java.sql.SQLException: The database session time zone is not set
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)

This is got on the line

res.getTimestamp(“TIME_WITH_ZONE_LOCAL”);

as the TimeZone is not set in the JDBC Connection object.

SELECT Approach 2

And this is how getTimeWithZone() looks like:

We get the following sysouts:

insert_1: 1, TIME=11:30:00:000 India Standard Time, TIME_LOCAL=06:00:00:000 India Standard Time
insert_2: 2, TIME=06:30:00:000 India Standard Time, TIME_LOCAL=06:00:00:000 India Standard Time

Note that in both cases, the returned TimeZone is wrongly fetched. Again, the JDBC driver is buggy.

SELECT Approach 3

As with INSERT, we will try to bypass the JDBC driver as follows:

The following sysouts are got:

insert_1: 1, TIME=11:30:00:000000000 +05:30, TIME_LOCAL=11:30:00:000000000 +05:30
insert_2: 2, TIME=11:30:00:000000000 AUSTRALIA/ADELAIDE, TIME_LOCAL=11:30:00:000000000 +05:30

Note that the values returned are exactly as intended.

Hibernate Example

Let us try and translate the above code into Hibernate. The first inference that we can draw from the above discussion is that get() on TIMESTAMP WITH LOCAL TIME ZONE will not work with the usual approach, and we have to define our custom data type to customise get() on PreparedStatement.

Hibernate Approach 1

This is how my custom data type (to map to TIMESTAMP WITH LOCAL TIME ZONE) looks like:

And this is how my entity looks like:

As expected, this will not save/get correct TimeZone data as it depends on the buggy JDBC driver.

Hibernate Approach 2

Here, as before, we use the TO_TIMESTAMP_TZ() function to circumvent the buggy JDBC driver. We keep the TimestampType for mapping to TIMESTAMP WITH LOCAL TIME ZONE and add TimestampType2 to map to TIMESTAMP WITH TIME ZONE.

And this is how my modified entity looks like:

This set up gives us the desirable save/get results with the correct TimeZone value.

Conclusion

I take this opportunity to illustrate the advantage of Open Source Software as opposed to proprietary ones. If the ojdbc sources were in public domain, we could have patched the bug instead of having to circumvent them as in the present instance.

Resources

The sources can be found https://github.com/paawak/blog/tree/master/code/OracleTimeZoneTest.

There are three test cases which illustrates the above discussion. I have used the ojdbc driver 5 for this example. Since I am using Maven for building this, the libraries are not a part of this distribution. You can grab any Oracle JDBC driver from here.

Using custom data type in Hibernate

Why need custom data type?

I have a java.util.Calendar object which I want to persist in the db, along with the TimeZone. Suppose I am working with a db which does not have support for storing TimeZone. How do I proceed? One of the easiest solutions would be to store the entire timestamp as a VARCHAR. Granted, but how do I instruct Hibernate to use a java.util.Calendar object instead of a String? Its done by annotating with @Type(type=””), where type should be the fully qualified name of a class implementing the org.hibernate.usertype.UserType interface.

Custom UserType

Hibenate Entity

Resources

The sources can be found https://github.com/paawak/blog/tree/master/code/HibernateCustomDataType

Id generation in Hibernate with Sequence

I have the following SQL Script:

The Hibernate entity for this would be:

Error in named query: org.hibernate.hql.ast.QuerySyntaxException: table is not mapped

I was taking advantage of the NamedQueries feature in Hibernate annotation, when suddenly I was bombed with this queer error:

ERROR – SessionFactoryImpl.<init>(363) | Error in named query: findMaxId
org.hibernate.hql.ast.QuerySyntaxException: AccountGroup is not mapped [SELECT MAX(id) FROM AccountGroup]

My AccountGroup looks like this:

As is evident from the error message, the HQL compiler is not able to map any table to “AccountGroup”. But this is strange, since I have already mapped it in my hibernate.cfg.xml. After a few minutes of futile googling, the solution dawned upon me.

Solution 1:

Include an additional annotation @Table:

Solution 2:

Modify your query to have the fully qualified class name:

Configuring connection pooling with Teneo

Connection pooling is one thing that needs to be done on a production server. Spring is becoming an extremely popular choice with Hibernate for its seamless integartion. But with Teneo, its a different ball-game altogether. Fortunately for us, Hibernate provides one such hook via the property in the Environment.CONNECTION_PROVIDER configuration. It takes in the name of a class implementing the interface ConnectionProvider.

I prefer the Apache Commons DBCP. You will find the example here. When configuring Teneo, you need to set:

MyConnectionProvider.java looks like: