Use native SQL data types when assigning Date/Timstamp parameters

[Thomas Hallgren]

The PostgreSQL driver does not accept String assignements to a
parameter
that is of date or timestamp type. This patch ensures that the correct
java.sql.Date and java.sql.Timestamp types are used.

--------------- src/hirondelle/web4j/database/SqlStatement.java
---------------
diff --git a/src/hirondelle/web4j/database/SqlStatement.java b/src/
hirondelle/web4j/database/SqlStatement.java
index 79258be..1d86bb3 100644
--- a/src/hirondelle/web4j/database/SqlStatement.java
+++ b/src/hirondelle/web4j/database/SqlStatement.java
@@ -13,10 +13,13 @@

import java.math.BigDecimal;
import java.sql.Connection;
+import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
+import java.sql.Time;
+import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
@@ -299,38 +302,41 @@

private void setDateTime(Object aParam, PreparedStatement
aStatement, int aIdx) throws SQLException {
DateTime dateTime = (DateTime)aParam;
- String formattedDateTime = "";
+
if (
dateTime.unitsAllPresent(Unit.YEAR, Unit.MONTH, Unit.DAY) &&
dateTime.unitsAllAbsent(Unit.HOUR, Unit.MINUTE, Unit.SECOND)
){
fLogger.finest("Treating DateTime as a date (year-month-
day).");
- formattedDateTime =
dateTime.format(DbConfig.getDateFormat(fSqlId.getDatabaseName()));
+ aStatement.setDate(aIdx, new
Date(dateTime.getMilliseconds(TimeZone.getDefault())));
+ return;
}
- else if (
+
+ if (
dateTime.unitsAllAbsent(Unit.YEAR, Unit.MONTH, Unit.DAY) &&
dateTime.unitsAllPresent(Unit.HOUR, Unit.MINUTE, Unit.SECOND)
){
fLogger.finest("Treating DateTime as a time (hour-minute-
second).");
- formattedDateTime =
dateTime.format(DbConfig.getTimeFormat(fSqlId.getDatabaseName()));
+ aStatement.setTime(aIdx, new
Time(dateTime.getMilliseconds(TimeZone.getDefault())));
+ return;
}
- else if (
+
+ if (
dateTime.unitsAllPresent(Unit.YEAR, Unit.MONTH, Unit.DAY) &&
dateTime.unitsAllPresent(Unit.HOUR, Unit.MINUTE, Unit.SECOND)
) {
fLogger.finest("Treating DateTime as a date+time (year-month-
day-hour-minute-second).");
- formattedDateTime =
dateTime.format(DbConfig.getDateTimeFormat(fSqlId.getDatabaseName()));
+ aStatement.setTimestamp(aIdx, new
Timestamp(dateTime.getMilliseconds(TimeZone.getDefault())));
+ return;
}
- else {
- String message =
- "Unable to format DateTime using the
DateTimeFormatForPassingParamsToDb setting in web.xml." +
- " The units present in the DateTime object do not match any
of the expected combinations. " +
- "If needed, you can always format the DateTime manually in
your DAO, and pass a String to the database instead of a DateTime."
- ;
- fLogger.severe(message);
- throw new IllegalArgumentException(message);
- }
- aStatement.setString(aIdx, formattedDateTime);
+
+ String message =
+ "Unable to format DateTime using the
DateTimeFormatForPassingParamsToDb setting in web.xml." +
+ " The units present in the DateTime object do not match any of
the expected combinations. " +
+ "If needed, you can always format the DateTime manually in
your DAO, and pass a String to the database instead of a DateTime."
+ ;
+ fLogger.severe(message);
+ throw new IllegalArgumentException(message);
}

[John O'Hanley]

What is the name and version of the postgres driver?
Are you absolutely sure that this behavior can't be configured with a driver setting?

[John O'Hanley]

It looks like this is configurable in this instance.

[Thomas Hallgren]

Yes, you can let the server infer the type by setting the "stringtype=unspecified" in the driver. But why have the user
be concerned about property settings when there's a solution that will work on all types of jdbc drivers?

Passing dates and times as strings also adds two other concerns. You must:

a) Make sure that the string format used is what the server will expect.
b) Make sure that the timezone used when converting is the same at both ends.

Passing native types, those concerns don't apply.

[John O'Hanley]

But will it work?

DateTime doesn't have the same sematics as java.util.Date at all. It doesn't hold time zone information. So you can't map one to the other without a Time Zone.

Your posted code uses a default TimeZone for the JRE. That's not going to be appropriate all the time.

TimeZoneSource - web4j defines this to let the app define what the 'real' time zone is for a given request.

[Thomas Hallgren]

On 2012-04-06 15:51, John O'Hanley wrote:
>
> Your posted code uses a default TimeZone for the JRE. That's not going
> to be appropriate all the time.

Agree. The patch could be improved to use the configured TZ.