Converting a Java Project to Use JPA

In this post I walk through some of the gotchas when converting a java application that works with raw SQL strings to one using ORM (Object Relational Mapping) via JPA (Java Persistence API). I will be converting a simple application from “Generic Java GUI Netbeans Project with Embedded Database” that only has 2 entities: Post and Comment.

The finished product is in a git branch called JPA. If you don’t use git you can download this sample project as a zip from MediaFire.

You can view all the changes to convert to JPA in this Github diff.

Netbeans makes the initial setup very simple by generating persistence.xml (you find the persistence unit name here) for you, as well as the the entities for you from your database.

SQL needs to be rewritten to Java Persistence Query Language

This isn’t much an issue really; in the long run it does you a favour since it is database vendor independent.

Change from:

ResultSet rs = db.executeQuery("SELECT * FROM post ORDER BY date_created DESC");


List<Post> rs = db.createQuery("SELECT p FROM Post p ORDER BY p.dateCreated DESC").getResultList();

Default Values are Lost

I noticed something strange when adding a Post entity: the created_date attribute shows up as a null when I convert to use JPA. My DDL (Database Definition Language) looks like this (Derby DB SQL):

    name VARCHAR(250) NOT NULL,
    content VARCHAR(500),

So each time I create a Post, I’m expecting the date_created attribute to show the current date, but it doesn’t. So all the SQL code where I have DEFAULT is basically replaced with null when I use JPA.


The workaround is to code the default values into the attribute field of the entity classes. Here is the dateCreated attribute inside Post:

@Column(name = "DATE_CREATED")
private Date dateCreated = new Date(); // new Date() returns the current timestamp

Exceptions in JPA Only Happen in Runtime

So when converting the code, I realized that in the places where SQLException would appear, Netbeans puts up an error saying that SQLException doesn’t happen here:

Sqlexception is never thrown

That’s ok I think. But what’s weird was that it offered to remove the try-catch block as a solution! Wow wow wow, stop. Aren’t there exceptions? Well, turns out there’s PersistenceException.

The problem? It’s a subclass of RuntimeException. I don’t exactly know what was the reason the exception was happening in runtime, but without the try-catch block, the procedure is going to fail (Here the Post entity cannot have null value for Name attribute) silently.

Now for a before and after. Before:

String sql = "INSERT INTO post (name, title, content) VALUES (?, ?, ?)";
try {
    PreparedStatement ps = core.DB.getInstance().getPreparedStatement(sql);
    ps.setString(1, authorField.getText());
    ps.setString(2, titleField.getText());
    ps.setString(3, postTxtArea.getText());
    JOptionPane.showMessageDialog(this, "Post has successfully been added.", "Successfully added post!", JOptionPane.INFORMATION_MESSAGE);
    dispatchEvent(new WindowEvent(this, WindowEvent.WINDOW_CLOSING));
} catch (SQLException ex) {
    JOptionPane.showMessageDialog(this, ex.toString(), "Invalid content... or some shit like that", JOptionPane.ERROR_MESSAGE);


try {
    Post p = new Post();
    JOptionPane.showMessageDialog(this, "Post has successfully been added.", "Successfully added post!", JOptionPane.INFORMATION_MESSAGE);
    dispatchEvent(new WindowEvent(this, WindowEvent.WINDOW_CLOSING));
} catch (PersistenceException ex) {
    JOptionPane.showMessageDialog(this, ex.toString(), "Invalid content... or some shit like that", JOptionPane.ERROR_MESSAGE);

The following dialog should pop up when the Author field is empty:


Well, this output doesn’t just happen automatically. There’s still one more issue that I’ll get to next:

Empty Strings are not Null

In my DDL, I have a rule that Post cannot have null value for Name attribute. Yet for some reason a string “” is not a null value in JPA. It is actually stored in the database as “”.

How is “” not null?

stored as empty string

This kind of shit doesn’t happen when simply working with raw SQL strings.

There are workarounds for this online: one of them was using the annotations @Size(min=1) or @NotNull. Unfortunately I’m using Java SE 8 (@Size is currently supported up to Java EE 7 as of this writing) and I’m not using Spring (for @NotNull).

So what I ended up doing was place the validation in the setter method:

public void setName(String name) {
    if (name.trim().isEmpty()) return; // empty strings begone! = name;

As you can imagine, I have to do this for every attribute in every entity that doesn’t accept empty strings.

You Need to Manually Manage One To Many Relationships

I had this issue that when I add a one to many relationship: Post has many Comments, now I add a Comment to a Post. I don’t see the changes immediately though; had to restart the application to see the new Comments. I would have thought that JPA would update the Comments collection inside the Post, but it didn’t.

Here’s how my setPostId function in my Comment entity looks like:

public void setPostId(Post postId) {
    this.postId = postId;

This is because (as aggravating as it sounds) in JPA, it is the responsibility of the application, or the object model to maintain relationships. By that it meant that it is the responsibility of the programmer to manually code in the links when a setter is called:

public void setPostId(Post postId) {
    this.postId = postId;
    postId.getCommentCollection().add(this); // programmer needs to add this himself

I’m going to be a bit honest here: this is kinda inconvenient, and retarded. In Rails this sort of things is done automatically, and it should be. In JPA I need to remind myself that each time I have a one-to-many relationship I need to manually link them together or it doesn’t update automatically/only updates after application is restarted.


There may be other quirks I may miss out since I’m only dealing with a very simple project that doesn’t even have update and delete, so feel free to let me know about in the comments below.

Yea, I know I complain a bit, but working with raw SQL strings is going to be pretty hard to manage in the long run. You’re dealing with simply strings, most errors only pop up in runtime, and your IDE can’t assist you much. With JPA when you’re writing in Java Persistence Language, Netbeans can actually figure out data types and attribute names:

autocomplete in java persistence language string

So unless you really need that sort of performance boost, using an ORM would be the right call.


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.