Easy database queries for Java
Jinq queries are based on Java 8 streams. Java streams provide a functional approach to working with data in Java. Most functional languages offer a similar framework for working with data.
In the streams model, if you have a collection of data somewhere, you can create a stream over that data. The stream will pull data out of that collection one at a time for you to process. For example, if you have a database of city objects, you can create a stream of those city objects. Cities will be streamed out of the database, one at a time.
Once you have a stream of data, you can write code for processing this data as it passes through the stream. For example, you can write some code that filters some of the data. If you have a stream of city objects, your filter code will be run for each city passing through the stream. For each city, your code can decide whether to discard the city object or to pass it along in the stream. The picture below shows a filter that only passes along cities with a population of over 25 million.
You can attach multiple processing operations to the stream, creating a processing chain. In the diagram below, there are two processing stages. The first stage filters city objects so that only cities with populations above 25 million can through through the stream. The second stage of processing transforms the data as it passes through. It takes each city object and extracts the name of the city. Only the names of the cities are then passed down the stream.
Data only passes through a stream once, and you only get to see each object one at a time. If you want to do more complicated operations or to store the result of your processing, you have to collect all the objects at the end of the stream and put them into a list.
Jinq adapts the Java stream model for databases by allowing you to stream database entities and by providing additional stream operations that are useful for expressing database operations. Do note that the Jinq query model is only conceptually similar to the Java 8 stream model. Jinq does not actually stream database data to your computer and does not process your data in a processing chain. Jinq analyzes the processing chain and rewrites it as database query that can be run efficiently by the database.
A basic Jinq query looks like the code below:
streams.streamAll(em, City.class) .where( city -> city.getPopulation() > 25000000 )
The first line creates a stream of all the City objects from the database referred to by the variable em. The method streamAll() returns a special JinqStream that you can perform stream processing on.
The second line filters that stream. Calling the where() method sets up a processing stage in the stream for filtering data. As a parameter, that method takes some code for doing filtering. This code is in the form of a function that takes a city as input. The function returns true to pass the city along in the stream or false to discard the city. Conceptually, as city objects are sent along the stream, the function will be called for each city, and the function can decide whether to keep the city in the stream or not.
Note: The method for filtering is called where() to match the terminology used in database queries.
A typical database query in Jinq might look like the code below:
List<String> results = streams.streamAll(em, City.class) .where( c -> c.getPopulation() > 25000000 ) .select( c -> c.getName() ) .toList();
This query creates a stream of cities from a database. It then uses there where() method to filter the cities so that the stream only contains cities with a population of over 25 million. Then, the query uses the method select() to transform the stream of cities. For each city, it extracts the name of the city and passes the name only back into the stream. Finally, these names are collected into a normal Java List. Since the names of the cities are strings, this list is a list of strings.
When this code is run, Jinq will analyze the code and rewrite it into a database query that can be run efficiently on a database:
SELECT C.Name FROM City C WHERE C.Population > 25000000
Jinq is written using normal Java code, but not all Java code can be translated by Jinq into database queries. For Jinq to automatically translate your Java code into database queries, you must use operations that are available in the database query language. If you use operations that can't be translated, Jinq will simply run your code normally. You will still get the results that you expect, but your code will not take advantage of possible performance benefits from being run as a database query.
For Jinq to translate the functions you pass to it into database queries, your functions must satisfy these restrictions:
The queries that an application needs to execute on a database are not completely static. Applications need to make variants of their queries to handle different situations. Jinq supports these variants in two ways.
Parameters allow you to substitute different values into a query. Instead of having to use constant values in a query, you can use a different value depending on what you need at runtime. Parameters are specified in Jinq by referring to an outside variable in a Jinq query. By setting the variable to different values, different queries are created.
int parameter = 100; streams.streamAll(em, Customer.class) .where( c -> c.getId() == parameter )
Variables used as parameters in queries can be any of the basic JPA data types supported by Jinq. The variables must be local variables. This restriction is required because Jinq uses Java serialization to find the code of the lambda function used in the query and to find the values of parameters to the query. Accessing non-local variables in a query like fields can cause problems with this serialization.
Jinq translates your Java code into database queries at runtime. As such, you can change your queries at runtime. Since Jinq queries are written using Java code, it's not possible to change that code at runtime. But it is possible to compose the different parts of a Jinq query in different ways at runtime. Below is some code which illustrates how a Java servlet can create slightly different queries based on different URL parameters. The code creates a stream of product information from a database. Then, based on whether different restrictions are specified in the URL parameters, the code will selectively apply different where() restrictions to this stream, resulting in different final queries. At the end, it then runs the resulting query.
// Get a stream of products for a product listing
JinqStream<Product> stream =
streams.streamAll(em, Product.class);
// If request contains a name restriction
String name = request.getParameter("name");
if (name != null)
stream = stream.where( p -> p.getName().equals(name) );
// If request contains a price restriction
String price = request.getParameter("price");
if (price != null) {
long priceLimit = Long.valueOf(price);
stream = stream.where( p -> p.getPrice() < priceLimit );
}
// Return the list of found products
return stream.toList();
This composition of a query doesn't have to happen entirely within a single method either. It's also possible to compose a query across different methods by passing the database stream to different methods for processing.
Jinq has basic support for JPA-compliant object-relational mappers such as EclipseLink and Hibernate. If you have an existing project using JPA, you can easily add Jinq to your project and use Jinq for some of your queries. Jinq will issue queries using JPA's JPQL, so you can mix JPA queries and Jinq queries without any issues.
To use Jinq with your JPA code, you should either
Jinq queries are written by taking streams of database data and filtering and transforming that data. To use Jinq when using JPA for database access, you need a way to get a Jinq stream of JPA data. You can do this with the org.jinq.jpa.JinqJPAStreamProvider class.
The JinqJPAStreamProvider's constructor normally takes a JPA EntityManagerFactory as a parameter. The JinqJPAStreamProvider is intended to be a singleton in your application that only needs to be created once.
EntityManagerFactory entityManagerFactory =
Persistence.createEntityManagerFactory("JPA");
JinqJPAStreamProvider streams =
new JinqJPAStreamProvider(entityManagerFactory);
In some cases, it is easier to get a reference to an EntityManager instead of an EntityManagerFactory. You can also create JinqJPAStreamProvider objects from an EntityManager whenever you want to perform a Jinq query although this is less efficient.
JinqJPAStreamProvider streams = new JinqJPAStreamProvider(entityManager.getMetamodel());
Once you've created a JinqJPAStreamProvider, you can then use the streamAll() method to get a Jinq stream of JPA entities from a database. For example, if you have a Customer JPA entity and a JPA EntityManager called em, then you can get a stream of Customer objects from your database using the code below:
JinqStream<Customer> customers = streams.streamAll(em, Customer.class);
Once you have this stream of entities, you can filter and transform it using the Jinq approach:
List<Customer> customers = streams
.streamAll(em, Customer.class)
.where( c -> c.getName().equals("Bob") )
.toList();
Some JPA implementations have incomplete or incorrect information in their JPA Metamodels. In these situations, Jinq will not have enough information to correctly translate your code into queries.
If this happens, you can explicitly pass the necessary information into Jinq. You do this by using the registerAssocationAttribute() method of the JinqJPAStreamProvider. You pass in three parameters. The first is the method that you want Jinq to recognize. You can use Java reflection to find this method. The second parameter is a String with the field name that Jinq should translate the method to. Whenever Jinq sees a call to the method in your code, it will translate that into an access of the field that you specified. Lastly, the third parameter describes whether the association is a plural association or not. In most cases, this last parameter should always be false unless the method returns a List or Collection, in which case, the parameter should be true.
The code below tells Jinq about an association for the Customer.getName() method. Whenever Jinq sees this method, it will translate it to an access of the name field.
JinqJPAStreamProvider streams =
new JinqJPAStreamProvider(entityManagerFactory);
streams.registerAssociationAttribute(
Customer.class.getMethod("getName"), "name", false);
After registering that association, if Jinq were to encounter the following code:
streams.streamAll(em, Customer.class) .select( c -> c.getName() ) .toList();
Then Jinq would translate c.getName() into name:
SELECT C.name FROM Customer C
Jinq currently has basic support for the basic JPA data types. The table below shows the main operations supported for each data type.
| Java type | Supported operations |
|---|---|
| String | equals(), compareTo() * |
| BigDecimal, BigInteger, Integer, int, Double, double, Long, long | ==, <, <=, >, >=, !=, +, -, *, / |
Float, float  | ==, <, <=, >, >=, !=, +, -, *, / |
| Boolean, boolean | !, &&, ||, == |
| byte[] | |
| java.util.Date, java.sql.Date, java.sql.Time, java.sql.Timestamp, java.util.Calendar | equals(), before(), after() |
| enums | ==, != |
| entities | ==, != |
| java.util.Collection | contains(), JPQL.isIn() |
| java.util.UUID | equals(), ==, !=, compareTo() ** |
| AttributeConverter types *** | equals(), ==, != |
Hibernate 5.2 contains preliminary support for Java 8's java.time.* APIs. When Jinq is used with Hibernate 5.2, it supports the operations listed below using these data types. When Jinq is used with its default configuration in which "isAllEqualsSafe" is enabled, then Jinq also supports the equals() method on these data types as well.
| Java type | Supported operations |
|---|---|
| Instant | isBefore(), isAfter() |
| LocalDate | isBefore(), isAfter(), isEqual() |
| LocalTime | isBefore(), isAfter() |
| LocalDateTime | isBefore(), isAfter(), isEqual() |
| OffsetTime | isBefore(), isAfter(), isEqual() |
| OffsetDateTime | isBefore(), isAfter(), isEqual() |
| ZonedDateTime | isBefore(), isAfter(), isEqual() |
Jinq generally does not generally support casts between different data types. This is due to a limitation of JPQL, which does not support casting data between types in queries. As such, Jinq cannot translate Java code that contains casts into equivalent JPQL queries.
JPQL does support implicit widening casts when performing certain operations. For example, when adding an integer to a double, the integer is automatically widened to a double. This is known as numeric promotion.
Jinq also lets you use implicit or explicit casts in your code if they are consistent with Java numeric promotion rules. If you are performing a mathematical operation involving two values, you can cast one of the values to a wider type so that both values have the same type. The chart below shows the numeric promotion priority for Java types accepted by Jinq. For example, when multiplying an int and a long, Java will automatically cast the int to a long. Jinq will allow the cast because Jinq is able to properly translate that code into JPQL.
| Java Type Numeric Promotion Priority |
|---|
| Double/double |
| Float/float |
| BigDecimal |
| BigInteger |
| Long/long |
| Integer/int |
When adding a long and a BigDecimal, Java will not perform any automatic casts, but you can explicitly convert the long to a BigDecimal before you perform the addition, and Jinq will accept the code and properly translate it into JPQL.
// NetWorth is a BigDecimal, but parameter is a long.
// Jinq allows parameter to be converted to a
// BigDecimal so that it can be compared to NetWorth.
long parameter = 5000000000l;
List<Customer> customers = streams
.streamAll(em, Customer.class)
.where( c -> c.getNetWorth().compareTo(
new BigDecimal(parameter)) > 0 )
.toList();
Jinq can translate various Java methods into equivalent JPQL functions. When you use those Java methods in your code, Jinq will automatically translate them into functions that can run on the database.
| Java | Translated JPQL | |
|---|---|---|
| Math.abs() / BigDecimal.abs() / BigInteger.abs() | ABS() | |
| Math.sqrt() | SQRT() | |
| int % | MOD() | |
| String.toUpperCase() / String.toLowerCase() | UPPER() / LOWER() | |
| String.trim() | TRIM() | |
| String.substring(start,end) | SUBSTRING() | |
| String.length() | LENGTH() | |
| String.indexOf() | LOCATE() | |
| String.contains() | LOCATE() > 0 | |
| String.startsWith() | LOCATE() = 1 | |
| String + | CONCAT() | |
| JPQL.like() | LIKE |
Jinq has experimental support for user-defined database functions. Because this functionality is experimental, the error-checking may not be robust, and the API may change in the future.
In order to use custom SQL functions, you must first create your function in the database and register the function with your ORM so that it is available in JPA queries (in many cases, it might not be necessary to register anything with the ORM). Then, you must register the function with Jinq.
To use a custom function in Jinq, you must create a static Java method that your Jinq Java code can call in queries. This method does not need to have a proper implementation. It can be empty inside. When your Jinq query is converted to SQL, the user-defined function in your database will be invoked instead of the static method you defined.
class Functions {
static int myFunc(String a, String b)
{
return 0;
}
}
Then, you must register the function with Jinq so that Jinq knows to translate calls to that function into calls to the user-defined function in your database. You do this by calling the method JinqJPAStreamProvider.registerCustomSqlFunction() with the static method and the database function as parameters.
JinqJPAStreamProvider streams =
new JinqJPAStreamProvider(entityManagerFactory);
streams.registerCustomSqlFunction(
Functions.class.getMethod("myFunc",
String.class, String.class),
"dbFunction");
You can then use the static method in your queries, and Jinq will properly translate it to use your custom SQL function.
customerStream .where(c -> Functions.myFunc(c.getName(), "Bob") == 1);
Although Java does support null references, it does not have support for null propagation and the three-valued logic used by JPQL and other databases. For example, the expression 1 + null evaluates to different things in Java or JPQL
| Code | Result | |
|---|---|---|
| 1 + null | Java: NullPointerException | |
| 1 + NULL | JPQL: NULL |
When your Java code for Jinq queries is translated into JPQL queries to run on a database, database rules for handling NULLs will be used instead of normal Java rules.
If your Java code compares a reference to null, Jinq will translate that code into "IS NULL" or "IS NOT NULL" operations when translated to JPQL.
| Java | Translated JPQL | |
|---|---|---|
| employee.getManager() == null | employee IS NULL | |
| employee.getManager() != null | employee IS NOT NULL |
It is sometimes necessary for a query to return multiple values. Since Java does not provide any generic tuple objects that can be used in these situations, Jinq provides its own tuple objects. They can be found in the package org.jinq.tuples.
| Tuple type | # Values | Accessors | ||
|---|---|---|---|---|
| Pair | 2 | getOne(), getTwo() | ||
| Tuple3 | 3 | getOne(), getTwo(), getThree() | ||
| Tuple4 | 4 | getOne(), getTwo(), getThree(), getFour() | ||
| Tuple5 | 5 | getOne(), getTwo(), getThree(), getFour(), getFive() | ||
| Tuple6 | 6 | getOne(), getTwo(), getThree(), getFour(), getFive(), getSix() | ||
| Tuple7 | 7 | getOne(), getTwo(), getThree(), getFour(), getFive(), getSix(), getSeven() | ||
| Tuple8 | 8 | getOne(), getTwo(), getThree(), getFour(), getFive(), getSix(), getSeven(), getEight() |
The tuples are immutable, so their contents can only be set when they are created. Below is an example of a Pair tuple being used to return two String values in a query.
customerStream.select( c -> new Pair<>(c.getFirstName(), c.getLastName()) );
It is also possible to read from a tuple in a query. In fact, several of the Jinq APIs like join() or group() return tuples, so it is sometimes necessary to do so.
List<Account> bobsAccounts = customerStream
.where( c -> c.getFirstName().equals("Bob") )
.join( c -> new Pair<>(c, c.getAccount()) )
.select( pair -> pair.getTwo() )
.toList()
By default, Jinq uses its own objects for storing multiple return values: Pair, Tuple3, Tuple4, Tuple5, etc. These tuples are useful for holding miscellaneous results from a query that may have varied types. Unfortunately, these tuples aren't very descriptive as to their contents, so it may be cumbersome to use them in complex queries. Jinq allows you to define your own objects to be used as tuples.
Jinq must be made aware of how these tuples are initialized and used. These tuples can be registered with Jinq by using JinqJPAStreamProvider.registerCustomTupleStaticBuilder or JinqJPAStreamProvider.registerCustomTupleConstructor to register a static method or constructor respectively that is used to build the tuple object. When you register the tuples, you can also register the methods used to read values from the tuples. You can register multiple constructors and static builder methods for the same tuple objects. The construction parameters of the different constructors and static methods must always be in the same order and must fill a prefix of its tuple values. For example, you can have a constructor for setting the first five values of your custom tuple and another constructor for the first two values of your custom tuple. You cannot have a constructor that only sets the second and fifth values of your custom tuple though.
public static class CustomerPurchases
{
String name;
long count;
public CustomerPurchases(String name, long count)
{
this.name = name; this.count = count;
}
public String getName() { return name; }
public long getCount() { return count; }
}
JinqJPAStreamProvider streams =
new JinqJPAStreamProvider(entityManagerFactory);
streams.registerCustomTupleConstructor(
CustomerPurchases.class.getConstructor(
String.class, Long.TYPE),
// Registering the getters are optional
CustomerPurchases.class.getMethod("getName"),
CustomerPurchases.class.getMethod("getCount"));
Here are some examples of how different types of queries can be written in Jinq.
The where() method lets you filter database data.
customerStream
.where( c -> c.getName().equals("Bob") );
You must pass a function to the where() method. For each element in the stream, this function should return true if the element should be kept or false if the element should be filtered out. The function can contain logical operators like || or &&. The current implementation of Jinq can only handle a small number of these operators, however.
cityStream
.where( c -> c.getPopulation() > 10000
&& c.getLandArea() > 300 );
Sometimes, you only want certain fields of a database record. In that case, you can use the select() method to only return the data you want to use.
customerStream .select( c -> c.getFirstName() );
The select() method takes a function as input. This function is applied to each element of the stream. The function can convert the element to another form. For example, it can return only certain fields of the data, or it can perform some calculation on the data.
cityStream .select( c -> c.getPopulation() / c.getLandArea() );
If you want to return more than one piece of data, you can do this by putting the different pieces of data in a Pair or Tuple object.
customerStream.select( c -> new Pair<>(c.getFirstName(), c.getLastName()) );
You can also use if statements to do more complicated transformations of data.
customerStream.select( c -> {
if (c.getCountry().equals("US"))
return "US";
else
return "Other";
});
Databases contain different tables for different entities. If the relationships between these entities are known to Jinq, then you can easily navigate between different entities in your queries. For example, the query below gets the names of the country that each city is in. These joins involving 1:1 and N:1 links can be used in most Jinq methods such as where() or select().
cityStream .select( c -> c.getCountry().getName() );
If you want to return the original City object as well as the Country object, you can use a Pair to return both the city and its associated country.
cityStream .select( c -> new Pair<>(c, c.getCountry()) );
In some cases, you want to join one element with more than one other element. For example, you might want a list of countries and the cities in them. If you have a stream of Country objects, you can join them with the cities in each country to make a stream of Country and City objects. With the join() method, takes a function as input. For each element of the stream, the function should return a stream of objects to join with that element. Jinq will then merge the element with the stream. It will stream out new Pair objects containing the element and each element of the stream.
// Look at European countries only
JinqStream<Country> countries = countryStream
.where( c -> c.getContinent().equals("Europe") );
// For each country, join with the list of cities.
// This gives us a stream of country-city pairs.
// Because Country.getCities() returns a List, we need
// to use JinqStream.from() to convert the List to a
// Stream.
JinqStream<Pair<Country,City> > pairs = countries
.join( c -> JinqStream.from(c.getCities()) );
The example above shows a join involving 1:N navigational links. N:M navigational links are also supported.
Because it is cumbersome to convert Collection objects into JinqStream objects, Jinq provides a convenience method that can be used for joining to a Collections returned by 1:N and N:M navigational links. (Note: The Scala version of Jinq supports automatic implicit conversions of Java Collections to Jinq iterators, so these convenience methods are not necessary in Scala.)
// Look at European countries only
JinqStream<Pair<Country,City> pairs = countryStream
.where( c -> c.getContinent().equals("Europe") )
.joinList( c -> c.getCities() );
Jinq also supports cross-joins between arbitrary entities. To do this, the function passed to the join method simply returns a stream of all the entities of a certain type. Unfortunately, creating a stream of an entity requires a reference to an EntityManager. All outside variables used in a Jinq query must be serializable and be a basic JPA type. An EntityManager does not qualify.
To get around this problem, Jinq has a variant of the join() method. In this variant, the function passed to the join() method takes two parameters. The first parameter is the element of the stream being joined. The second parameter is a special source of streams that can be used to create new streams without needing an EntityManager. This stream source will create streams using the same EntityManager used to create the original stream.
// This code cannot be translated to a database query // because Jinq does not allow you to pass in the // EntityManager em into the query. customerStream.join( c -> streams.streamAll(em, Account.class) ); // This variant is ok. The stream source is supplied by // Jinq instead of coming from outside. customerStream.join( (c, source) -> source.stream(Account.class) );
In addition to supporting normal joins, Jinq also supports left outer joins. With a normal join, each element is paired up with elements from another stream. If this other stream is empty, then nothing is output. With a left outer join, each element is still paired up with elements from another stream. But if this other stream is empty, a pair is still output using the element and NULL.
For example, suppose we have a stream of Country objects. If we then do a left outer join between each country and the mega-cities in that country, we will end up with the results below. With a normal join, no pair would be outputted for Belgium because there are no extremely large cities in Belgium.
| Countries | Left Outer Join |
|---|---|
| Spain, cities: [Barcelona, Madrid] | Pair(Spain, Barcelona) Pair(Spain, Madrid) |
| Canada, cities: [Toronto] | Pair(Canada, Toronto) |
| Belgium, cities: [] | Pair(Belgium, null) |
The query to do the left outer join between countries and the cities in each country is shown below. The query starts with a stream of countries. The method leftOuterJoin() is then called on that stream. The leftOuterJoin() method takes a function and applies it to each country in the stream. For each country, this function returns a stream of objects to join with. In this case, the function get the list of cities in each country, creates a stream from that list, and returns it. To generate proper JPQL queries, the function passed to the leftOuterJoin() method must perform its join using a navigational link. Arbitrary outer joins are not allowed.
countryStream .leftOuterJoin(c -> JinqStream.from(c.getCities()))
The example above shows a left outer join using a 1:N or N:M navigational link. It's also possible to do a left outer join involving a 1:1 or N:1 navigational link. In that situation, you need to return a stream containing a single object only. Instead of using JinqStream.from() to create a stream from a Collection, you should use JinqStream.of() to create a stream containing a single object.
EmployeeStream .leftOuterJoin(e -> JinqStream.of(e.getSpouse()))
The example returns a list of employees and their spouses. If an employee has no spouse, the left outer join means that the employee with be paired with NULL.
If your JPA provider has support for JPA 2.1, Jinq also support a variant of leftOuterJoin() that passes a stream source to the join lambda. The join lambda is not limited to only returning navigational queries but can also be used for cross-joins with arbitrary entity streams. The leftOuterJoin() variant also lets you specify the conditions under which elements from the two streams should be joined. Below is an example of such a query that lists cities and their museums. Although there is no direct association link between museums and cities, museums have a cityName field that can be used for the join.
CityStream
.select( city -> city.getName() )
.leftOuterJoin(
(city, source) -> source.stream(Museum.class),
(city, museum) -> city.equals(museum.getCityName())
)
When the above code is translated to JPQL, it will result in a query using OUTER JOIN...ON.
SELECT A.name, B
FROM City A OUTER JOIN Museum B
ON A.name = B.cityName
The selectAll() is a variant of the join() and select() methods. Similar to the join() methods, the function passed to the selectAll() method should return a stream of data. The returned streams of data will be flattened and concatenated together. The returned streams of data are not paired up with the existing contents of the stream, so the selectAll() method behaves like a traditional select() method as well.
The query below returns a list of mountains in France that are taller than 3000m. The query does involve traversing a N:M association, so a join() could have been used in the query. But only the mountains are needed, so there is no need to pair up the mountains with countries and to return countries in the results. As a result, the selectAll() method was used instead.
streams.streamAll(em, Country.class)
.where(c -> c.getName().equals("France"))
.selectAllList(c -> c.getMountains())
.where(m -> m.getHeight() > 3000)
.toList();
Jinq also supports JPQL's JOIN FETCH queries, which are useful for executing certain types of queries more efficiently.
When reading entities from a database, it is sometimes useful to read some related entities as well from the same database queries. If these related entities are read separately, it can result in a storm of extra database queries. For example, when reading a list of countries from a database, you might know that you are also interested in examining the cities in each of those countries. It can be very inefficient to fetch the city data separately from the country data.
List<Country> countries =
streams.streamAll(em, Country.class)
.where(c -> c.getContinent.equals("Europe"))
.toList();
for (Country c: countries) {
System.out.println("Cities in country: " + c.getName());
// Getting the list of cities results in a new database
// query for each country.
for (City city: c.getCities()) {
System.out.println(" " + city.getName());
}
}
The joinFetch() method allows you to fetch the city data when reading the country data. This city data will not be included in the query results, but will be cached in memory. Navigating to city data from a country will not result in a database query being executed.
// Use joinFetch() to fetch cities while reading countries
List<Country> countries =
streams.streamAll(em, Country.class)
.where(c -> c.getContinent.equals("Europe"))
.joinFetchList(c -> c.getCities())
.toList();
for (Country c: countries) {
System.out.println("Cities in country: " + c.getName());
// Cities were already cached, so no new queries
// are executed here
for (City city: c.getCities()) {
System.out.println(" " + city.getName());
}
}
The standard JinqStream does not support these queries. A JPAJinqStream must be used instead. Jinq's normal JPA stream provider returns a JPAJinqStream. The JPAJinqStream supports joinFetch(), joinFetchList(), leftOuterJoinFetch(), and leftOuterJoinFetchList() methods, which are "FETCH" versions of the normal Jinq join() methods.
The joinFetch() methods do not alter the results returned by the query. They act as a hint telling JPA to follow a single association or navigational link to a related entity and to cache the results. Due to differences in the implementations of JOIN FETCH among different JPA providers, it may also be necessary to add a call to the distinct() method to prevent an entity from being returned multiple times in the query results.
The crossJoin() method performs a full join between the items of two streams. Unlike the joinList() method, crossJoin() takes another stream directly as a parameter instead of a lambda returning a stream. This difference can sometimes make it easier to write queries that join unrelated entities. Below is an example that joins a stream of countries with a stream of patents, resulting in a full of join of all the possible combinations of countries and patents.
JinqStream<Country> countries =
streams.streamAll(em, Country.class);
JinqStream<Patent> patents =
streams.streamAll(em, Patent.class)
.where(p -> p.isGlobal());
List<Pair<Country, Patent>> localPatents =
countries.crossJoin(patents).toList();
The crossJoin() method can only be used to join streams that represent queries based on the same entity manager.
Support for this method is still experimental. Parts of the implementation may not be complete and the error-checking may not be robust.
Jinq has methods for computing simple sums and other aggregates over objects. For example, the code below computes the sum of some double values.
streams.streamAll(em, Order.class) .sumDouble( o -> o.getTotalValue() );
When an aggregation method is called, the query for the aggregation is executed immediately and the result is returned. This is unlike other stream operations which simply return another stream which can be used to build up a processing chain. Aggregation methods use up the stream and materialize the result immediately; they do not process the data lazily.
To count the number of items there are in a stream, you can use the count() method.
streams.streamAll(em, Customer.class) .count();
The method count() returns a Long value. The count includes null entries.
Jinq offers various sum...() methods for calculating the sum of values from the stream. For type safety reasons, there are different sum...() methods for the different types that you are adding up.
| Java type | sum...() |
|---|---|
| int/Integer | sumInteger() |
| long/Long | sumLong() |
| double/Double | sumDouble() |
| BigInteger | sumBigInteger() |
| BigDecimal | sumBigDecimal() |
All the different sum...() methods take a function as input. This function is applied to each element of the stream. The function should return the value that should be added to together.
The example below calculates the total amount of sales. For each sale, it multiplies the number of items sold by the per item price. Then it adds them all together to get the total sales.
long totalSales = streams.streamAll(em, Sale.class) .sumInteger( s -> s.getQuantity() * s.getPrice() );
The min() and max() methods can find the minimum or maximum value in a stream. They each take a function as input. The function is applied to each item of the stream and should return the value that Jinq should find the minimum or maximum of. The value can be a numeric type or other known Comparable type such as a Date.
Date mostRecentSale =
streams.streamAll(em, Sale.class)
.max( s -> s.getDate() );
The avg() method calculates an average over items from the stream. You pass a function to the avg() method that takes an item of the stream as input and returns the value to be averaged. For example, the code below shows how to calculate the average sale value. For each sale, it multiplies the number of items sold by the per item price, giving the total value of that particular order. Then those sale values are averaged.
double average = streams.streamAll(em, Sale.class) .avg( s -> s.getQuantity() * s.getPrice() );
Using multiple queries to compute more than one aggregate function can be inefficient because the database might repeat the same computations for those different queries. For example, in the code below, two queries are used to calculate the maximum salary and average salary of all the employees at a company. It would be more efficient for a database to iterate over the salaries one time and compute the average and maximum at the same time. Since two queries are used, most databases will actually iterate over all the salaries twice, once for each query.
long maxSalary =
streams.streamAll(em, Employee.class)
.max(e -> e.getSalary());
double avgSalary =
streams.streamAll(em, Employee.class)
.avg(e -> e.getSalary());
Jinq provides functionality for computing multiple aggregates using a single query. To do this, you should use the method aggregate(). The aggregate() method takes the stream of data, makes copies of that stream, and then passes that stream to multiple functions. Those functions should, in turn, take a stream as input and calculate aggregates on the stream. The aggregate() method then takes the aggregates calculated by the functions and puts them together in a tuple object. If two aggregates are calculated using two functions, a Pair object will be returned. If three aggregates are calculated using three functions, a Tuple3 object will be returned. If four aggregates are calculated, a Tuple4 object will be returned. And so on.
In the code below, a stream of employees is created. The aggregate() method is called, and two methods are passed in. That aggregate() method will make two copies of the employee stream, passing one stream to the first function and the other copy to the second function. The first function takes the stream of employees and calculates the maximum salary. The second function takes the stream of employees and calculates the average salary. Since there are two functions, the aggregate() method will a Pair of the two computed aggregates.
Pair<Long, Double> salaryAggregates =
streams.streamAll(em, Employee.class)
.aggregate(
stream -> stream.max(e -> e.getSalary())
stream -> stream.avg(e -> e.getSalary())
);
Also see the notes on grouping and multiple aggregates for more details about using multiple aggregates.
The methods sortedBy() and sortedDescendingBy() allow you to sort your query results. Unlike sorting methods used by Java collections, these sorting methods do not use a Comparator for sorting. Instead, the methods take a function as input. This function takes an element of the stream as input and returns the field or expression that the stream should be sorted by. For example, the query shown below sorts customer orders by the value of those orders.
streams.streamAll(em, Order.class) .sortedBy( o -> o.getTotalValue() );
The sortedBy() method sorts items in ascending order while sortedDescendingBy() can be used to sort items in descending order. Streams can be sorted multiple times. The last specified sort will become the primary sort key, the second-last specified sort will become the secondary sort key, and so on for all the specified sorting keys.
// Employees will be sorted by country first. // Employees with the same country will be sorted // by name. streams.streamAll(em, Employee.class) .sortedBy( e -> e.getName() ) .sortedBy( e -> e.getCountry() );
Sometimes, it is desireable to only have partial results for a query returned. For example, when viewing query results on a web page, the results might be broken up into different pages instead of listing everything on a single page. The Java Streams API has methods limit() and skip() that can also be used by Jinq for queries. The limit() method only lets a fixed number of items pass through the stream. The skip() method discards a certain number of elements from the beginning of the stream. The query below shows an example of how limit() can be used to get the 10 largest accounts for a company.
streams.streamAll(em, Account.class) .sortedDescendingBy( a -> a.getBalance() ) .limit(10);
When querying a database, you might not want any repeat elements in your data set. To filter out the repeat entries so that you are only left with unique entries, you can use the stream method distinct(). For example, the code below gets the list of different stores of a company, and finds the different countries where the stores are located.
streams.streamAll(em, Store.class) .select( s -> s.getAddress().getCountry() ) .distinct();
It's also possible to use the distinct() method in combination with aggregation. When using distinct() in combination with aggregation methods that take a function as input like sum() or avg(), the functions should simply pass through the distinct elements directly to be aggregated.
streams.streamAll(em, Store.class) .select( s -> s.getAddress().getCountry() ) .distinct() .count();
Jinq streams have a group() method that provide an easy to express grouping operations. Grouping operations allow you divide up your data into groups based on which stream items have the same details. For example, a stream of city objects can be grouped together by country. Cities that are in the same country will all be grouped together in the same group. Once the stream data it grouped together, you can then perform aggregate operations over the data in each group.
The group() method takes 2 or more parameters.
The first parameter is a function that calculates a grouping key. This function is applied to each element of the stream, and it should return the value used to group elements together. This value is called the grouping key. Elements of the stream that share the same grouping key are grouped together.
The rest of the parameters to the group() method are functions used to calculate aggregates over groups. Each group is passed to these methods, and the methods should return a calculated aggregate. These functions are similar to the functions used to calculate multiple aggregates in the aggregate() method. Each of these functions is passed two parameters. The first parameter is the grouping key value of the group. The second parameter is a stream of all the elements that are in the group.
After the aggregates are calculated, the group() method returns a tuple containing the grouping key, and the calculated aggregates. For example, if the group() method is called with three functions—one to calculate the grouping key, and two functions for calculating aggregates—then the group() method will return a Tuple3 object to hold those three values.
Below is some grouping code that calculates the number and total population of a countries' cities. It does this by taking a stream of cities and grouping the cities by country. Then, for each country, it takes the stream of cities from that country and adds together the population of each city and also counts how many cities there are.
List<Tuple3<String, Long, Long>> results =
streams.streamAll(em, City.class)
.group(
c -> c.getCountry(),
(country, stream) -> stream.sumInteger(
city -> city.getPopulation()),
(country, stream) -> stream.count());
A diagram illustrating what the code does is given below. The streamAll() method returns a stream of City objects. Each City object lists the name of the city, the country where the city is located, and the population of the city. When the group() method is called, the first function is applied to each city to calculate the grouping key. The function groups together the cities by country. These groups are then passed to the next two functions to calculate aggregates for the groups. The first aggregate function computes the total population of the cities in a country. For example, for the group "Germany", the function receives of stream of the two main city regions in Germany, Berlin and Ruhr. It calculates the sum of the populations of the items in that stream, resulting in the result of 10M. Similarly, the second aggregate function simply counts the number of cities in a country. For example, for the country group "France", the stream for the group only contains the city Paris, so the function will only return 1. The group() returns a stream of the groups and calculated aggregate values.
Some Java compilers have problems with type inference for functions inside functions. This structure is used by Jinq for multiple aggregates, groups, and subqueries. This particularly happens when calculating aggregates involving min() and max(). The Java compiler might complain about methods returning Objects instead of the expected type. In those cases, you need to supply hints to the Java compiler. Simply cast the type of the return value of an aggregate to the expected type, and the Java compiler should be able to infer the rest of the types.
In the code below, the result of people.min() has to be casted to a Date to help the Java type inferencing determine the type of the result.
Pair<Long, Date> results =
streams.streamAll(em, Person.class)
.aggregate(
people -> people.count(),
people -> (Date)people.min(
person -> person.getDateOfBirth()));
In a Jinq query, it is possible to use the same stream twice. This is not a problem when the query is translated to a database query since no streams will be used internally when calculating the result. If Jinq is used for in-memory data and the query is not translated to a database query, the use of the same stream twice will cause an error since Java does not allow a stream to be used twice. And example of this is shown below. The query creates a Pair object by taking a stream and calculating an average population and a maximum population over the stream. As such, the stream is used twice. Although, technically, Java does not allow this, Jinq will ignore this problem and converting the code to a database query.
streams.streamAll(em, City.class)
.aggregate(
cities -> cities.count(),
cities -> new Pair<>(
cities.avg(c -> c.getPopulation())
(int)cities.max(c -> c.getPopulation())));
Jinq primarily supports subqueries inside select() and where() methods. Due to uneven or non-existent JPQL support for subqueries in FROM clauses, Jinq does not support FROM subqueries or subqueries inside join() methods. Subqueries should return a single value. This single value can be the result of an aggregation or a stream that only holds one value.
To make a subquery involving aggregation, you can take a stream, use aggregation to reduce that stream to a single value, and then use that value in your query. For example, the query below looks for countries with more than five cities. For each country, it gets a stream of the cities in the country. Then, it counts the number of cities in that stream. Finally, it compares that count to see if it is greater than five.
streams.streamAll(em, Country.class)
.where( c ->
JinqStream.from(c.getCities()).count() > 5 );
To make a subquery involving a stream with only one value, you can use the getOnlyValue() method to extract the value in the stream and use it in your query. For example, the query below looks for countries with a population greater than the population of the city of LA.
streams.streamAll(em, Country.class)
.where( (c, source) ->
c.getPopulation() >
source.stream(City.class)
.where(city -> city.getName().equals("LA"))
.select(city -> city.getPopulation())
.getOnlyValue());
The following stream operations are allowed to be used in subqueries:
| Allowed Subquery Operations |
|---|
| where() |
| select() |
| distinct() |
| join() |
| count() |
| avg() |
| max() |
| min() |
| sum...() |
The stream operations that Jinq provides are more expressive than what is allowed by the underlying JPQL query language. Since all Jinq code is valid Java code, Jinq can simply directly execute that parts of your code that cannot be converted into a JPQL query that can run on a database. If you need to generate a JPQL query representing your code though, you must restrict yourself to a subset of the Jinq stream operations.
Below is a diagram showing what combinations of Jinq stream operations will lead to valid JPQL queries, assuming that the contents of each lambda are translatable to JPQL. Starting from the top of the diagram, you can trace along different Jinq operations that produce valid queries.
To verify that a particular query is fully translatable to JPQL, you can pass the exceptionOnTranslationFail hint to the query. This will tell Jinq to throw an exception at runtime if the query was not fully translated into a query.
Jinq provides a convenience method for creating subqueries involving the JPQL IN operator called JPQL.isIn(). Jinq also accepts the variants Collection.contains(), JPQL.isInList(), and JPQL.listContains(). These methods can be used to check whether a Collection or Stream contains a certain element. The Collection or Stream can be the result of a subquery.
Although the behavior of JPQL.isIn() can be emulated using other types of Jinq subqueries, the use if JPQL.isIn() can be more succinct or easier to understand than those other forms.
// Find the countries with a city named San Jose
streams.streamAll(em, Country.class)
.where(c ->
JPQL.isIn("San Jose",
JinqStream.from(c.getCities())
.select(city -> city.getName())))
The main useful feature of JPQL.isIn() is that it can be used with Collections passed into the query as parameters.
// Find employees named Ann or Bob
List<String> names = new ArrayList<>();
names.add("Ann");
names.add("Bob");
streams.streamAll(em, Employee.class)
.where(e -> names.contains(e.getName()))
.toList();
Jinq's JinqStream has a method called .exists(), which can be used to create subqueries using JPQL's EXISTS. In a query, simply create a subquery and then call .exists() on that subquery to create a query that checks whether the subquery returns any values or not.
// Find the countries with a city named San Jose
streams.streamAll(em, Country.class)
.where(c ->
JinqStream.from(c.getCities())
.where(city -> city.getName().equals("San Jose"))
.exists());
JPQL is the query engine underlying Jinq for JPA, and it currently does not support set operations like UNION, INTERSECT, or EXCEPT. As a result, Jinq cannot directly support such operations either. In some limited cases, it is possible to emulate these set operations using ANDs and ORs. JPAJinqStreams provide the methods orUnion(), andIntersect, and andNotExcept to allow programmers to express union and intersection set operations in those limited cases where they can be emulated using AND and OR expressions.
For example, consider what happens when the two queries below are UNIONed together.
streams.streamAll(em, Country.class)
.where(c -> c.startsWith("a"));
UNION
streams.streamAll(em, Country.class)
.where(c -> c.startsWith("z"));
The query can be rewritten as
streams.streamAll(em, Country.class)
.where(c -> c.startsWith("a")
|| c.startsWith("z"));
The orUnion(), andIntersect(), and andNotExcept() methods take another stream as a parameter. The methods will then attempt to perform a union, intersection, or set difference operation respectively on the elements of the two streams, returning the result in a new stream. Since the set operations are emulated using AND and OR, the methods are very restrictive on when they can be used. In general, the two streams must be identical except for having different where() restrictions on them. The two streams must also represent queries based on the same entity manager.
Despite the heavy restrictions on when the methods can be used, there are some cases when they are useful. In some cases, it is necessary to programmatically specify a where() expression, and these methods allow that. Also, in some situations, Jinq may have trouble generating a succinct query for a complex where() expression. In those situations, these methods provide an alternate way for programmers to specify the same expression and to directly specify how the underlying AND and OR operations should be used in the final expression.
For example, the query below programmatically constructs a query that finds stores in the given cities and countries.
JPAJinqStream<Store> stores = null;
for (Location loc: restrictions) {
String country = loc.getCountry();
String city = loc.getCity();
JPAJinqStream<Store> cityStore =
streams.streamAll(em, Store.class)
.where(s -> s.getCountry().equals(country))
.where(s -> s.getCity().equals(city));
if (stores != null) {
stores = cityStore;
} else {
stores = stores.orUnion(cityStore);
}
}
return stores.toList();
Support for these methods is still experimental. Parts of the implementation may not be complete and the error-checking may not be robust.
Support for other database operations will be added based on need. If you need support for additional functionality, please post a request to the Jinq Google group.
The JPA version of Jinq can be configured by passing configuration information in the form of "hints" to the Jinq query system. These hints can be applied to all Jinq queries or to single queries only.
To apply a hint to all queries, use the setHint() method on the JinqJPAStreamProvider which you use to create streams of entities.
streams.setHint("exceptionOnTranslationFail", true);
To apply a hint to only a single query, use the setHint() method on an individual JinqStream streams.
streams.streamAll(em, Customer.class)
.setHint("automaticPageSize", 10000)
.where( c -> c.getName().equals("Bob") )
The setHint() method takes two parameters. The first parameter is a string with the name of the hint to set. The second parameter is the value for the hint.
The exceptionOnTranslationFail hint is used to tell Jinq what to do when it encounters Java code that it cannot translate into a database query. Normally, if Jinq cannot translate some code into a database query that can run on the database, Jinq will emulate the effect of running a query by directly run the Java code instead. The database data will be transferred to the computer and the Java code will be applied directly to the data. Jinq will will produce the correct result, but a large amount of data may need to be transferred from the database. If you would prefer it if Jinq told you when a query cannot be generated from your code, you can set this hint to true. Jinq will then throw an IllegalArgumentException when it cannot create a query from your code.
The automaticPageSize hint is used to change how Jinq streams its results. In order to support large datasets that do not fit entirely into memory, Jinq will automatically try to stream result sets from the database instead of holding the entire result set in memory. JPA does not support result streaming, so for large result sets, Jinq will issue multiple smaller JPA queries for different subsets of the result set. By default, Jinq will break query results into chunks or pages of 10000 results. When Jinq issues a query, it will ask for the first 10000 results. If it finds more than 10000 results, then once the first 10000 results have been streamed, it will then ask for the next 10000, and so on until the full result set has been transferred. Although this prevents your Java code from hanging due to running out of memory, some databases may return inconsistent results when a single large query is broken into smaller queries in this way. Also, if your results are already quite large because they contain BLOBs or other large data, 10000 results might already exceed the memory you have available. The automaticPageSize hint allows you to set the number of results that Jinq should should break result sets into.
The queryLogger hint lets you install a callback in Jinq for inspecting the queries that Jinq generates. The callback should be a JPAQueryLogger object. Every time Jinq is about to run a JPQL query, it will pass the query string to the callback.
The lambdaClassLoader hint allows you to tell Jinq to use a certain ClassLoader instance to find the bytecode for the Java lambda functions that it is translating into a database query. Jinq first looks up the bytecode for lambda functions by using the system class loader. If it cannot find the lambda function there, it will use the class loader used to load Jinq. If it still cannot find the lambda function there, it will look up the lambda code using the lambdaClassLoader specified in the hint. Some systems use different classloaders for loading different parts of their codebases to help with security and modularity. For those systems, it is necessary to use this hint to tell Jinq which classloader to use to find the code used by your Jinq queries so that Jinq can translate the code into database queries.
The useCaching hint tells Jinq to cache its analysis of lambdas. This makes Jinq faster because if it encounters same the same query code repeatedly, it doesn't have to repeatedly analyze the same code over and over again. By default, caching is on.
The javax.persistence.fetchgraph hint allows you to pass JPA entity graphs to the underlying JPA query layer in order to optimize your entity fetching. In Jinq 2.0.x, this hint is an alias for the jakarta.persistence.fetchgraph hint.
The jakarta.persistence.fetchgraph hint is only available in Jinq 2.0.x. It allows you to pass JPA entity graphs to the underlying JPA query layer in order to optimize your entity fetching.
There are some methods that Jinq doesn't definitively know whether they can be safely converted into queries or not. Usually, these methods are safe, but Jinq can't be sure. By default, Jinq assumes that these methods are safe, but these assumptions can be disabled. The isObjectEqualsSafe hint tells Jinq to assume that Object.equals(), Objects.equals(), and Guava's Objects.equal() are safe. The isAllEqualsSafe hint tells Jinq to assume that all calls to equals() are safe. The isCollectionContainsSafe hint tells Jinq to assume that all calls to contains() on subclasses of Collection are safe.
Jinq sits on top of a stack of many other pieces of enterprise software. Due to the complex interactions of these components, various issues inevitably crop up that will need to be worked around.
When using aggregation is subqueries, EclipseLink's JPQL implementation occasionally misidentifies the return type of the subquery. For example, it may assume that calculating a count() will return an integer instead of a long. This will result in a class cast exception. As a workaround, you can explicitly cast the return type of the subquery to a Number object.
When using parameters in queries, EclipseLink assumes that the types of the parameters will not change the overall typing of the expression that the parameters appear in. For example, in the expression (1 + parameter), EcliseLink will assume that the type of parameter will be an integer since 1 is also an integer. If the parameter variable is a double, the overall expression will evaluate to a double due to numeric promotion. In those cases, EclipseLink will still assume that the expression returns an integer. This mismatch will cause a class cast exception.
In order to convert operations on entities into query operations, Jinq queries your JPA provider for information about entities and their methods. Hibernate's implementation of the Criteria API seems to provide incorrect information to Jinq about entities that use composite keys that use other entities as keys. This sort of mapping is sometimes used for some types of many-to-many associations. In those cases, Jinq will complain that it has encountered an unknown method when it sees certain entity methods that it should be aware of. The workaround is to use the JinqJPAStreamProvider.registerAssociationAttribute() method to manually register these methods with Jinq, so that Jinq will know how to translate these methods to JPQL.
Hibernate's support for BigDecimal numbers will occasionally infer types incorrectly. It will assume expressions will return doubles or other types when they should return BigDecimal numbers.
Sorting result sets by the value of a subquery may not work properly in Hibernate.
Grouping results by entity and then returning that entity in a query may not work properly in Hibernate.
Calculating the aggregates of distinct values might not work properly in Hibernate.
Hibernate sometimes incorrectly infers the types of parameters. Unlike EclipseLink, it will not automatically numerically promote your parameters to the correct type.