Hello All,
I’m using Hibernate 3 with a legacy application on an oracle9i database. We are persisting data that has a field named "node order." The data represents a hierarchy tree (similar a file tree like windows explorer).

I would like to create a SortedSet of 3 items and have their order persisted. For example, I’d like the application to persist.

(DB contents)Name sort-order:
Item-A-1 1
Item-A-2 2
Item-A-3 3
Item-B-1 1
Item-B-2 2

(A and B represent different levels on the hierarchy and are contained in different Sets)


I don’t want to use an oracle sequence because the user has the option of rearranging the items or inserting new items after their creation.

My old JDBC code knew when a batch began and ended, so it kept an internal variable to represent sort-order and assigned it to the DB.

I cannot use a trigger due to error ORA-04091, which won’t allow me to select from table I’m updating.

Is there functionality built into hibernate to represent sort order in collections?

If not, is there a means of inserting a query value into my mapping to assign the following to “node order?”
SELECT COALESCE(MAX(node_order), 0)+1 FROM my_table WHERE root_id = <my new root_id>;

Can I map a field to a DB function?

Is there an Oracle hack to do this? It seems to be quite difficult to get an Oracle trigger to let you read from a table you’re inserting into.

Thanks in advance,
Steven

Could you use a list instead of a set? Lists are ordered and there is functionality in Hibernate to maintain the ordering. Look at index mapping here:

http://www.hibernate.org/hib_docs/v3/re ... ns-mapping