Restful Database Access

There are numerous options to connect to databases in the Light platform, and this tutorial is only one of the most straightforward options. It gives you the flexibility to support multiple databases with just configuration changes. However, you are working with generic JDBC configuration and losing fine-grained control on each specific database JDBC driver. If you are using one particular database and want more settings on the JDBC driver, please use the data-source module in light-4j.

Also, for large-scale microservices deployment, you are seldom dealing with the database directly from your service. Chances are your service is working with in-memory cache or in-memory data grid or some No-SQL database directly. For service to service orchestration, you might take a look at the light-eventuate-4j to adopt Event Sourcing and CQRS as your architecture pattern.

In this tutorial, we will walk through the following steps with Oracle/Postgres/Mysql for light-rest-4j framework. If you use light-graphql-4j or light-hybrid-4j frameworks, the steps will be somewhat different, but the concept is the same. Please note, the specification we use in this tutorial is based on OpenAPI 3.0, which is migrated from the Swagger 2.0.

  • How to use HikariCP for JDBC connection pool.
  • How to initialize database connection pool and plug it into light-4j startup hooks.
  • How to connect to the database instance.
  • How to query database tables.
  • How to update database tables.
  • How to performance test API application with wrk.

This tutorial shows how to use the SQL database in your microservices-based on light-4j frameworks.

Preparation

To follow the steps below, please make sure you have the same working environment. As this tutorial is targeted to Java developers and I assume you have the knowledge to work on a Java project on your personal computer. For people who are not Java developers, you can have Docker installed and pull the running image from Docker Hub to try the service created in this tutorial. A computer with macOS or Linux (Windows should work, but I never tried) Install git Install Docker Install JDK 11 and Maven Install Java IDE (IntelliJ IDEA Community Edition is recommended, and it is free) Create a working directory under your user home directory called networknt. Read More »

Specification

First, let’s build an OpenAPI 3.0 specification with several endpoints to demo database access. You will need swagger editor to create a specification. Once the specification is created, you can export it into both YAML and JSON format. YAML is easy for designers to work in an editor and is easy to be consumed by the light-codegen and light-rest-4j framework. Here is the OpenAPI 3.0 specification created and it can be found in model-config Read More »

Generation

With the specification in place, we can generate the code with light-codegen There are three different ways to generate the code with light-codegen: Local build or download the released jar Docker container Script with docker container To learn how to use the tool, please refer to the light-codegen tutorial Generate code with a local build Clone and build light-codegen cd ~/networknt git clone https://github.com/networknt/light-codegen.git cd light-codegen mvn clean install -DskipTests For this demo, I am going to generate the code into light-example-4j/rest/openapi/database/generated folder so that users can check the code later on from this repository. Read More »

Test

In the previous steps, we have generated the project and built it. Now the service is up and running. Let’s access it from the curl command. Single query. curl -k https://localhost:8443/v1/query Result: {"id":123,"randomNumber":456} Multiple queries with the default number of objects returned. curl -k https://localhost:8443/v1/queries Result: [{"id":123,"randomNumber":456},{"id":567,"randomNumber":789}] Multiple queries with ten objects returned. curl -k https://localhost:8443/v1/queries?queries=10 Result: [{"id":123,"randomNumber":456},{"id":567,"randomNumber":789}] Multiple updates with the default number of objects updated. curl -k https://localhost:8443/v1/updates Result: Read More »

Oracle, MySQL and Postgres Scripts

For database access, we are going to prepare three scripts for Oracle, MySQL, and Postgres. The three databases are all relational databases, and the SQL scripts are very similar. There are slight differences in the DDL, and you should only focus on the database you are going to use for your project and ignore others. Also, the framework supports other types of SQL and NoSQL databases as well, but you might need to update pom. Read More »

Start Database

In the previous step, we’ve already prepared the scripts for Oracle, MySQL, and Postgres databases and put these script files under light-example-4j/rest/openapi/database. At this moment, you should have two sub-folders under the database directory: generated and dbscript. To work on our service, we need to start database standalone for now. Depending on which database you are working on, you can choose one of them below. For this demo, we use MySQL, and later on, we can switch to Postgres and Oracle. Read More »

