File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

problem in sorting query in JSTL

 
sachin yadav
Ranch Hand
Posts: 156
  • 0
  • Mark post as helpful
  • send pies
  • 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.
 
Darren Edwards
Ranch Hand
Posts: 69
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Shouldn't the servlet side of the code read


[ June 21, 2006: Message edited by: Darren Edwards ]
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64173
83
IntelliJ IDE Java jQuery Mac Mac OS X
  • 0
  • Mark post as helpful
  • send pies
  • 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
  • 0
  • Mark post as helpful
  • send pies
  • 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 ..'.
 
Stefan Evans
Bartender
Posts: 1640
9
  • 0
  • Mark post as helpful
  • send pies
  • 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 <cut value="${sort_order}"/>

Cheers,
evnafets
 
sachin yadav
Ranch Hand
Posts: 156
  • 0
  • Mark post as helpful
  • send pies
  • 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 <cut value="${sort_order}"/>

Cheers,
evnafets
 
Christophe Verré
Sheriff
Pie
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • 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
  • 0
  • Mark post as helpful
  • send pies
  • 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: 1640
9
  • 0
  • Mark post as helpful
  • send pies
  • 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
Pie
Posts: 14691
16
Eclipse IDE Ubuntu VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • 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.
 
Kevinolo Lee
Greenhorn
Posts: 2
  • 0
  • Mark post as helpful
  • send pies
  • 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.

 
Junilu Lacar
Bartender
Pie
Posts: 6529
21
Java Linux Mac Scala Spring
  • 2
  • Mark post as helpful
  • send pies
  • 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
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Agreed. :)
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic