Can I create both MyISAM and InnoDB tables in the same database using Hibenrate hbm2ddl

Question

I need both MyISAM tables and InnoDB tables in my database, I am using hbm2ddl to create them. Can I create both MyISAM and InnoDB tables in the same database using Hibenrate hbm2ddl? It seems that selecting the dialect forces me to use one or the other.

Answer 1

Well, as you wrote, Hibernate will generate InnoDB tables if you use the MySQL5InnoDBDialect:

public class MySQL5InnoDBDialect extends MySQL5Dialect {

    public boolean supportsCascadeDelete() {
        return true;
    }

    public String getTableTypeString() {
        return " ENGINE=InnoDB";
    }

    public boolean hasSelfReferentialForeignKeyBug() {
        return true;
    }

}

Using this dialect will cause Hibernate to add the ENGINE=InnoDB at the end of the CREATE TABLE statements. But this is global setting, you can't tweak this behavior at the entity level.

To use hbm2ddl and mix both tables engines, my suggestion would be to ALTER specific tables after the facts. To do so, you could use 5.7. Auxiliary database objects. From the documentation:

5.7. Auxiliary database objects

Auxiliary database objects allow for the CREATE and DROP of arbitrary database objects. In conjunction with Hibernate's schema evolution tools, they have the ability to fully define a user schema within the Hibernate mapping files. Although designed specifically for creating and dropping things like triggers or stored procedures, any SQL command that can be run via a java.sql.Statement.execute() method is valid (for example, ALTERs, INSERTS, etc.). There are essentially two modes for defining auxiliary database objects:

The first mode is to explicitly list the CREATE and DROP commands in the mapping file:

<hibernate-mapping>
    ...
    <database-object>
        <create>CREATE TRIGGER my_trigger ...</create>
        <drop>DROP TRIGGER my_trigger</drop>
    </database-object>
</hibernate-mapping>

The second mode is to supply a custom class that constructs the CREATE and DROP commands. This custom class must implement the org.hibernate.mapping.AuxiliaryDatabaseObject interface.

<hibernate-mapping>
    ...
    <database-object>
        <definition class="MyTriggerDefinition"/>
    </database-object>
</hibernate-mapping>

Additionally, these database objects can be optionally scoped so that they only apply when certain dialects are used.

<hibernate-mapping>
    ...
    <database-object>
        <definition class="MyTriggerDefinition"/>
        <dialect-scope name="org.hibernate.dialect.Oracle9iDialect"/>
        <dialect-scope name="org.hibernate.dialect.Oracle10gDialect"/>
    </database-object>
</hibernate-mapping>

Another option would be to (ab)use the Hibernate import.sql feature to perform the ALTER.