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.


 

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:maven-embedded-glassfish-plugin:3.1.1: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</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(name = "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 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

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.

    Tags: , , , , , ,

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

    1. Ralf Klinger Says:

      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:

      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:

      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.

    Leave a Reply

    Please leave these two fields as-is:

    Protected by Invisible Defender. Showed 403 to 80,965 bad guys.

    Search
    Categories