Best approach for Java/Maven/JPA/Hibernate build with multiple database vendor support?

Question

I have an enterprise application that uses a single database, but the application needs to support mysql, oracle, and sql*server as installation options. To try to remain portable we are using JPA annotations with Hibernate as the implementation. We also have a test-bed instance of each database running for development.

The app is building nicely in Maven, and I've played around with the hibernate3-maven-plugin and can auto-generate DDL for a given database dialect.

What is the best way to approach this so that individual developers can easily test against all three databases and our Hudson based CI server can build things propertly.

More specifically:

1) I thought the hbm2ddl goal in the hibernate3-maven-plugin would just generate a schema file, but apparently it connects to a live database and attempts to create the schema. Is there a way to have this just create the schema file for each database dialect without connecting to a database?

2) If the hibernate3-maven-plug insists on actually creating the database schema, is there a way to have it drop the database and recreate it before creating the schema?

3) I am thinking that each developer (and the hudson build machine) should have their own separate database on each database server. Is this typical?

4) Will developers have to run Maven three times...once for each database vendor? If so, how do I merge the results on the build machine?

5) There is a hbm2doc goal within hibernate3-maven-plugin. It seems overkill to run this three times...I gotta believe it'd be nearly identical for each database.

Answer 1

1) Is there a way to have this just create the schema file for each database dialect without connecting to a database?

Set export to false. Something like this:

<plugin>
  <groupId>org.codehaus.mojo</groupId>
  <artifactId>hibernate3-maven-plugin</artifactId>
  <version>2.2</version>
  <configuration>
    <components>
      <component>
        <name>hbm2ddl</name>
        <implementation>annotationconfiguration</implementation>
      </component>
    </components>
    <componentProperties>
      <export>false</export><!-- do not export to the database -->
      <drop>true</drop>
      <configurationfile>src/main/resources/hibernate.cfg.xml</configurationfile>
      <outputfilename>my_schema.ddl</outputfilename>
    </componentProperties>
  </configuration>
</plugin>

2) If the hibernate3-maven-plug insists on actually creating the database schema, is there a way to have it drop the database and recreate it before creating the schema?

See above. But just in case, for this set update to true:

<plugin>
  <groupId>org.codehaus.mojo</groupId>
  <artifactId>hibernate3-maven-plugin</artifactId>
  <version>2.2</version>
  <configuration>
    <components>
      <component>
        <name>hbm2ddl</name>
        <implementation>annotationconfiguration</implementation>
      </component>
    </components>
    <componentProperties>
      <export>true</export>
      <update>true</update><!-- update the schema -->
      <drop>true</drop>
      <configurationfile>src/main/resources/hibernate.cfg.xml</configurationfile>
      <outputfilename>my_schema.ddl</outputfilename>
    </componentProperties>
  </configuration>
  <dependencies>
    <dependency>
      <groupId>org.apache.derby</groupId>
      <artifactId>derbyclient</artifactId>
      <version>10.5.3.0_1</version>
    </dependency>
  </dependencies>
</plugin>

3) I am thinking that each developer (and the hudson build machine) should have their own separate database on each database server. Is this typical?

Yes, and I consider this as a best practices (see Use one database instance per developer).

4) Will developers have to run Maven three times...once for each database vendor? If so, how do I merge the results on the build machine?

Yes, very likely and I would use profiles for each database. On the build machine, I would build a matrix project.

5) There is a hbm2doc goal within hibernate3-maven-plugin. It seems overkill to run this three times...I gotta believe it'd be nearly identical for each database.

I'm not used to this tool but I guess there might be some little variation in the output (e.g. with the primary key generation). I would generate the schema documentation for each database but at release time only (there is certainly no need to run that at each build).