Foreword

Ninja provides two principal ways on how to access relational databases.

The classic way is to use ninja-db-classic. A module that bundles migrations and JPA.

The upcoming and new way to access relational databases is via ninja-db.

Ninja-db has certain advantages over ninja-db-classic: It is more modular and supports multiple databases.

But there's nothing wrong with using ninja-db-classic.

JPA via ninja-db-classic

JPA is the de-facto standard for persistence in Java and Ninja provides out-of-the box support for JPA 2.0. JPA support is implemented by Hibernate and transaction handling is facilitated by Guice Persist.

Quickstart

We prepared an archetype to get you up and running. Simply execute:

mvn archetype:generate -DarchetypeGroupId=org.ninjaframework -DarchetypeArtifactId=ninja-servlet-jpa-blog-archetype

and hit

mvn package ninja:run

You can access the application at

Note: The application works out-of-the-box with an in-memory db (h2). If you like to change to another db, like Postgresql or MySQL, just review the Configuration section below.

At this point you could use the existent Entities as an example to create your own ones, extending this way the Model and the Application.

Models

The models by convention should be put under the package “models”. A typical model looks like:


import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class GuestbookEntry {
    
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    Long id;

    private String text;
    private String email;
    
    public GuestbookEntry() {}
    
    public String getText() {
        return text;
    }
    public void setText(String text) {
        this.text = text;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }

}

In essence the model is a POJO with some annotations. This is already enough to tell JPA where and what to save.

Please refer to https://docs.oracle.com/javaee/7/tutorial/persistence-intro.htm for an exhaustive coverage of the topic.

Well. We configured the stuff - we know how to write models. But what can we do with the models?

Saving and querying

To be honest. Ninja is just reusing excellent libraries to provide you with JPA. In that case it is Guice and especially Guice Persist (https://github.com/google/guice/wiki/GuicePersist).

Ninja just offers a convenient out of the box configuration and maps the modes to the persistence units.

Let's have a look at a controller that does some querying:

@Inject 
Provider<EntityManager> entitiyManagerProvider;

@UnitOfWork
public Result getIndex() {

    EntityManager entityManager = entitiyManagerProvider.get();

    Query q = entityManager.createQuery("SELECT x FROM GuestbookEntry x");
    List<GuestbookEntry> guestbookEntries = (List<GuestbookEntry>) q.getResultList();

    String postRoute = router.getReverseRoute(ApplicationController.class, "postIndex");

    return Results
            .html()
            .render("guestbookEntries", guestbookEntries).
            render("postRoute", postRoute);


}

Two things here are important:

The injected Provider for an EntityManager
The method that is annotated with @UnitOfWork

The entity manager is the key component that allows you to update / save and query data based on your models. But JPA has to open connections, save data, maintain caches - and you'd possibly go crazy if you'd have to manage that for each controller method yourself. This is what @UnitOfWork is for. Simply annotate your method with that annotation and Guice Persists will handle all the boilerplate for you.

But @UnitOfWork only handles connections and does not help you with transactions. This is what @Transactional is for. @Transactional automatically opens and closes transactions around the annotated method. Make sure you are using import com.google.inject.persist.Transactional; for @Transactional.

Saving is also straight forward:

import com.google.inject.persist.Transactional;

@Transactional
public Result postIndex(GuestbookEntry guestbookEntry) {

    logger.info("In postRoute");        

    EntityManager entityManager = entitiyManagerProvider.get();

    entityManager.persist(guestbookEntry);


    return Results.redirect(router.getReverseRoute(ApplicationController.class, "getIndex"));

}

Saving really is just a call to entityManager.perist(…). It can not get much simpler. But again - don't forget to annotate your method with @Transactional.

Summing up:

For read-only queries you should use @UnitOfWork (and it may be faster because there are no transactions started). You can wrap either a controller method or method of your service class.
For saving / updating and deleting data always use @Transactional. The same here: you can wrap either a controller method or method of your service class.
For several transactions within one HTTP request or scheduler invocation:

a. use @UnitOfWork around the controller or service method and use @Transactional or programmatic API of the EntityManager to demarcate transactions within the same @UnitOfWork
b. use @Transactional or programmatic API of the EntityManager to demarcate transactions within the same request or scheduler invocation without @UnitOfWork

Configuration

Two things are important when it comes to configuring JPA.

Setting a database and persistence unit at your application.conf
The META-INF/persistence.xml

First of all you have to set your database credentials in application.conf:

You also have to set the database connection string, username and password like so:

db.connection.url=jdbc:postgresql://localhost:5432/ra
db.connection.username=ra
db.connection.password=

Of course you can take advantage of Ninja's different modes and specify a different database in test and in production:

# development database
db.connection.url=jdbc:postgresql://localhost:5432/ra
db.connection.username=ra
db.connection.password=password

# testing database
%test.db.connection.url=jdbc:postgresql://localhost:5432/test
%test.db.connection.username=ra
%test.db.connection.password=password

# production database
%prod.db.connection.url=jdbc:postgresql://myserver:5432/production_db
%prod.db.connection.username=user
%prod.db.connection.password=password

To activate JPA you have set a variable called ninja.jpa.persistence_unit_name

ninja.jpa.persistence_unit_name=mypersistenceunit

This tells Ninja what persistence unit to select from persitence.xml. You can of course again specify different persistence units for different modes:

ninja.jpa.persistence_unit_name=dev_unit
%test.ninja.jpa.persistence_unit_name=test_unit
%prod.ninja.jpa.persistence_unit_name=prod_unit

This causes Ninja to use dev_unit in dev, test_unit in test and prod_unit in prod. You can then use for instance a db for testing, another regular PostgreSQL database for development and a highly tuned connectionpooled PostgreSQL in production. All of them with different connection strings of course.

To make that finally come to live you have to configure the second JPA component

a file called META-INF/persistence.xml which can look like:

<?xml version="1.0" encoding="UTF-8"?>

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
    version="2.0">

    <!-- Database settings for development and for tests -->
    <persistence-unit name="dev_unit" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

        <properties>
            <property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" />

            <property name="hibernate.show_sql" value="true" />
            <property name="hibernate.format_sql" value="true" /> 
            
            <!-- 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>

    <!-- production database - with sensible connect strings optimized for the real servers. -->
    <persistence-unit name="prod_unit" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

        <properties>
            <property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" />

            <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>
</persistence>

The file will reside under META-INF/persistence.xml.

The default way to operate you persistence units is by using transaction-type=RESOURCE_LOCAL. It gives you a lot more control and predictability over what is happening and when stuff gets saved. Ninja works best in that mode because the framework is responsible for setting up/shutting down the JPA's EntityManagerFactory and EntityManagers in oppose to JTA mode where transactions and EntityManagers are injected/managed by JEE containers.

If you want to know more about JPA please refer to the official docs at: https://docs.oracle.com/javaee/7/tutorial/persistence-intro.htm .