Hibernate version:2.1
I have not been able to order the columns of my database tables the way I want.

For example, in the mapping shown below I have mapped my attributes in the following order:
S_ID, SUPER_S_ID, SUPER_S_LIST_INDEX, S_ALLOWED_T, SELECTED_T_ID

In my database, however, I get the following order (note: S_ALLOWED_T is represented by a separate table):

S_ID, SUPER_S_ID, SELECTED_T_ID, SUPER_S_LIST_INDEX

This means that the SELECTED_T_ID attribute which I expected to be the last column of the table is placed between the two different SUPER_S columns... and of course I would like the two SUPER_S columns to be adjecent. Could somebody please give me a hint?

Randahl



[...]
<class name="dk.x.S" table="S">
<id name="id" type="integer" unsaved-value="null" >
<column name="S_ID" not-null="true"/>
<generator class="native"/>
</id>
[...]
<list name="subelements" lazy="true">
<key column="SUPER_S_ID"/>
<index column="SUPER_S_LIST_INDEX"/>
<one-to-many class="dk.x.S"/>
</list>
<many-to-one
name="superElement"
class="dk.x.S"
column="SUPER_S_ID"
unique="true"
/>
<set name="allowedT" table="S_ALLOWED_T">
<key column="S_ID"/>
<many-to-many
class="dk.x.T"
column="T_ID"
/>
</set>
<many-to-one
name="selectedT"
class="dk.x.T"
column="SELECTED_T_ID"
unique="false"
/>
</class>
[...]

eh? there is no ordering of columns in the relational model. I don't understand what you are talking about.

Thanks for your reply. To clarify, let me give you a simple example.

If I use SQL to create a table customer with attributes "id, name, bonus" and if I then retrieve all my customers using "select * from customer" my database client will definately show a table similar to this one

ID NAME BONUS
0 Hansen 2973
1 Lundbye 3030
2 Robin 1404

This is exactly what I expect. I would have been quite surprised had it shown a table similar to this one

BONUS ID NAME
...

When I create the table I mention in my post, I do not get my columns ordered the way I want. As mentioned, the column I expect to come last is placed between two other columns - why is that?

Thanks

Randahl

You should never use SELECT *, except for very informal ad hoc queries.

In the relational model, there is no "column order", and in the long run it is not really possible to maintain any ordering of columns, since the ALTER TABLE command does not specify any ordering.

You should think of the order the columns are displayed in when you type SELECT * as -completely- arbitrary. Yes, most databases will probbaly display them in the same order as your CREATE TABLE command, but you should think of this as "coincidence".

Thanks. The reason I asked in the first place is this: When you work with test data and write SQL scripts which put new test data into your database, it is nice to have a sensible column ordering. In my customer example I could write a simple script like this

insert into CUSTOMER values (0, 'Randahl', 930)

Now, if hibernate does not create tables with a sensible column ordering, developing these test data scripts is a little harder because the schema is harder to remember.

I do not disagree, that from a theoretical point of view, you do not need to think of the order of columns, but from a practical one I think there are many cases where having a sensible column order makes a lot of sense.

Another example could be external systems accessing your database through JDBC. The developers of those external systems will probably complain if your tables look all messed up, because the will be writing SQL scripts to interact with the database.

But, as you mentioned, specifying a sensible column ordering is not supported. Do you think it will be supported in 3.0?

Thanks again for answering my question.

Randahl

As far as I can tell, its not really possible to do this. Columns in the table could come from different places in the mapping document (a class mapping, a collection mapping, in HB3 a join mapping). So the "order" would depend upon the order in which mapping documents were read.

I mean, Table already uses a SequencedHashMap for the collection of columns, so they are just going in in the order they are read out of the mapping document.

Without knowing the internals of hibernate, I was thinking of a pragmatic solution along the lines of

<column name="NAME" column-index="3"/>

which would simply place the name column as the third column in the table.

Of course then hibernate would have to throw an exception if the table had only two columns or if two columns had the same index. Nevertheless, this would be trivial to use; however, I do not know if it would be trivial to implement...


Randahl