Creating an Index on a Property using the mapping file

markricard · **Posted:** Sun Feb 26, 2006 7:22 pm

We are having some difficulty getting the index="xx" attribute to actually create an index in mysql. Below is the mapping file. Pay particular attention to the "username" property.

Code:

<?xml version="1.0" encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
                            "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
                            "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping package="foo.data.user">

    <class name="User" table="user">
        <id name="key" column="oid" type="foo.hibernate.user.types.HiberUserKeyType" unsaved-value="null" >
            <generator class="foo.hibernate.user.types.UserKeyGenerator"/>
        </id>
 
        <property name="firstName" column="firstName" type="foo.hibernate.user.types.HiberFirstNameType" not-null="true" />
        <property name="lastName" column="lastName" type="foo.hibernate.user.types.HiberLastNameType" not-null="true" />
        <property name="username" column="username" type="foo.hibernate.user.types.HiberUsernameType" index="userName" not-null="true"/>

    </class>
    
</hibernate-mapping>

and we also tried this version :

Code:

      <property name="username" type="foo.hibernate.user.types.HiberUsernameType">
         <column name="username"   not-null="true"   length="255" index="IDX_USERNAME"/>
      </property>

After running code that uses this to insert a row into the newly created mysql table, a careful inspection of the resulting table structure shows that an index was never created for that property with that name. We delete the table and rerun with the same result.

Code:

        Session session = HibernateSessionFactory.currentSession();
        User user = User.create();
        user.setUsername(Username.create("fidjhkheo"));
        user.setFirstName(FirstName.create("ahkjhj"));
        user.setLastName(LastName.create("khkkjhha"));
        
        Transaction t = session.beginTransaction();
        session.save(user);
        t.commit();

No exceptions, no output other than the insert sql (show_sql="true"). We are using MySQL 5 with Hibernate3.jar. We expected Hibernate to create this table and index for us because we have hibernate.hbm2ddl.auto="update" in the main cfg file.

Any help greatly appreciated!

kgignatyev · **Posted:** Tue Feb 28, 2006 6:06 pm

My usual suggestion: do NOT use Hibernate's schema management features, control the DB schema yourself with specialized tool like ErWin or DeZign.

Rin DB creation/initialization/update/population scripts from Ant build file like this:

<target name="sql-script">
<property name="script" value="set-script-name"/>
<property name="onerror" value="abort"/>
<available file="src/main/etc/db/${jdbc.db}/${script}" property="db" value="${jdbc.db}"/>
<property name="db" value="common"/>
<sql driver="${jdbc.driver}" classpathref="build.classpath" onerror="${onerror}"
userid="${jdbc.user}"
password="${jdbc.password}"
url="${jdbc.url}"
src="src/main/etc/db/${db}/${script}"
/>
</target>

<target name="db-init" description="create DB schema">
<antcall target="sql-script">
<param name="onerror" value="continue"/> 
<param name="script" value="drop_db.sql"/>
</antcall>
<antcall target="sql-script">
<param name="script" value="create_db.sql"/>
</antcall>
<antcall target="sql-script">
<param name="script" value="init_db.sql"/>
</antcall>
<echo>Please run 'db-populate' task to insert test data</echo>
</target>