• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

problem in sorting query in JSTL

 
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi all,

i am doing a code using JSTL to fire a query. everything come fine except the resule is not sorted as desired. i am putting the code below--



now the resule is always sorted by USER_ID. if i want to sort it using USER_NAME i pass parameter from controller to this page in sort_oredr variable which comes fine but the result doesn't sort by name, only by id. if i hardcode USER_NAME in query then the result is as desired. please suggest.
 
Ranch Hand
Posts: 69
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Shouldn't the servlet side of the code read


[ June 21, 2006: Message edited by: Darren Edwards ]
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Darren Edwards:
Shouldn't the servlet side of the code read



I'm not sure where you're coming from with that. No mention has been made of the source of "sort_order".
 
Darren Edwards
Ranch Hand
Posts: 69
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I can't see anything technically wrong with the code and jsp sample that's been posted (especially as it works when hardcoded) and I think it's pretty strange to get a request attribute, then set it back exactly the same. IMO, it's more likely that 'sort_order' should come from an external source and getAttribute/getParameter is an easy typo to make. Lots of assumptions though, which is why the answer is phrased as more of a question back to the poster, rather than 'this is what you did wrong ..'.
 
Bartender
Posts: 1845
10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Prepared statements are meant to substitute in values only.
You can't use it in this manner to specify the sort order, any more than you could use it to specify which table to select from eg Select * from ? is an illegal use.
This isn't a limitation of the JSTL sql library - its how prepared statements work in Java.

You might try this:


This will dynamically construct the sql for you - ie the sql string for the query is dynamic, as opposed to using the same string with a prepared statement, and substituting in values.
If you don't have a JSP2 container, replace ${sort_order} with <c:out value="${sort_order}"/>

Cheers,
evnafets
 
sachin yadav
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Stefan,

thanx for provideing such productive information. But the proble still persists. I have user all the techniques you havee described but now my server (oracle 10g) is throwing this Erorr in error log:-



i have checked the value of sort_order it's coming fine in the page, but the sorting is not done. what you say.....




Originally posted by Stefan Evans:

You might try this:


This will dynamically construct the sql for you - ie the sql string for the query is dynamic, as opposed to using the same string with a prepared statement, and substituting in values.
If you don't have a JSP2 container, replace ${sort_order} with <c:out value="${sort_order}"/>

Cheers,
evnafets

 
Sheriff
Posts: 14691
16
Eclipse IDE VI Editor Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Is there actually an attribute called "sort_order" in your request ? It seems to be empty/null.
 
sachin yadav
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
i have checked sort_order, and sure that this is not null.

Originally posted by Satou kurinosuke:
Is there actually an attribute called "sort_order" in your request ? It seems to be empty/null.

 
Stefan Evans
Bartender
Posts: 1845
10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Did you try the following?


Both approaches worked for me on Tomcat5.0.28 with oracle 9 database.

I guess now is the time to bring up the standard reponse of - you shouldn't be using SQL in a JSP page anyway. Write the query in a bean/servlet using java code.
That way you have full control over the JDBC access, and know exactly what it is doing.
 
Christophe Verré
Sheriff
Posts: 14691
16
Eclipse IDE VI Editor Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
That's strange. At least it works fine on my machine.
Where did you check the content of sort_order ?
It really looks empty.
 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi, I had the same trouble and I spent a lot of time solving it. Sorry to be resurrecting this thread after so many years; I think it's still relevant!


As Stefan Evans has pointed out, the syntax of ORDER BY ? <sql:param value = ... /> doesn't work because we want to ORDER BY COLUMN_NAME, rather than ORDER BY a particular VALUE of some column in the table.
The syntax of sql:param value is really reserved for actual values in the table and not column names.

Therefore, we need to use ORDER BY <c:out value = ... />

However, if the jsp page contains the sql query and you execute it, the query will execute immediately and in my case, sort_order was NULL. This all happens BEFORE the user gets to select or choose any values. Even if it was a droplist with some default value, the sql executes before the values get populated. In other words, it will throw some SQL syntax error because you're ordering on NULL.

--
Anyway, to cut the long story short, this is my code for my project (book store):

<sql:query var="result" dataSource="jdbc/yourjdbcconnection">
SELECT * FROM book
ORDER BY
<c:choose>
<c:when test="${param.sort == null}">year</c:when>
<c:otherwise>
<c:out value="${param.sort}"/>
</c:otherwise>
</c:choose>
</sql:query>

Essentially, the param.sort was null when the page first loads, so I hard-coded a default value as "year". When the form is submitted without a null value, I then take the value param.sort.

You can also avoid this problem if the sql query is done on a separate page once the user fills up a form and hits "submit" - i.e. you really ensure that no null values are passed into the sql query.

 
Sheriff
Posts: 17644
300
Mac Android IntelliJ IDE Eclipse IDE Spring Debian Java Ubuntu Linux
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Kevinolo Lee wrote:Sorry to be resurrecting this thread after so many years; I think it's still relevant!
...

Anyway, to cut the long story short, this is my code for my project (book store):

<sql:query var="result" dataSource="jdbc/yourjdbcconnection">
SELECT * FROM book
ORDER BY
...

You can also avoid this problem if the sql query is done on a separate page once the user fills up a form and hits "submit" - i.e. you really ensure that no null values are passed into the sql query.


After all these years, it's amazing that this problem still comes up.

The best way to avoid this problem is to not execute SQL queries from the JSP. Queries should be done in the data layer, not the presentation layer. All the presentation layer should do is iterate over a collection that is already in the order that you want to display.
 
Kevinolo Lee
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Agreed. :)
 
You're not going crazy. You're going sane in a crazy word. Find comfort in this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic