OrderBy

[Neil Barton]

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]

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 SELECT column_name, ... FROM table_name or with ordering SELECT column_name, ... FROM table_name ORDER BY column_name

[Wouter Oet]

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

[Campbell Ritchie]

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]

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]

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?