I just announced the new Spring Boot 2 material, coming in REST With Spring:

>> CHECK OUT THE COURSE

1. Introduction

Jinq provides an intuitive and handy approach for querying databases in Java. In this tutorial, we’ll explore how to configure a Spring project to use Jinq and some of its features illustrated with simple examples.

2. Maven Dependencies

We’ll need to add the Jinq dependency in the pom.xml file:

<dependency>
    <groupId>org.jinq</groupId>
    <artifactId>jinq-jpa</artifactId>
    <version>1.8.22</version>
</dependency>

For Spring, we’ll add the Spring ORM dependency in the pom.xml file:

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-orm</artifactId>
    <version>5.0.3.RELEASE</version>
</dependency>

Finally, for testing, we’ll use an H2 in-memory database, so let’s also add this dependency to the pom.xml file:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.196</version>
</dependency>

3. Understanding Jinq

Jinq helps us to write easier and more readable database queries by exposing a fluent API that’s internally based on the Java Stream API.

Let’s see an example where we’re filtering cars by model:

jinqDataProvider.streamAll(entityManager, Car.class)
  .where(c -> c.getModel().equals(model))
  .toList();

Jinq translates the above code snippet into a SQL query in an efficient way, so the final query in this example would be:

select c.* from car c where c.model=?

Since we’re not using plain-text for writing queries and use a type-safe API instead this approach is less prone to errors.

Plus, Jinq aims to allow faster development by using common, easy-to-read expressions.

Nevertheless, it has some limitations in the number of types and operations we can use, as we’ll see next.

3.1. Limitations

Jinq supports only the basic types in JPA and a concrete list of SQL functions. It works by translating the lambda operations into a native SQL query by mapping all objects and methods into a JPA data type and a SQL function.

Therefore, we can’t expect the tool to translate every custom type or all methods of a type.

3.2. Supported Data Types

Let’s see the supported data types and methods supported:

  • Stringequals(), compareTo() methods only
  • Primitive Data Types – arithmetic operations
  • Enums and custom classes – supports == and != operations only
  • java.util.Collection – contains()
  • Date API – equals(), before(), after() methods only

Note: if we wanted to customize the conversion from a Java object to a database object, we’d need to register our concrete implementation of an AttributeConverter in Jinq.

4. Integrating Jinq with Spring

Jinq needs an EntityManager instance to get the persistence context. In this tutorial, we’ll introduce a simple approach with Spring to make Jinq work with the EntityManager provided by Hibernate.

4.1. Repository Interface

Spring uses the concept of repositories to manage entities. Let’s look at our CarRepository interface where we have a method to retrieve a Car for a given model:

public interface CarRepository {
    Optional<Car> findByModel(String model);
}

4.2. Abstract Base Repository

Next, we’ll need a base repository to provide all the Jinq capabilities:

public abstract class BaseJinqRepositoryImpl<T> {
    @Autowired
    private JinqJPAStreamProvider jinqDataProvider;

    @PersistenceContext
    private EntityManager entityManager;

    protected abstract Class<T> entityType();

    public JPAJinqStream<T> stream() {
        return streamOf(entityType());
    }

    protected <U> JPAJinqStream<U> streamOf(Class<U> clazz) {
        return jinqDataProvider.streamAll(entityManager, clazz);
    }
}

4.3. Implementing the Repository

Now, all we need for Jinq is an EntityManager instance and the entity type class.

Let’s see the Car repository implementation using our Jinq base repository that we just defined:

@Repository
public class CarRepositoryImpl 
  extends BaseJinqRepositoryImpl<Car> implements CarRepository {

    @Override
    public Optional<Car> findByModel(String model) {
        return stream()
          .where(c -> c.getModel().equals(model))
          .findFirst();
    }

    @Override
    protected Class<Car> entityType() {
        return Car.class;
    }
}

4.4. Wiring the JinqJPAStreamProvider

