Eclipselink read complex object model in an ordered way

Question

Hi,

I need to read a complex model in an ordered way with eclipselink. The order is mandantory because it is a huge database and I want to have an output of a small portion of the database in a jface tableview. Trying to reorder it in the loading/quering thread takes too long and ordering it in the LabelProvider blocks the UI thread too much time, so I thought if Eclipselink could be used that way, that the database will order it, it might give me the performance I need. Unfortunately the object model can not be changed :-(

The model is something like:

@SuppressWarnings("serial")
@Entity
public class Thing implements Serializable {
     @Id
     @GeneratedValue(strategy = GenerationType.TABLE)
     private int id;
     private String name;
     @OneToMany(cascade=CascadeType.ALL)
     @PrivateOwned
     private List<Property> properties = new ArrayList<Property>();
     ...
     // getter and setter following here
}
public class Property implements Serializable { 
    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    private int id;

    @OneToOne
    private Item item;

     private String value;
     ...
     // getter and setter following here
}
public class Item implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.TABLE)
    private int id;
    private String name;
     ....
     // getter and setter following here    
}
// Code end

In the table view the y-axis is more or less created with the query

Query q = em.createQuery("SELECT m FROM Thing m ORDER BY m.name ASC");

using the "name" attribute from the Thing objects as label.

In the table view the x-axis is more or less created with the query

Query q = em.createQuery("SELECT m FROM Item m ORDER BY m.name ASC");

using the "name" attribute from the Item objects as label.

Each cell has the value

Things.getProperties().get[x].getValue()

Unfortunately the list "properties" is not ordered, so the combination of cell value and x-axis column number (x) is not necessarily correct. Therefore I need to order the list "properties" in the same way as I ordered the labeling of the x-axis.

And exactly this is the thing I dont know how it is done. So querying for the Thing objects should return the list "properties" "ORDER BY name ASC" but of the "Item"s objects. My ideas are something like having a query with two JOINs. Joing Things with Property and with Item but somehow I was unable to get it to work yet.

Thank you for your help and your ideas to solve this riddle.

Answer 1

Try the @OrderBy JPA annotation. Something like

@OrderBy('name ASC')
@OneToMany(cascade=CascadeType.ALL)
private List<Property> properties = new ArrayList<Property>();

Answer 2

May be the answer to this other question could help you: http://stackoverflow.com/questions/1105099/defining-the-order-of-a-list

I think you may have to use another query to get the list of properties order by item.name for each thing.

Something like:

SELECT p FROM Property p WHERE p.thing = ?1 ORDER BY p.item.name