• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to do sorting with mysql?

 
Ranch Hand
Posts: 178
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
He loves you so much! And I'm baking the cake! I'm going to put this tiny ad in the cake:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic