Ninja Framework Tutorial – PART 3: Connect and Migrate a Database (H2, MySQL)

This a part of a series of tutorials on Ninja Framework. You can click here to see the full set.


I’ll talk a bit about the built-in embedded H2 database, followed by how to setup and connect to a MySQL database. Though this guide is specific to MySQL, you can apply the same thing to other database vendors as well.


I initially thought of starting off from the previous tutorial, but couldn’t get the persistence.xml thingy to work (I’ll get to that later). I concluded that it’s easier to just start from a template that is already connected to a database. So to start off, create a maven project as prescribed in the first part of my tutorial, but instead of choosing the “ninja-servlet-archetype-simple” in the “New Project” step, pick  the “ninja-servlet-jpa-blog-archetype”. The setup procedures after that remain the same.

2015-04-23 23_01_55-Home page

And then you just run it! It just works right off the bat – a blog website example in which you can add blog posts but can’t delete or modify what you create – without needing to start any database service. This had me puzzled for awhile since I actually didn’t know something called “embedded databases” at that time.

H2 Embedded Database

You should go through this while reading Ninja Framework’s JPA guide.

An embedded database is simply a database that is tied directly with the application itself; it’s like a part of the application itself instead being some separate thing that you have to run separately. That’s why you didn’t have to run the H2 server and the website runs fine.

The database itself is actually stored in “PROJECT_DIR/target”. The *.mv.db files, which were created at first run, contains all that table data. To access them you could download a stable release of H2 database and run the H2 console with the help of this guide.

In the JDBC URL you have to set the full file path of the database file, without the *.mv.db extension:

jdbc:h2:E:/Documents/Web Dev/NinjaPlayground/target/h2database

Take note that the username is “ra” instead of the default “sa” you saw on the quickstart guide. Details of this is in the “PROJECT_DIR/src/main/java/conf/application.conf” file:


The dot(“.”) points to your project directory. So anyways now you can manage your database outside your website.

2015-04-23 23_27_20-H2 Console

Note that due to the nature of embedded databases, you can’t access the database from the H2 console and access from your website at the same time. You can try logging in to the site and it’ll return a 500 error (you’ll have to wait a while for it to show up).

In-memory Database

In-memory database is what it say it is: it’s a database running in your RAM, and it’s bloody fast. Why would you want to use it? Well, mock testing your database models would be a good one. If you noticed, your site was actually doing some testing procedures when (in fact Ninja Framework makes a big deal about tests) you execute “Clean and Build”. Right now the tests use an embedded database, but you can easily change it to be in-memory for a speed boost. Simply change the JDBC connection string from:




The “DB_CLOSE_DELAY=-1” option just prevents H2 from wiping clean the database when a connection closes; it waits for the JVM to stop running before doing so. So now running the tests each time your database is a clean slate. You can test this out by right-click your project and select “Test”. The tests will all pass, yet there was no database file created.

2015-04-24 00_33_13-ninjaBlog - NetBeans IDE 8.0.2

I won’t be going through TDD (Test Driven Development) in this series, but is a great place to know what it is. I’d highly recommend you understand what it is as it would likely improve the quality of the software you write.

How is All of this Working?

First off you need to understand that your Ninja website doesn’t just know how to understand H2 on it’s own. It uses the Java Database Connectivity (JDBC) API interface layer to access your database, and you need a JDBC compliant driver that implements this interface layer to enable this interface to talk your database.

I’m not talking sense? Well, simply put, all you need to know is that JDBC enables you to talk to a broad variety of different tabular databases (and there are a lot of them) in a consistent manner regardless of how the database is implemented. Just select the type of database you want; in this case you add the driver to your pom.xml. Therefore you’ll find this piece of code:


Yea – the power of Maven! You don’t even have to download and install the driver yourself. After that you’ll have to configure the database in the application.conf as explained earlier. Now notice the line:

ninja.jpa.persistence_unit_name = h2

This “h2” maps to a persistence unit inside of “WEB-INF/persistence.xml”. By default entering the file Netbeans will show you the “Design” view – it never really seems to work properly though, so I’d suggest going to source view (like how real programmers roll):

2015-04-24 00_09_23-ninjaBlogStart - NetBeans IDE 8.0.2

