CODEWITHSUNDEEP
javasqlspringspring-mvcspring-data-jpa
Habibur Rahman
Habibur Rahman

Reputation: 81

Order by clause query in Spring MVC with MapSqlParameterSource

I am trying to a SQL query in Spring MVC 4 with order by clause. But it does not work. I am using MapSQLParameterSource class to define sql parameters.

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("lowerLimit", lowerLimit);
    params.addValue("upperLimit", upperLimit);
    params.addValue("filter", filter.trim()+"%");
    params.addValue("order", order);
    String sql = "SELECT * FROM tbl_Subject WHERE subjectName LIKE :filter ORDER BY :order limit :lowerLimit, :upperLimit";

It does not work actually. does not order any columns. When I try to hard coded it works fine as aspect. 

String sql = "SELECT * FROM tbl_Subject WHERE subjectName LIKE :filter ORDER BY subjectId DESC limit :lowerLimit, :upperLimit";

How to I Order by clause query with MapSqlParameterSource.

Upvotes: 1

Views: 599

Answers (1)

Youcef LAIDANI
Youcef LAIDANI

Reputation: 59950

The problem is in this line :

params.addValue("order", order);//consider this return col_name

This will be translate it to :

.. ORDER BY 'col_name' limit ..
            ^        ^

and this is a wrong syntax, logically after ORDER BY you have to pass a column name and not a value.

.. ORDER BY col_name limit ..
            ^^^^^^^^

Instead you have to pass it to the query directly but be carfull to pass the correct name, this can cause Syntax error or SQL Injection if you pass a wrong input

Upvotes: 2

Related Questions