aspose file tools*
The moose likes Object Relational Mapping and the fly likes OrderBy Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "OrderBy" Watch "OrderBy" New topic
Author

OrderBy

Neil Barton
Greenhorn

Joined: Aug 24, 2011
Posts: 7
I have created an application in Netbeans. In one of the modules it shows a list of suppliers. I have created a combobox, bound it to a database table and it shows all of the entries in the table. But they are in the order of the database (i.e. identifier order), I want them to be in Alphabetical order. How do I change the order?

I've tried going into the persistence class where it shows the query:
@NamedQuery(name = "Supplierdb.findAll", query = "SELECT s FROM Supplierdb s"),

and I've tried adding an ORDER BY statement to that. I've also tried various formats of :orderby but I can't find any real description of how to use that.

It seems like a simple thing to do - how do you do it?

Thanks,
Neil
Unnar Björnsson
Ranch Hand

Joined: Apr 30, 2005
Posts: 164
I would think that the easiest way would be to sort the result with SQL, I don't know why it is not working for you.
SELECT s FROM Supplierdb s

Im no SQL expert but this query looks strange, it should be or with ordering
Wouter Oet
Saloon Keeper

Joined: Oct 25, 2008
Posts: 2700

And in JPQL it is something like SELECT s FROM Supplierdb s ORDER BY s.name.


"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." --- Martin Fowler
Please correct my English.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39435
    
  28
Are you putting those values into a List or a Vector or an array, for the combo box? If so, you can use methods of the java.util.Collections and java.util.Arrays classes to sort those arrays/Collections.
Neil Barton
Greenhorn

Joined: Aug 24, 2011
Posts: 7
I agree, SQL is the easiest option but when I put an order by on the query it is ignored. I know it is running that query because if I put rubbish in it fails. The data when returned is going straight into the copmbo button as it is bound to it. I can't find a way to get to any collection to sort it. The persistence bean was built by connecting to the database and importing the data from the database. I then bound it to the combo button. As I say, it works fine but the order is wrong. This is the code that has been generated :
@Entity
@Table(name = "supplier")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "Supplierdb.findAll", query = "SELECT s FROM Supplierdb"),
@NamedQuery(name = "Supplierdb.findBySupplierID", query = "SELECT s FROM Supplierdb s WHERE s.supplierID = :supplierID"),
@NamedQuery(name = "Supplierdb.findByName", query = "SELECT s FROM Supplierdb s WHERE s.name = :name"),
@NamedQuery(name = "Supplierdb.findByNotes", query = "SELECT s FROM Supplierdb s WHERE s.notes = :notes"),
@NamedQuery(name = "Supplierdb.findByStatus", query = "SELECT s FROM Supplierdb s WHERE s.status = :status")})

I tried putting an order by clause on the end of the 'findall' query but, as I say, it is ignored. I have found some syntax for an rderby statement but I cannot find anywhere that the compiler will allow this. I've been following this tutorial to get me this far: http://netbeans.org/kb/docs/java/gui-binding.html

Any help appreciated, it seems a shame to get htis far and be defeated by such a stupid thing!

Thanks,
Neil
Neil Barton
Greenhorn

Joined: Aug 24, 2011
Posts: 7
Still haven't solved it! I got round it before but want to do it again in another app. I followed the tutuorial to get this far so I have:
Created an entity class called category as a persistent entity class from the database. That appears to have the SQL in it that is responsible.
Created a combobox and bound it to the categoryList class that is created using the 'importdata to form' button
Created a renderer to display it

The result is great and it shows all the categories as held in the DB - but they are not sorted.

How do I sort them? I tried putting an ORDER BY clause on the SQL in the Category entity class so it reads: "SELECT c FROM Category ORDER BY c.name" but it ignores it. "Order by 1" has no effect either. Where do I need to put the order by clause to affect the sort?


 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: OrderBy