Java’s String.repeat(int) method is an example of a “small” addition to Java (JDK 11) that I find myself frequently using and appreciating. This post describes use of JDK 11-introduced String.repeat(int) for easier custom generation of SQL WHERE clauses with the appropriate number of “?” parameter placeholders for use with PreparedStatements.
Many Java developers do not need to manually build PreparedStatements with the approprite number of parameter placeholders because they take advantage of a JPA implementation, other ORM framework, or library that handles it for them. However, the demonstrations in this post show how String.repeat(int) can make light work of any implementation that needs to build up a string with a specified number of repeated pieces.
Building SQL IN Condition with Dynamic Number of Parameters
A common approach used in Java applications for building a custom SQL SELECT statement that queries a particular database column against a collection of potential values is to use the IN operator and pass all potential matching values to that IN operator.
One Java implementation approach for building the IN operator portion of the SQL SELECT‘s WHERE clause is to iterate the same number of times as there are parameters for the IN operator and to use a conditional within that loop to determine the how to properly add that portion of the in-progress IN portion. This is demonstrated in the next code listing:
/**
* Demonstrates "traditional" approach for building up the
* "IN" portion of a SQL statement with multiple parameters
* that uses a conditional within a loop on the number of
* parameters to determine how to best handle each.
*
* @param columnName Name of database column to be referenced
* in the "IN" clause.
* @param numberPlaceholders Number of parameters for which
* placeholder question marks ("?") need to be added.
* @return The "IN" portion of a SQL statement with the
* appropriate number of placeholder question marks.
*/
public String generateInClauseTraditionallyOne(
final String columnName, final int numberPlaceholders)
{
final StringBuilder inClause = new StringBuilder();
inClause.append(columnName + " IN (");
for (int placeholderIndex = 0; placeholderIndex < numberPlaceholders; placeholderIndex++)
{
if (placeholderIndex != numberPlaceholders-1)
{
inClause.append("?, ");
}
else
{
inClause.append("?");
}
}
inClause.append(")");
return inClause.toString();
}
A second traditional approach for building up the IN clause to use a dynamic number of parameter placeholders is to again loop the same number of times as there are parameters, but append exactly the same new text each iteration. After iteration is completed, the extra characters are chopped off the end. This approach is shown in the next code listing:
/**
* Demonstrates "traditional" approach for building up the
* "IN" portion of a SQL statement with multiple parameters
* that treats each looped-over parameter index the same and
* the removes the extraneous syntax from the end of the
* generated string.
*
* @param columnName Name of database column to be referenced
* in the "IN" clause.
* @param numberPlaceholders Number of parameters for which
* placeholder question marks ("?") need to be added.
* @return The "IN" portion of a SQL statement with the
* appropriate number of placeholder question marks.
*/
public String generateInClauseTraditionallyTwo(
final String columnName, final int numberPlaceholders)
{
final StringBuilder inClause = new StringBuilder();
inClause.append(columnName + " IN (");
for (int placeholderIndex = 0; placeholderIndex < numberPlaceholders; placeholderIndex++)
{
inClause.append("?, ");
}
inClause.delete(inClause.length()-2, inClause.length());
inClause.append(")");
return inClause.toString();
}
JDK 11 introduced a set of useful new String methods that include String.repeat(int). The String.repeat(int) method boils these approaches for generating a custom IN operator with dynamic number of parameter placeholders to a single line as shown in the next code listing:
/**
* Demonstrates JDK 11 {@link String#repeat(int)} approach
* for building up the "IN" portion of a SQL statement with
* multiple parameters.
*
* @param columnName Name of database column to be referenced
* in the "IN" clause.
* @param numberPlaceholders Number of parameters for which
* placeholder question marks ("?") need to be added.
* @return The "IN" portion of a SQL statement with the
* appropriate number of placeholder question marks.
*/
public String generateInClauseWithStringRepeat(
final String columnName, final int numberPlaceholders)
{
return columnName + " IN (" + "?, ".repeat(numberPlaceholders-1) + "?)";
}
With the use of String.repeat(int), a single line accomplishes the task at hand and there’s no need for explicit looping or explicit instantiation of a StringBuilder.
Building SQL OR Conditions with Dynamic Number of Parameters
Multiple SQL or conditions can be used instead of IN to test against multiple values. This is a must if, for example, the number of paramaters is over 1000 and you’re using an Oracle database that only allows IN to support up to 1000 elements.
/**
* Demonstrates "traditional" approach for building up the
* "OR" portions of a SQL statement with multiple parameters
* that uses a conditional within a loop on the number of
* parameters to determine how to best handle each.
*
* @param columnName Name of database column to be referenced
* in the "OR" clauses.
* @param numberPlaceholders Number of parameters for which
* placeholder question marks ("?") need to be added.
* @return The "OR" portions of a SQL statement with the
* appropriate number of placeholder question marks.
*/
public String generateOrClausesTraditionallyOne(
final String columnName, final int numberPlaceholders)
{
final StringBuilder orClauses = new StringBuilder();
for (int placeholderIndex = 0; placeholderIndex < numberPlaceholders; placeholderIndex++)
{
if (placeholderIndex != numberPlaceholders-1)
{
orClauses.append(columnName).append(" = ? OR ");
}
else
{
orClauses.append(columnName).append(" = ?");
}
}
return orClauses.toString();
}
/**
* Demonstrates "traditional" approach for building up the
* "OR" portions of a SQL statement with multiple parameters
* that treats each looped-over parameter index the same and
* the removes the extraneous syntax from the end of the
* generated string.
*
* @param columnName Name of database column to be referenced
* in the "OR" clauses.
* @param numberPlaceholders Number of parameters for which
* placeholder question marks ("?") need to be added.
* @return The "OR" portions of a SQL statement with the
* appropriate number of placeholder question marks.
*/
public String generateOrClausesTraditionallyTwo(
final String columnName, final int numberPlaceholders)
{
final StringBuilder orClauses = new StringBuilder();
for (int placeholderIndex = 0; placeholderIndex < numberPlaceholders; placeholderIndex++)
{
orClauses.append(columnName + " = ? OR ");
}
orClauses.delete(orClauses.length()-4, orClauses.length());
return orClauses.toString();
}
The use of String.repeat(int) makes this easy as well:
/**
* Demonstrates JDK 11 {@link String#repeat(int)} approach
* for building up the "OR" portions of a SQL statement with
* multiple parameters.
*
* @param columnName Name of database column to be referenced
* in the "OR" clauses.
* @param numberPlaceholders Number of parameters for which
* placeholder question marks ("?") need to be added.
* @return The "OR" portions of a SQL statement with the
* appropriate number of placeholder question marks.
*/
public String generateOrClausesWithStringRepeat(
final String columnName, final int numberPlaceholders)
{
final String orPiece = columnName + " = ? OR ";
return orPiece.repeat(numberPlaceholders-1) + columnName + " = ?";
}
Posts
0 comments:
Post a Comment