Query DB

In the previous step, we have created a private static data source in each handler. In this step, we are going to use that connection pool to access the MySQL database. Before we add logic to query the database, let’s copy connection to query folder. cd ~/networknt/light-example-4j/rest/openapi/database cp -r connection query And add a constructor that accepts two integers as parameters for RandomNumber in the model folder. This will ensure that it is easy to construct an object from QueryGetHandler. Read More »

Connection Pool

To connect to the database, we need to have service.yml that can inject a connection pool to the microservice you are building. Now we have a generated project with the configuration for MySQL, let’s take a look at the generated service.yml, and you can see the following section for MySQL connection pool configuration. service.yml in ~/networknt/light-example-4j/rest/openapi/database/generated/src/main/resources/config # Singleton service factory configuration/IoC injection singletons: # StartupHookProvider implementations, there are one to many and they are called in the same sequence defined. Read More »

Multiple Queries

In the previous step, we have updated the QueryGetHandler to retrieve data from the database for a single record. In this step, we are going to retrieve multiple records from the database. Let’s build multiple queries based on the codebase of the single query. cd ~/networknt/light-example-4j/rest/openapi/database cp -r query queries Now let’s update queries project for QueriesGetHandler.java package com.networknt.database.handler; import com.fasterxml.jackson.databind.ObjectMapper; import com.networknt.config.Config; import com.networknt.database.model.RandomNumber; import com.networknt.handler.LightHttpHandler; import com.networknt.service.SingletonServiceFactory; import io. Read More »

Multiple Updates

Let’s copy the queries to updates in order to work on updates. cd ~/networknt/light-example-4j/rest/openapi/database cp -r queries updates Now let’s update UpdatesGetHandler.java in the updates folder. package com.networknt.database.handler; import com.fasterxml.jackson.databind.ObjectMapper; import com.networknt.config.Config; import com.networknt.database.model.RandomNumber; import com.networknt.handler.LightHttpHandler; import com.networknt.service.SingletonServiceFactory; import io.undertow.server.HttpServerExchange; import io.undertow.util.Headers; import javax.sql.DataSource; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.Map; import java.util.concurrent.Callable; import java.util.concurrent.ConcurrentHashMap; import java.util.concurrent.Future; public class UpdatesGetHandler implements LightHttpHandler { private static final DataSource ds = SingletonServiceFactory. Read More »

Unit Test

In previous steps, we have updated three endpoints in the service and switch from Mysql to Oracle and Postgres without changing the code. In an official environment, config files like service.yml will be externalized so that you can even switch database during runtime just restart your instance whether your service is running as a Java process with “java -jar” or in Docker container. In this step, we are going to create some unit test cases. Read More »

Performance

In the previous step, we’ve updated project with H2 database to perform the unit test without any external database. In this step, we are going to run some performance test with wrk in Docker to give you a ballpark how fast this database demo is. We are not going to run it on a real testing server with service and database separated on two physical machines. Everything here is on my desktop with an AMD 2700x CPU. Read More »

H2 database

Normally H2 database is used for junit and integration test. But in some cases, for example for PoC project, we may want to use an embedded mode database, then H2 database will be a good choice to be use. For H2 embedded mode, an application opens a database from within the same JVM using JDBC. This is the fastest and easiest connection mode. H2 embedded mode includes two type of setting: Read More »

Oracle

In the previous steps, we have tried the single query, multiple queries, and updates to the MySql database. In this step, we are going to switch from MySql database to Oracle in the configuration service.yml and update pom.xml to include Oracle DB driver as part of the dependency. The first step is to start the Oracle database in Docker. The command has shown above. Next, we need to manually install Oracle JDBC driver jar into your local maven repository. Read More »

Postgres

In the previous step, we have switched to Oracle database from Mysql and in this step, we are going to switch from MySql to Postgres to further demonstrate how to config different database without change the service code. The only thing that needs to update is the configuration service.yml and pom.xml to include Postgres DB driver as part of the dependency. The first step is to start the Postgres database in docker. Read More »