How to create multi-column index using HBM2DDL

fbos · **Posted:** Sun Dec 28, 2003 2:17 pm

Hi,

I have a schema which would benefit from multi-column indexes. The table in question has fields like user_id, lastName, firstName, etc.

I offer the user the ability to filter his contacts using any of lastName, firstName, etc. All searches are related to the specific user doing the search.

My .hbm.xml file looks like this:

Code:

    <class name="Contact" proxy="Contact" table="contacts">
        <id name="id" column="contact_id" type="long">
            <generator class="native"/>
        </id>

        <component name="name" class="Name">
            <property name="lastName" column="lastname" type="string" length="40"/>
            <property name="firstName" column="firstname" type="string" length="40"/>
            <property name="middleInitial" column="middleinitial" type="string" length="4"/>
        </component>
        <property name="firm" column="firm" type="string" length="80"/>
        <many-to-one name="user" column="user_id" not-null="true"/>
    </class>

    <query name="contacts.filteredByName"><![CDATA[
        from Contact c
        where c.user.id = :user_id
            and (lower(c.name.lastName) like lower(:startsWith)
                or lower(c.name.firstName) like lower(:startsWith)
                or lower(c.firm) like lower(:startsWith))
        order by c.name.lastName asc, c.name.firstName asc, c.firm asc
    ]]></query>

The indexes I would like to create would be on user_id + lastName, user_id + firstName, etc.

Is there a way to specify such multi-column indexes in my mappings file, such that the hbm2ddl generator will pick these up and generate the appropriate SQL statements ?

I can always create the indexes manually, but I thought it would be nice if I could do it once and for all in my mapping file.

I am using Hibernate 2.1.1 on JDK 1.4.2 with MySQL 3.23 as the back-end, on Tomcat 4.1.28.

I searched the web using Google, and the Hibernate forums, and the only items returned are concerning Map mappings with multi-column indexes.

Thanks !
Fran

gavin · **Posted:** Wed Dec 31, 2003 6:43 am

Just add index="index_name" to <column> elements.

fbos · **Posted:** Wed Dec 31, 2003 9:38 am

Great !

Thanks for the tip.

In the end, this is what my hbm.xml file looks like:

Code:

<hibernate-mapping>
    <class name="Contact" proxy="Contact" table="contacts">
...
        <many-to-one name="user" not-null="true">
            <column name="user_id" index="by_user_lastname"/>
            <column name="user_id" index="by_user_firstname"/>
            <column name="user_id" index="by_user_middleinitial"/>
            <column name="user_id" index="by_user_firm"/>
        </many-to-one>

        <component name="name" class="jobnudge.Name">
            <property name="lastName" type="string" length="40">
                <column name="lastname" index="by_user_lastname"/>
            </property>
            <property name="firstName" type="string" length="40">
                <column name="firstname" index="by_user_firstname"/>
            </property>
            <property name="middleInitial" type="string" length="4">
                <column name="middleinitial" index="by_user_middleinitial"/>
            </property>
        </component>

        <property name="firm" type="string" length="80">
            <column name="firm" index="by_user_firm"/>
        </property>
...
    </class>
<hibernate-mapping>

I don't think this is mentionned in the user documentation. Should I post a patch in the Hibernate JIRA ?

Thanks !
Fran

emmanuel · **Posted:** Wed Dec 31, 2003 11:16 am

fbos wrote:

I don't think this is mentionned in the user documentation. Should I post a patch in the Hibernate JIRA ?

Yes please

fbos · **Posted:** Wed Dec 31, 2003 11:18 am

Submitted a patch as issue HB-591
http://opensource.atlassian.com/project ... key=HB-591