«
»

Easy Database Migrations using Flyway, Java EE 6 and GlassFish

April 28th, 2013 by

Database migrations often are a necessity in the application development and maintenance life-cycle.

Whenever we need to apply changes to the database structure, insert new data fragments and in doing so want to be sure that this all happens with some control and versioning.

The following tutorial shows how implement this for a simple Java EE 6 web application to be run on a GlassFish application server in a few quick steps using the Flyway framework, an eager initialized Singleton EJB and some Maven wiring.


Contents

  1. Project Setup /Dependencies
    1. Basic Dependencies
    2. Embedded GlassFish Maven Plugin
  2. Creating a JEE Web Application
    1. Persistence Layer / JPA
    2. Repository Layer EJB
    3. Web Layer
  3. Adding Flyway Database Migrations
    1. Singleton EJB to run migrations
    2. SQL Script Migrations
      1. Creating a Table
      2. Adding books
      3. Changing the table schema
    3. Java File Migrations
  4. Quick Run with the Maven Embedded GlassFish Plugin
  5. Tutorial Sources
  6. Resources
  7. Alternative: Liquibase and WildFly
  8. Article Updates
 

Project Setup /Dependencies

Before we’re ready to start coding, we need to add some dependencies to our project .. the basic set includes the Java EE 6 API, a persistence provider (e.g. EclipseLink or Hibernate etc..) and – of course – the flyway framework.

Basic Dependencies

This is the basic set to compile and run the following examples..

<properties>
	[..]
	<flyway.version>2.1.1</flyway.version>
</properties>
<dependencies>
	<dependency>
		<groupId>org.glassfish.main.extras</groupId>
		<artifactId>glassfish-embedded-all</artifactId>
		<version>3.1.2.2</version>
		<scope>provided</scope>
	</dependency>
	<dependency>
		<groupId>org.hibernate</groupId>
		<artifactId>hibernate-entitymanager</artifactId>
		<version>4.2.0.Final</version>
	</dependency>
	<dependency>
		<groupId>com.googlecode.flyway</groupId>
		<artifactId>flyway-core</artifactId>
		<version>${flyway.version}</version>
	</dependency>
</dependencies>

Embedded GlassFish Maven Plugin

The Maven Embedded GlassFish Plugin allows us to easily startup a preconfigured GlassFish with our application deployed in our development environment and offers a lot options in its configuration.

To integrate the plugin into the project, we should add the following lines to our pom.xml.

One important thing here: Due to the fact that the latest Flyway version uses a Boolean for its migration schema in the database, I had to add a dependency to a more actual version of the Derby database to the GlassFish plugin. Otherwise it could use an old version of Derby where this data type is not supported.

Because I am lazy, I have also added a default goal that cleans, packages and runs the embedded GlassFish when we run mvn in the project directory ;)

<properties>
	<derby.version>10.9.1.0</derby.version>
</properties>
 
<build>
    <defaultGoal>clean package org.glassfish.embedded:maven-embedded-glassfish-plugin:3.1.2:run</defaultGoal>
	<plugins>
		<plugin>
			<groupId>org.apache.maven.plugins</groupId>
			<artifactId>maven-war-plugin</artifactId>
			<version>2.1.1</version>
			<configuration>
				<failOnMissingWebXml>false</failOnMissingWebXml>
			</configuration>
		</plugin>
		<plugin>
			<groupId>org.glassfish.embedded</groupId>
			<artifactId>maven-embedded-glassfish-plugin</artifactId>
			<version>3.1.2</version>
			<dependencies>
				<dependency>
					<groupId>org.apache.derby</groupId>
					<artifactId>derbyclient</artifactId>
					<version>${derby.version}</version>
				</dependency>
				<dependency>
					<groupId>org.apache.derby</groupId>
					<artifactId>derby</artifactId>
					<version>${derby.version}</version>
				</dependency>
			</dependencies>
			<configuration>
				<goalPrefix>embedded-glassfish</goalPrefix>
				<app>${basedir}/target/${project.artifactId}-${project.version}.war</app>
				<autoDelete>true</autoDelete>
				<port>8080</port>
				<name>flyway-tutorial</name>
			</configuration>
		</plugin>
	</plugins>
</build>

Creating a JEE Web Application

Now that we’ve assembled all dependencies we’re finally ready to create our JEE application and add some database migrations …

