Hi All,

I am in a situation where I need to add an @IndexColumn annotation to a List that only had @CollectionOfElements previously. Adding the annotation is easy, the difficult part is writing the sql migration to insert the correct values into the new IndexColumn.

For instance the table underlying the property used to look like this:

--------------------------------
| DataRow_id | element |
--------------------------------
| 1 | foo |
| 1 | bar |
| 1 | baz |
| 2 | aaa |
| 2 | bbb |
| 2 | ccc |
--------------------------------

After adding the @IndexColumn(name="dataindex") annotation I need it to look like this so hibernate reads and orders the data correctly:

----------------------------------------------
| DataRow_id | element | dataindex |
----------------------------------------------
| 1 | foo | 0 |
| 1 | bar | 1 |
| 1 | baz | 2 |
| 2 | aaa | 0 |
| 2 | bbb | 1 |
| 2 | ccc | 2 |
----------------------------------------------

The best I've been able to do in sql alone (across oracle, mysql, sqlserver & derby databases) is this:

----------------------------------------------
| DataRow_id | element | dataindex |
----------------------------------------------
| 1 | foo | 1 |
| 1 | bar | 2 |
| 1 | baz | 3 |
| 2 | aaa | 4 |
| 2 | bbb | 5 |
| 2 | ccc | 6 |
----------------------------------------------

However, the issue with this data is that hibernate loads the second row as a List of size 7, with elements 0-3 being null, instead of a List of
size 3 no null elements. I can write some java code to "patch-up" the dataindex values at this point, but I would much prefer making the migration in sql alone to avoid a support headache with some customers.

Any help someone may have crafting the query to create the dataindex values for hibernate's @IndexColumn semantics would be greatly appreciated.

Thanks in advance,
Evan Leonard

Tried that in mysql5.0

I have a table test with column id,element, dataidx
I suppose the table in your last situation ie dataix goes from 1 to 6.
I assume that for a given id( or dataRow_id) I get a dataIndex in a specific range with no holes (like 4 5 6 and not like 4 7 9)

I need to duplicate table test in test2 because mysql doesn't support having a select on the same table than the update



You can drop the duplicate table.

Hope it helped.

That's great. I have to make a couple small changes to guarantee your assumption, but after that your suggestion works great. Thank so much.

-Evan