Friday, February 8, 2008

Fun with JDBC CallableStatements

I spent the better part of a day debugging an issue involving stored procedure calls over Spring JDBC, and came across some interesting gotchas which I felt might help others save some time in the future, so I thought I'd write a quick article about it.

Basically, I was given a defect where decimal values from the database were being rounded somewhere in my service method, and was able to isolate it to within my StoredProcedureImpl class. In Spring JDBC, a common approach to calling a stored procedure is to extend StoredProcedure, wherein you declare the input and output parameters and their corresponding types:


private class MyStoredProcedure extends StoredProcedure {
private static final String SQL = "sysdate";

public MyStoredProcedure(DataSource ds) {
setDataSource(ds);
setFunction(true);
setSql(SQL);
declareParameter(new SqlOutParameter("date", Types.DATE));
compile();
}

public Map execute() {
return execute(new HashMap());
}
}


This works well, and is indeed an elegant way of calling a stored procedure, as it eliminates all of the JDBC nastiness (try / catch / finally (try / finally / etc / etc) / etc). The problem lies in the (obscure?) fact that when you specify DECIMAL or NUMERIC types for input or output parameters in the JDBC API, you are expected to also specify a scale, or it will default to 0 (and either truncate or round, depending on your database driver).

From the Java API for CallableStatement:


void registerOutParameter(int parameterIndex,
int sqlType,
int scale)
throws SQLException

Registers the parameter in ordinal position parameterIndex
to be of JDBC type sqlType. All OUT parameters must be
registered before a stored procedure is executed.

The JDBC type specified by sqlType for an OUT parameter
determines the Java type that must be used in the get method
to read the value of that parameter.

This version of registerOutParameter should be used
when the parameter is of JDBC type NUMERIC or DECIMAL.


Parameters:
parameterIndex - the first parameter is 1, the second
is 2, and so on
sqlType - the SQL type code defined by java.sql.Types.
scale - the desired number of digits to the right
of the decimal point. It must be greater than or
equal to zero.



As it turns out, Spring JDBC's SqlParameter class didn't support setting scale on them until 2.0.5 (and here), and SqlOutParameter didn't have this in a final release until 2.5.

So, to make a long story short (too late, I know), if you are calling stored procedures in JDBC which use DECIMAL or NUMERIC parameters in either the input or output, you must specify a scale to use, and if you are currently running a non-current version of Spring (less than 2.5.1), this is a great reason to upgrade.

P.S. When researching this issue, I noticed a new set of classes in the Spring JDBC set (SimpleJdbc) which looked cool; they may warrant an article later on...

Monday, February 4, 2008

Be wary of SimpleDateFormat

Java's SimpleDateFormat class provides a convenient method of parsing arbitrary strings into Date objects, and formatting Dates back into strings. However, like the rest of the standard Date/Time classes in the core API, there are a couple of important things you need to be aware of when working with this class. Ignore these caveats and there is a good chance you will be spending an inordinate amount of time debugging obscure issues with dates!

Can you guess the output of the following program?

import java.text.SimpleDateFormat;
import java.util.Date;

public class DateTest {

public static void main(String[] args) throws Exception {
String fmt =
"yyyyMMdd";

String testDate = "20080530";

Date dt = (new SimpleDateFormat(fmt)).parse(testDate);

System.out.println(dt.toString());

testDate = "2008-05-30";

dt = (new SimpleDateFormat(fmt)).parse(testDate);
System.
out.println(dt.toString());
}
}

Believe it or not, it is:

Fri May 30 00:00:00 EDT 2008
Wed Dec 05 00:00:00 EST 2007

SimpleDateFormat will not throw an error if it receives string input which does not conform to its specified format string; it will instead silently construct an incorrect date! One possible solution (although I have seen cases where this also will not work) is:

DateFormat df = new SimpleDateFormat(fmt);
df.setLenient(false);
System.
out.println(df.parse(testDate));

Resulting in:

Exception in thread "main" java.text.ParseException: Unparseable date: "2008-05-30"
at java.text.DateFormat.parse(Unknown Source)
at sandbox.DateTest.main(
DateTest.java:23)

This should serve as a good example as any of the importance of good unit tests!

Lastly, it is also important to know that SimpleDateFormat is not thread-safe! This means that you must not use it as a member variable of a multithreaded service class, for example (Servlet, MessageDrivenBean, etc).

Since it can be expensive to keep instantiating a new SimpleDateFormat on each service request, a good practice is to store an instance in a ThreadLocal variable:


private ThreadLocal<DateFormat> myDateFormat = new ThreadLocal<DateFormat>(){
@Override protected DateFormat initialValue() {
return new SimpleDateFormat("yyyyMMdd");
}
};