Persistence Layer / JPA

First of all we’re specifying our persistence layer using the Java Persistence API.

Therefore we’re creating a simple book entity. I have omitted getters, setters, hashCode and equals implementations here to keep it short…

package com.hascode.entity;
 
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
 
@Entity
public class Book {
	@Id
	@GeneratedValue
	private Long id;
 
	private String title;
 
	private String author;
 
	// getter, setter, hashCode, equals ...
}

Afterwards we’re configuring our persistence unit named hascode-jta-unit by adding the following persistence.xml to src/main/resources/META-INF:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
	version="1.0">
	<persistence-unit name="hascode-jta-unit"
		transaction-type="JTA">
		<provider>org.hibernate.ejb.HibernatePersistence</provider>
		<jta-data-source>jdbc/__default</jta-data-source>
		<class>com.hascode.entity.Book</class>
	</persistence-unit>
</persistence>

A word about the datasource .. jdbc/__default is a datasource that is already preconfigured starting the embedded GlassFish or creating a new domain based upon the default schema domain.xml so we don’t have to create any resources here.

When implementing a real world scenario one could add a configured domain.xml to the project and reference this configuration in the glassfish-plugin’s configuration to setup or gain access to other non-default datasources..

Repository Layer EJB

We’re going to model our repository layer using a stateless EJB and a service interface.

This is the service interface that abstracts the logic implemented in the stateless EJB (please don’t hit me, Adam Bien ;)

package com.hascode.ejb;
 
import java.util.List;
import com.hascode.entity.Book;
 
public interface BookService {
	public abstract List<Book> findAll();
}

This is our EJB .. its sole feature is to return a list of all available books from the database using an injected entity manager..

package com.hascode.ejb;
 
import java.util.ArrayList;
import java.util.List;
 
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
 
import com.hascode.entity.Book;
 
@Stateless
public class BookEJB implements BookService {
	@PersistenceContext(unitName = "hascode-jta-unit")
	private EntityManager em;
 
	@Override
	public List<Book> findAll() {
		List<Book> books = em.createQuery("SELECT b FROM Book b", Book.class)
				.getResultList();
		if (books == null)
			books = new ArrayList<Book>();
		return books;
	}
}

Web Layer

Last but not least. we’ll be adding a simple servlet to print a list of available books.

Because we’re using CDI for dependency injection here we must not forget to add an empty beans.xml to the directory src/main/webapp/WEB-INF!

The following servlet maps to the url part /books and simply prints all books returned from the repository layer..

package com.hascode.servlet;
 
import java.io.IOException;
import java.util.List;
 
import javax.inject.Inject;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import com.hascode.ejb.BookService;
import com.hascode.entity.Book;
 
@WebServlet(name = "bookServlet", urlPatterns = "/books", loadOnStartup = 1)
public class BookServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
 
	@Inject
	private BookService bookEJB;
 
	@Override
	protected void doGet(final HttpServletRequest req,
			final HttpServletResponse resp) throws ServletException,
			IOException {
		List<Book> books = bookEJB.findAll();
		if (books.isEmpty()) {
			resp.getWriter().append("no books");
			return;
		}
 
		resp.getWriter().append(
				books.size() + " books:\n---------------------------------\n");
		for (Book book : books) {
			resp.getWriter().append("- " + book.getTitle() + "\n");
		}
	}
}

Adding Flyway Database Migrations

Now to the last step – applying changes to the database using database migrations and flyway.

Singleton EJB to run migrations

We want flyway to check for new changes and execute them if necessary whenever our application is deployed or redeployed.

To hook into the JEE lifecycle is quite easy using a singleton EJB and declaring it to be eager initialized using @Startup .

The following EJB forwards the injected JDBC datasource to a new instance of the flyway framework, prints every migration step and initializes the database migration.

package com.hascode.ejb;
 
import java.util.logging.Logger;
 
import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.ejb.EJBException;
import javax.ejb.Singleton;
import javax.ejb.Startup;
import javax.sql.DataSource;
 
import com.googlecode.flyway.core.Flyway;
import com.googlecode.flyway.core.api.MigrationInfo;
 
@Singleton
@Startup
public class JeeDbMigrator {
	private final Logger log = Logger.getLogger(JeeDbMigrator.class.getName());
 
	@Resource(lookup = "jdbc/__default")
	private DataSource dataSource;
 
