Bind parameters for ORDER BY in NamedParameterJDBCTemplate

Question

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());

score 17 · Accepted Answer · answered Jul 25 '12 at 21:41

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.

thanks for clarifying. I completely oversaw that I was trying to bind part of the query. Thanks again. — aprajitha, Jul 26 '12 at 06:19

score 3 · Answer 2 · answered Jun 11 '20 at 16:31

3

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;

answered Jun 11 '20 at 16:31

Fakipo

182
2
17

Bind parameters for ORDER BY in NamedParameterJDBCTemplate

2 Answers2