File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JSP and the fly likes How to do sorting with mysql? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Java » JSP
Bookmark "How to do sorting with mysql?" Watch "How to do sorting with mysql?" New topic
Author

How to do sorting with mysql?

Andrew Parker
Ranch Hand

Joined: Nov 12, 2001
Posts: 178
Hi,
I created a jsp, servlet and a mysql db. There are 3 attributes : id, name and salary of the staff db. I want to sort by id, name, salary and show them in jsp page. How should I do it?
Here is the servlet:
[code]
public synchronized void SortStaff(Staff staff)throws StaffException
{
int result = 0;
java.sql.PreparedStatement pstmt = null; try
{
pstmt = conn.prepareStatement("select * from records order by id");
pstmt.setString(1,staff.getStaffName()); pstmt.setDouble(2,staff.getStaffSalary()); pstmt.setInt(3,staff.getStaffID());
result = pstmt.executeUpdate(); }
catch(SQLException se)
{
throw new StudentException("Sort Staff error");
}
finally
{
try { if(pstmt!=null)pstmt.close();}
catch (Exception e) { throw new StudentException("Sort Staff error"); }
}
if(result < 1)
throw new StudentException("Sort Staff error"); }
Moreover, how should I click sort by id, name or salary and go to the proper try block?
Thanks for help.
Andrew
Hartmut Ludwig
Ranch Hand

Joined: Aug 31, 2002
Posts: 51
Originally posted by Andrew Parker:

Moreover, how should I click sort by id, name or salary and go to the proper try block?

First of all you should be able to use the same Method for all three sorting tasks.
So you need to write a sorting-Method that takes the field you want to sort as a String parameter.
This parameter will be taken from the URL-Request (by adding it to the URL that calls the servlet).
The link would point to something like:
http://www.example.com/myServlet?sorting=salary
You can now get the parameter "sorting" within the servlet and use it within the SQL-Statement. You have chosen to use a prepared statemet. This is generally a good choice, but you did not use it as intended.
Try to find out more about prepared statements. They offer the ability to enter question marks which are replaced by the user input.
You can use a question mark in the "order by" clause of your statement and replace it with the given order parameter.
One more suggestion:
It makes no sense to put a finally-block within a try-block. Finally is done in the end of processing after try and cach have been done.
So first handle catch-blocks and than add a finally block in the very end.
You could also close the statements in try and catch directly and don't need any finally at all.
good luck
Hartmut
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to do sorting with mysql?