	@PostConstruct
	private void onStartup() {
		if (dataSource == null) {
			log.severe("no datasource found to execute the db migrations!");
			throw new EJBException(
					"no datasource found to execute the db migrations!");
		}
 
		Flyway flyway = new Flyway();
		flyway.setInitOnMigrate(true);
		flyway.setDataSource(dataSource);
		for (MigrationInfo i : flyway.info().all()) {
			log.info("migrate task: " + i.getVersion() + " : "
					+ i.getDescription() + " from file: " + i.getScript());
		}
		flyway.migrate();
	}
}

SQL Script Migrations

Adding SQL scripts for database migrations is simple: Just add a directory db/migration e.g. in src/main/resources and add a file in the format V<Number>__A_descriptive_title.sql to this directory.

Most times I’m using the current date in the format yyyyMMddhhmm as number e.g. V201304281820__I_added_some_books.sql.

We’re adding three SQL scripts here:

Creating a Table

Migration file added: V201304161230__Create_book_table.sql

CREATE TABLE Book (
 id INT PRIMARY KEY,
 title VARCHAR(200) NOT NULL
);
Adding books

Migration file added: V201304171620__Insert_books.sql

INSERT INTO Book (id, title) VALUES (1, 'The first book');
INSERT INTO Book (id, title) VALUES (2, 'Another book');
INSERT INTO Book (id, title) VALUES (3, 'The third book');
Changing the table schema

Migration file added: V201305011700__Alter_book_table_add_author.sql

ALTER TABLE Book ADD author VARCHAR(200);

Java File Migrations

In addition to simple SQL scripts it is also possible to create migrations using Java classes. We simply need to implement the JdbcMigration interface according to our needs.

The following Java migration adds another book to the database…

package db.migration;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
 
import com.googlecode.flyway.core.api.migration.jdbc.JdbcMigration;
 
public class V201305162030__Insert_additional_books implements JdbcMigration {
 
	@Override
	public void migrate(final Connection con) throws Exception {
		PreparedStatement statement = con
				.prepareStatement("INSERT INTO Book (id, title) VALUES (4, 'You wont believe it - the fourth book')");
		statement.execute();
	}
 
}

Finally, our project directory structure should look similar to this one:

$ tree
.
├── pom.xml
└── src
 └── main
     ├── java
     │   ├── com
     │   │   └── hascode
     │   │       ├── ejb
     │   │       │   ├── BookEJB.java
     │   │       │   ├── BookService.java
     │   │       │   └── JeeDbMigrator.java
     │   │       ├── entity
     │   │       │   └── Book.java
     │   │       └── servlet
     │   │           └── BookServlet.java
     │   └── db
     │       └── migration
     │           └── V201305162030__Insert_additional_books.java
     ├── resources
     │   ├── db
     │   │   └── migration
     │   │       ├── V201304161230__Create_book_table.sql
     │   │       ├── V201304171620__Insert_books.sql
     │   │       └── V201305011700__Alter_book_table_add_author.sql
     │   └── META-INF
     │       └── persistence.xml
     └── webapp
         └── WEB-INF
             └── beans.xml

Quick Run with the Maven Embedded GlassFish Plugin

We’re starting the embedded GlassFish by simply running

mvn

Now there should be lot of log output .. important are the following lines from the flyway framework:

