Parameterized SQL in Java

When it comes to parameterized SQL in Java, there are a number of ways to achieve the same result. Here are my current thoughts with a simple example.

try-with-resources

If you're not already using TWR, it sure beats the pants off manually closing Connections, PreparedStatements, etc. Check out the Java documentation here (and see it in use in the example below)!

PreparedStatement::setInt (etc)

I'll explain for each example below, but we switched from hard-coding the parameter indices to incrementing a variable for each parameter, because we ran into issues when we would change the number of parameters in a SQL statement and had to manually change the indices (which occasionally led to mistakes).

parameterIndex++

This is our current standard. We start with int parameterIndex = 1 and increment it within the .setInt() method call using the postfix increment operator. The postfix operator will increment the value after it's used (hence staring the variable at 1). This works great, because it's reasonably clear and causes no issues when we add or remove parameters (the indices still line up). The two drawbacks are 1) using the postfix increment operator in this way may be confusing to those unfamiliar with it and 2) the IDE may bug you that you're incrementing a variable on the last line but not using the updated value (you could simply leave the postfix operator off of the last .setInt() call, but it's just another thing to remember and could cause trouble if a parameter gets added to the end and the operator isn't added back, so we keep it).

++parameterIndex

When using the prefix increment operator, we instead start our parameterIndex at 0, because it'll now be incremented before it's actually used in the .setInt() call. This solved the IDE warning issue described above when using the postfix increment operator, but it has the downside of being less clear simply because many developers are less familiar with the prefix operator (it's less commonly used than the postfix increment operator, which is common in basic loops).

Hard-coded parameterIndex

This is the most basic technique. It's simple; it's clear; and it works...that is, until you add a third parameter in between the first two and forget to update the last index from 2 to 3. Where this causes the most pain is when there is a large number of parameters that could possibly be added to or removed from the query in the future.

Further Reading

Interested in learning more about prefix/postfix increment operators (and more)? Check out Oracle's tutorial.