This post originated from an RSS feed registered with Java Buzz
by Russell Beattie.
Original Post: MySQL JDBC Tip Of The Day #2
Feed Title: Russell Beattie Notebook
Feed URL: http://www.russellbeattie.com/notebook/rss.jsp?q=java,code,mobile
Feed Description: My online notebook with thoughts, comments, links and more.
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.
Previous Topic
