Easy database queries for Java
Jinq queries allow you to write database queries in Java using a functional style. Java 8 provides a Streams API for letting programmers manipulate data in a functional style. For example, if you want to get a list of Customers named "Alice" using Java 8's Streams API, this is what you would write:
customers.stream()
.filter( c -> c.getName().equals("Alice") );
Initially, that code starts with the variable customers which is a List of Customer objects. It then calls the stream() method to stream the Customer objects out of that list.
Then, the filter() method is called. The filter() method will iterate over all of the objects in the stream and chooses the objects it is interested in. It returns a new stream containing only those objects it selected before. The other objects are filtered out.
The filter() method takes one parameter: a function for choosing which objects to keep. In the example, the function is used for iterating over Customer objects in the stream. The function takes a Customer named c as input and returns true if the name of the customer c is Alice.
So what the filter() method does is that it iterates over all the Customer objects in the stream. For each customer, it calls the given function. If the customer is named Alice, the function will return true, and the filter() method will keep that customer in the new stream. If the customer is not named Alice, the function will return false, and the filter() method will ignore the customer and not put it in the new stream.
Although the functional-style of Java 8 can be confusing at first, it is much more compact than older styles of Java. It also more flexible. In order to be faster, the filter() method might filter things in parallel in multiple threads instead of sequentially. In the case of Jinq, the filter() method might filter the data by converting the function to a database query!
Jinq provides an API similar to the Java 8 Streams API that lets you work with data in a database. This API uses methods named after database commands, so that the resulting code closely resembles existing database query languages. For example, Java 8's Streams API uses the methods filter() and map() to filter and transform data, respectively:
customers.stream()
.filter( c -> c.getName().equals("Alice") )
.map( c -> c.getAddress() );
The above code takes a stream of Customer objects, chooses the ones named Alice, and gets their addresses. In Jinq, the same code would use the methods where() and select() to transform the data instead:
db.customerStream()
.where( c -> c.getName().equals("Alice") )
.select( c -> c.getAddress() );
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.Address FROM Customers C WHERE C.Name = 'Alice'
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:
Any SQL92 query can generally be rewritten in Jinq. Here are some examples of how different types of queries can be written in Jinq.
The where() method lets you filter database data.
db.customerStream().where(
c -> c.getName().equals("Bob") );
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.
db.customerStream().select( c -> c.getFirstName() );
If you want to return more than one field, you can store these different fields of data in a Pair or Tuple object.
db.customerStream().select( c -> new Pair<>(c.getFirstName(), c.getLastName()) );
You can also use if statements to do more complicated transformations of data.
db.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 Customer objects that own each Account object.
db.accountStream().select( a -> a.getCustomer().getName() );
You can also do cross-joins between arbitrary entities.
db.customerStream().join(db.accountStream());
Jinq has methods for computing simple sums and other aggregates of objects. For example, the code below computes the sum of some double values.
db.orderStream() .sumDouble( o -> o.getTotalValue());
You can also calculate more than one aggregate at a time.
db.orderStream()
.where( o -> o.getTotalValue() > 1000)
.aggregate(
data -> data.size(),
data -> data.sumDouble( o -> o.getTotalValue() ));
To remove duplicate entries, you can use the unique() method so that only unique results are returned.
db.customerStream() .select( c -> c.getCountry()) .unique();
Jinq lets you chain together your queries. It also has some support for nesting queries in other queries.
db.getOrders() .where( o -> o.getTotalValue() > 1000 ) .select( o ->o.getCustomer() ) .unique() .where( c -> c.getAccounts().sumInt(a -> 1) > 5 );
Although you can use nested queries to group data together, Jinq offers a group() method that makes it easier to write grouping operations. The method takes two functions as input. The first function returns the fields that the data will be grouped together by. After Jinq has grouped all the data together, it will take the data from each group and apply the second function, which computes aggregate values for each group.
For example, the code below groups customers together by country. Then for each country, it takes the customers in those countries and adds up the salaries of those customers.
db.customerStream()
.group( c -> c.getCountry(),
setOfCustomers -> setOfCustomers.sumDouble(
cc -> cc.getSalary()) );
Finally, a query may want its results sorted or to have only partial results returned. Sorting is supported by letting programmers pass in a function which describes which fields should be compared. The firstN() method can be used to restrict how many results are returned.
top10Accounts = db.accountStream() .sortedByDoubleDescending(a -> a.getBalance()) .firstN(10);