Add "Database" section

[decebals]

Explain how to deal with database.
It's simple and natural to work with micro/lite JDBC frameworks like:

• jdbi
• iciql
• sql2o
• ebean
• requery

Show some examples code from real life application.

[mhagnumdw]

I use only JPA/Hibernate + Guice Persist. If you want I can write about it later.

[mhagnumdw]

The steps below describe how to use Pippo with JPA/Hibernate + Guice Persist

In this example

• the database is H2
• a HTTP Filter manage EntityManager during HTTP request
• using Pippo Controllers

Let's now configure JPA/Hibernate + Guice Persist for use in our application.

Initially we need to define some dependencies in pom.xml

<properties>
    <hibernate.version>5.2.1.Final</hibernate.version>
    <google.guice.version>4.0</google.guice.version>
</properties>

<dependencies>
    <!-- Engine to Pippo convert to Json -->
    <dependency>
        <groupId>ro.pippo</groupId>
        <artifactId>pippo-gson</artifactId>
        <version>${pippo.version}</version>
    </dependency>

    <!-- In this example we will work with Controllers -->
    <dependency>
        <groupId>ro.pippo</groupId>
        <artifactId>pippo-controller</artifactId>
        <version>${pippo.version}</version>
    </dependency>

    <!-- Our controllers will be able to inject Guice components -->
    <dependency>
        <groupId>ro.pippo</groupId>
        <artifactId>pippo-guice</artifactId>
        <version>${pippo.version}</version>
    </dependency>

    <!-- Hibernate -->
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>${hibernate.version}</version>
    </dependency>

    <!-- Guice extension to manager the EntityManager lifecycle -->
    <dependency>
        <groupId>com.google.inject.extensions</groupId>
        <artifactId>guice-persist</artifactId>
        <version>${google.guice.version}</version>
    </dependency>

    <!-- H2 database driver -->
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>1.4.194</version>
    </dependency>
</dependencies>

Now we need to set up a JPA peristence unit in /src/main/java/META-INF/persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
    xmlns="http://xmlns.jcp.org/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">

    <persistence-unit name="pippojpaPersistenceUnit" transaction-type="RESOURCE_LOCAL">
        <properties>
            <!-- Oracle -->
            <!--
            <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle12cDialect" />
            <property name="javax.persistence.jdbc.driver" value="oracle.jdbc.OracleDriver"/>
             -->
            <!-- Postgres -->
            <!--
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQL95Dialect" />
            <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver"/>
             -->
            <!-- H2 -->
            <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect" />
            <property name="javax.persistence.jdbc.driver" value="org.h2.Driver"/>

            <!-- Hibernate 5.2: none (default value), create-only, drop, create, create-drop, validate, and update -->
            <property name="hibernate.hbm2ddl.auto" value="create" />

            <!--
            <property name="hibernate.hbm2ddl.import_files" value="importInicial.sql,import.sql" />
             -->

            <property name="javax.persistence.jdbc.url" value="jdbc:h2:./db/h2/pippojpa/pippojpa;AUTO_SERVER=true" />
            <property name="javax.persistence.jdbc.user" value="sa"/>
            <property name="javax.persistence.jdbc.password" value="sa"/>

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

            <!-- AUTO  : The Session is sometimes flushed before query execution -->
            <!-- COMMIT: The Session is only flushed prior to a transaction commit -->
            <property name="org.hibernate.flushMode" value="COMMIT"/> <!-- default: AUTO -->

            <property name="hibernate.connection.autocommit" value="false"/> <!-- default is false -->

            <property name="hibernate.connection.pool_size" value="3"/>

            <property name="hibernate.enable_lazy_load_no_trans" value="false"/> <!-- default is false -->
        </properties>
    </persistence-unit>

</persistence>

We will need a filter to enable the HTTP Request unit of work and to have guice-persist manage the lifecycle of active units of work. In other words: to manage the EntityManager during the HTTP request lifecycle.

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.google.inject.Injector;
import com.google.inject.persist.UnitOfWork;

import ro.pippo.core.route.RouteContext;
import ro.pippo.core.route.RouteHandler;

/**
 * Filter created based on {@link com.google.inject.persist.PersistFilter}.
 * <p>
 * https://github.com/google/guice/wiki/JPA
 * </p>
 */
class PersistFilter implements RouteHandler {

