Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to do sorting with mysql?

 
Andrew Parker
Ranch Hand
Posts: 178
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic