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

Java installed & environment variable set up
Basic Spring-Boot knowledge
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:

sudo apt-get update : to update our source list
sudo apt-get install mysql-server : to properly install MySQL server binary
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:

Here we can clearly see that:

We've chosen MySQL as database engine
The JDBC driver version is 5.1.46
Java 8+ is required for Speedment to work as we're using the Stream API
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 :

speedment:clear allowing to remove all generated code
speedment:clearTables removing all tables, columns, indexes etc from the config file
speedment:edit modifies the speedment.json-file without using tool
speedment:generate to generate code from the speedment.json file with no tool
speedment:init to initialize a new Speedment project without using tool
speedment:reload reloads meta data and merges changes with the existing config file
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:

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:

id: the primary key of the table, an auto-increment field
title: the title of the article
content: the article's full content
published_date: the published date

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:

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:

MySQL: jdbc:mysql://localhost:3306/speedmentdemo (correct as we're using MySQL)
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:

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:

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

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.

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:

[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 17.126 s
[INFO] Finished at: 2018-11-04T13:19:39+01:00
[INFO] Final Memory: 37M/295M
[INFO] ------------------------------------------------------------------------

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:

server.port=8080

info.app.name=SpeedmentDemo
spring.application.name=SpeedmentDemo

logging.level.root=INFO
logging.level.xyz.dassiorleando.speedmentdemo=DEBUG

spring.speedment.logging=true
spring.speedment.url=jdbc:mysql://localhost:3306/speedmentdemo?useUnicode=yes&characterEncoding=UTF-8&characterSetResults=UTF-8
spring.speedment.username=root
spring.speedment.password=root

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:

/**
 * To create an article
 * @param articleDTO
 * @return
 */
@PostMapping("/articles")
public Article create(@RequestBody @Valid ArticleDTO articleDTO) {
    log.debug("Create an article with the properties {}", articleDTO);

    // build the article before saving
    Article article = new ArticleImpl()
            .setTitle(articleDTO.getTitle())
            .setContent(articleDTO.getContent())
            .setPublishedDate(new Timestamp(System.currentTimeMillis()));
    try {
        articleManager.persist(article);
    } catch (SpeedmentException se) {
        System.out.println("Failed to persist " + article + ". " + se.getMessage());
    }
    return article;
}

Update an Article

Similarly, the update is as follows:

/**
 * To update an article
 * @param articleDTO
 * @return
 */
@PutMapping("/articles")
public Article update(@RequestBody @Valid ArticleDTO articleDTO) {
    log.debug("Update the article of id {} with the properties {}", articleDTO.getId(), articleDTO);

    // Update the article matching the incoming id
    Optional<Article> article = articleManager.stream()
            .filter(Article.ID.equal(articleDTO.getId()))
            .findFirst();

    article.ifPresent(l -> {
        l.setTitle(articleDTO.getTitle());
        l.setContent(articleDTO.getContent());
        articleManager.update(l);
    });
    return article.orElse(null);
}

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:

/**
 * We find an article by id
 * @param id
 * @return
 */
@GetMapping("/articles/{id}")
public Article findById(@PathVariable @NotNull int id) {
    log.debug("Load the article by id: {}", id);
    return articleManager.stream().filter(Article.ID.equal(id)).findAny().orElse(null);
}

Find All Articles

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

/**
 * Get the list of all saved articles
 * @return
 */
@GetMapping("/articlesCustom")
public List<Article> list() {
    log.debug("We get the list of articles");
    return articleManager.stream().collect(toList());
}

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:

@GetMapping(path = "/articles", produces = "application/json")
public String get(
        @RequestParam(name = "filter", defaultValue = "[]") String filters,
        @RequestParam(name = "sort", defaultValue = "[]") String sorters,
        @RequestParam(value = "start", defaultValue = "0") long start,
        @RequestParam(value = "limit", defaultValue = "25") long limit) {

    return getHelper(
        ControllerUtil.parseFilters(filters, ArticleFilter::new).collect(toList()),
        ControllerUtil.parseSorts(sorters, ArticleSort::new).collect(toList()),
        start,
        limit
    );
}

Article Deletion

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

/**
 * Delete an article by id
 * @param id
 */
@DeleteMapping("/articles/{id}")
public void deleteById(@PathVariable @NotNull int id) {
    log.debug("Delete the article of title: {}", id);

    // first look for the corresponding article
    Optional<Article> article = articleManager.stream()
            .filter(Article.ID.equal(id))
            .findFirst();
    article.ifPresent(l -> articleManager.remove(l));
}

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.

1. start mysql first
2. mvn clean install
3. mvn spring-boot:run

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.

Test the API with Postman

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

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:

FROM    stream()
SELECT    map()
WHERE    filter() (before collecting)
HAVING    filter() (after collecting)
JOIN    flatMap()
DISTINCT    distinct()
UNION    concat(s0, s1).distinct()
ORDER BY    sorted()
OFFSET    skip()
LIMIT    limit()
GROUP BY    collect(groupingBy())
COUNT    count()

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.

Community

View Database Tables as Standard Java Streams Using Speedment

Prerequisites

Overview

Install MySQL on ECS

Using Speedment with a Maven Project

Start the Speedment Tool

Write the Application

Create an Article

Update an Article

Find an Article by Id

Find All Articles

Article Deletion

Compile and Run the App

Test the API with Postman

SQL as Stream

Conclusion

Read previous post:

Read next post:

Alibaba Clouder

You may also like

Comments

Alibaba Clouder

Related Products

ECS(Elastic Compute Service)

API Gateway