How to order my DB results using servlets??

[Andres Gonzalez]

Hi ranchers: here's the problem:
I perform a database connection on my servlets, retrieve some records, create a bean for every record, put it in a vector and then in the session.
In other words, a vector of beans. Now, when I get to the JSP i take the vector and display all the information I need. And it's working ok. The results are not too many, so I discard having problems with big results (thousands).
Now I wanna add a new feature. The user should be able to order the result either by date or price. So my question is: In case the user selects for example, date, should I forward the request again to the servlet, perform a database connection AGAIN using ORDER BY, populate my vector AGAIN and do the same processing that I did in the first stage?
I think it's a waste of time but can't find any other solution, knowing that I have all the results in my vector in the session.
any suggestions? is this the way to do it?

thanks..

[William Brogden]

If I understand your problem correctly, you can certainly sort your stored beans without having to go back to the database. Use an array of beans instead of a Vector, create a Comparator that works with your beans, and use it with the Arrays class static sort( Object[], Comparator ) method. All in the java.util package.
I would worry about memory problems if your beans are not Serializable.
Bill

[Thomas Paul]

If you have set up the comparator, you can run the static Collections.sort method against the vector.
Collections.sort(myVector)
This article has a brief explanation of using Comparators to sort objects in a Collection:
http://www.javaranch.com/newsletter/July2002/newsletterjuly2002.jsp#collections

[Andres Gonzalez]

 

Originally posted by Thomas Paul:
If you have set up the comparator, you can run the static Collections.sort method against the vector.
[/URL]

Against the vector or against the array??
If I set up an array, how do I define its size?

[Thomas Paul]

 

Originally posted by Andres Gonzalez:

Against the vector or against the array??
If I set up an array, how do I define its size?

Collections.sort() can run against any object that implements the List interface (that includes Vector).

[William Brogden]

You can get the data out of a Vector as an array of type Object[] by means of the toArray() method, thus automatically getting the right size. See the java.util package API for this and other alternatives.
Either the Collections operation on the original Vector (or ArrayList) or the Array operation on a derived array will work, and should be about the same speed.
Bill

[Andres Gonzalez]

Guys, it's not sorting . THis is what I've done:
my bean (ProductBean):

public class ProductBean extends Object implements java.io.Serializable, Comparable
{
public static final Comparator PRICE_ORDER = new Comparator( ) {
public int compare(Object o1, Object o2) {
ProductBean pr1 = (ProductBean)o1;
ProductBean pr2 = (ProductBean)o2;
return pr1.getItem_price().compareTo(pr2.getItem_price());
}
};
public int compareTo(Object obj) {
ProductBean p = (ProductBean)obj;
return item_price.compareTo(p.getItem_price());
}
//getter and setter methods......
private String item_price;
.........
...
}

Now, in my servlet I do this:

//database stuff...
Vector listResults = new Vector();
while (results.next())
{
listResults.add(makeBean(results,myBroker));
}
Collections.sort(listResults,ProductBean.PRICE_ORDER);

Notice that makeBean is a method that creates a ProductBean with the result from the database. It is added to the vector.
By the way, Thomas, in the article you sent me I think there's a right parenthesis missing ( ) ).

public class Employee implements Comparable {
public static final Comparator SOC_SEC_ORDER = new Comparator( ) {
public int compare(Object o1, Object o2) {
Employee emp1 = (Employee)o1;
Employee emp2 = (Employee)o2;
return emp1.getSocSecNum( ).compareTo(emp2. getSocSecNum( )[B]Here[/B];
}
};
public static final Comparator NAME_ORDER = new Comparator( ) {
public int compare(Object o1, Object o2) {
Employee emp1 = (Employee)o1;
Employee emp2 = (Employee)o2;
return emp1.getName( ).compareTo(emp2. getName( ) [B]Here[/B];
}
};
}

any suggestions..?
thanks

[Thomas Paul]

This line in the comparator seems a little odd:
return pr1.getItem_price().compareTo(pr2.getItem_price());
Is item_price a String? Because this should not work if price is a float. And the sort order will be odd if the price is a String.

You probably want something more like:
ProductBean pr1 = (ProductBean)o1;
ProductBean pr2 = (ProductBean)o2;
float f1 = pr1.getItem_price();
float f2 = pr2.getItem_price();
if (f1 == f2) return 0;
if (f1 > f2) return 1;
return -1;

[Andres Gonzalez]

Thank You So Much !@!! ..
another question.. How's the procedure to order by strings, if this
return pr1.getItem_name().compareTo(pr2.getItem_name());
might not work properly??

[Thomas Paul]

The problem isn't with ordering of strings. The problem is ordering numbers when they are stored as strings.
Look at this sequence which would be properly sorted for strings:
12,3456.99
4,5667.21
456.00
71.21
95,213.81
Looks odd but it is in correct string order.