Custom schema generation with Hibernate annotations

For a Java developer, Hibernate 3 annotations offer a terrific way to model the domain layer. Without much effort, you can get Hibernate to generate your database schema automatically, dispensing entirely with SQL scripts. Back in the real world, however, you still need to account for the sometimes obscure naming conventions used by your database administrator. In this article, Java Power Tools author John Ferguson Smart shows you how to generate database schemas automatically with Hibernate while still making your DBA happy.

Hibernate 3 annotations are a great way to manage your database persistence. With annotations, you don’t need to bother with XML mapping files, and you can leverage the usually sensible default behavior to drastically reduce the amount of code you need to maintain. Even better, Hibernate comes with a great tool that lets you generate the database schema automatically. So Hibernate creates and updates the database schema all by itself, and you don’t have to worry about arcane SQL scripts.

First steps: Updating your database schema

Getting Hibernate to update your database schema automatically is easy enough. All you need to do is to set the hibernate.hbm2ddl.auto property, as shown in Listing 1.

Listing 1. Configuring Hibernate to update your database schema automatically

<hibernate-configuration>
   <session-factory>       
      <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>
      <property name="hibernate.hbm2ddl.auto">create-drop</property>
      ...
      <!-- Persistent classes -->
      <mapping class="com.mycompany.myapp.domain.Client"/>
      <mapping class="com.mycompany.myapp.domain.Order"/>
      ...
   </session-factory>   
</hibernate-configuration>

Setting this property to create-drop will create a brand-new database each time you start the application, which is great for integration tests, but not so hot anywhere else. If you set this value to update, on the other hand, Hibernate will only create the database if it doesn’t already exist, and will update any existing tables to match your current domain model.

Now, by default, Hibernate will generate a set of tables and fields that look very much like your Java classes, which, from your point of view as a Java developer, is just fine. Consider for example the simple annotated persistent class in Listing 2.

Listing 2. A simple persistent class

@Entity
public class Client implements Serializable {


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

    private String firstName;

    private String lastName;
    ...
}

For this class, Hibernate would by default generate an SQL schema along the lines of what you see in Listing 3.

Listing 3. The SQL table generated for the Client class using the default Hibernate options

create table Client (
        id bigint generated by default as identity (start with 1),
        firstName varchar(255),
        lastName varchar(255),
        ...
        primary key (id)
    );

Ye old naming conventions

This is all very well and good, and will work fine. But then come your local friendly DBAs, who will be glad to tell you about their set of database naming conventions — conventions that date from before the demise of the dinosaurs, and that aren’t about to change! These database conventions usually have quite valid reasons to exist, and in any case DBAs tend to be a conservative lot. What’s a developer to do?

One simple solution is to use the name attribute of the @Entity and @Column annotations to override the default name generation, as shown in Listing 4.

Listing 4. Annotating the Client class to customize the generated SQL schema

@Entity(name="T_CLIENT")
public class Client implements Serializable {
    ...
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="CLIENT_ID")
    private Long id;

    @Column(name="FIRST_NAME")
    private String firstName;

    @Column(name="LAST_NAME")
    private String lastName;
    ...
}

This will work, but it becomes a wee bit tiresome when you have a lot of tables. Indeed, you have to remember to do it for each and every table and field. There has to be a better way, right? Well, there is! You can define a naming strategy in your Hibernate session factory to override the default behavior. This basically involves writing a class that tells Hibernate how you want table and field names formatted.

A good place to start is the ImprovedNamingStrategy class, which basically converts camel case class names (SomeDomainEntity) to names in lower case with underscores (some_domain_entity). You need to provide this class when you create your Hibernate session at startup. If you are using Spring, you simply create a naming strategy bean and provide it to the session factory. Listing 5 illustrates what a typical Spring configuration would look like.

Listing 5. Configuring automatic schema generation in Spring

    
 <bean id="sessionFactory" 
           class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
       <property name="dataSource" ref="dataSource" />
       <property name="configLocation" value="classpath:/hibernate.cfg.xml" />
       <property name="configurationClass" value="org.hibernate.cfg.AnnotationConfiguration" />
       <property name="namingStrategy" ref="namingStrategy" />
    </bean>   
    
    <bean id="namingStrategy" class="org.hibernate.cfg.ImprovedNamingStrategy"/>

With this naming strategy, Hibernate would generate a script that looks something like the one in Listing 6.

Listing 6. The SQL script created using the Spring Hibernate configuration

    create table client (
        id bigint generated by default as identity (start with 1),
        first_name varchar(255),
        last_name varchar(255),
        ...
        primary key (id)
    );

That’s nice, but it doesn’t always solve all your problems. Typically, database naming conventions are more demanding. For example, each table might have to start with T_ and be in uppercase (T_CLIENT for the Client class, for example). Or each field in a table might need to start with a table-specific prefix (CLI_FIRST_NAME and CLI_LAST_NAME, for instance). To automate this sort of constraint, you need to write your own naming strategy implementation.

The custom naming strategy implementation

Probably the easiest way to do this is to extend the ImprovedNamingStrategy class. This class provides some useful default implementations, so you only have to implement the methods that you really need.

You can override the tableName() and columnName() methods for general-purpose formatting tasks, such as putting everything in upper case. These methods are called whenever Hibernate generates a table or column name, even when you specify the column names in the annotations. The addUnderscores() method, inherited from the ImprovedNamingStrategy class, can come in handy here, as shown in Listing 7.

Listing 7. A simple customized naming strategy

public class MyNamingStrategy extends ImprovedNamingStrategy implements NamingStrategy {

    @Override
    public String columnName(String columnName) {
        return addUnderscores(columnName).toUpperCase();
    }

