View Database Tables as Standard Java Streams Using Speedment

By Dassi Orleando, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud’s incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.

Prerequisites

  1. Java installed & environment variable set up
  2. Basic Spring-Boot knowledge
  3. Alibaba Cloud account

Overview

Speedment is an open source Java ORM toolkit. It analyzes and generates the Java representation of the data model while being able to abstract the explicit SQL query with the famous Java Stream.

In this article, we’ll walk through how to use Speedment to query a MySQL database without the need to write any SQL instructions. We’ll be running our app on an Alibaba Cloud Elastic Compute Service (ECS) instance.

Install MySQL on ECS

Here are the terminal commands showing how to install MySQL into an Alibaba Cloud Ubuntu ECS machine, the last one is to configure our installation and make it more secured:

  1. sudo apt-get update : to update our source list
  2. sudo apt-get install mysql-server : to properly install MySQL server binary
  3. mysql_secure_installation : the guide to make the installations more secured in term of password strength, clean default credentials …

Note: The Speedment open source version supports MariaDB, MySQL and PostgreSQL; to handle more, an enterprise version is available.

Using Speedment with a Maven Project

As stated in the introduction, the purpose of using Speedment is to be able to perform all kinds of SQL queries to our database without writing any SQL statements.

The recommended way to have a freshly generated Maven project for this is by using the Speedment Initializer that will greatly help us for the project file structure.

Following the initializer link, let’s just fill the corresponding details for our project then hitting the DOWNLOAD button as illustrated in the screenshot below:

Image for post
Image for post

Here we can clearly see that:

  1. We’ve chosen MySQL as database engine
  2. The JDBC driver version is 5.1.46
  3. Java 8+ is required for Speedment to work as we’re using the Stream API
  4. We opted to generated a Spring project, meaning it’s a web app

Next, we need to unzip the generated project and import into our IDE, some of them are known to work well with Speedment as Netbeans 8.2, IDEA 2018.1 or Eclipse Oxygen.

Start the Speedment Tool

The generated project comes with a lot of configurations, some maven dependencies and plugins as Speedment Enterprise Maven Plugin. It’s the plugin allowing the use of the Speedment tool via the simple maven goals/targets :

  1. speedment:clear allowing to remove all generated code
  2. speedment:clearTables removing all tables, columns, indexes etc from the config file
  3. speedment:edit modifies the speedment.json-file without using tool
  4. speedment:generate to generate code from the speedment.json file with no tool
  5. speedment:init to initialize a new Speedment project without using tool
  6. speedment:reload reloads meta data and merges changes with the existing config file
  7. speedment:tool is starting the graphical tool to connect to an existing database

Note: Most of these operations are accessible from the Speedment Graphical Tool.

From IntelliJ we can see all these maven targets as shown in the image below:

Image for post
Image for post

For the sake of the demo we’ve created a MySQL database named speedmentdemo with the database user root (password is root too) including a single table Article with the following columns:

  1. id: the primary key of the table, an auto-increment field
  2. title: the title of the article
  3. content: the article’s full content
  4. published_date: the published date
Image for post
Image for post

Now, let’s run the graphical tool from the IDE by right clicking on the speedment:tool goal then choose Run Maven Build to have the UI which is a basic form that allows to connect to an existing database:

Image for post
Image for post

We’ve filled our info to be connected to the right database of our server (either in local or a remote one).

From the connection screen form it’s also possible to provide the database connection url corresponding to the selected database:

  1. MySQL: jdbc:mysql://localhost:3306/speedmentdemo (correct as we’re using MySQL)
  2. PostGreSQL: jdbc:postgresql://localhost:3306/speedmentdemo

With a successful connection, Speedment will analyses the provided data sources metadata and creates the corresponding configurations in the left menu:

Image for post
Image for post

Here we can make a lot of other customs configurations for example the corresponding Java fields name to use, the type to consider when mapping the Java POJO with the database column.

Customize the published_date config Java Alias:

Image for post
Image for post

Customize the article config in general, so that we generate the spring RestController too. Let’s be sure the Generate @RestController option is enabled:

Image for post
Image for post

For the article table, we can also update the details so that the generated controller will come with more or less features as filters, sorters, skip/limit or enable/disable CORS.

Image for post
Image for post

At the end of our configurations let’s press the Generate button in the top toolbar to generate the corresponding configurations files/classes. We can now close the UI and compile the whole project to be sure all is well:

Write the Application

Now it’s time to customize our application to add more logics to CRUD on the Article table using the generated code.

Here’s our Spring application.properties file content with our database fields necessary for the Speedment GeneratedSpeedmentdemoConfiguration class to connect to the database:

More details about the Spring integration can be found in this guide or here.

Create an Article

Let’s create an article using the Steam API with the title and content fields:

Update an Article

Similarly, the update is as follows:

The update occurred only on the fields we defined into the ifPresent section, for example up here we don’t update the published date field.

Find an Article by Id

Query of a single Article by its id is straightforward and can be done in this way while still using the Stream API:

Find All Articles

Now, accessing the list of all saved articles could be done in one line:

We called it articlesCustom because the generated Article controller has already a Get (/articles) rest call defined by its generated parent class called GeneratedArticleController.

That one implementation allows us to query all the articles while filtering and sorting our results, here is this inherited call:

Article Deletion

Let’s end with the CRUD by deleting an Article by id:

The full Article controller can be found into the repository on Github and More CRUD operations details are illustrated into the Speedment guide.

Compile and Run the App

Run the following code to compile and run our app.

At the end the application runs on the default port 8080. For a custom one, we only need to update the port field in the application.properties file.

Image for post
Image for post

Test the API with Postman

Let’s test the Article creation rest call with Postman. Here’s the query and its result in image:

Image for post
Image for post

SQL as Stream

From our CRUD operations up here we seen how we can query our MySQL database without writing any SQL statements, here are some Speedment equivalent to know:

Read more Stream to SQL Equivalences here.

Conclusion

In this detailed article, we’ve seen how to build a Spring-Boot APP using Speedment to perform database operations on a MySQL database management system.

The full source code can be found on GitHub.

Reference:https://www.alibabacloud.com/blog/view-database-tables-as-standard-java-streams-using-speedment_594368?spm=a2c41.12516277.0.0

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store