CODEWITHSUNDEEP
javajdbcnullprepared-statement
Alex Stybaev
Alex Stybaev

Reputation: 4693

Setting null to PreparedStatement

The typical situation: we have some nullable column with type Integer and it might be both null or some int value. So we use the following:

private static void setIntOrNull(PreparedStatement stmt, int col, Integer i)
    throws SQLException
    {
        if (i == null)
            stmt.setNull(col, java.sql.Types.INTEGER);
        else
            stmt.setInt(col, i);
    }

But for me this case is kind of bad practise - to change the external object within internal void method (reffering to Robert Martin's "Clean Code" Chapter 17: Smells and Heuristics, Functions, F2). I try to avoid such situation, but this time I just could not find a better solution. Maybe someone could help me with one?

Upvotes: 4

Views: 932

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1499730

I don't think it's particularly bad, really. Let's see:

  • It's void, so it must have side-effects, or be useless
  • It's static, so it can't have any effect on "the object it's called" on; there isn't one!

So either the side-effect is going to be a global one (e.g. external such as a file system, internal such as static variable, or temporal e.g. sleeping) or it will affect one of the objects referred to by the parameters.

The int isn't an object, Integer objects are immutable, so the only thing it can be affecting is the PreparedStatement. As one of the purposes of prepared statements is to collect parameter data, it seems entirely reasonable that the method will do so. The method name makes this even clearer - where else is it going to "set" an int or null, if not on the prepared statement?

Basically: don't worry :)

You may want to make this even clearer by moving it to a public "helper" class such as PreparedStatementHelper - that makes it potentially obvious that really you'd like these methods to be on PreparedStatement, but they're not so you've got to have a static method instead to act on a PreparedStatement.

One alternative would be to create your own wrapper class which would maintain a PreparedStatement itself, and have an instance setIntOrNull method... but in the long run I believe that would be a lot more complicated, for little benefit.

Upvotes: 5

Related Questions