Maven Plugins for Manipulating Databases

Some projects have database in their infrastructure, and deploying these projects involves building database schema and initializing some tables with default data.

Using Maven, you can get great help in this point. Maven can help you in building and manipulating your project database through its POM file configuration and in your defined order of execution.

Maven built on plugins. You add plugins configuration into your POM file as your build script needs. Maven has a great set of database manipulation plugins, for example:

1. Mojo, SQL Maven Plugin


2. Torque, Maven2-plugin

I recommend the first one for simple usage, Mojo SQL Maven Plugin. It is great and simple plugin for basic database manipulation. You can create your SQL script file(s) and define an execution configuration inside the plugin tag and configure it to run after specific maven lifecycle phase.

You can have more than one execution, everyone have its own lifecycle phase to run after and have its SQL script files set to run against the configured DB.

For example, the next configuration will run after the “package” phase and execute the given SQL script file against the given mysql DB configuration:

<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>sql-maven-plugin</artifactId>
<version>1.0</version>
<dependencies>
 <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.0.5</version>
 </dependency>
</dependencies>
<configuration>
    <driver>com.mysql.jdbc.Driver</driver>
    <autocommit>true</autocommit>
</configuration>
<executions>
    <execution>
        <id>deploy-schema-data</id>
        <phase>package</phase>
        <goals>
            <goal>execute</goal>
        </goals>
             <configuration>
                <url>jdbc:mysql://localhost/test-db</url>
                <username>root</username>
                <password>root</password>
                <srcFiles>
   <srcFile>
    src/main/resources/sql/init-data.sql
   </srcFile>
                </srcFiles>
             </configuration>
    </execution>
</executions>
</plugin>