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

Question

I have a corporate application that uses one database, but the application must support mysql , oracle and sql * server as installation options.

To try and stay portable, we use JPA annotations with Hibernate as an implementation. We also have a testbed instance for each database working for development.

The application works fine in Maven , and I played with hibernate3-maven-plugin and can automatically generate DDL for this dialect of the database.

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

More specific:

• I thought the hbm2ddl target in hibernate3-maven-plugin would just generate a schema file, but apparently it connects to a live database and tries to create a schema. Is there a way for this to simply create a schema file for each dialect of the database without connecting to the database?

• If hibernate3-maven-plugin insists on actually creating the database schema, is there a way to reset the database and recreate it before creating the schema?

• I think that every developer (and the Hudson build machine) should have their own separate database on each database server. Is this typical?

• Will developers run Maven three times ... once for each database manufacturer? If so, how do I combine the results on an assembly machine?

• In hibernate3-maven-plugin there is a hbm2doc target. It seems too difficult to run this three times ... I believe that it will be almost identical for each database.

Answer 1

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

Set export to false . Something like that:

 <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 hibernate3-maven-plug insists on actually creating the database schema, is there a way to reset the database and recreate it before creating the schema?

See above. But just in case, for this set update is 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 think that every developer (and the hudson build machine) should have their own separate database on each database server. Is this typical?

Yes, and I think this is the best method (see Use one database instance for each developer ).

4) Will developers run Maven three times ... once for each database manufacturer? If so, how do I combine the results on an assembly machine?

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

5) In hibernate3-maven-plugin there is a hbm2doc target. It seems too difficult to run this three times ... I believe that it will be almost identical for each database.

I'm not used to this tool, but I think that there may be a slight change in the output (for example, with primary key generation). I would generate schematic documentation for each database, but only at release time (of course, there is no need to run it in each assembly).