DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Related

  • Java EE 6 Pet Catalog with GlassFish and MySQL
  • How to LINQ Between Java and SQL With JPAStreamer
  • Express Hibernate Queries as Type-Safe Java Streams
  • Architecture and Code Design, Pt. 1: Relational Persistence Insights to Use Today and On the Upcoming Years

Trending

  • Scaling Microservices With Docker and Kubernetes on Production
  • The End of “Good Enough Agile”
  • How Kubernetes Cluster Sizing Affects Performance and Cost Efficiency in Cloud Deployments
  • Implementing API Design First in .NET for Efficient Development, Testing, and CI/CD
  1. DZone
  2. Data Engineering
  3. Databases
  4. Become a Master of Java Streams (Part 4): Database Streams

Become a Master of Java Streams (Part 4): Database Streams

Let's dive into Java Streams more deeply. Today's lesson? Setting up relational database streams.

By 
Per-Åke Minborg user avatar
Per-Åke Minborg
·
Julia Gustafsson user avatar
Julia Gustafsson
·
Nov. 01, 19 · Tutorial
Likes (9)
Comment
Save
Tweet
Share
32.8K Views

Join the DZone community and get the full member experience.

Join For Free

stream

You can almost see the data flowing...

SQL has always been a declarative language whereas Java for a long time has been imperative. Java streams have changed the game. Code your way through this hands-on-lab article and learn how Java streams can be used to perform declarative queries to an RDBMS database, without writing a single line of SQL code. You will discover, there is a remarkable similarity between the verbs of Java streams and SQL commands.

Not quite what you're looking for? Take a look at Querying Databases Using Java Streams.

This article is the fourth out of five, complemented by a GitHub repository containing instructions and exercises to each unit.

  • Part 1: Creating Streams

  • Part 2: Intermediate Operations

  • Part 3: Terminal Operations

  • Part 4: Database Streams

  • Part 5: Creating a Database Application Using Streams

Database Streams 

When you familiarized yourself with the operations of Streams, you may have noticed a resemblance to the SQL constructs. Some of them have a more or less a direct mapping to Stream operations, such as LIMIT and COUNT. This resemblance is utilized by the open-source project Speedment to provide type-safe access to any relational database using pure Java.

This table shows how Speedment maps between SQL and Java Streams.

We are contributors to the Speedment open-source project and we will describe how Speedment allows us to use a database as the stream source and feed the pipeline with rows from any of the database tables.

As depicted in the visualization above, Speedment will establish a connection to the database and can then pass data to the application. There is no need to write any code for the database entries since Speedment analyses the underlying database and automatically generates all the required entity classes for the domain model. It saves a lot of time when you don't have to write and maintain entity classes by hand for each table you want to use.

Sakila Database

For the sake of this article, as well as the exercises, we use the MySQL example database Sakila as our data source. The Sakila database models an old-fashioned movie rentals business and therefore contains tables such as Film and Actor. An instance of the database is deployed in the cloud and is open for public access.

Speedment Manager

In Speedment, the handle to a database table is a called a Manager. The managers are part of the automatically generated code.


A Manager acts as a handle to a database table and can act as a stream source. In this case, every row corresponds to an instance of Film. 

A Manager in Speedment is instantiated by calling:

FilmManager films = speedment.getOrThrow(FilmManager.class);


Note: Speedment is an instance that can be obtained from an ApplicationBuilder (more on this topic in the next article).

If the FilmManager::stream is called, the result is a Stream to which we are free to apply any intermediate or terminal operations. For starters, we collect all rows in a list.