Ok – this file is important – but only few things are important. So when you check out the persistence-unit name with “h2”, pay attention to these few options, cause we’ll need to understand them before configuring the MySQL equivalent:

<property name="hibernate.connection.driver_class" value="org.h2.Driver" />
<property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect" />

For every different database these 2 lines will guaranteed need to be changed. Frankly I’m not so sure where to find a one place that contains every configuration for persistence.xml there, but you can find a list of dialects here. Aside that I guess googling something like “persistence.xml hibernate mysql” would work (which was what I did).

Another 2 lines you should check out is this:

<property name="hibernate.show_sql" value="false" />
<property name="hibernate.format_sql" value="false" />

Turning those on (set to true) will output every single SQL query in your output window (which I never bother to look at; it clogs up my output window). If you find that useful for debugging in dev mode by all means turn it on. You’d probably want to turn it off when you deploy your application though.

And that’s it! It’s that simple right? Add the driver, setup the configuration and put stuff inside persistence.xml. Glorious.

Let’s Connect to MySQL!

Ok! Now we put the pieces together. I assume that if you already wanted to connect to MySQL you’d know how to setup the database server. Since I’m in Windows, I will be using XAMPP since it has everything built in (even stuff you’d probably never use in your life). So make sure your MySQL database server is running (it would default to use port 3306 unless you change it) and also run Apache since PHPMyAdmin uses it.

2015-04-24 00_30_39-XAMPP Control Panel v3.2.1   [ Compiled_ May 7th 2013 ]

Now in pom.xml, same place where the H2 dependency XML node is placed, add this piece of code:


I actually just found this by googling “mysql maven dependency” and this link was the first to pop up. You should be able to find drivers for SQL Server or IBM DB2 the same way I guess.

Now you tweak the settings in you application.conf: = true
ninja.jpa.persistence_unit_name = mysql


You should make sure that “acooldbname” actually exists this time; server mode databases don’t get created themselves at runtime like embedded databases.

“root” is the default username for MySQL, though it’s good practice that you setup each database with its unique username and a secure password. Personally I don’t bother with this in a dev environment, but in production you should.

But wait… what if we can do both? Ninja has predefined modes that makes it convenient to do this (click here to read about configuration and modes in Ninja): = true = mysql


Just in case you need a refresher:

If no mode is set explicitly Ninja will use the prod mode. That means if you deploy Ninja via a war file to an arbitrary servlet container Ninja will use the prod mode. Ninja’s SuperDevMode will set the mode to dev. And Ninja’s testcases will use mode test by default.

Now, if you’re wondering why is there a “”, I’ll get to that shortly.


As discussed prior, you’ll have to add some XML configuration thing to your persistence.xml:

<persistence-unit name="mysql" transaction-type="RESOURCE_LOCAL">

    <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
    <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />

    <property name="hibernate.show_sql" value="false" />
    <property name="hibernate.format_sql" value="false" /> 

    <!-- Connection Pooling settings -->
    <property name="hibernate.connection.provider_class"
    value="org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider" />

    <property name="hibernate.c3p0.max_size" value="100" />
    <property name="hibernate.c3p0.min_size" value="0" />
    <property name="hibernate.c3p0.acquire_increment" value="1" />
    <property name="hibernate.c3p0.idle_test_period" value="300" />
    <property name="hibernate.c3p0.max_statements" value="0" />
    <property name="hibernate.c3p0.timeout" value="100" />

And running it now will give you failures!

Introducing… Database Migration

Wow what? It was working just now! But how do you think the database just magically appeared with all the data inside? This is the magic of database migration built in to Ninja Framework with Flyway. Moving your database from development to production is now as simple as moving it together with your website, add a database with the right specifications and configurations, and it will be populated with tables at first run. Database migration, though, takes it another step by also doing “version control” with your databases. That means you can incrementally modify your database (what database continues to stay the same after initial deployment anyway?) with your site. Flyway keeps track of this by adding a separate table to your database called “schema_version”. However, Flyway doesn’t allow you to downgrade changes like Rails or Laravel.

So that’s all cool and all, but it doesn’t explain why it failed. To solve this, check out the “V1__.sql” (it’s recommended you follow their naming convention) file under “db/migration”. As of this writing it looks like this:

