I'm getting ready to move into production on a Hibernate-based application. As part of that move, we are moving from a MySQL database to DB2/AS400. Hibernate made this a snap - so first of all, thanks for such an awesome tool.

For now, I'm creating the database using the <schemaexport> ant task - which reads from my XDoclet-generated .hbm.xml files. The DBA on the 400 noticed that no indexes where created, which makes sense. When I run <schemaexport> in MySQL, I see indexes being created, but I'm assuming those are simply for the foreign key constraints?

I know its possible to map columns to pre-existing indexes, but I'm wondering if its possible to generate those indexes in the first place (using Ant/XDoclet/etc.)? Or do all indexes (particularly multi-column) need to be created manually?

Thanks,

Matt

There is an index attribute. Check the DTD

I saw that, and looking at the DTD:



It looks like it can only be applied to columns. I guess I'm looking for a @hibernate.index tag or something that'll create an index on the commonly accessed columns. For example:



Or should this type of "index creation" be left to the DBA?

Thanks,

Matt

<!ATTLIST column index CDATA #IMPLIED>

You specify the name of the index on all three columns.

I'll interpret that as:

"No, Hibernate's <schemaexport> task cannot create multi-column indexes for you. You can map your columns to a multi-column index, but it cannot create them for you."

Thanks,

Matt

Of course it can, just as gavin said, put an index element with the same name on all included properties.

Wrong interpretation.

SchemaExport will create a multi-column index for you if specify index attributes.

Nice. To create and use an index, I had to change:

@hibernate.property column="username" not-null="true"

to

@hibernate.property
@hibernate.column name="username" index="indexname" not-null="true"

Thanks,

Matt

Hi guys,

I'm pretty new to hibernate, from your conversation I understand how I can create a one-to-one index.

I was wondering does hibernate support mapping of one column to multiple indexes? such as:

column1
/**
* @hibernate.property
* @hibernate.column
* name="column1"
* index="index1"
**/

column2
/**
* @hibernate.property
* @hibernate.column
* name="column2"
* index="index1"
* index="index2"
**/


What I want to achieve is essentially the following script:
CREATE UNIQUE INDEX index1 ON store (
column1 ASC,
column2 ASC
);

CREATE INDEX index2 ON store (
column2 ASC
);

Thanks in advance.

Not possible at present.

Wow that was quick, thanks for the prompt reply, Gavin.

One more thing, is it possible to say assign the id as index? if I were to use

@hibernate.id
generator-class="assigned"

mraible wrote :


In Oracle, indexes are implicitly created only for Primary and Unique keys and not for Foreign keys. However for MySQL versions 4.1.2 and above they seem to be created implicitly for FK's as well.



As a general rule, all foreign keys should be indexed.

Sanjiv

Is there any plan for making this possible? (Or has it since become possible, say in Hibernate 3?) We have a lot of use cases for a single column in multiple multi-column indexes.

If not, should I make a JIRA entry for it?

Cheers,
Rob

you may now use a comma-separated list of index names

In Hibernate 3 only, or is this also supported in Hibernate 2.1.8?

Thanks,
Rob