View Database Tables as Standard Java Streams Using Speedment

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.

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 …

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.

  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

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
  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
  1. MySQL: jdbc:mysql://localhost:3306/speedmentdemo (correct as we’re using MySQL)
  2. PostGreSQL: jdbc:postgresql://localhost:3306/speedmentdemo
[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.

server.port=8080info.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

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);
}

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());
}
@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));
}

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

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()

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.

--

--

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
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com