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

Install MySQL on ECS

  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

  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

  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

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

/**
* 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

/**
* 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

/**
* 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

/**
* 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

/**
* 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

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

Test the API with Postman

SQL as Stream

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

--

--

--

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

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Root me Walkthrough(THM)

Elements of transformation

Apsara Cloud Network Platform: The Technology of Application Load Balancer (ALB)

PiCharts: Easy JavaScript charts with Ruby

Getting data out of word file (docx) and storing in a list using python

DevSecOps best practices for enterprises leveraging Kubernetes

Midtype as a Firebase Alternative

A comparison of Midtype versus Firebase’s offerings.

Crossing the Serverless Chasm

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

More from Medium

Distributed Database Systems

Kafka Dynamic Configuration & Multiple Error Handler

Compiling and running Java Source code using GraalVM and Oracle Java Development Kit

9 Insanely Helpful Kafka Commands Every Developer Must Know