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.

Abstract

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.

Intro

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:

db.connection.url=jdbc:h2:./target/h2database
db.connection.username=ra
db.connection.password=

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:

%test.db.connection.url=jdbc:h2:./target/h2database_test

to…

%test.db.connection.url=jdbc:h2:mem:test;DB_CLOSE_DELAY=-1

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 artofunittesting.com 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:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.182</version>
</dependency></pre>

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:

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.6</version>
</dependency>

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:

ninja.migration.run = true
ninja.jpa.persistence_unit_name = mysql

db.connection.url=jdbc:mysql://localhost:3306/acooldbname
db.connection.username=root
db.connection.password=

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):

%prod.ninja.migration.run = true
%prod.ninja.jpa.persistence_unit_name = mysql

%prod.db.connection.url=jdbc:mysql://localhost:3306/prodninjadb
%prod.db.connection.username=explicitusername
%prod.db.connection.password=VeryVerySecurePassword!!

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 “ninja.migration.run”, I’ll get to that shortly.

persistence.xml

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

<persistence-unit name="mysql" transaction-type="RESOURCE_LOCAL">
  <provider>org.hibernate.ejb.HibernatePersistence</provider>

  <properties>
    <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" />
  </properties>
</persistence-unit>

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
CREATE TABLE Article (
 id bigint UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 content varchar(5000),
 postedAt timestamp,
 title varchar(255)
);

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

CREATE TABLE User (
 id bigint UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 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/SetupDao.java” – 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 _ 127.0.0.1 _ ninjadb _ phpMyAdmin 4.0.4.1

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 _ 127.0.0.1 _ ninjadb _ schema_version _ phpMyAdmin 4.0.4.1

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.

Conclusion

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.

Advertisements

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

    • Thanks for your feedback! Unfortunately I kinda lost interest in continuing this series, so it’ll just be a 3 part series (I’ll update that now. Didn’t thought someone would notice hehe). However, given if you know how to setup the database, you can pretty much take off from here given their example project.

      Like

    • Frankly, granted the choice, I’d use Rails or ASP.NET MVC. I’ve used Struts2 for an assignment that forces us to use java. Looking in retrospect, part of me thinks I should have used Ninja instead. Ninja seems to have more things nicely packaged together so you don’t need to figure so much stuff on your own.

      Like

    • Did you modify your previous migrations? You should never do that in production once migrations are committed. That’s not how migrations work. You should only add migrations to correct your mistakes. But (if you’re just developing and trying stuff out), should you want to do that, your best bet is to wipe out your database and restart the migration from the beginning.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s