Spring JDBC support allows us to concentrate on the SQL code rather than on <rant>babysitting the JDBC framework and handling the checked exceptions it throws</rant>.
The trouble comes when you’re trying to debug the SQL statement itself, especially if you use a lot of positional parameters and if you have the SQL statement in several String constants.

Why parameters

The named parameters allow us to pass values to the SQL statement without having to worry about the dangers of SQL injection. Also, because the SQL statement remains the same, the RDBMS can cache the execution plan for the statement. This would not be possible if we included the (changing) parameters in the SQL statement.

Why several constants?

There is nothing stopping you from writing the SQL statement in one big String. The trouble begins when the SQL statement gets very complicated. Consider this statement:

select
	[many-columns]
from
	(select ROW_NUMBER() over (order by i.SupplierInvoiceCode) as row,
		[many-other-columns mapped to many-columns]
		from
			TableA i inner join
			TableB mo on mo.id = i.memberOutlet inner join
			TableC ma on ma.id = mo.memberAccount inner join
			TableD dc on dc.id = i.distributionChannel inner join
			TableE s on s.id = i.supplier inner join
			TableF m on m.id = ma.member
		where
			(i.supplierInvoiceCode like ?) and
			(i.invoiceDate >= ? and i.invoiceDate <= ?) and
			((i.distributionChannel = ?) or (? is null)) and
			[many-more-conditions]
			((i.collectedFromMember = ?) or (? is null)) and
			((? is null) or ((? = 1) and
				(select sum(systemDispute) from
					InvoiceLine il where il.invoice = i.id) > 0) or
			[more-and-more-conditions]
	) as InvoiceDtoWithNumbers  where row >= ? and row <= ?

In the interest of your own sanity, it would be better to split the SQL to its parts and construct its final form as

static String SELECT_SQL =
     "select " + MAPPED_COLUMNS +
     " from (select ROW_NUMBER() over (order by i.SupplierInvoiceCode) as row," +
     COLUMNS +
     " from " + FROM_SQL +
     " where " + WHERE_SQL +
     " ) as InvoiceDtoWithNumbers " +
     " where row >= ? and row <= ?";

What about those parameter pairs?

Notice in particular the ((someColumn = ?) or (? is null)) pattern. Let’s take a more concrete example ((id = ?) or (? is null)). If both positional parameters contain the same value, then supplying null will mean that the RDBMS will not apply the restriction ((id = null) or (null is null)) evaluates to true and most RDBMSs are capable of optimising tautologies. The effect of setting two nulls means that we don’t care what the value of id is. This is helpful in various searches, where null means “we don’t care”.
Conversely, if we supply some value other than null the RDBMS will eliminate the contradiction ((? is null)) and perform the requested restriction.
 

Debugging that thing?

Now, imagine there is a bug in the SQL statement: it is selecting too many or too few rows. How do you debug it? You can put a breakpoint in your IDE and copy the text of the SQL statement, but it will be full of positional parameters. If you run the SQL statement in a query runner, you will get the very helpful message:

>[Error] Script lines: 1-47 -------------------------
 Unexpected parameter marker at position 1777. 

Not good, not good at all.

PreparedStatementParameterSetter to the rescue

To debug the statement successfully, we implemented the PreparedStatementParameterSetter. It takes the PreparedStatement instance. Then, rather than calling PreparedStatement.set* methods, you call similar methods of the PreparedStatementParameterSetter. The calls map to appropriate calls on the PreparedStatement, but the setter also remembers the parameters. The PreparedStatementParameterSetter also automatically advances the parameter position. This means that your client code looks like this:

public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
    PreparedStatement ps = con.prepareStatement(this.sql);
    PreparedStatementParameterSetter setter = new PreparedStatementParameterSetter(ps);
    setter.setWildcardString(argument.getSupplierInvoiceCode());
    setter.setDate(DateUtils.minDate(argument.getFromInvoiceDate()));
    setter.setDate(DateUtils.maxDate(argument.getToInvoiceDate()));
    setter.setPossibleNullLongPair(argument.getDistributionChannelId());
    setter.setPossibleNullLongPair(argument.getSupplierId());
    setter.setPossibleNullLongPair(argument.getMemberAccountId());
    setter.setPossibleNullLongPair(argument.getMemberId());
    ...
    if (logger.isDebugEnabled()) {
        logger.debug(setter.prepareSql(this.sql));
    }
}

You can see the call to the String prepareSql(String sqlWithPositionalParameters). The method will replace all positional parameters with the values you set and return the SQL statement. You can take the returned SQL statement and debug it in your favourite query analyzer.
 

Summary

Using the PreparedStatementParameterSetter in your [Spring] JDBC code simplifies the setting and debugging of complex SQL statements.
The PreparedStatementParameterSetter is one of the Spring Centre of Excellence library of code and you can download it here. You will also need to download the JdbcDaoUtils.java.