How to LINQ Between Java and SQL With JPAStreamer

If you're a Java developer who’s been longing for a more streamlined and intuitive way of querying databases, similar to the elegant LINQ constructs in C#, then you should check out the open-source library JPAStreamer. In this article, I will explore the powerful capabilities of this Java tool that brings a LINQ-like experience to your Hibernate queries. 

Before diving deeper into JPAStreamer, I’ll describe some desirable properties of LINQ. A LINQ (Language Integrated Query) is a C# expression that can transform data from a variety of data sources, spanning from simple linear data structures to databases. Here is a basic example from the LINQ documentation that operates on an array of integers to find arbitrary scores greater than 80:

int[] scores = { 97, 92, 81, 60 };

IEnumerable<int> scoreQuery =
    from score in scores
    where score > 80
    select score;

foreach (int i in scoreQuery)
{
    Console.Write(i + " "); // Output: 97 92 81
}

As seen above, the LINQ API closely resembles SQL constructs, however, is type-safe to use in C# contexts. In Java, we can express the same operations in a fluent and intuitive manner using the Stream API introduced in Java 8:

final int[] scores = {97, 92, 81, 60}
Stream.of(scores)
    .filter(score -> score > 80)
    .foreach(System::out); 

Although not identical to SQL, many of the Java Stream operations have SQL siblings, e.g., filter (where), sorted (order), and map (select). 

Both LINQ and the Stream API emphasize a declarative style of programming, allowing developers to express what they want to achieve rather than specifying how to do it - an approach that enhances code readability and maintainability. 

So, Java Streams Is Equivalent to C# LINQ?

No, it’s not that simple. Let’s say our scores represent historic results from a gaming app, and thus are stored in a database rather than an in-memory array. To motivate players, we want to present a list of all-time high scores. With LINQ to SQL, the input array can simply be replaced with a reference to an Object Model entity; i.e., the C# metamodel of the database. Upon execution, LINQ will automatically issue a native query to the database and is equipped with join operations and query comprehension syntax to write more complex queries.

Java Streams, on the other hand, cannot be converted to SQL queries...or can they?

Introducing JPAStreamer 

JPAStreamer is an open-source JPA extension that bridges the gap between Java Streams and database queries. Using a custom implementation of the Java Stream interface, JPAStreamer can automatically translate Streams to efficient database queries and supports join and projection constructs to cover more ground. 

Unlike LINQ to SQL, JPAStreamer is not a standalone ORM but instead uses an existing JPA provider such as Hibernate to manage the database metamodel consisting of standard JPA Entities. Going forward, I’ll assume the gaming application uses Hibernate to manage the historic game sessions. Past scores are represented in a Score table in the database, and our JPA entity model has an associated Score Entity as follows:

@Entity
@Table(name = "score", schema = "game-db")
public class Score implements Serializable {
    public Score() {}
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
     @Column(name = "score_id", nullable = false, updatable = false, columnDefinition = "smallint(5)")
    private Integer scoreId;

    @Column(name = "value", nullable = false, columnDefinition = "varchar(255)")
    private Integer value;
    
    @Column(name = "date", nullable = false, columnDefinition = "timestamp")
    private LocalDateTime date;
    
    @ManyToOne
    @JoinColumn(name="username", nullable = false, updatable = false, insertable = false)
    private Player player;

    // Setters and getters are left out for clarity…
}

As seen above, the score can be described by an id, a value, the date of play, and an associated player. 

Once the JPA metamodel is in place, JPAStreamer can be installed by adding the Maven dependency below: 

<dependency>
    <groupId>com.speedment.jpastreamer</groupId>
    <artifactId>jpastreamer-core</artifactId>
    <version>3.0.1</version>
</dependency>

(For Gradle installation, read more here. If you are using the Quarkus ecosystem a JPAStreamer extension is available in the Quarkiverse.)

Upon installation, the project needs to be rebuilt to trigger the generation of JPAStreamer's own metamodel. This process is completely automated and the output can be found in the target/generated-sources folder. In this case, JPAStreamer generates a Score$ and Player$ class that we can use to formulate predicates that can be interpreted by the JPAStreamer query optimizer. 

Sound fuzzy? Let’s get concrete by writing a query that will filter out the top 10 scores: 

JPAStreamer jpaStreamer = JPAStreamer.of(“game-db”); 
jpaStreamer.stream(Score.class)
    .sorted(Score$.value.reversed())
    .limit(10)
    .foreach(System::out); 

JPAStreamer is instantiated with a single line of code, referencing the persistence unit by its imagined name “game-db”. The second line creates a stream from the JPA entity Score. The scores are then sorted from high to low and the top 10 results are selected. Much like with LINQ, the execution of the query is delayed until the terminal operation is called; in this case, the foreach operator that prints the scores. Most importantly, this Stream will not materialize all the scores in the database as it is automatically translated to a SQL query: 

select
    score0_.score_id as score_id1_0_,
    score0_.value as value2_0_,
    score0_.date as date3_0_,
    score0_.username as username4_0_,
from
    score score0_
order by
    score0_.value dsc limit ?, ?

To give credit to the players on the high-score list we may also want to display the name of the player associated with each score. As can be seen in the Score entity above, it has a Many-to-One relation to the Player class. I will not show an imaginative JPA Player Entity, but each player is uniquely identified by their username and has a field for first and last name. 

We can obtain the name of the associated player by joining in the player for each of the scores. Using JPAStreamer means we need to update the Stream source to not only include Score entities but also the joint Player objects. This join is defined using a Stream Configuration as shown below. As before, we gather the top ten scores and map these entries to their respective players. Lastly, the players and their scores are printed in the console. 

JPAStreamer jpaStreamer = JPAStreamer.of("game-db");

Map<Score, Player> scoreMap = jpaStreamer.stream(
  StreamConfiguration.of(Score.class).joining(Score$.player))
    .sorted(Score$.value.reversed())
    .limit(10)
    .collect(toMap(
        Function.identity(),
        Score::getPlayer
    )
);

scoreMap.forEach(
    (s, p) -> System.out.format("%s %s: %s points\n",
        p.getFirstName(),
        p.getLastName(),
        s.getValue()
    )
);

Conclusion 

JPAStreamer brings the power of type-safe and fluid querying to the world of Java development, offering developers an intuitive and expressive way to interact with relational databases. With its declarative syntax, lazy evaluation, and composability, JPAStreamer bears striking similarities to C#'s LINQ library. By adopting JPAStreamer, Java developers can benefit from a concise and efficient approach to querying databases, ultimately leading to cleaner and more maintainable code.

Resources

• Getting Started with JPAStreamer
• JPAStreamer Demo Repository