Apr 28, 2013 6:14:50 PM com.hascode.ejb.JeeDbMigrator onStartup
INFO: migrate task: 201304161230 : Create book table from file: V201304161230__Create_book_table.sql
Apr 28, 2013 6:14:50 PM com.hascode.ejb.JeeDbMigrator onStartup
INFO: migrate task: 201304171620 : Insert books from file: V201304171620__Insert_books.sql
Apr 28, 2013 6:14:50 PM com.hascode.ejb.JeeDbMigrator onStartup
INFO: migrate task: 201305011700 : Alter book table add author from file: V201305011700__Alter_book_table_add_author.sql
Apr 28, 2013 6:14:50 PM com.hascode.ejb.JeeDbMigrator onStartup
INFO: migrate task: 201305162030 : Insert additional books from file: db.migration.V201305162030__Insert_additional_books
Apr 28, 2013 6:14:50 PM com.hascode.ejb.JeeDbMigrator onStartup
INFO: migrate task: 201306252219 : Do some stuff from file: V201306252219__Do_some_stuff.sql
Apr 28, 2013 6:14:50 PM com.googlecode.flyway.core.metadatatable.MetaDataTableImpl createIfNotExists
INFO: Creating Metadata table: "APP"."schema_version"
Apr 28, 2013 6:14:50 PM com.googlecode.flyway.core.command.DbMigrate migrate
INFO: Current version of schema "APP": << Empty Schema >>
Apr 28, 2013 6:14:50 PM com.googlecode.flyway.core.command.DbMigrate applyMigration
INFO: Migrating schema "APP" to version 201304161230
Apr 28, 2013 6:14:50 PM com.googlecode.flyway.core.command.DbMigrate applyMigration
INFO: Migrating schema "APP" to version 201304171620
Apr 28, 2013 6:14:50 PM com.googlecode.flyway.core.command.DbMigrate applyMigration
INFO: Migrating schema "APP" to version 201305011700
Apr 28, 2013 6:14:50 PM com.googlecode.flyway.core.command.DbMigrate applyMigration
INFO: Migrating schema "APP" to version 201305162030
Apr 28, 2013 6:14:50 PM com.googlecode.flyway.core.command.DbMigrate logSummary
INFO: Successfully applied 4 migrations to schema "APP" (execution time 00:00.540s).

We should now be able to run the servlet when opening the following URL in a browser: http://localhost:8080/flyway-tutorial/books (or http://localhost:8080/flyway-migration-tutorial-1.0.0/books):

Servlet output in the browser

Servlet output in the browser

In the project directory there is now a file named derby.log and it points to the location of the embedded database.

Introspecting this target we can see that the migrations were all run with success and that flyway manages all information about migrations and their state in a table named schema_version:

Persisted books in the database

Persisted books in the database

Flyway migration state

Flyway migration state

Tutorial Sources

Please feel free to download the tutorial sources from my Bitbucket repository, fork it there or clone it using Git:

git clone https://bitbucket.org/hascode/flyway-migrations-tutorial.git

Resources

Alternative: Liquibase and WildFly

Meanwhile I’ve written another article covering Liquibase as database migration framework and Wildfly as application server: “Java EE 7 Database Migrations with Liquibase and Wildfly“.

Article Updates
  • 2014-07-31: Link to Liquibase/WildFly article added.
  • 2014-10-07: Maven coordinates, Maven goal shortcut and @Resource JNDI lookup fixed (thanks Stephan & Mauro!).

Tags: , , , , , , ,

This entry was posted on Sunday, April 28th, 2013 at 4:57 pm and is filed under Enterprise, Java. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