    private static final Logger log = LoggerFactory.getLogger(PersistFilter.class);

    private static final String KEY_UNIT_OF_WORK = "UnitOfWork";

    private final boolean begin;
    private final Injector injector;

    private PersistFilter() {
        throw new RuntimeException("instancing is not allowed");
    }

    private PersistFilter(Injector injector, boolean begin) {
        this.begin = begin;
        this.injector = injector;
    }

    static PersistFilter buildBegin(Injector injector) {
        return new PersistFilter(injector, true);
    }

    static PersistFilter buildEnd() {
        return new PersistFilter(null, false);
    }

    @Override
    public void handle(RouteContext routeContext) {
        log.trace("PersistFilter, begin: {} (end: {})", begin, !begin);
        if (begin) { // begin
            final UnitOfWork unitOfWork = begin();
            routeContext.setLocal(KEY_UNIT_OF_WORK, unitOfWork);
            routeContext.next();
        } else { // end
            final UnitOfWork unitOfWork = routeContext.removeLocal(KEY_UNIT_OF_WORK);
            if (unitOfWork != null) {
                end(unitOfWork);
            }
        }
    }

    private UnitOfWork begin() {
        log.trace("unitOfWork.begin");
        UnitOfWork unitOfWork = injector.getInstance(UnitOfWork.class);
        unitOfWork.begin();
        return unitOfWork;
    }

    private void end(UnitOfWork unitOfWork) {
        log.trace("unitOfWork.end");
        if (unitOfWork != null) {
            unitOfWork.end();
        }
    }

}

For Guice to manage the EntityManager it needs to know the persistence unit to create the instances in the dependency injections.
Let's then create a Guice Module pointing to our persistence unit. It's very simple.

import com.google.inject.Binder;
import com.google.inject.Module;
import com.google.inject.persist.jpa.JpaPersistModule;

public final class JPAGuiceModule implements Module {

    @Override
    public void configure(Binder binder) {
        binder.install(new JpaPersistModule("pippojpaPersistenceUnit"));
    }

}

Let's now build a JPA entity, a DAO, and a controller to test our configuration.

Below is a simple JPA entity called City.

import java.io.Serializable;

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

@Entity
public class City implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID", unique = true, updatable = false, nullable = false)
    private Long id;

    @Column(name = "NAME", nullable = false)
    private String name;

    public City() {
        super();
    }

    public City(String name) {
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "City [id=" + id + ", name=" + name + "]";
    }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((id == null) ? 0 : id.hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        City other = (City) obj;
        if (id == null) {
            if (other.id != null)
                return false;
        } else if (!id.equals(other.id))
            return false;
        return true;
    }

}

CityDAO.java
Here is a very simple implementation just to demonstrate this use case.

import java.io.Serializable;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;

import com.google.inject.Inject;
import com.google.inject.Provider;
import com.google.inject.persist.Transactional;

public class CityDAO {

    @Inject
    private Provider<EntityManager> emProvider;

    private EntityManager getEntityManager() {
        return emProvider.get();
    }

    // Transaction is not required
    public List<City> getAll() {
        final String qlString = "FROM " + City.class.getName();
        TypedQuery<City> query = getEntityManager().createQuery(qlString, City.class);
        return query.getResultList();
    }

    // Transaction is not required
    public City getById(Serializable id) {
        return getEntityManager().find(City.class, id);
    }

    @Transactional
    public City save(City city) {
        City entity = persist(city);
        getEntityManager().flush();
        return entity;
    }

    @Transactional
    public void deleteById(Serializable id) {
        City entity = getEntityManager().find(City.class, id);
        // Case of attached entity - simply remove it
        if (getEntityManager().contains(entity)) {
            getEntityManager().remove(entity);
        } else {
            // Case of unattached entity, first it is necessary to perform
            // a merge, before doing the remove
            entity = getEntityManager().merge(entity);
            getEntityManager().remove(entity);
        }
    }

    private <T> T persist(T entity) {
        // Case of new, non-persisted entity
        if (extractId(entity) == null) {
            getEntityManager().persist(entity);
        } else if (!getEntityManager().contains(entity)) {
            // In the case of an attached entity, we do nothing (it
            // will be persisted automatically on synchronisation)
            // But... in the case of an unattached, but persisted entity
            // we perform a merge to re-attach and persist it
            entity = getEntityManager().merge(entity);
        }
        return entity;
    }

