Persisting Ordered Domain Objects

A ver开发者_JAVA技巧y common use case in many web applications is that domain objects can be ordered by a user in the web interface - and that order is persisted for later; but I've noticed that every time I need to implement this, I always end up coming up with a solution that is different and adds considerable weight and complexity to a simple domain object. As an example, suppose I have the persistent entity Person

Person {
   long id
   String name
}

A user goes to /myapp/persons and sees all people in the system in the order in which they receive compensation (or something) - all of the people can be clicked and dragged and dropped into another position - when the page is loaded again, the order is remembered. The problem is that relational databases just don't seem to have a good way of doing this; nor do ORMs (hibernate is what I use)

I've been thinking of a generic ordering methodology - but there will be some overhead as the data would be persisted separately which could slow down access in some use cases. My question is: has anyone come up with a really good way to model the order of persistent domain objects?

I work in JavaEE with Hibernate or JDBCTemplate - so any code examples would be most useful with those technologies as their basis. Or any conceptual ideas would be welcome too.

UPDATE: I'm not sure where I went wrong, but it seems I was unclear as most have responded with responses that don't really match my question (as it is in my head). The problem is not how do I order rows or columns when I fetch them - it is that the order of the domain objects change - someone clicks and drags a "person" from the bottom of the list to the top of the list - they refresh the page and the list is now in the order they specified.

---

When fetching results, just build HQL queries with a different ORDER BY clause, depending on what the user has used last time

---

The problem is that relational databases just don't seem to have a good way of doing this; nor do ORMs (hibernate is what I use)

I'm not sure where you would get this impression. Hibernate specifically has support for mapping indexed collections (which is a "list" by another name), which usually boils down to storing a "list-index" column in the table holding the collection of items.

An example taken directly from the manual:

<list name="carComponents"
    table="CarComponents">
    <key column="carId"/>
    <list-index column="sortOrder"/>
    <composite-element class="CarComponent">
        <property name="price"/>
        <property name="type"/>
        <property name="serialNumber" column="serialNum"/>
    </composite-element>
</list>

This would allow a List<CarComponents> to be associated with your root entity, stored in the CarComponents table with a sortOrder column.

---

One possible generic solution:

Create a table to persist sort information, simplest case would be be one sortable field per entity with a direction:

table 'sorts'
* id: PK
* entity: String
* field: String
* direction: ASC/DESC enumeration (or ascending boolean flag)

It could be made more complicated by adding a userId to do per-user sorting or by adding a sort_items table with a foreign key to support sorting by multiple fields at a time.

Once you're persisting the sort information, it's a simple matter of adding Order instances to criteria (if that's what you're using) or concatenating order by statements to your HQL.

This also keeps your entities themselves free of and ordinal information, which in this case sounds like the right approach since the ordering is purely for user interaction purposes.

Update - Persisting entity order Given the fact that you want to be able to reorder entities, not just define a sort for them, then you really do need to make an ordinal or index value part of the entity's definition.

The problem, as I'm sure you realize is the number of entities that would need to be updated, with the worst case scenario being moving the last entity to the top of the list.

You could use an increment value other than 1 (say 10) so you would have:

ordinal | name
10      | Crosby
20      | Stills
30      | Nash
40      | Young

Most of the time, updating the row would involve selecting two items and updating one. If I want to move Young to position 2, I select current item 2 and the previous item from the database to get the ordinals 10 and 20. Use these to create the new ordinal ((20 - 10) / 2 + 10 = 15). Now do a single update of Young with an ordinal of 15.

If you get to the point where division by two yields the same index as one of the entities you just loaded, that means it's time to spawn a task to normalize the ordinal values according to your original increment.

---

As far as I know, JPA 2.0 provides support for ordered lists: https://secure.wikimedia.org/wikibooks/en/wiki/Java_Persistence/Relationships#Order_Column_.28JPA_2.0.29

---

I think that relational databases cannot do better than a dedicated ordering column.

The idea of "order" is not really defined in SQL for anything but cursors, and they are not a core relational concept but rather an implementation detail.

For all I know the only thing to do is to abstract the ordering column away with @OrderColumn (JPA2, so Hibernate 3.5+ compatibile).