11 Responses to “Easy Database Migrations using Flyway, Java EE 6 and GlassFish”

  1. Ralf Klinger Says:
    June 20th, 2013 at 4:03 pm

    Helo Micha,

    what is the advantage of using flyway instead of using the update-mode of hibernate.hbm2ddl.auto? If I’m scared in using automatic db updates I can not see any difference in trusting hibernate or flyway. Creating the update.sql is possible via hibernate-tools hbm2ddl.export() too.

    Thanks Ralf

  2. micha kops Says:
    June 22nd, 2013 at 9:26 pm

    Helo Ralf,

    I think it is a natural thing to be scared of every automated update that might ruin your production system with ease – I am scared, too :)

    There are plenty of migration frameworks out there and I’ve used classical migration frameworks like mybatis, c5-db-migration or dbdeploy also as I’ve used a JPA persistence provider’s DDL API to track schema updates in SQL scripts, check them by hand and deploy them manually .. I think the framework is less interesting than the deployment and testing lifecycle that the framework supports.

    You could also use Hibernate to generate DDL scripts and afterwards add them to a migration framework of your choice to keep track of your database migrations and be able to update your test instance with ease.

  3. Stephan Says:
    March 24th, 2014 at 11:28 pm

    Hi Micha,

    thank you for the great tutorial. There is a problem with the datasource injection. In glassfish 4 you have to use @Resource(lookup = “jdbc/__default”) instead of @Resource(name = “jdbc/__default”), if you want to select a other datasource. The name parameter will alway return the jdbc/__default datasource, use of lookup will return the right given datasource. I don’t know if this is the same with glassfish 3.

  4. mauro Says:
    October 7th, 2014 at 3:43 pm

    hi , I have downloaded your sources from
    https://bitbucket.org/hascode/flyway-migrations-tutorial
    i have open the maven project with netbeans and run with :
    (right-click on project , custom goals and pasted the :clean package
    embedded-glassfish:run)

    clean package embedded-glassfish:run

    but from the console of netbeans i get:
    —————————————————-

    cd
    /home/utente_javaee7/NetBeansProjects/progetti_hasCode.com/hascode-flyway-migrations-tutorial-7c0ded2563c1;
    JAVA_HOME=/home/utente_javaee7/jdk1.7.0_25
    /home/utente_javaee7/netbeans-8.0/java/maven/bin/mvn clean package
    embedded-glassfish:run
    Scanning for projects…
    The POM for org.glassfish:maven-embedded-glassfish-plugin:jar:3.1.2 is
    missing, no dependency information available
    Failed to retrieve plugin descriptor for
    org.glassfish:maven-embedded-glassfish-plugin:3.1.2: Plugin
    org.glassfish:maven-embedded-glassfish-plugin:3.1.2 or one of its
    dependencies could not be resolved: Failed to read artifact descriptor
    for org.glassfish:maven-embedded-glassfish-plugin:jar:3.1.2
    Downloading:
    http://repo.maven.apache.org/maven2/org/codehaus/mojo/maven-metadata.xml
    Downloading:
    http://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-metadata.xml

    Downloaded:
    http://repo.maven.apache.org/maven2/org/apache/maven/plugins/maven-metadata.xml
    (13 KB at 12.4 KB/sec)

    Downloaded:
    http://repo.maven.apache.org/maven2/org/codehaus/mojo/maven-metadata.xml
    (20 KB at 19.1 KB/sec)
    ————————————————————————
    BUILD FAILURE
    ————————————————————————
    Total time: 5.449s
    Finished at: Tue Oct 07 09:43:13 CEST 2014
    Final Memory: 7M/17M
    ————————————————————————
    No plugin found for prefix ‘embedded-glassfish’ in the current project
    and in the plugin groups [org.apache.maven.plugins, org.codehaus.mojo]
    available from the repositories [local
    (/home/utente_javaee7/.m2/repository), central
    (http://repo.maven.apache.org/maven2) -_ [Help 1]

    To see the full stack trace of the errors, re-run Maven with the -e
    switch.
    Re-run Maven using the -X switch to enable full debug logging.

    For more information about the errors and possible solutions, please
    read the following articles:
    [Help 1]
    http://cwiki.apache.org/confluence/display/MAVEN/NoPluginFoundForPrefixException

    —————————

    how i can resolve? escuse me but i not have many experience with maven
    and how resolve the problems with dependencies.
    Please help me
    tank you ,
    mauro

  5. micha kops Says:
    October 7th, 2014 at 4:30 pm

    Hi Stephan,

    thanks for your input! I’ve updated the part with the resource-lookup!

  6. micha kops Says:
    October 7th, 2014 at 4:32 pm

    Hi Mauro,

    there was an error with the Maven coordinates (changed with the minor version from org.glassfish to org.glassfish.embedded). I’ve updated the source in the repository as well as the article and I’ve confirmed with a clean install that now all dependencies are downloadable with Maven.

    Cheers,

    Micha

  7. mauro Says:
    October 8th, 2014 at 5:12 am

    Tank you for your response.
    i will try .

    A question on maven : how i can run with -e and or -x switch with netbeans the maven project ? Right click on project custom goals and next where i can put the option – e and/or -x option ?

    Mauro

  8. mauro Says:
    October 8th, 2014 at 8:03 am

    after run with:

    clean package embedded-glassfish:run

    if i would get into browser the application i have to write:

    http://localhost:8080/flyway-migration-tutorial-1.0.0/books
    and not the http://localhost:8080/flyway-tutorial/books

  9. micha kops Says:
    October 8th, 2014 at 8:12 am

    Hi Mauro,

    I’m glad it’s working for you now! I had updated the URL in the article yesterday -> “(or http://localhost:8080/flyway-migration-tutorial-1.0.0/books)”

    Cheers,

    Micha

  10. Yuri Says:
    November 30th, 2017 at 9:41 pm

    Thank you for a great introduction to the tool!

  11. Micha Kops Says:
    December 1st, 2017 at 7:19 am

    Thanks Yuri,

    you’re welcome! :)

    Cheers

    Micha

Recent Articles
Search
Archive
Tags
Categories
Links