Java Stream ORM Now with JOINs
Speedment is a Java Stream ORM Toolkit and Runtime that allows you to view database tables as standard Java Streams. Because you do not have to mix Java and SQL, the application becomes much more compact making it faster to develop, less prone to errors and easier to maintain. Streams are also strictly type-safe and lazily constructed so that only a minimum amount of data is pulled in from the database as elements are consumed by the streams.
The new version Speedment 3.1.1 “Homer” now also supports dynamically joined tables to be viewed as standard Java Streams. This is a big deal when developing Java applications that explore relations between database tables.
In the examples below, I have used the open-source Sakila film database content for MySQL that you can download here. Speedment works for any major relational database type such as Oracle, MySQL, Microsoft SQL Server, PostgreSQL, DB2, MariaDB, AS400 and more.
Streaming over a Single Table
The following code snippet will create a List
of all Film
objects that has a Film.RATING of “PG-13” and where the List
is sorted in Film.LENGTH order:
List<Film> list = films.stream() .filter(Film.RATING.equal("PG-13")) .sorted(Film.LENGTH) .collect(toList());
The stream will be automatically rendered to a SQL query under the hood. If we enable Stream logging, we will see the following (prepared statement “?”-variables given as values in the end):
SELECT `film_id`,`title`,`description`,`release_year`, `language_id`,`original_language_id`, `rental_duration`,`rental_rate`, `length`,`replacement_cost`,`rating`,`special_features`, `last_update` FROM `sakila`.`film` WHERE (`rating` = ? COLLATE utf8_bin) ORDER BY `length` ASC values:[PG-13]
Thus, the advantage is that you can express your database queries using type-safe Java and then consume the result by means of standard Java Streams. You do not have to write any SQL code.
Joining Several Tables
Appart from the table “film”, the Sakila database also contains other tables. One of these is a table called “language”. EachFilm
entity has a foreign key to the Language
being spoken in the film using a column named “language_id”.
In this example, I will show how we can create a standard Java Stream that represents a join of these two tables. This way, we can get a Java Stream of matching pairs of Film/Language
entities.
Join
objects are created using the JoinComponent
which can be obtained like this:
// Visit https://github.com/speedment/speedment // to see how a Speedment app is created. It is easy! Speedment app = …; JoinComponent joinComponent = app.getOrThrow(JoinComponent.class);
Once we have grabbed the JoinComponent
, we can start creatingJoin
objects like this:
Join<Tuple2<Film, Language>> join = joinComponent .from(FilmManager.IDENTIFIER) .innerJoinOn(Language.LANGUAGE_ID).equal(Film.LANGUAGE_ID) .build(Tuples::of);
Now that we have defined our Join
object we can create the actual Java Stream:
join.stream() .map(t2 -> String.format( "The film '%s' is in %s", t2.get0().getTitle(), // get0() -> Film t2.get1().getName() // get1() -> Language )) .forEach(System.out::println);
This will produce the following output:
The film 'ACADEMY DINOSAUR' is in English The film 'ACE GOLDFINGER' is in English The film 'ADAPTATION HOLES' is in English ...
In the code above, the methodt2.get0()
will retrieve the first element from the tuple (aFilm
) whereas the method t2.get1()
will retrieve the second element from the tuple (aLanguage
). Default generic tuples are built into Speedment and thus Tuple2
is not a Guava class. Speedment does not depend on any other library. Below you will see how you can use any class constructor for the joined tables. Again, Speedment will render SQL code automatically from Java and convert the result to a Java Stream. If we enable Stream logging, we can see exactly how the SQL code was rendered:
SELECT A.`film_id`,A.`title`,A.`description`, A.`release_year`,A.`language_id`,A.`original_language_id`, A.`rental_duration`,A.`rental_rate`,A.`length`, A.`replacement_cost`,A.`rating`,A.`special_features`, A.`last_update`, B.`language_id`,B.`name`,B.`last_update` FROM `sakila`.`film` AS A INNER JOIN `sakila`.`language` AS B ON (B.`language_id` = A.`language_id`)
Interestingly, the Join object can be created once and be re-used over and over again to create new Streams.
Many-to-Many Relations
The Sakila database also defines a handful of Many-to-Many relations. For example, the table “film_actor” contains rows links films to actors. Each film can have multiple actors and each actor might have appeared in multiple films. Every row in the table links a particular Film
to a specific Actor
. For example, If a Film
depicts 12 Actor entities, then
FilmActor contains 12 entries all having the same film_id but different actor_ids. The purpose of this example is to create a complete list of all films and the appearing actors in a Java Stream. This is how we can join the three tables together:
Join<Tuple3<FilmActor, Film, Actor>> join = joinComponent .from(FilmActorManager.IDENTIFIER) .innerJoinOn(Film.FILM_ID).equal(FilmActor.FILM_ID) .innerJoinOn(Actor.ACTOR_ID).equal(FilmActor.ACTOR_ID) .build(Tuples::of); join.stream() .forEach(System.out::println);
The code above will produce the following output (formatted for readability):
... Tuple3Impl { FilmActorImpl { actorId = 137, filmId = 249, lastUpdate = 2006-02-15 05:05:03.0 }, FilmImpl { filmId = 249, title = DRACULA CRYSTAL, description =..., ActorImpl { actorId = 137, firstName = MORGAN, lastName = WILLIAMS,...} } Tuple3Impl { FilmActorImpl { actorId = 137, filmId = 254, lastUpdate = 2006-02-15 05:05:03.0 }, FilmImpl { filmId = 254, title = DRIVER ANNIE, description = ..., ActorImpl { actorId = 137, firstName = MORGAN, lastName = WILLIAMS, ...} } Tuple3Impl { FilmActorImpl { actorId = 137, filmId = 263, lastUpdate = 2006-02-15 05:05:03.0 }, FilmImpl { filmId = 263, title = DURHAM PANKY, description = ... }, ActorImpl { actorId = 137, firstName = MORGAN, lastName = WILLIAMS,... } } ...
Joins with Custom Tuples
As we noticed in the example above, we have no actual use of the FilmActor
object in the Stream since it is only used to link Film
and Actor
objects together during the Join phase.
When Join
objects are built using the build()
method, we can provide a custom constructor that we want to apply on the incoming entities from the database. The constructor can be of any type so you can write your own Java objects that holds, for example, Film
and Actor
or any of the columns they contain and that are of interest.
In this example, I proved a (lambda) constructor that just discards the linking FilmActor
objects altogether:
Join<Tuple2<Film, Actor>> join = joinComponent .from(FilmActorManager.IDENTIFIER) .innerJoinOn(Film.FILM_ID).equal(FilmActor.FILM_ID) .innerJoinOn(Actor.ACTOR_ID).equal(FilmActor.ACTOR_ID) .build((fa, f, a) -> Tuples.of(f, a)); join.stream() .forEach(System.out::println);
The code above will produce the following output (formatted for readability):
... Tuple2Impl { FilmImpl { filmId = 249, title = DRACULA CRYSTAL, description = ... }, ActorImpl { actorId = 137, firstName = MORGAN, lastName = WILLIAMS, ...} } Tuple2Impl { FilmImpl { filmId = 254, title = DRIVER ANNIE, description = A... }, ActorImpl { actorId = 137, firstName = MORGAN, lastName = WILLIAMS,...} } Tuple2Impl { FilmImpl { filmId = 263, title = DURHAM PANKY, description = ... }, ActorImpl { actorId = 137, firstName = MORGAN, lastName = WILLIAMS,...} } ...
Thus, we only get matching pairs of Film
and Actor
entities where there is an appearance of an actor in a film. The linking object FilmActor
is never seen in the Stream.
Take it for a Spin!
Over the course of this article, you have learned how to stream over one or several database tables using Speedment.
Visit Speedment open-source on GitHub and try it out!
Read all about the new JOIN functionality in the User’s Guide.
Published on Java Code Geeks with permission by Per Minborg, partner at our JCG program. See the original article here: Java Stream ORM Now with JOINs Opinions expressed by Java Code Geeks contributors are their own. |