-- the first script for migration
create table Article (
 id bigint generated by default as identity,
 content varchar(5000),
 postedAt timestamp,
 title varchar(255),
 primary key (id)

create table Article_authorIds (
 Article_id bigint not null,
 authorIds bigint

create table User (
 id bigint generated by default as identity,
 fullname varchar(255),
 isAdmin boolean not null,
 password varchar(255),
 username varchar(255),
 primary key (id)

alter table Article_authorIds
add constraint FK_f9ivk719aqb0rqd8my08loev7
foreign key (Article_id)
references Article;

Therein lies another limitation of Flyway: your migration is specific to one database vendor only, since SQL is a little different from one database to another.

Well, you can say this is shitty, but chances of you jumping from one database to another is pretty rare (I think), so let’s take it that being stuck with just MySQL is not a bad thing.

So now you change the SQL syntax to be complaint with MySQL:

-- the first script for migration
 content varchar(5000),
 postedAt timestamp,
 title varchar(255)

CREATE TABLE Article_authorIds (
 Article_id bigint UNSIGNED not null,
 authorIds bigint UNSIGNED

 fullname varchar(255),
 isAdmin boolean not null,
 password varchar(255),
 username varchar(255)

ALTER TABLE Article_authorIds
add foreign key (Article_id)
references Article(id);

So now (fingers crossed) you should be able to run the blog example using MySQL! You’ll see the table created with data added inside. The SQL migration however, didn’t insert any data to the database; you can change what gets added in “dao/” – that piece of code, is database vendor independent. I will not be going through that code for this part of the tutorial though.

Anyways, here’s the tables inside PHPMyAdmin:

2015-04-24 10_00_47-localhost _ _ ninjadb _ phpMyAdmin

Hey wait a minute! What about the tests? They’re still using H2 database aren’t they? Yet if you run them now it’s still working! H2 tries to be compatible with other databases. In fact, it also has compatibility modes to mimic other database vendors (Read their documentation for instructions on how to configure that) but… when I added a “MODE=MySQL” the whole thing just flops so… up to you to decide whether having 2 database vendors in one project is a good idea or not I guess.

Adding a Migration

Now I noticed something about the “article_authorids” junction/bridge/join/map/linking/intersection table: there is a foreign key constraint on one side linking the primary key in the “article” table, but none for the “user” table (by the way you should come up with more obvious names for junction tables than the one in this example).

So let’s make that change now. Add a V3__.sql file alongside the other migration files:

ALTER TABLE Article_authorIds
add foreign key (authorIds)
references user(id);

But hey – V2__.sql is empty! Why don’t we just change that instead? Well, although it is empty it has already been executed (schema_version table):

2015-04-24 10_38_24-localhost _ _ ninjadb _ schema_version _ phpMyAdmin

If you insist, you could remove the V2__.sql entry and run your project again. Honestly I would just wipe out the whole database just to be safe.

Note that rollback of changes in the even that a migration failed is not supported in every database. It is therefore recommended you test your migration SQL in PHPMyAdmin and make sure it works before adding it to your project. If crap happens there you could just wipe out your database and run your project again.

So there you go! The foreign keys are working properly now.


That is enough for part 3 (phew)! I congratulate you for making it this far (:. Next part I’ll be walking through some Java code in building a basic CRUD website.


Ninja Framework Tutorial – PART 2: Adding CSS, Javascript Libraries using WebJars

This a part of a series of tutorials on Ninja Framework. You can click here to see the full set.


I’ll guide you on installing JavaScript, CSS external libraries in Ninja without needing to add the actual files into your project directory. In the end of this guide you will install a JavaScript WYSIWYG editor (TinyMCE) in your site:

2015-04-20 20_09_05-Home page

Intro: Something’s Missing…

Let me tell you a story of how I learned about WebJars. Let’s go to the site we’ve set up in the first part of this tutorial. Open it up on Chrome and press F12 to see developer options. As of this writing (not sure if it’s a bug or whatnot), the site itself would look totally fine, but there actually is an error message in the console:

2015-04-20 20_18_00-Home page


So What Happened?

You should walk through this with Ninja Framework’s static assets tutorial.

The 404 error is what it is: your site is looking for a file that is not present in your site directory. Here, the file in question is “tiny_mce.js”.

So where is it?

If you search your project for “tiny_mce.js”, it will direct you to “PROJECT_DIR\src\main\java\views\layout\defaultLayout.ftl.html”. You’ll find a line:

<script type="text/javascript" src="/assets/webjars/tinymce-jquery/4.0.16/jscripts/tiny_mce/tiny_mce.js" ></script>

One thing I initially tried was searching the project directory for *.js files. It isn’t there.  So why request for something that you’re not using?

The strange catch? The JavaScript library (TinyMCE – platform independent web based Javascript HTML WYSIWYG editor control) acually exists in your project, but not physically in your project directory. It is actually added to the site as a WebJar.

If you search “dependency” in your “pom.xml” file, you will find that TinyMCE is added as an XML node:


What is a WebJar and Why Should I Use it?

According to the Ninja Framework site says:

The webjars project ( started by James Ward is an excellent initiative that unites good old Java dependency management with static web libraries like bootstrap.

Should you use it? You don’t have to. You could simply have downloaded TinyMCE from their website and paste it into your assets folder (“PROJECT_DIR\src\main\java\assets”). You can that access it as so:

<script type="text/javascript" src="/assets/tinymce/tiny_mce.js" ></script>

Isn’t that simpler?

You could do it that way, but it does make your site source code bigger since you bundled it together with your dependencies. If you use a version control system (VCS) like git, you make big commits each time you upgrade your dependencies because you have to override/remove/add a lot of files. Using a dependency management system makes upgrading as simple as changing the version number, and in the case of our TinyMCE example:




Of course, you should have to change that version number in where you include the libraries, because the version is also part of the directory structure, but that’s it; change 2 lines of code, and you are using the latest version (assuming nothing breaks in your site after the upgrade).

Twitter’s Bower does somewhat the same thing, but instead of XML you modify a json file. Anyways that’s besides the point. Here’s what it says it does:

Web sites are made of lots of things — frameworks, libraries, assets, utilities, and rainbows. Bower manages all these things for you. Bower works by fetching and installing packages from all over, taking care of hunting, finding, downloading, and saving the stuff you’re looking for.

That’s a layman interpretation of “Explicitly and easily manage the client-side dependencies in JVM-based web applications”.

Again, just bear in mind it both Bower and WebJar only handles client side, frontend dependencies; stuff that only your browser understands: CSS, JavaScript and HTML.

So the WebJar is There… but Where?

It’s odd that in the Ninja documentation it says:

You can reference bootstrap from your html pages via the following url:

<link href="/webjars/bootstrap/2.1.1/css/bootstrap.min.css" rel="stylesheet">

But how on earth does it know that the WebJar is there? I’ll answer this by looking for TinyMCE.

Go to and look for “Classic WebJars”. In the filter type “tinymce” and you’ll see what you’re looking for. Under “Build Tool”, select “Maven”. Under “TinyMCE jQuery”, select version 4.0.16.

2015-04-20 21_10_54-WebJars - Classic WebJars

Ok. I see the Maven XML, but it doesn’t explain how on earth how to find these files. (as obvious as it may seems I took awhile to figure it out) Under the “Files” column in TinyMCE jQuery, you’ll see “80 Files”. Click it and you see the file list. That is how you locate your dependencies.

2015-04-20 21_15_27-WebJars - Classic WebJars

How do you make sense of this list? Replace “META-INF/resources” with “assets”. Also, what you’ll learn, is that the js file we are looking was not called tiny_mce.js in the first place. It’s “tinymce.min.js”.

Gotcha! 😀

The include line for the library is actually:

<script type="text/javascript" src="/assets/webjars/tinymce-jquery/4.0.16/tinymce.min.js"></script>

Once you fix this, you’ll see that 404 error in your console disappear.

Let’s Install TinyMCE!

After this I’m expecting to go figure on your own based on the instructions in TinyMCE’s website. You’ll need to add the includes into “defaultLayout.ftl.html”, make sure it is at the <head> tag, and shift jQuery include on top of that (your browser will tell you if you forget).

Then look for “index.ftl.html” and add

<form method="post">



…and you should see the final result as shown in the beginning of this post.


That’s all for part 2. In part 3 I intend to so some database CRUD stuff – keep an eye for that!