PreparedStatement setString(...) for all, even if corrsponding data type is an integer

Question

I have come across the following codes i feel it is doing the wrong thing:

(Note that this is JDK 1.4.2, therefore the list is not typed)

StringBuffer queryBuffer = new StringBuffer();
ArrayList temp = new ArrayList();

... 
queryBuffer.append("and sb.POSTCODE = ? ");
temp.add(postcode);
...

conn = ConnectionManager.getConnection();       
pstmt = conn.prepareStatement(queryBuffer.toString());

This is what i am concerned about:

for(int i=0; i<temp.size(); i++) {
    log.debug("setString("+ (i+1) + "," + (String)temp.get(i) + ")");
    pstmt.setString(i+1, (String)temp.get(i));
}

But i have noted that some of the corresponding data types (field) in the database are integer, and dates, would this be alright?

Answer 1

Consider using the PreparedStatement setObject() method instead of setString().

The PreparedStatement setObject() will attempt to convert any of the java.lang types for you if the type is unknown at compile time.

so with an updated for loop (assuming you have java 5.0) and generic null handling:

int i = 0;
for(Object value : temp) {
    if (value == null) {
        // set null parameter if value type is null and type is unknown
        pstmt.setNull(++i, Integer.MIN_VALUE); 
    } else {
        pstmt.setObject(++i, value);
    }
}

Note that setNull() can accept a type as the 2nd parameter if it is known.