Thursday, February 14, 2013

Updating a date column in Oracle using JDBC

Here is something bizarre that I discovered today:   When you need to update an Oracle date type field in a JDBC result set, you must feed it a java.sql.Date object and not a java.util.Date object.

I couldn't figure out why my code wouldn't compile, the compiler said it was expecting a String and a Date, which is what I was giving it.  A google search revealed that it is expecting a different Date object   Unlike the java.util.Date, the java.sql.Date only contains the Year/Month/Day.

So, after figuring all this out, I got eclipse to compile and then went and talked to the DBA, who then says, ohh, I should of told you that you don't need to send the date, I will create it when you sent the UNID.   This blog post is the result of 90 minutes spent doing something that I didn't need to do, but I certainly learned something from it.  Hopefully someone down the road can benefit from this.

Here is the code:

How to construct a java.sql.Date.  Note: that you need to be specific which Date you are constructing.
Calendar cal = Calendar.getInstance();
java.sql.Date today = java.sql.Date.valueOf(cal.get(cal.YEAR) + ":" + cal.get(cal.MONTH) + ":" + cal.get(cal.DATE));

Using the java.sql.Date object:  (This compiles!)
rs.updateDate("ORACLE_DATE_COLUMN", today);

I guess if you needed the date and time, you would have to write to two columns using updateTime().

No comments:

Post a Comment