How to sort columns in ascending and descending order

gecastill · February 1, 2006, 2:46pm

I want to be able to sort any column in my JSP [dynamic HTML table] page in ascending or descending order by clicking on the header of the column. So if a user clicks on a header column the first time that column should be sorted in ascending order then if that same header column gets click again the column should be sorted in descending order and so forth.

So far I can only sort any column in ascending order. When a user clicks on a column header I pass the SQL table column name to a JavaScript method which sets a hidden tag value to the SQL table column name and then it is set to a String variable which in turn gets set to a String variable [the ORDER BY clause] in the JavaBean which then builds the SQL statement with the ORDER BY clause

Please advise, thanks.

hooknc · February 1, 2006, 9:28pm

Use the ORDER ASC or ORDER DESC clause. it should work for you.

gecastill · February 1, 2006, 9:59pm

hooknc,

I know how to use the ORDER BY clause with ASC and DESC order, what I am trying to figured out is how to dynamically build a ORDER BY clause with ASC and DESC order.

In a JSP page, if a user clicks on a column header for the first time and the column is not sorted in any order I want to sort the table by the column header in ascending order. If the same column header is click the second time, after the first click on that same column, I want to sort the dynamic HTML table by the column header in descending order. see example below:

SELECT * FROM contactlist ORDER BY userName;
±-------±----------------±------------+
| userID | userName | phoneNumber |
±-------±----------------±------------+
| 10 | David Crozier | 4347994367 |
| 5 | Frank Smith | 4347993030 |
| 8 | Gibran Castillo | 4347991709 |
| 2 | Jane Doe | 4347935555 |
| 4 | Janet Doe | 4347993045 |
| 7 | Jeremy Brown | 4347993320 |
| 3 | Jimmy Doe | 4347937755 |
| 9 | Joe Donahoe | 4347992390 |
| 1 | John Doe | 4347995555 |
| 6 | Wanda Brown | 4347992920 |
±-------±----------------±------------+
10 rows in set (0.00 sec)

SELECT * FROM contactlist ORDER BY userName DESC;
±-------±----------------±------------+
| userID | userName | phoneNumber |
±-------±----------------±------------+
| 6 | Wanda Brown | 4347992920 |
| 1 | John Doe | 4347995555 |
| 9 | Joe Donahoe | 4347992390 |
| 3 | Jimmy Doe | 4347937755 |
| 7 | Jeremy Brown | 4347993320 |
| 4 | Janet Doe | 4347993045 |
| 2 | Jane Doe | 4347935555 |
| 8 | Gibran Castillo | 4347993209 |
| 5 | Frank Smith | 4347993030 |
| 10 | David Crozier | 4347994367 |
±-------±----------------±------------+
10 rows in set (0.03 sec)

Please advise, thanks.

dc_dalton · February 1, 2006, 10:29pm

You would just add the order by and asc or desc based on the column they click. You would have to set up the column header links so it knows how it is sorted at the moment though.

Let’s say its sorted by username asc by default. In the link to the JSP you can pass a variable (in a get string) something like this:

<a href=‘myJsp.jsp?sortIs=asc&columnName=name’>Header</a>

Then in your JSP test the sortIs variable and do the opposite: (I’ll do this cheap and quick BTW)


String query = "SELECT * FROM contactlist ORDER BY ";

