Sakila: Sample app project setup

This sample application will integrate quite a few very nice open source tools available to every developer:

  • Postgresql – database
  • Flyway – database migration tool
  • Jooq – Java object oriented querying + HikariCP connection pool
  • Dagger 2 – dependency injection
  • SparkJava – fast and simple web server + GSON Json serializer
  • JavaScript Polymer SPA application framework
  • Vaadin Elements components

The application will consist of many modules :

Postgresql – database

Initialize sample database

For start we will install sample sakila database  in the local Postgresql database. Restore downloaded file into locally created database.

Sakila sample database is open source demo database and represent database model of DVD rental store. It  consist of  15 relational tables, 7 views and few other database details and it’s full of data.

Well that’s database with full of test data for development environment. If we need empty database (for production for example) we need to start initialization DDL script to build the database.

To create the script from the existing database use the command pg_dump which is capable of exporting database in the form of sql commands :

To export database without any data , only schema definitions we use “schema only” (-s) option.

Flyway migrations

Create flyway config file and “migrations” folder under the project root.

Add “fw” command somewhere on the path.

Put the “V1.0.1__sakila_init.sql” file in the migrations folder. If everything works as expected the “info” command should report the pending migration.

Flyway migration and initial database state after database restore

After restoring the database with test data in it we need to “baseline” initial migration. Initial sql script to create empty database was bypassed with restore. The V1.0.1__sakila_init.sql migration script was still pending.

With the baseline command we agree that the migration is not needed and you mark the migration version as migrated.

 

Setup java server project

In the IDE (IntelliJ IDEA Community 2017.2) create new console type project “sakilaweb/server”.

Setup git-bash terminal as default intellij terminal

Jooq – object oriented querying

Create jooq config file and add jooq command somewhere on the path.

Bash command:

Add “jooq-3.10.1.jar” library to project dependencies. Add “postgresql-42.1.4.jar” if you use the same database.

Run code generation tool with “jooq” command in the terminal at the project root.

After code was successfully generated in the “./database” folder you will get a bunch of database related code ready made ( database schema, POJOs, and DAOs).

The project with generated code will now look like :

Setup Dagger 2

Configure IDEA for annotations processor.

Add dagger dependencies (dagger-compiler only as “Provided” because it is used only for code generation ).

Setup SparkJava web server

Add few references to the project dependencies and setup “hello-world” web sample just to be sure everything is setup us expected before start real coding.

Create main procedure as :

Now if you run the application you should already get the first page:

Publish to the github

First enable VSC support in the local project and add .gitignore file to the project. Next we add files to the local git repository created in the project.

If we want  to push code to the remote repository we need to create it to have repository to commit to. Login to the github and create new empty repository.

The code for the server side project is available here.

 

 

Next : In the next installment I will put the generated database layer into the use and expose first REST service.

 

 

 

 

 

Java Spring Boot project setup

You can get source code of this project from github repository “SpringBootMyApp” .

Create Spring Boot Maven project

Go to Spring Boot project generator web site and select minimal project definition with dependencies :

  • Web:  dependency for embedded tomcat server
  • jOOQ: integrated SQL query language and data model code generator
  • Flyway: database model migration tool
  • Postgresql JDBC driver

After downloading ZIP project file, unzip it to some folder and open folder in IntelliJ IDE environment.

Startup class

The project will automatically include embedded tomcat server and spring application start will configure whole application at startup.

Setup project properties

Under src/main/resources find file application.properties and add configuration for database access  and flyway configuration. Your application will start migration procedure at every application start and syncronize to the correct version automatically.

Flyway migration files

You need a folder where your SQL migration files will reside:

Add first migration file, for example “create currency table” will be saved in file V1_0_0__currency.sql:

Be extra careful with names , first part (V1_0_0) is version of migration file, second part of the name is a description, separated with two underline characters.

The scripts must be correct SQL! Validate it first in database environment. If script is not valid, migration procedure will break and you will probably have hard time to figure out what was wrong.

Now you run your application and your database must be prepared according to migration script changes automatically. Flyway start automatically with your application.

More information on this video tutorial for flyway and spring boot..

Maven plugin settings

If you want more control around code generation and migration life cycle, you add flyway and jooq plugins:

