Russell Beattie

Not that I'm particularly happy with the Java community at the moment, but here's something else I noticed the other day while perusing the PreparedStatement code in MySQL's Connector/J. The setObject() method does the right thing and determines the type of object being passed to it including nulls before setting the database. A snippet:

    public void setObject(int parameterIndex, Object parameterObj)
        throws SQLException {
        if (parameterObj == null) {
            setNull(parameterIndex, java.sql.Types.OTHER);
        } else {
            if (parameterObj instanceof Byte) {
                setInt(parameterIndex, ((Byte) parameterObj).intValue());
            } else if (parameterObj instanceof String) {
                setString(parameterIndex, (String) parameterObj);
...
            } else if (parameterObj instanceof java.sql.Clob) {
                setClob(parameterIndex, (java.sql.Clob) parameterObj);
            } else if (parameterObj instanceof java.util.Date) {
                setTimestamp(parameterIndex,
                    new Timestamp(((java.util.Date) parameterObj).getTime()));
            } else {
                setSerializableObject(parameterIndex, parameterObj);
            }
        }
    }

For sure, there is going to be a performance hit as it iterates through the comparisions, but what this allows is for a nice little convenience method for dynamically creating paramaterized Updates or Inserts like this:

public long doSQL(String sql, List params)
        throws SQLException
{

        Connection conn = DbPool.getConnection();
        PreparedStatement pstmt = conn.prepareStatement(sql);

        Iterator it = params.iterator();

        int count = 0;

        while(it.hasNext()){
                count++;
                pstmt.setObject(count, it.next());

        }

        log.debug("PreparedStatement: " + pstmt.toString());

        pstmt.execute();

        id = ((com.mysql.jdbc.PreparedStatement) pstmt).getLastInsertID();

        pstmt.close();
        DbPool.closeConnection(conn);

        return id;

}

Now if I'm cranking through my app and refactoring SQL statements and params all the time, I can just add a ? and add another item to the ArrayList, without having to count question marks, etc.

Pretty damn nifty.

-Russ