MySQL index creation using JPA (Hibernate and Batoo)

Question

I am experimenting indexes generation using JPA. I already know JPA specifications themselves does not contemplate a standard way to generate indexes, but (some) providers support this feature. I am testing the newly born Batoo (http://batoo.jp/) agains Hibernate and I am curious about an unexpected (for my company) behaviour. We have 2 classes, firstly defined without any index and on which we defined two indexes in a second time. When we looked at the generated tables we found out that no index was actually created. Then we made an exact copy of those two classes with the indexes immediately in place and those indexes were reflected on the db. Here the important configuration and class files.
The jpa-beans.xml (for Hibernate)

<?xml version="1.0" encoding="UTF-8"?>
<beans:beans profile="hibernate" xmlns:beans="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns="http://www.springframework.org/schema/data/jpa"
  xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/data/jpa
    http://www.springframework.org/schema/data/jpa/spring-jpa.xsd">

    <beans:import resource="classpath:spring/jpa-common.xml"/>

    <beans:bean id="entityManagerFactory"
            class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <beans:property name="dataSource" ref="dataSource" />
        <beans:property name="jpaVendorAdapter">
            <beans:bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                <beans:property name="database" value="MYSQL" />
                <beans:property name="showSql" value="true" />
            </beans:bean>
        </beans:property>
        <beans:property name="persistenceUnitName" value="jpa.sample" />
        <beans:property name="persistenceXmlLocation" value="classpath:META-INF/persistence.xml"/>
        <beans:property name="jpaProperties">
            <beans:props>
                <beans:prop key="hibernate.enable_lazy_load_no_trans">true</beans:prop>
                <beans:prop key="hibernate.hbm2ddl.auto">update</beans:prop>
            </beans:props>
        </beans:property>
    </beans:bean>

    <beans:bean class="org.springframework.orm.jpa.JpaTransactionManager" id="transactionManager">
        <beans:property name="entityManagerFactory" ref="entityManagerFactory" />
        <beans:property name="jpaDialect">
            <beans:bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect" />
        </beans:property>
    </beans:bean>

</beans:beans>

For Batoo, we simply change the jpaVendorAdapter to our custom one and the jpaProperties map in order to use the properties provided by Batoo.

The annotated Java classes:
Address (the uniqueConstraints in @Table annotation was added when the table already existed on the DB) and CopyOfAddress (the uniqueConstraints in @Table annotation immediately there). setter and getter omitted.

@Entity
@Table(uniqueConstraints={
    @UniqueConstraint(name="uniqueAddress", columnNames={
        "zipCode", "street", "number", "city", "country"
    })
})
public class Address extends BaseEntity
{
  private String zipCode;
  private String street;
  private String number;
  private String city;
  private String country;

  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  Long id;

  @ManyToOne(cascade={CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH})
  private User user;
  ...
}

User and CopyOfUser

@Entity
public class User extends BaseEntity
{
  @Column(unique=true)
  private String username;

  private String fullName;

  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  private Long id;
  ...
}

We expected the table created on the db to be identical, but here the dump obtained using mysqldump (just the relevant create table statements):

CREATE TABLE `Address` (
  `zipCode` varchar(255) DEFAULT NULL,
  `street` varchar(255) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `number` varchar(255) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `id` bigint(20) DEFAULT NULL,
  KEY `FK_A2CD7CE3` (`user_id`),
  CONSTRAINT `FK_A2CD7CE3` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `CopyOfAddress` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `city` varchar(255) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `number` varchar(255) DEFAULT NULL,
  `street` varchar(255) DEFAULT NULL,
  `zipCode` varchar(255) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `zipCode` (`zipCode`,`street`,`number`,`city`,`country`),
  KEY `FKA0BC6A08E9A4D67F` (`user_id`),
  CONSTRAINT `FKA0BC6A08E9A4D67F` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `User` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `username` varchar(255) DEFAULT NULL,
  `fullName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `CopyOfUser`;

CREATE TABLE `CopyOfUser` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `username` varchar(255) DEFAULT NULL,
  `fullName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

As you can see, in both User and Address no indexes are created, while they are in the exact copies. Does anyone know why? And, as a side question, does anyone know why the name we provided for the index in CopyOfAddress is not considered?

EDIT
If it may help, I can add I am using Hibernate 4.1.7.Final and Batoo 2.0.1.0-RTM.

Answer 1

I dig into more details using some log, and I found out that Hibernate (don't know about Batoo) creates indexes only on table creation, whereas it alters table only in case of (and with the sole purpose of) adding new columns (it does not delete existing ones even if the existing db table contains no data). Here some log to prove this:

Creating a new table from scratch, with unique constraints in place

DEBUG [tool.hbm2ddl.SchemaUpdate] create table CopyOfCopyOfAddress (id bigint not null auto_increment, city varchar(255), country varchar(255), number varchar(255), street varchar(255), zipCode varchar(255), user_id bigint, primary key (id), unique (zipCode, street, number, city, country))
DEBUG [tool.hbm2ddl.SchemaUpdate] alter table CopyOfCopyOfAddress add index FK50EC293CE9A4D67F (user_id), add constraint FK50EC293CE9A4D67F foreign key (user_id) references User (id)

Adding a new column to an existing table, where unique constraints are set up into the java file but not into the db

DEBUG [tool.hbm2ddl.SchemaUpdate] alter table Address add column zipCode2 varchar(255)

In this second case, I am expecting to see also a line like alter table Address add unique(zipCode, street, number, city, country); (remember Address.java), so it becomes obvious Hibernate will not add unique constraints (at least using standard JPA annotations) to existing table!
If I am not proved wrong, I will soon accept this as the answer since I don't know what else to test and I don't find any documentation.