if (request.getParameter("sortIs").equals("asc") {
 query += " userName DESC";
 }
else {
 query += "userName ASC";
 }

Now I also passed the columnName variable that can be used to know which column they clicked on, so in your header links you have the column name and the way its already sorted. You can even make that sortIs variable something like “none” if the results aren’t currently sorted by that column.

So then in your JSP the first thing you do is find out which header they clicked on by testing the columnName variable, then once you know that you check how it was sorted on the page and then sort it the opposite way. Of course if it comes in as “none” you will want to then decide how it should be sorted (I’d use asc)

This is the “down and dirty” way to do it … it could be done with sessions or something like that but this way you dont have to sweat the sessions working as you are dynamically hard coding in the links.

Hope I understood your problem and this helps … I’m knee deep in income tax BS and needed a break!

gecastill · February 2, 2006, 7:23pm

dc dalton,

Thank you very much for your ideas and hints. I finally figured out
I did it differently from the way you were telling me, but thanks anyways.

Best Regards,
Gibran E Castillo

7stud · February 3, 2006, 7:42am

I know how to use the ORDER BY clause with ASC and DESC order, what I am trying to figured out is how to dynamically build a ORDER BY clause with ASC and DESC order.

The whole reason I learned javascript was to do that task without having to go back to the server and execute a php page to do the ordering. My thought was that all the information I needed was already on the page, and I just need to rearrange it, so why should I have to go back to the server to do anything.

javascript can do it for you more quickly and without clogging your server with unneeded requests. Learn it.

hooknc · February 3, 2006, 8:43pm

The problem with javascript…

Tools

Internet Options…

Security

Custom Level

Scripting

Active scripting: Disable.

shrug

What does he do if his uses have turned off javascript?

rushiku · February 3, 2006, 9:20pm


<noscript><meta http-equiv='refresh' content='0; url=turnYourDamnScriptOn_idiot.html' /></noscript>

Should do the trick

gecastill · February 6, 2006, 8:46pm

7stud,

I know JavaScript and I have made tables sortable with JavaScript, but this time I want to do it on the server side.

I got it working by making the Bean that I use in the JSP with a session scope; however, the only problem I am having is that after a while the connection dies. I am not sure why yet, I don’t think that it is b/c it time out, b/c it dies as I am sorting the table by a header column.

I get the following error message in my web browser:
Error Encountered At Server
[Microsoft][SQLServer 2000 Driver for JDBC]Object has been closed.

Error Encountered At Server, see Stack Trace:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Object has been closed.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.base.BaseConnection.validateClosedState(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.validateClosedState(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQuery(Unknown Source)
at com.danriver.webmethods.product.DropDownBean.getAllValuesfromDB(DropDownBean.java:92
)
at com.danriver.webmethods.product.DropDownBean.fetchValuesfromDB(DropDownBean.java:273
)
at org.apache.jsp.PDSampleApprovalReport_jsp._jspService(PDSampleApprovalReport_jsp.jav
a:370)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:204)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterCh
ain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java
:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:260)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(St
andardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(St
andardPipeline.java:643)
at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(St
andardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2415)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(St
andardPipeline.java:643)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170
)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(St
andardPipeline.java:641)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(St
andardPipeline.java:641)

gecastill · February 6, 2006, 8:48pm

rushiku,

Thanks for your hint, that is good to know.

dc_dalton · February 6, 2006, 10:22pm

Did you get your sql exception straightened out?

gecastill · February 8, 2006, 3:24pm

All,

For future reference, the following code snippet is the core idea of what I did to make a dynamic HTML table sortable in a JSP page.

<html>
<head>
<title>Sortable HTML table :: Demo</title>
<script language=“JavaScript” type=“text/JavaScript”>
<!–
/**
* Make modifications to the sort column and sort order.
*/
function reSortData( sortColumn ) {
if ( sortColumn == document.form_searchSort_report.hd_sortCol.value ) {
// The same column was selected. Toggle the sort order.
if ( document.form_searchSort_report.hd_sortOrd.value == ‘ASC’ ) {
document.form_searchSort_report.hd_sortOrd.value = ‘DESC’;
} else {
document.form_searchSort_report.hd_sortOrd.value = ‘ASC’;
}
} else {
// A different column was selected.
document.form_searchSort_report.hd_sortCol.value = sortColumn;
document.form_searchSort_report.hd_sortOrd.value = ‘ASC’;
}
// Submit the form.
document.form_searchSort_report.submit();
}
–>
</script>
</head>

&lt;body&gt;
    &lt;%
    %&gt;
    &lt;form name="form_searchSort_report" method="POST" action="sortableDemo.jsp"&gt;
        &lt;%-- These are hidden inputs that will be populated by the reSortData() JavaScript function. --%&gt;
        &lt;input type="hidden" name="hd_sortCol" value="&lt;%=request.getParameter("hd_sortCol")%&gt;"&gt;
        &lt;input type="hidden" name="hd_sortOrd" value="&lt;%=request.getParameter("hd_sortOrd")%&gt;"&gt;

        &lt;p&gt;
        &lt;a href="#" onClick="reSortData('userID');"&gt;User ID&lt;/a&gt;
        &lt;a href="#" onClick="reSortData('userName');"&gt;User Name&lt;/a&gt;
        &lt;/p&gt;
    &lt;/form&gt;
&lt;/body&gt;

</html>