Using a Prepared Statement and Variable Bind Order by in Java with Jdbc Driver

Using a prepared statement and variable bind Order By in Java with JDBC driver

Placeholders ? can only be used for parameter values but not with column and sort order directions. So the standard way to do this as is pointed e.g. here is to use String#format() or something similar to append your column name and order value to your query.

Another option is to use Spring Data JPA where you can give to your method as an argument an instance of type Sort which can contain all needed info for database to sort.

Using a variable instead of a parameter index with a JDBC prepared statement

As kd304 mentioned in the comment to my posting, this is a very nice solution if you don't want to incorporate another 3rd party library (like Spring) into your project: Javaworld Article: Named Parameters for PreparedStatement

PreparedStatement with list of parameters in a IN clause

What I do is to add a "?" for each possible value.

var stmt = String.format("select * from test where field in (%s)",
                         values.stream()
                         .map(v -> "?")
                         .collect(Collectors.joining(", ")));

Alternative using StringBuilder (which was the original answer 10+ years ago)

List values = ... 
StringBuilder builder = new StringBuilder();

for( int i = 0 ; i < values.size(); i++ ) {
    builder.append("?,");
}

String placeHolders =  builder.deleteCharAt( builder.length() -1 ).toString();
String stmt = "select * from test where field in ("+ placeHolders + ")";
PreparedStatement pstmt = ... 

And then happily set the params

int index = 1;
for( Object o : values ) {
   pstmt.setObject(  index++, o ); // or whatever it applies 
}

prepared statement missing bind variable within a LIKE clause

The above post worked fine for straightforward PreparedStatement with bind variable but I had another similar issue. I am using the Apache Lucerne index builder for more complex queries ie. ranges with an index definition like:

CREATE CUSTOM INDEX counterparty_column_index ON counterparty (filter_column)
USING 'com.stratio.cassandra.lucene.Index'
WITH OPTIONS = {
    'schema' : '{
        fields : {
        controlling_team     : {type : "text", analyzer : "english"},
        relationship_manager : {type : "text", analyzer : "english"},
        review_date          : {type : "date", pattern : "dd-MM-yyyy"}
        }
    }'
};

and the statement defined as :

    log.info("CounterpartyStatement [selectByReviewDateAndTeamStmt]");
    selectByReviewDateAndTeamStmt       =  cassandraDatasource.getSession().prepare(" select  id, parent_id, ultimate_parent_id, internal_ref, short_name, long_name, controlling_team, " +
                                            " country_incorp, country_operate, company_reg, relationship_manager, credit_rating, rating_source, pd, lgd, review_date, sector, defaulted, own_bank_entity " +
                                            " from counterparty where filter_column = '{ " 
                                                         + " filter : {type:\"range\", field:\"review_date\", lower:\"01-01-2000\", upper:\"%s\"},"
                                                         + " query : {type:\"contains\", field:\"controlling_team\", values:[\"%s\"]},"
                                                         + " refresh:true"
                                                         + " }'");   

This formatting was necessary because the standard PreparedStatement bind was not picking up the usual ? placeholders.

The code then became:

log.info("CounterpartyDAO.getRmCounterpartiesForReview [" + rm + "]");
    PreparedStatement pStmt = statementFactory.getSelectByReviewDateAndRmStmt();

    String query = pStmt.getQueryString();
    Date maxDate = dateUtils.addDays( dateUtils.getToday(), 3);
    String maxDateString = dateUtils.getAnyDate(maxDate, "dd-MM-yyyy");
    query = String.format( query, maxDateString, rm );

    ResultSet rs = cassandraDatasource.getSession().execute( query );

which also worked fine, the dateUtils is just a package I wrote for manipulating dates etc.

PreparedStatement IN clause alternatives?

An analysis of the various options available, and the pros and cons of each is available in Jeanne Boyarsky's Batching Select Statements in JDBC entry on JavaRanch Journal.

The suggested options are:

• Prepare SELECT my_column FROM my_table WHERE search_column = ?, execute it for each value and UNION the results client-side. Requires only one prepared statement. Slow and painful.
• Prepare SELECT my_column FROM my_table WHERE search_column IN (?,?,?) and execute it. Requires one prepared statement per size-of-IN-list. Fast and obvious.
• Prepare SELECT my_column FROM my_table WHERE search_column = ? ; SELECT my_column FROM my_table WHERE search_column = ? ; ... and execute it. [Or use UNION ALL in place of those semicolons. --ed] Requires one prepared statement per size-of-IN-list. Stupidly slow, strictly worse than WHERE search_column IN (?,?,?), so I don't know why the blogger even suggested it.
• Use a stored procedure to construct the result set.
• Prepare N different size-of-IN-list queries; say, with 2, 10, and 50 values. To search for an IN-list with 6 different values, populate the size-10 query so that it looks like SELECT my_column FROM my_table WHERE search_column IN (1,2,3,4,5,6,6,6,6,6). Any decent server will optimize out the duplicate values before running the query.

None of these options are ideal.

The best option if you are using JDBC4 and a server that supports x = ANY(y), is to use PreparedStatement.setArray as described in Boris's anwser.

There doesn't seem to be any way to make setArray work with IN-lists, though.

---

Sometimes SQL statements are loaded at runtime (e.g., from a properties file) but require a variable number of parameters. In such cases, first define the query:

query=SELECT * FROM table t WHERE t.column IN (?)

Next, load the query. Then determine the number of parameters prior to running it. Once the parameter count is known, run:

sql = any( sql, count );

For example:

/**
 * Converts a SQL statement containing exactly one IN clause to an IN clause
 * using multiple comma-delimited parameters.
 *
 * @param sql The SQL statement string with one IN clause.
 * @param params The number of parameters the SQL statement requires.
 * @return The SQL statement with (?) replaced with multiple parameter
 * placeholders.
 */
public static String any(String sql, final int params) {
    // Create a comma-delimited list based on the number of parameters.
    final StringBuilder sb = new StringBuilder(
        String.join(", ", Collections.nCopies(possibleValue.size(), "?")));

    // For more than 1 parameter, replace the single parameter with
    // multiple parameter placeholders.
    if (sb.length() > 1) {
        sql = sql.replace("(?)", "(" + sb + ")");
    }

    // Return the modified comma-delimited list of parameters.
    return sql;
}

For certain databases where passing an array via the JDBC 4 specification is unsupported, this method can facilitate transforming the slow = ? into the faster IN (?) clause condition, which can then be expanded by calling the any method.

Sqlite + java, weird behavior with prepared statement

It's actually surprise that this syntax is accepted by the SQL engine. It can probably fail in some others.

Anyway, the sort is interpreted as a string constant, so all the rows will have same value for the sort algorithm. Therefore, as they have the same value, it keeps the original order.

You cannot use such dynamic statement, the only way is the second solution which appends the sort directly to statement string.

---

Related Topics