aspose file tools*
The moose likes JSP and the fly likes problem in sorting query in JSTL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » JSP
Bookmark "problem in sorting query in JSTL" Watch "problem in sorting query in JSTL" New topic
Author

problem in sorting query in JSTL

sachin yadav
Ranch Hand

Joined: Nov 24, 2005
Posts: 156
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

Joined: Aug 17, 2005
Posts: 69
Shouldn't the servlet side of the code read


[ June 21, 2006: Message edited by: Darren Edwards ]
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61766
    
  67

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".


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Darren Edwards
Ranch Hand

Joined: Aug 17, 2005
Posts: 69
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

Joined: Jul 06, 2005
Posts: 1027
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

Joined: Nov 24, 2005
Posts: 156
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

Joined: Nov 24, 2005
Posts: 14688
    
  16

Is there actually an attribute called "sort_order" in your request ? It seems to be empty/null.


[My Blog]
All roads lead to JavaRanch
sachin yadav
Ranch Hand

Joined: Nov 24, 2005
Posts: 156
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

Joined: Jul 06, 2005
Posts: 1027
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

Joined: Nov 24, 2005
Posts: 14688
    
  16

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

Joined: Dec 01, 2014
Posts: 2
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

Joined: Feb 26, 2001
Posts: 5288
    
  10

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.


Junilu - [How to Ask Questions] [How to Answer Questions]
Kevinolo Lee
Greenhorn

Joined: Dec 01, 2014
Posts: 2
Agreed. :)
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: problem in sorting query in JSTL