    @Override
    public String tableName(String tableName) {
        return addUnderscores(tableName).toUpperCase();
    }
}

Next, you might want to override classToTableName() and propertyToColumnName(), which convert your class and member variables. The fairly simple example in Listing 8 demonstrates how to add a T_ prefix to table names and convert property names to upper case with underscores.

Listing 8. Adding a T_ prefix to your table names

@Override
    public String classToTableName(String className) {
        return "T_" + tableName(className);
    }
    
    @Override
    public String propertyToColumnName(String propertyName) {
        return addUnderscores(propertyName).toUpperCase();
    }

A more complex naming strategy

The NamingStrategy interface is actually rather simple, and limited in many ways. For one thing, you cannot tell from the method parameters what table you are dealing with at any given time. This could be limiting if, for instance, you need to add a table-specific prefix to the name of each column, as some database conventions require. You can get around this limitation to some extent by adding a member variable to store the current table, which you assign in the classToTableName() method. For a given table, this method will be called before propertyToColumnName() is called. For example, the code in Listing 9 creates a three-letter prefix for a table and adds it both to the table name and to the name of each column in the table.

Listing 9. A more complex naming strategy implementation

public class MyNamingStrategy extends ImprovedNamingStrategy implements NamingStrategy {

    private String currentTablePrefix;

    @Override
    public String classToTableName(String className) {
        currentTablePrefix = className.substring(0, 3).toUpperCase() + "_";
        return "T" + currentTablePrefix + tableName(className);
    }

    @Override
    public String propertyToColumnName(String propertyName) {
        return currentTablePrefix + addUnderscores(propertyName).toUpperCase();
    }

    @Override
    public String columnName(String columnName) {
        return addUnderscores(columnName).toUpperCase();
    }

    @Override
    public String tableName(String tableName) {
        return addUnderscores(tableName).toUpperCase();
    }
}

Using this new naming strategy, Hibernate would generate something like the code in Listing 10.

Listing 10. The Client table generated using the more complex naming strategy

create table TCLI_CLIENT (
        CLI_ID bigint generated by default as identity (start with 1),
        CLI_FIRST_NAME varchar(255),
        CLI_LAST_NAME varchar(255),
        ...
        primary key (CLI_ID)
    );

Foreign keys

Foreign keys, which are in general difficult to automate, pose a trickier problem. By default, Hibernate generates arbitrary foreign key names like “FKAB1273D65CCF7AB”, which are not likely to endear you to your DBA. To get around this, you need to use the @ForeignKey annotation, as shown in Listing 11.

Listing 11. Overriding a foreign key name

@Entity
    public class Order {
        ...
        @JoinColumn(name = "CLIENT_ID")
        @ManyToOne(optional = false)
        @ForeignKey(name = "FK_CLIENT_ORDERS")
        private Client client;
        ...
    }

Many-to-many relationships

Of course, with complex relationships (such as many-to-many relationships), things become a bit more complicated. For example, the class in Listing 12 represents a social networker, who naturally has lots of friends. In this case, you need to use annotations like @JoinTable and @ForeignKey, complete with both the name and inverseName attributes, to get the DDL generation working properly.

Listing 12. Working with many-to-many relationships

@Entity
    public class SocialNetworker {
        @ManyToMany
        @JoinTable(name = "TFRD_FRIEND",
                   joinColumns = {@JoinColumn(name = "NETWORKER_ID") },
                   inverseJoinColumns = {@JoinColumn(name = "FRIEND_ID") } 
        )
        @ForeignKey(name = "FK_SNT_FRIENDS", 
                    inverseName="FK_FRD_FRIENDS")  
        }
        private Set<SocialNetworker> friends = new HashSet<SocialNetworker>();
        ...
    }

Show me the SQL!

This is fine for development and integration tests, and maybe even user acceptance testing platforms, but even the friendliest of DBAs may hesitate to let you update a production database automatically. At some point, the database team will want to look at an SQL script of some kind. Yet again, Hibernate has an answer, in the form of the SchemaExport tool. You can use this tool to generate your schema in plain old SQL.

Of course, this is the sort of thing you really want to do as part of the build process. If you are using Maven, for example, you can use the hibernate3-maven-plugin to generate the database schema automatically. The key component properties here, shown in Listing 13, are create (which creates the schema) and format (which pretties it up for the DBAs). You can set drop and export to false, as you’re not updating the database itself at this point.

Listing 13. Generating the database creation script

<plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>hibernate3-maven-plugin</artifactId>
                <version>2.1</version>
                <executions>
                    <execution>
                        <phase>process-classes</phase>
                        <goals>
                            <goal>hbm2ddl</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <components>
                        <component>
                            <name>hbm2ddl</name>
                            <implementation>annotationconfiguration</implementation>
                        </component>
                        <component>
                            <name>hbmdoc</name>
                        </component>
                    </components>
                    <componentProperties>
                        <configurationfile>/target/classes/hibernate.cfg.xml</configurationfile>
                        <outputfilename>schema.ddl</outputfilename>
                        <namingstrategy>mycompany.myapp.IRDNamingStrategy</namingstrategy>
                        <drop>false</drop>
                        <create>true</create>
                        <export>false</export>
                        <format>true</format>
                    </componentProperties>
                </configuration>
            </plugin>

This will generate an SQL script that you can then give to your DBAs.

In conclusion

In large organizations, DBA naming conventions are here to stay. If you want to work with them, you will need to take into account their conventions. Luckily, this doesn’t automatically mean that you have to give up your Hibernate-generated database schemas and go back to hand-cut SQL. By using a combination of naming strategies and annotations, you can have the best of both worlds.