Auxiliary database objects

Hibernate's DDL export tool allows us to generate the SQL from our hbm mappings. However it is possible that we need to execute some additional sql that cannot be generated from the Hibernate mapping files. One way to do is by using the import.sql file. We can add all our triggers/procedures and other sql objects in this file and manage this file. Hibernate will execute this file as part of the database creation process.The other method is to create auxiliary database objects.
These allow for the CREATE and DROP of arbitrary database objects such as procedures or functions. The object is specified in the hbm file.

<?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="com.sql.index">
    <class name="User">
        <id name="id" type="long">
            <generator class="native" />
        </id>
        <property name="firstName">
            <column name="FIRST_NAME" />
        </property>        
        
    </class>
    <database-object>
        <create>
            create table User_History (
                id bigint not null auto_increment,
                First_Name varchar(255),
                Last_Name varchar(255),
                primary key (id)
            )
        </create>
        <drop>
            drop table if exists User_History
        </drop>
        <dialect-scope name="org.hibernate.dialect.MySQLDialect"></dialect-scope>
    </database-object>
</hibernate-mapping>

The database-object element is used here to create an additional User_History table, that is not mapped in the hbms. The create and drop elements specify the appropriate SQL statements. If we were to run the hbm Exporter tool, the SQL generated is as follows:

    drop table if exists User_History
    drop table if exists User
    create table User (
        id bigint not null auto_increment,
        FIRST_NAME varchar(255),
        primary key (id)
    )
    create table User_History (
         id bigint not null auto_increment,
        First_Name varchar(255),
        Last_Name varchar(255),
        primary key (id) 
    )

There is also a <dialect-scope> element that allows us to specify the databases on which the SQL generated must be executed. In our <database-object> we have specified only the MySqlDialect. This means that the sql inside the database-object will be executed only if the database used is MySQL. This is useful if we need to execute certain sql on specific databases only.
If we need more control over this functionality we have the AbstractAuxiliaryDatabaseObject