www.rolfje.com

People who have read my last post are now probably removing the Date classes from their beans, replacing the code with things as new Long(new Date.getTime()) and such. This is also what I started doing, until a collegue of mine pointed out that the TypeHandler I wrote last saturday could also do the trick. This would mean my beans could still contain clean Dates. I was so close, and failed to realize that I (or ibatis) had that power.

So I set out to do this and it works wonderfully. What I did was the following. I changes all postgres timestamp fields to bigints, like you read yesterday. I left my beans unchanged, containing java.util.Date objects for representing dates. So now all we need to do is write a typehandler which converts the date to a long and back, so Postgress and the buggy JDBC driver don’t realize it’s actually a Date.

To do this I wrote the following TypeHandler:

public class DateTypeHandler
implements TypeHandlerCallback {

public void setParameter(ParameterSetter setter,
Object parameter)
throws SQLException {
if (parameter == null) {
setter.setNull(Types.BIGINT);
} else {
Date datetime = (Date) parameter;
setter.setLong(datetime.getTime());
}
}

public Object getResult(ResultGetter getter)
throws SQLException {
long millis = getter.getLong();
if (getter.wasNull()) {
return null;
}
return new Date(millis);
}

public Object valueOf(String s) {
return s;
}
}

Now, all we need to do is tell ibatis to use this typehandler whenever we try to insert or retreive a java.util.Date. We do this by adding the following line to the ibatis SqlMapConfig.xml file, just after the typealias definitions:

<typeHandler javaType="java.util.Date"
callback="com.bestlaps.database.typehandlers.DateTypeHandler"/>

After doing this, you can cary on doing things as normal. Beans can contain java.util.Date, and ibatis happily inserts and retreives them in postgres bigint columns without any timezone conversion. Please note that timezones are not stored at all here, so when you retreive a Date from the database, the java.util.Date object will have your local JVM’s devault timesone offset. You can happily ignore this, especially if you’re not going to do any calculations with it.

The beauty of this solution is, that when there is a solution for the silly timezone conversion problem/bug, we can simply convert all database fields to timestamps, and remove the typehandler reference from the SqlMapConfig.xml file, and you’re all set without changing a single line of code.

Thanks Joris!

I am using Ibatis and PostgreSQL for this great project, www.bestlaps.com. However, we’ve recently ran into a strange problem where we store a java.util.Date into a postgress TIMEZONE column, which works fine. However, when you want to retreive it, Ibatis returns a “StringIndexOutofBoundsException” at position 23. For some reason, the result returned by Postgres can not be converted back into a java.util.Date.

I’ve tried really hard to circumvent this problem with custom setter methods (parsing the results myself), and using jodatime (a great java date and time replacement library). The fix is as simple as it is strange: Make Ibatis convert the timestamp into a java.sql.Date, without changing your code.

So: You have your normal bean, in which you use java.util.Date. Then, make your Ibatis sqlMap as usual, but for the timestamp column you put the following in the resultmap: javaType=”java.sql.Date”.

Strange, but it works for now. Maybe more info on this later.

UPDATE: Okay, after spending the whole weekend with this problem I discovered that the trick I just described does noet work. java.sql.Date has no time component, so when you retreive it, your java.util.Date in the bean will have a 00:00 time. So I had to search a bit further.

When querying with SquirrelSQL, I can insert and retreive a timestamp to and from Postgress without it being changed. So it seems that it is no Postgress bug. When I am using Ibatis to insert a java.util.Date into Postgress, it gets “timezone corrected”, although I specifically told Postgress to store timestamps without timezones.

The time gets corrected by 2 hours. This seems to come from the util.Date class, which seems to have a default offset of -120 minutes, although I did not set that, and did not request any timezone to be set.

I tried to make my own typeHandler for Ibatis, where I could convert the java.util.Date to a java.sql.TimeStamp, and give it directly to the PreparedStatement. But even that did not help. I am guessing the bug is in the Postgress JDBC driver, although that conflicts with the fact that SquirelSQL (which is als a Java/JDBC program) can do it.

THE FIX: After more than 16 hours (!) of research I decided I would go for the workaround. We are behind schedule allready for the www.bestlaps.com project, so I needed a quick fix. The way to go was dissapointingly simple: Convert Date’s to Longs (with getTime()) and store the Longs in the database as number. This way ibatis and postgress don’t know that it’s a time. Pay attention to the fact that you need a postgress BIGINT to store the large number of milliseconds since the epoch.

Final thoughts: It seems that people who do not know how to handle timezones have built code to handle timezones. For Java, Postgres, Ibatis, or somewhere around that neighbourhood. Oh well, we knew that Java doesn’t have a great Date system. A promising solution I found (but not used yet) is JodaTime.

I really hope the bug gets fixed soon so I can have proper dates in the database when we go live. For now, Longs work (and will probably stay in to the end of time).