Enterprise Java Development@TOPIC@
This chapter will take you through steps that will populate your database with a (simple) database schema. A database schema is required by any module that directly interacts with a RDMBS. The JPA provider can automatically generate a database schema but that is generally restricted to early development and quick prototypes. A module within the data tier will ultimately be responsible for providing a separate artifact the create and/or migrate the schema from version-to-version. That is typically finalized by humans knowledgable about particular databases and can be aided by tool(s) we introduce in this exercise.
Create a set of ddl scripts in src/main/resources/ddl to handle creating the schema, deleting rows in the schema, and dropping tables in the schema. Make sure each script has the word "create", "delete", or "drop" in its file name to match some search strings we'll use later. Have the database generate a value for the primary key. That value should not be allowed to be null.
`-- src
|-- main
| |-- java
| `-- resources
| |-- ddl
| | |-- emauto_create.ddl
| | |-- emauto_delete.ddl
| | `-- emauto_drop.ddl
`-- test
|-- java
`-- resources We could actually skip this step and have the persistence provider create the table for us. That approach is great for quick Java-first prototypes. However, creating the schema outside of the persistence provider is a more realistic scenario for larger developments.
# src/main/resources/ddl/emauto_create.ddl
CREATE TABLE EM_AUTO (
ID BIGINT generated by default as identity (start with 1) not null,
MAKE VARCHAR(32),
MODEL VARCHAR(32),
COLOR VARCHAR(32),
MILEAGE INT,
CONSTRAINT em_autoPK PRIMARY KEY(ID)
)
# src/main/resources/ddl/emauto_delete.ddl
DELETE FROM EM_AUTO;
# src/main/resources/ddl/emauto_drop.ddl
DROP TABLE EM_AUTO if EXISTS;You can perform a sanity check of the above scripts by pasting them into the DB UI SQL area and executing.
Add the standard database setup and teardown scripts. This allows us to create a legacy database schema and write classes that map to that schema. We will later have the persistence provider create the schema for us when we are in quick prototype mode. First create the reusable portion of the definition in the pluginManagement section. This will define the version, database dependencies, and property information for all to inherit.
<build>
<pluginManagement>
<plugins>
...
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>sql-maven-plugin</artifactId>
<version>${sql-maven-plugin.version}</version>
<dependencies>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>${h2db.version}</version>
</dependency>
</dependencies>
<configuration>
<username>${jdbc.user}</username>
<password>${jdbc.password}</password>
<driver>${jdbc.driver}</driver>
<url>${jdbc.url}</url>
</configuration>
</plugin>
</plugins>
</pluginManagement>
</build>
Next add the potentially project-specific portion to a build-plugins-plugin section that would normally be in the child module. However, when you add this to the module -- do so within a profile that is wired to always run except when the system property -DskipTests is defined. This is a standard maven system property that builders use to build the module and bypass both unit and integration testing. By honoring the property here -- our module will only attempt to work with the database if we ware not skipping tests. Note the !bang-not character means "the absence of this system property".
<profiles>
...
<profile>
<id>testing</id>
<activation>
<property>
<name>!skipTests</name>
</property>
</activation>
<build>
<plugins>
<plugin>
<!-- runs schema against the DB -->
<groupId>org.codehaus.mojo</groupId>
<artifactId>sql-maven-plugin</artifactId>
<executions>
<!-- place execution elements here -->
</executions>
</plugin>
</plugins>
</build>
</profile>
</profiles>
Configure the sql-maven-plugin executions element to run any drop scripts in the source tree before running tests.
<execution>
<id>drop-db-before-test</id>
<phase>process-test-classes</phase>
<goals>
<goal>execute</goal>
</goals>
<configuration>
<autocommit>true</autocommit>
<fileset>
<basedir>${basedir}/src</basedir>
<includes>
<include>main/resources/ddl/**/*drop*.ddl</include>
</includes>
</fileset>
<onError>continue</onError>
</configuration>
</execution>
Note that we are controlling when the scripts are executed using the phase element. This is naming a well known Maven lifecycle phase for the build.
Configure the sql-maven-plugin executions element to run any scripts from the source tree to create schema before running tests.
<execution>
<id>create-db-before-test</id>
<phase>process-test-classes</phase>
<goals>
<goal>execute</goal>
</goals>
<configuration>
<autocommit>true</autocommit>
<fileset>
<basedir>${basedir}/src</basedir>
<includes>
<include>main/resources/ddl/**/*create*.ddl</include>
</includes>
</fileset>
<print>true</print>
</configuration>
</execution>
Configure the sql-maven-plugin executions element to run any populate scripts from the source tree to add rows to the database before running tests.
<execution>
<id>populate-db-before-test</id>
<phase>process-test-classes</phase>
<goals>
<goal>execute</goal>
</goals>
<configuration>
<autocommit>true</autocommit>
<fileset>
<basedir>${basedir}/src</basedir>
<includes>
<include>test/resources/ddl/**/*populate*.ddl</include>
</includes>
</fileset>
</configuration>
</execution>
Configure the sql-maven-plugin executions element to run any drop scripts after testing. You may want to comment this out if you want to view database changes in a GUI after the test.
<execution>
<id>drop-db-after-test</id>
<phase>test</phase>
<goals>
<goal>execute</goal>
</goals>
<configuration>
<autocommit>true</autocommit>
<fileset>
<basedir>${basedir}/src</basedir>
<includes>
<include>main/resources/ddl/**/*drop*.ddl</include>
</includes>
</fileset>
</configuration>
</execution>
Build and run the tests. The schema should show up in the DB UI.
$mvn clean test -P\!h2db -Ph2srv
Remember to turn off (-P!profile-name) the embedded profile (h2db) if active by default and turn on the server profile (h2srv) if you wish to use the server and DB UI while the unit test is running. The DB UI can only inspect the embedded file once all active clients close the file. The backslash is only needed for commands from the bash shell.
In this chapter you added a (trivial) schema definition for your module. This schema definition was used to manage (create, delete, and drop) the schema within the database. Although we will show that schema can be generated automatically by the JPA persistence provider and managed at runtime -- this feature is only feasible for functional unit testing and quick prototypes. Any real application will require a separate database schema artifact finalized by developers to be tuned appropriately for the target database.