    private Object extractId(Object entity) {
        return getEntityManager().getEntityManagerFactory().getPersistenceUnitUtil().getIdentifier(entity);
    }

}

CityController.java

import java.util.List;

import com.google.inject.Inject;

import ro.pippo.controller.Controller;
import ro.pippo.controller.DELETE;
import ro.pippo.controller.GET;
import ro.pippo.controller.POST;
import ro.pippo.controller.Path;
import ro.pippo.controller.Produces;
import ro.pippo.controller.extractor.Bean;
import ro.pippo.controller.extractor.Param;

@Path("/pages")
public class CityController extends Controller {

    @Inject
    private CityDAO cityDAO;

    @GET("/city/getAll")
    @Produces(Produces.JSON)
    public List<City> cities() {
        return cityDAO.getAll();
    }

    @GET("/city/get/{id}")
    @Produces(Produces.JSON)
    public City getCity(@Param("id") Long id) {
        return cityDAO.getById(id);
    }

    @DELETE("/city/delete/{id}")
    @Produces(Produces.TEXT)
    public String delete(@Param("id") Long id) {
        cityDAO.deleteById(id);
        return "City with id " + id + " was deleted";
    }

    @POST("/city/save")
    @Produces(Produces.JSON)
    public City save(@Bean City city) {
        return cityDAO.save(city);
    }

}

Finally we need to make final configurations in the PippoApplication.onInit()

import com.google.inject.Guice;
import com.google.inject.Injector;
import com.google.inject.Module;
import com.google.inject.persist.PersistService;

import ro.pippo.controller.ControllerApplication;
import ro.pippo.guice.GuiceControllerFactory;

public class PippoApplication extends ControllerApplication {
    @Override
    protected void onInit() {
        // http://docs.jboss.org/hibernate/orm/5.2/topical/html_single/logging/Logging.html
        System.setProperty("org.jboss.logging.provider", "slf4j"); // Required for Hibernate when using slf4j

        Module guiceModule = new JPAGuiceModule();
        Injector injector = Guice.createInjector(guiceModule); // create guice injector
        setControllerFactory(new GuiceControllerFactory(injector)); // register GuiceControllerFactory

        PersistService service = injector.getInstance(PersistService.class);
        service.start(); // starts the persistence unit

        // This filter should be applied only to contexts that may require the EntityManager
        ANY("/pages/.*", PersistFilter.buildBegin(injector));

        addControllers(CityController.class);

        // Here your other controllers, routes, configurations, ...

        // Cleanup - It should actually be the last call of the onInit() method
        ANY("/pages/.*", PersistFilter.buildEnd()).runAsFinally();
    }
}

Here we have Pippo fully configured to work with Hibernate / JPA, including with the automatic management of resources allocated by EntityManager through the HTTP filter created.

Launch the application... Lets test... Let's use cURL to make requests.

$ # getting all cities, must be empty
$ curl http://localhost:8338/pages/city/getAll
[]

$ # inserting three cities and getting the IDs
$ curl -X POST http://localhost:8338/pages/city/save -H 'content-type: multipart/form-data' -F name=city1
{"id":1,"name":"city1"}
$ curl -X POST http://localhost:8338/pages/city/save -H 'content-type: multipart/form-data' -F name=city2
{"id":2,"name":"city2"}
$ curl -X POST http://localhost:8338/pages/city/save -H 'content-type: multipart/form-data' -F name=city3
{"id":3,"name":"city3"}

$ # getting all cities (there must be three)
$ curl http://localhost:8338/pages/city/getAll
[{"id":1,"name":"city1"},{"id":2,"name":"city2"},{"id":3,"name":"city3"}]

$ # query the city with ID 1
$ curl http://localhost:8338/pages/city/get/1
{"id":1,"name":"city1"}

$ # delete the city with ID 2
$ curl -X DELETE http://localhost:8338/pages/city/delete/2
City with id 2 was deleted

$ # getting all cities, but the city with ID 2 no longer available
$ curl http://localhost:8338/pages/city/getAll
[{"id":1,"name":"city1"},{"id":3,"name":"city3"}]

$ # done!

[mhagnumdw]

⬆️
@decebals, let me know about any improvement. I put a complete example. Maybe you want to reduce something.

[decebals]

@mhagnumdw It's OK. Thanks!