Add this properties to the properties section in pom.xml file:

  • Database connection settings in pom.xml will be used by jooq code generator and flyway migration maven plugins.

Flyway and Jooq dependency and version

You probably already have it from generated pom.xml, add it if you don’t have it.

Version was not required by spring boot but I put version in because I encounter differences between plugin version and spring boot version of the libraries.

Now you need to define plugin definitions as :

Flyway and Jooq maven commands

After you add plugin definitions to maven pom.xml file you get new lifecycle commands :

When flyway commands are used directly they search for SQL files in the target “classes” folder and not in the source tree (“src”). When project compiled the files, target folder is synchronized with the current version of code in the source tree.

After compiling project you could run “flyway:migrate” command for example. You can always check files in the target folder and delete it if you are not sure you have the latest version of the files.

Jooq code generation

Jooq generate data model code from your database. The code is added to the project in the namespace as you defined in plugin definition.  It’s wise to split plugin definition to properties and plugin definition part.

Every time you compile project, database model code is regenerated. Check here for more information about jooq.

You can check how database model code is regenerated simply by deleting a line from one of generated files and recompile the project again.

More about jooq & spring boot can be found here.

Create JSON service

Now I need to test the environment if everything is set in place as should be. I will create simple currency service as sample application and test basic CRUD operations.

Create simple data container class

To be able to send JSON payload back and forth from client to server we create a simple transport class:

Service class and database interaction with Jooq

Now we create service class with which we will interact with database :

REST controller with router mapping

On the end we need a controller class to expose access points for REST service:

Don’t forget to instantiate services with DI (dependency injection) with @Autowire annotation. This simplify development a lot !

Interactive testing

The service should in this point work as expected, just run application and navigate to “localhost:8080/api/currency”.

You can search for a specific currency with added path variable appended to Url address:

Postman application for Http API testing

To inspect service in more details on the client side, you can use Postman application.

Look at example ofadding a currency with a POST message:

In the headar I add “content-type” variable with value “application/json” and in the payload a json message with a new currency json structure. On the right side I received “200-OK” and a currency record with a record and new row identifier registered in the database.

 

Flyway – database migration tool

How to setup migration tool for any IDE

There are many ways how to setup migration tool for a specific project, here I will use a command line approach.

As you already know by now, flyway just runs your SQL files with migration DDL commands.  You create new file for each change, incrementally as your database evolve.

We need a folder where we will put files under the project tree.

File names should follow special naming convention for versioned migrations.

Command “fw”

Because I use bash (cygwin) environment inside Netbeans IDE, i will first enable “flyway” tool as a command inside cygwin bash terminal. I create “fw” command file and put it in the “bin” folder which is on the PATH.

Netbeans project structure

I have simple hello world java web project. I added a resources, db and migrations folders for data migration files:

Configuraton

In root folder, where I will run “fw” command, I added configuration file with the name flyway.conf. This file contain important config settings for flyway to work.

There are many possible settings. You can take prepared flyway.conf file as a base for your configuration file.  Look at conf folder under flyway installation folder, there is sample conf file with comments around every possible configuration key.

Final structure

In simplified view (I omit lot of files here) the structure of a project folder looks like :

Migrations SQL files

This two files are simple migration files:

and second file where we add new field to the table :

Running migrations

There are only few commands. For start, you need only two (info and  migrate). After you write your first migration file, just try out “info” command. You should see pending migration record for your SQL file.

$ fw info

Now you can migrate first file:

$ fw migrate

After adding next SQL file in “migrations” folder you can again try status of current migrations with “info” comand:

You see there is a Pending change from your last migration file. After another “migrate” command, again this migrations will all be marked as “Installed”.

Ok, that’s it ! Migrations are now in full working state !

How to change “current” folder to “project” folder

For successful execution of any flyway command we need to be in proper folder first. The proper folder is folder where flyway.conf file reside. This is project root folder in my example.

Don’t forget to install “TerminalExtras” plugin for Netbeans, then you can just press Alt+. when you staying on the project root node. Change directory (cd) command will be executed inside terminal window and focus will be set to terminal window.

2016-11-23-00_56_35-helloworld-netbeans-ide-8-2