When you distribute your application a common task is to create the database tables and prefill them with some static data. You could do this using an SQL script but this complicates the installation process, especially if you use an embedded database such as Derby. By utilizing DdlUtils and a Jetty life cycle you can automate this whole process, creating the database just before your application starts.
DdlUtils reads the table definitions and static data from XML files you distribute along with your application. The easiest way to create those XML’s is to generate them from an existing database with an Ant task.
The build.xml example below generates two XML files (ddl.xml & dml.xml) out of a MySQL database.
Take note that the Maven 2 Ant tasks are used to construct the classpath. If you don’t use Maven 2, make sure ddlutils and your jdbc driver are on the classpath.
<project name="eHourDdlExport"
default="database-dump"
basedir="."
xmlns:artifact="antlib:org.apache.maven.artifact.ant">
<artifact:dependencies pathId="dependency.classpath">
<dependency groupId="org.apache.ddlutils" artifactId="ddlutils" version="1.0"/>
<dependency groupId="log4j" artifactId="log4j" version="1.2.13"/>
<dependency groupId="mysql" artifactId="mysql-connector-java" version="5.0.5"/>
</artifact:dependencies>
<target name="database-dump" description="Dumps the database structure">
<taskdef name="databaseToDdl" classname="org.apache.ddlutils.task.DatabaseToDdlTask">
<classpath refid="dependency.classpath"/>
</taskdef>
<databaseToDdl modelName="MyModel">
<database url="jdbc:mysql://127.0.0.1/db" driverClassName="com.mysql.jdbc.Driver" />
<writeSchemaToFile outputFile="src/main/resources/db/ddl.xml"/>
<writeDataToFile outputFile="src/main/resources/db/dml.xml"/>
</databaseToDdl>
</target>
</project>
Now that the data you want to preload is exported into a portable XML format it’s time to configure Jetty.
First register a JDNI datasource in the jetty.xml. In this example I’m using Derby with the create flag set since DdlUtils
can’t auto-create the database itself.
<New id="Datasource"
class="org.mortbay.jetty.plus.naming.Resource">
<Arg>jdbc/eHourDS</Arg>
<Arg>
<New class="org.apache.derby.jdbc.EmbeddedDataSource">
<Set name="DatabaseName">ehourDb</Set>
<Set name="createDatabase">create</Set>
</New>
</Arg>
</New>
Next thing is adding a new Jetty life cycle. Since Jetty executes the life cycles in the order they’re listed in
the jetty.xml, add it before the WebAppDeployer lifecycle (or whichever you use to boot up your main application)
as you want the database to be prepared before the actual application boots.
<Call name="addLifeCycle">
<Arg>
<New class="net.rrm.ehour.db.DbValidatorLifeCycle" />
</Arg>
</Call>
Creating the DbValidatorLifeCycle is easy, extend Jetty’s AbstractLifeCycle and override the doStart method. The JNDI
tree is already available so the datasource can be plucked from there.
public class DbValidatorLifeCycle extends AbstractLifeCycle
{
public void doStart() throws Exception
{
DataSource dataSource = getDataSource();
checkDatabaseState(dataSource);
}
private DataSource getDataSource() throws NamingException
{
InitialContext initialContext = new InitialContext();
DataSource datasource = (DataSource)initialContext.lookup("jdbc/eHourDS");
return datasource;
}
private void checkDatabaseState(DataSource datasource)
{
..
}
}
The final part is using DdlUtils to create the tables and load the data into the database.
The ddl.xml and dml.xml files were created by the ant task so make sure you distribute them along
with your application.
private checkDatabaseState(DataSource dataSource)
{
Connection connection = dataSource.getConnection();
Platform platform = PlatformFactory.createNewPlatformInstance(dataSource);
// create the tables
Database ddlModel = readModelFromXML("db/ddl.xml");
platform.createTables(ddlModel, false, false);
// insert the static data
DatabaseDataIO dataIO = new DatabaseDataIO();
DataReader dataReader = dataIO.getConfiguredDataReader(platform, ddlModel);
dataReader.getSink().start();
dataIO.writeDataToDatabase(dataReader, new File(""db/dml.xml").getAbsolutePath());
}
In this example the current state of the datamodel is not checked so if there’s any existing data
already in the model you might end up with some conflicts when you try to recreate the tables.
One solution is of course to drop everything beforehand but this is probably not preferred when you want to
retain existing data during an upgrade.
A possible solution is to version your datamodel. Create a separate ‘configuration’ table which holds a
record containing the version of the existing datamodel. Now based on the existence of the version you
can decide whether you have to do a platform.createTables or a platform.alterTables when the version doesn’t match.
Please test a bit and let me know if you ran into any problems.