List<Film> allFilms = films.stream().collect(toList());
FilmImpl { filmId = 1, title = ACADEMY DINOSAUR, …
FilmImpl { filmId = 2, title = ACE GOLDFINGER, …
FilmImpl { filmId = 3, title = ADAPTATION HOLES, …
…


Filtering and Counting

Let's look at a simple example that outputs the number of films having the rating "PG-13". Just like a regular Stream, we can filter out the films with the correct rating, and then count these entries.

long pg13FilmCount = films.stream()
   .filter(Film.RATING.equal("PG-13"))
   .count();

pg13FilmCount: 195


One important property that follows with Speedment's custom implementation of Streams is that the streams are able to optimize their own pipeline by introspection. It may look like the Stream will iterate over all rows of a table, but this is not the case. Instead, Speedment is able to translate the pipeline to an optimized SQL query that is passed on to the database. This means only relevant database entries are pulled into the Stream. Thus, in the example above, the stream will be automatically rendered to SQL similar to "SELECT ... FROM film WHERE rating = 'PG-13' "

This introspection requires that any use of anonymous lambdas (which do not contain any metadata that relates to the targeted column) are replaced with Predicates from Speedment Fields. In this case Film.RATING.equal("PG-13") returns a Predicate that will be tested on each Film and return true if and only if that Film has a Rating that is PG-13.

Although, this does not prevent us from expressing the predicate as:

.filter(f -> f.getRating().equals("PG-13"))


But this would force Speedment to fetch all the rows in the table and then apply the predicate, hence it is not recommended.

Finding the Longest Film

Here is an example that finds the longest film in the database using the max-operator with the Field Film.LENGTH:

Optional<Film> longestFilm = films.stream()
   .max(Film.LENGTH);

longestFilm: 
Optional[FilmImpl {filmId = 141, title = CHICAGO NORTH, length = 185, ...}]


Finding Three Short Films

Locating three short films (we defined short as <= 50 minutes) can be done by filtering away any films that are 50 minutes or shorter and picking the three first results. The predicate in the example looks at the value of the column "length" and determines if it is less than or equal to 50.

List<Film> threeShortFilms = films.stream()
    .filter(Film.LENGTH.lessOrEqual(50))
    .limit(3)
    .collect(toList());
threeShortFilms: [
    FilmImpl { filmId = 2, length = 48,..}, 
    FilmImpl { filmId = 3, length = 50, … }, 
    FilmImpl { filmId = 15, length = 46, ...}]


Pagination With Sorting

If we were to display all the films on a website or in an application, we would probably prefer to paginate the items, rather than loading (possibly) thousands of entries at once. This can be accomplished by combining the operation skip() and limit(). In the example below, we collect the content of the second page, assuming every "page" holds 25 entries. Recall that Streams do not guarantee a certain order of the elements, which means that we need to define an order with the sorted-operator for this to work as intended.

List<Film> filmsSortedByLengthPage2 = films.stream()
    .sorted(Film.LENGTH)
    .skip(25 * 1)
    .limit(25)
    .collect(toList());
filmsSortedByLengthPage2: 
[FilmImpl { filmId = 430, length = 49, …}, …]


Note: Finding the content of the n:th page is done by skipping (25 * (n-1)).
Note2: This stream will be automatically rendered to something like "SELECT ... FROM film ORDER BY length ASC LIMIT ? OFFSET ?, values:[25, 25]"

Films Starting With "A" Sorted by Length

We can easily locate any films starting with the capital letter "A" and sort them according to their length (with the shortest film first) like this:

List<Film> filmsTitleStartsWithA = films.stream()
   .filter(Film.TITLE.startsWith("A"))
   .sorted(Film.LENGTH)
   .collect(Collectors.toList());
filmsTitleStartsWithA: [
    FilmImpl { filmId=15, title=ALIEN CENTER, …, rating=NC-17, length = 46,
    FilmImpl { filmId=2, title=ACE GOLDFINGER, …, rating=G, length = 48,
… ]


Computing Frequency Tables of Film Length

We can also utilize the groupingBy-operator to sort the films in buckets depending on their lengths and count the total number of films in each bucket. This will create a so-called frequency table of film length.

Map<Short, Long> frequencyTableOfLength = films.stream()
    .collect(Collectors.groupingBy(
        Film.LENGTH.asShort(),
        counting()
    ));
frequencyTableOfLength: {46=5, 47=7, 48=11, 49=5, … }


Exercises

For this week's exercises, you do not need to worry about connecting a database of your own. Instead, we have already provided a connection to an instance of the Sakila database in the cloud. As usual, the exercises can be located in this GitHub repo. The content of this article is sufficient to solve the fourth unit which is called MyUnit4Database. The corresponding Unit4Database Interface contains JavaDocs which describe the intended implementation of the methods in MyUnit4Database.

public interface Unit4Database {

   /**
    * Returns the total number of films in the database.
    *
    * @param films manager of film entities
    * @return the total number of films in the database
    */
   long countAllFilms(FilmManager films);

The provided tests (e.g. Unit4MyDatabaseTests) will act as an automatic grading tool, letting you know if your solution was correct or not.

Next Article

So far, we have only scraped the surface of database streams. The next article will allow you to write standalone database applications in pure Java. Happy coding!

Further Reading

A Guide to Streams: In-Depth Tutorial With Examples

Working With Stream APIs in Java 1.8



If you enjoyed this article and want to learn more about Java Streams, check out this collection of tutorials and articles on all things Java Streams.

Database application Relational database Stream (computing) Java (programming language) sql master

Published at DZone with permission of Per-Åke Minborg, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Java EE 6 Pet Catalog with GlassFish and MySQL
  • How to LINQ Between Java and SQL With JPAStreamer
  • Express Hibernate Queries as Type-Safe Java Streams
  • Architecture and Code Design, Pt. 1: Relational Persistence Insights to Use Today and On the Upcoming Years

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!