Java | Execute SQL script using Maven

|
| By Webner

Whenever there is a change in the database whether we are altering database structure or adding/updating data inside database most of the times we create a script file and run it manually. We can automate this process using Maven.

Solution: Maven provides a plugin called flyway-maven-plugin to automate database upgrades.

Steps used to implement flyway-maven-plugin:

1.  Add following dependency to your pom.xml file:

<plugin>
<groupId>com.googlecode.flyway</groupId>
<artifactId>flyway-maven-plugin</artifactId>
<version>2.2.1</version>
<configuration>
<driver>com.mysql.jdbc.Driver</driver>
<url>database url</url>
<user>database username</user>
<password>database password </password>
</configuration>
<executions>
<execution>
<phase>compile</phase>
<goals>
<goal>migrate</goal>
</goals>
</execution>
</executions>
</plugin>

2.  Create sql files containing your queries inside src/main/resources/db/migration directory of your maven project. Sql file names should follow certain naming conventions for flyway maven plugin to work. There are two parts of the sql file name separated with double underscore (__). The first half of the name before (__) is used by maven as version number to find out whether the script has been executed already or not. Second half of the name after (__) is any name for the file.

First-time maven will run all the scripts but after that only new ones will be run and this will be tracked by the name of the sql file:

For Example:

If you are running migration first time, let’s create a file with name V1__initial_version.sql.
First letter (V) and (__) are compulsory, rest you can name it in your own way:

V1_0_12__custom_name_1_.sql
V1_0_12__custom_name_2_.sql
V1_0_12__custom_name_3_.sql
V1_0_12__custom_name_4_.sql etc.

There is a table inside database called schema_version which os be used by flyway plugin to find out new sql files.

As you can see in the screenshot the table contains version and the script fields. Version field contains the version number and Script field contains the name of the sql file. So if the file name is V1_0_12__custom_name_1.sql then 1.0.12 is the version number stored inside version column.

3.  Run following command to execute scripts against database:

Command:

mvn compile flyway:migrate

Flyway will run all new migrations to your database. All new migrations run during compilation of the project as well.

Leave a Reply

Your email address will not be published. Required fields are marked *