Avoiding CheckStyle magic number errors in JDBC queries

Question

I am working on a group project for class and we are trying out CheckStyle.

I am fairly comfortable with Java but have never touched JDBC or done any database work before this.

I was wondering if there is an elegant way to avoid magic number errors in preparedStatement calls, consider:

        preparedStatement = connect.prepareStatement("INSERT INTO shows "
                + "(showid, showtitle, showinfo, genre, youtube)"
                + "values (default, ?, ?, ?, ?);");
        preparedStatement.setString(1, title);
        preparedStatement.setString(2, info);
        preparedStatement.setString(3, genre);
        preparedStatement.setString(4, youtube);
        result = preparedStatement.executeUpdate();

The setString methods get flagged as magic numbers, so far I just added the numbers 3-10 or so to the ignore list for magic numbers but I was wondering if there was a better way to go about inserting those values into the statement. I also beg you for any other advice that comes to mind seeing that code, I'd like to avoid developing any nasty habits, e.g. should I be using Statement or is PreparedStatement fine? Will that let me refer to column names instead? Is that ideal? etc...

Thanks!

Answer 1

Create an utility method which does something like this:

public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException {
    for (int i = 0; i < values.length; i++) {
        preparedStatement.setObject(i + 1, values[i]);
    }
}

And use it as follows:

setValues(preparedStatement, title, info, genre, youtube);

or

Object[] values = {
    title, info, genre, youtube
};

setValues(preparedStatement, values);

More "best practices" with regard to basic JDBC coding can be found in this article.

Hope this helps.

Answer 2

I would suggest that even if you don't use Spring, try using the NamedParameterJdbcTemplate instead. Take a look at http://www.dzone.com/tutorials/java/spring/spring-named-parameter-jdbc-template.html for a tutorial on how to use it.