InfoQ Homepage News Simplifying Database Queries with Jinq

# Simplifying Database Queries with Jinq

This item in japanese

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.

Style

## Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

• ##### So now Java try to catch up C#

by Hung Nguyen /

• ##### Re: So now Java try to catch up C#

by wakin imgen /

• ##### Re: So now Java try to catch up C#

by Hung Nguyen /

• ##### Re: So now Java try to catch up C#

by wakin imgen /

• ##### Re: So now Java try to catch up C#

by Cameron Purdy /

• ##### Re: So now Java try to catch up C#

by wakin imgen /

• ##### Re: So now Java try to catch up C#

by Luis Espinal /

• ##### Re: So now Java try to catch up C#

by wakin imgen /

• ##### We still haven't learned

by Luis Espinal /

• ##### It's about how to use the tool, not about the tool itself

by Hung Nguyen /

• ##### Re: It's about how to use the tool, not about the tool itself

by Luis Espinal /

• ##### So now Java try to catch up C#

by Hung Nguyen /

Your message is awaiting moderation. Thank you for participating in the discussion.

So now Java try to catch up C# :)
The first one is lambda, then now linq, and another one could be async/await.
Love C#, love the beauty of IQueryable.

• ##### Re: So now Java try to catch up C#

by wakin imgen /

Your message is awaiting moderation. Thank you for participating in the discussion.

Same here, I love C#. Every so often that I hear that C# is a Java clone, that's so far from truth.

• ##### Re: So now Java try to catch up C#

by Hung Nguyen /

Your message is awaiting moderation. Thank you for participating in the discussion.

Ha ha, then you can tell them that Java is a clone of C/C++.
Even with Lambda expression that Java has finally implemented still 5 years after C# and far away behind C# in term of the easy of use and stylish.
Generic in Java not trully gerenic compared with C#.
Other beautiful features of C# that Java devs are dreaming about is Properties, Partial class, Event & Delegate, Object initializer etc.

The only thing make Java more popular than C# is because it was born years before C#, and it's open source. But now that fact will change soon since .NET vNext is open source and trully cross platforms.

• ##### Re: So now Java try to catch up C#

by wakin imgen /

Your message is awaiting moderation. Thank you for participating in the discussion.

Well said!!

• ##### Re: So now Java try to catch up C#

Your message is awaiting moderation. Thank you for participating in the discussion.

Yes, .NET and C# started life as a clean-room implementation of Java; that is what we call a clone. Before it ("Project Cool") was completed, Anders drove C# in a slightly different direction and Sun sued Microsoft over Java, so Microsoft's plans changed quite a bit before C# 1.0 was released.

Regardless, our industry is full of examples of "standing on the shoulders of giants", so why is it a bad thing that one language takes good ideas from another? Isn't that better than starting from scratch and putting all sorts of untested ideas into a language? C# would have sucked had they done that!

I think that it's a Very Good Thing (tm) that Java has examples (both good and bad) in C# that it can learn from as it evolves.

• ##### We still haven't learned

by Luis Espinal /

Your message is awaiting moderation. Thank you for participating in the discussion.

Why on Earth do we want to do this? We are tightly coupling elements from the database domain into application code. Saying getCountry() is no better than having a column name ("COUNTRY") as a literal in your code.

Type safety in this particular case doesn't give you anything. And neither does API fluidity (again, in this particular case, in these particular examples.)

Keep your query logic external (in a resource file, for instance) behind a high-level domain specific layer, and have your unit tests exercise those execution paths.

• ##### It's about how to use the tool, not about the tool itself

by Hung Nguyen /

Your message is awaiting moderation. Thank you for participating in the discussion.

Totally agreed @Cameron, it's always a good things for learning each other.

@Luis: LINQ is not about database, it's an abstraction to be extended to support many objects so that there's Linq2Objects for in-memory objects, there's Linq2XML for XML, there's Linq 2 Entities for database access which is used in Entity Framework. Regarding your concern about the possibility of tightly couple between database and domain, not to worry because you can hide everything related to IQueryable behind a Repository. It's about how you use the tool, not about the tool itself. Actually, your concern is shared by a lot of people at the first glance looking at some of the example codes of LINQ, especially who comes from DDD school.

• ##### Re: It's about how to use the tool, not about the tool itself

by Luis Espinal /

Your message is awaiting moderation. Thank you for participating in the discussion.

Actually, if you look at my previous comment, you see that I advocate hiding the detail under a domain-specific abstraction (a Repository, as you suggested, being an example of it.)

My concern, one that I have validated many times, is that people do not implement such abstractions. As a result, that tight coupling is spread all over.

Now, my comment wasn't so much regarding LINQ, but about Jinq. LINQ is supported from the ground up by the runtime, by the language and by the compiler.

Jinq on the other hand, it is just a fluid API with a class generator, placed on top of a language, Java, that does not have a transparent support that we can find with C# and LINQ.

The Java space is littered by such attempts of coupling fluid APIs to a language with no syntactic support for them. The results tend to be inflexible and unmaintainable (specially when the developer does not put the time to implement higher-level abstractions to hide them.)

• ##### Re: So now Java try to catch up C#

by wakin imgen /

Your message is awaiting moderation. Thank you for participating in the discussion.

@Cameron, yes, I am all for standing on giant's shoulder. But please don't call out C# is a Java clone which a lot of people seem to think so or do so.

• ##### Re: So now Java try to catch up C#

by Luis Espinal /

Your message is awaiting moderation. Thank you for participating in the discussion.

@Cameron, yes, I am all for standing on giant's shoulder. But please don't call out C# is a Java clone which a lot of people seem to think so or do so.

That's not what he said. Cameron stated, quite correctly, that C# started as a clean-room clone of Java. Then he stated, quite correctly also, that Anders (the father of C#) took it in a different direction... and Cameron's post implies that this has had positive results for C# as a language.

From my professional experience (having done work in both Java and C#, as well as C and C++), I'd say that C# is a more mature language than Java, and it is a lot more fun to work with.

C# properties, attributes, lambdas, async, LINQ, I love them all. It doesn't change the historical fact that C# started eons ago as a clone. That is a historically accurate statement, which is different from what some people do to dismiss C#'s capabilities (which is not what Cameron said at all.)

• ##### Re: So now Java try to catch up C#

by wakin imgen /

Your message is awaiting moderation. Thank you for participating in the discussion.

You said it so well, my fault. You are right. Thanks for making it clear!

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Is your profile up-to-date? Please take a moment to review and update.

Note: If updating/changing your email, a validation request will be sent

Company name:
Company role:
Company size:
Country/Zone:
State/Province/Region:
You will be sent an email to validate the new email address. This pop-up will close itself in a few moments.