In order to wire the JinqJPAStreamProvider instance, we’ll add the Jinq provider configuration:

@Configuration
public class JinqProviderConfiguration {

    @Bean
    @Autowired
    JinqJPAStreamProvider jinqProvider(EntityManagerFactory emf) {
        return new JinqJPAStreamProvider(emf);
    }
}

4.5. Configuring the Spring Application

The final step is to configure our Spring application using Hibernate and our Jinq configuration. As a reference, see our application.properties file, in which we use an in-memory H2 instance as the database:

spring.datasource.url=jdbc:h2:~/jinq
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.hibernate.ddl-auto=create-drop

5. Query Guide

Jinq provides many intuitive options to customize the final SQL query with select, where, joins and more. Note that these have the same limitations that we have already introduced above.

5.1. Where

The where clause allows applying multiple filters to a data collection.

In the next example, we want to filter cars by model and description:

stream()
  .where(c -> c.getModel().equals(model)
    && c.getDescription().contains(desc))
  .toList();

And this is the SQL that Jinq translates:

select c.model, c.description from car c where c.model=? and locate(?, c.description)>0

5.2. Select

In case we want to retrieve only a few columns/fields from the database, we need to use the select clause.

In order to map multiple values, Jinq provides a number of Tuple classes with up to eight values:

stream()
  .select(c -> new Tuple3<>(c.getModel(), c.getYear(), c.getEngine()))
  .toList()

And the translated SQL:

select c.model, c.year, c.engine from car c

5.3. Joins

Jinq is able to resolve one-to-one and many-to-one relationships if the entities are properly linked.

For example, if we add the manufacturer entity in Car:

@Entity(name = "CAR")
public class Car {
    //...
    @OneToOne
    @JoinColumn(name = "name")
    public Manufacturer getManufacturer() {
        return manufacturer;
    }
}

And the Manufacturer entity with the list of Cars:

@Entity(name = "MANUFACTURER")
public class Manufacturer {
    // ...
    @OneToMany(mappedBy = "model")
    public List<Car> getCars() {
        return cars;
    }
}

We’re now able to get the Manufacturer for a given model:

Optional<Manufacturer> manufacturer = stream()
  .where(c -> c.getModel().equals(model))
  .select(c -> c.getManufacturer())
  .findFirst();

As expected, Jinq will use an inner join SQL clause in this scenario:

select m.name, m.city from car c inner join manufacturer m on c.name=m.name where c.model=?

In case we need to have more control over the join clauses in order to implement more complex relationships over the entities, like a many-to-many relation, we can use the join method:

List<Pair<Manufacturer, Car>> list = streamOf(Manufacturer.class)
  .join(m -> JinqStream.from(m.getCars()))
  .toList()

Finally, we could use a left outer join SQL clause by using the leftOuterJoin method instead of the join method.

5.4. Aggregations

All the examples we have introduced so far are using either the toList or the findFirst methods – to return the final result of our query in Jinq.

Besides these methods, we also have access to other methods to aggregate results.

For example, let’s use the count method to get the total count of the cars for a concrete model in our database:

long total = stream()
  .where(c -> c.getModel().equals(model))
  .count()

And the final SQL is using the count SQL method as expected:

select count(c.model) from car c where c.model=?

Jinq also provides aggregation methods like sum, average, min, max, and the possibility to combine different aggregations.

5.5. Pagination

In case we want to read data in batches, we can use the limit and skip methods.

Let’s see an example where we want to skip the first 10 cars and get only 20 items:

stream()
  .skip(10)
  .limit(20)
  .toList()

And the generated SQL is:

select c.* from car c limit ? offset ?

6. Conclusion

There we go. In this article, we’ve seen an approach for setting up a Spring application with Jinq using Hibernate (minimally).

We’ve also briefly explored Jinq’s benefits and some of its main features.

As always, the sources can be found over on GitHub.

I just announced the new Spring Boot 2 material, coming in REST With Spring:

>> CHECK OUT THE LESSONS