CODEWITHSUNDEEP
springjdbctemplate
aprajitha
aprajitha

Reputation: 355

Bind parameters for ORDER BY in NamedParameterJDBCTemplate

I am trying to use NamedParameterJdbTemplate in a Spring MVC application. The issue is that the bind parameters do not seem to work (no sorting happens) when I include one of ORDER BY clauses listed below. However, a hard coded order by column name in the sql works.

ORDER BY column1 
ORDER BY column1 
ORDER BY column1 asc
ORDER BY column1 desc

For example, the below listed query does not work.

    private static final String SEARCH_ALL_BY_SORT_ORDER=
        " select FIRST_NM, MIDDLE_NM, LAST_NM, CUSTOMER_IDENTIFIER,  EMAIL_ADDRESS,  ACCOUNT_ID" +
        " from VIEW " +
        " where CUSTOMER_IDENTIFIER= :customerIdentifier " +
        " and ( REGEXP_LIKE(FIRST_NM, :firstName, 'i') " +
        "       or REGEXP_LIKE(LAST_NM, :lastName, 'i')  " +
        "       or REGEXP_LIKE(EMAIL_ADDRESS, :emailAddress, 'i')  )" +
        " order by :sortColumns";

The same query with a hard coded order by column works:

    private static final String SEARCH_ALL_BY_SORT_ORDER=
        " select FIRST_NM, MIDDLE_NM, LAST_NM, CUSTOMER_IDENTIFIER,  EMAIL_ADDRESS,  ACCOUNT_ID" +
        " from VIEW " +
        " where CUSTOMER_IDENTIFIER= :customerIdentifier " +
        " and ( REGEXP_LIKE(FIRST_NM, :firstName, 'i') " +
        "       or REGEXP_LIKE(LAST_NM, :lastName, 'i')  " +
        "       or REGEXP_LIKE(EMAIL_ADDRESS, :emailAddress, 'i')  )" +
        " order by LAST_NM";

Here's the relevant jdbctemplate code

Map <String, Object> params = new HashMap <String, Object>();
        params.put("customerIdentifier", customerIdentifier);
        params.put("firstName",  searchCriteria );
        params.put("lastName", searchCriteria );
        params.put("emailAddress",searchCriteria);
           // sortBy is COLUMN name
           // sortOrder is either 'asc' or 'desc'
        params.put("sortColumns", sortBy + " " + sortOrder);
           // Using just the column name does not work either
           //params.put("sortColumns", sortBy);

        namedParameterJdbcTemplate.query(SEARCH_ALL_BY_SORT_ORDER, params, new MemberMapper());

Upvotes: 9

Views: 8228

Answers (2)

Fakipo
Fakipo

Reputation: 190

As JB Nizet has already explained that parts of query cannot be used as bind keys (orderby :age). Therefore we will need to use concatenation here instead.

" order by "+ sortBy + " " + sortOrder;

Upvotes: 3

JB Nizet
JB Nizet

Reputation: 691953

Only values can be bound as parameters. Not parts of the query itself.

In the end, a prepared statement is generated and the parameters are bound to the prepared statement. The principle of a prepared statement is to prepare the execution plan of the query (of which the order by clause is a part), and to execute the query one or several times after, with varying parameters.

If the query is not complete, the execution plan can't be prepared and reused. So, for this part of the query, you'll need to generate the query dynamically using string concatenation, and not parameters.

Upvotes: 17

Related Questions