MySQL JDBC Tip Of The Day #2

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) {
                    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;



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


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


        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.


< Previous         Next >