BT

Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ

Topics

Choose your language

InfoQ Homepage News Simplifying Database Queries with Jinq

Simplifying Database Queries with Jinq

This item in japanese

Bookmarks

Jinq, a library to provide a DSL for database queries, has been made available for Java and Scala. The work is inspired by .NET's LINQ, and aims at enabling easy-to-write queries with support for type safety. As commented by Ming-Yee Iu, creator of the tool, work on Jinq started in 2006 under project name Queryll; however, it wasn't until streams and lambdas were added in Java 8 that a powerful solution could be implemented.

In contrast with other existing libraries, Jinq is not meant to provide full database access capabilities: Jinq is simply a query tool, and therefore can only be used to obtain data from the database. Data manipulation, either insertion, modification or deletion, will still require the developer to adopt some other mechanism. It is for this reason that Jinq includes support to work in conjunction with some of the most popular database access libraries, including all JPA-compatible schemes (like Hibernate or EclipseLink), and jOOQ.

Since it cannot serve as a replacement for existing libraries, some users seem to wonder about the added value of Jinq. To try and show where the value lies, we can compare the same query being performed using different tools.

Let's consider a database of cities and countries of the world. Let's also assume that we need to obtain a list of the countries whose capital has a population of more than three million people. Here is what the corresponding SQL query would look like:

SELECT country.name
FROM country
JOIN city ON country.capital_id = city.id
WHERE city.population > 3000000

Assuming typical mappings and configuration, this is what the equivalent query would look like using Hibernate:

List<String> = session.createQuery("SELECT country.name " +
        "FROM country JOIN city " +
        "WHERE city.population > 3000000")
        .list();

As we can see, HQL takes away some of the complexity of the query, but this is still inside a string, which means there is an array of potential errors that can only be discovered at runtime. Let's see now how we would write this query using jOOQ.

Result<Record> result = create.select(COUNTRY.NAME)
        .from(COUNTRY)
        .join(CITY).on(COUNTRY.CAPITAL_ID.equal(CITY.ID))
        .where(CITY.POPULATION.gt(3000000))
        .fetch();

This gives the programmer a much higher level of type safety: referring to the wrong field, table or operation will result in a compilation error. However, this code is not as fluid as it could be. Finally, let's see how this would be written using the functional approach of Jinq:

List<String> = streams.streamAll(em, City.class)
        .where(c -> c.getCountry().getCapital().equals(c)
                && c.getPopulation() > 3000000)
        .select(c -> c.getCountry().getName())
        .toList();

This code is much closer to the typical structures developers may create while working with streams of data, and allows them to retain a functional programming style when writing code. Internally though, despite the appearance of working with streams, Jinq will use a technique called "symbolic execution" to convert the above code into an actual SQL query that the database will be able to optimise.

At the bytecode level, when this code is reached, Jinq will not execute it directly. Instead, Jinq will go over the different steps and calculate the side effects that these would cause to incoming data; this is what is called symbolic execution. Once the combined side effect has been calculated, this will be transformed into the equivalent SQL query. If the calculated side effect is too complex for Jinq to transform into SQL, it will execute it as normal Java code, meaning the same result will be provided but potentially in a less performant way.

An important fraction of the Java development community has long requested an equivalent of .NET's LINQ in Java. This could never be fully implemented due to language design restrictions: the definition of .NET languages like C# was modified to integrate LINQ into the language, something that Java architects have always ruled out. For this reason, Jinq may be on of the closest possible approximations.
 

Rate this Article

Adoption
Style

BT