Using JINQ with JPA and H2

Martin MoisApril 26th, 2016Last Updated: April 25th, 2016

0 92 2 minutes read

A few days ago I have read the interesting interview with Ming-Yee Iu about JINQ. JINQ is, like the name already suggests, the attempt to provide something similar to LINQ for Java. The basic idea is to close the semantic gap between object-oriented code that executes queries on a relational data model. The queries for the relational database model should be easily integrated into the code such that it feels more natural.

The research behind LINQ came to the conclusion that the algorithms transforming the code into relational database queries work best with functional code. As Java 8 comes with the streams API, the author uses it to implement the ideas of his PhD in Java.

To get our hands dirty, we start with a simple project that uses Hibernate over JPA together with an H2 database and JINQ:

<dependencies>
    <dependency>
        <groupId>javax</groupId>
        <artifactId>javaee-api</artifactId>
        <version>${jee.version}</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>${h2.version}</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-entitymanager</artifactId>
        <version>${hibernate.version}</version>
    </dependency>
    <dependency>
        <groupId>org.jinq</groupId>
        <artifactId>jinq-jpa</artifactId>
        <version>1.8.10</version>
    </dependency>
</dependencies>

In order to use JINQ streams we have to create a provider that gets theEntityManagerFactory as argument:

EntityManagerFactory factory = Persistence.createEntityManagerFactory("PersistenceUnit");
JinqJPAStreamProvider streams = new JinqJPAStreamProvider(factory);

Having inserted some persons into our database, we can easily query them:

List<String> firstNames = streams.streamAll(entityManager, Person.class)
        .map(Person::getFirstName)
        .collect(toList());
firstNames.forEach(System.out::println);

Using the method streamAll() of the previously createdJinqJPAStreamProvider gives us access to all persons within the database. In this simple example we only want to output the first name of each person; hence we map the list and collect all results into a List. This list gets printed using the forEach() method and a reference to the println()method.

Taking a look at the generated SQL code, we see that all columns are selected:

select
    person0_.id as id1_4_,
    person0_.FIRST_NAME as FIRST_NA2_4_,
    person0_.ID_CARD_ID as ID_CARD_4_4_,
    person0_.LAST_NAME as LAST_NAM3_4_,
from
    T_PERSON person0_

Of course we can refine the statement using the select() method:

List<String> firstNames = streams.streamAll(entityManager, Person.class)
        .select(Person::getFirstName)
        .where(p -> p.equals("Homer"))
        .collect(toList());
firstNames.forEach(System.out::println);

Additionally we have also added a predicate (where firstName = 'Homer'):

select
    person0_.FIRST_NAME as FIRST_NA2_4_
from
    T_PERSON person0_ 
where
    person0_.FIRST_NAME='Homer'

Leaving this simple example, we now want to create a query that selects all geeks with first name “Christian” that work in a time and material project:

List<String> geeks = streams.streamAll(entityManager, Project.class)
        .where(p -> p.getProjectType() == Project.ProjectType.TIME_AND_MATERIAL)
        .joinList(Project::getGeeks)
        .where(g -> g.getTwo().getFirstName().equals("Christian"))
        .map(p -> p.getTwo().getFirstName())
        .collect(toList());
geeks.forEach(System.out::println);

As can be seen from the code above, we use the first where() clause to select all time and material projects. The joinList() invocation joins the geek table while the subsequent where() clause also restricts to only select geeks with first name “Christian”. Et voila, that is the created SQL query:

select
    geek2_.FIRST_NAME as col_0_0_ 
from
    T_PROJECT project0_ 
inner join
    T_GEEK_PROJECT geeks1_ 
        on project0_.id=geeks1_.PROJECT_ID 
inner join
    T_GEEK geek2_ 
        on geeks1_.GEEK_ID=geek2_.id 
where
    project0_.projectType='TIME_AND_MATERIAL'
    and geek2_.FIRST_NAME='Christian' limit ?

Conclusion: Having worked with JPA’s criteria API some time ago, I must say that the first steps with JINQ are more intuitive and where easier to write down. JINQ really helps to close the gap between the relational database world by using streams in Java 8.

Reference:

Using JINQ with JPA and H2 from our JCG partner Martin Mois at the Martin’s Developer World blog.