<commit_before>---
date: 2017-01-23T09:07:32-05:00
title: Database Access Tutorial
---

# Introduction

Most microservices will have to access database in order to fulfill consumer requests. 
In this tutorial, we will walk through the following steps with Oracle/Postgres/Mysql:

* How to setup database connection pool
* How to connect to the database instance
* How to do query database tables
* How to update database tables

# Preparation

In order to follow the steps below, please make sure you have the same working environment.

* A computer with MacOS or Linux (Windows should work but I never tried)
* Install git
* Install Docker
* Install JDK 8 and Maven
* Install Java IDE (Intellij IDEA Community Edition is recommended)
* Create a working directory under your user directory called networknt.

```
cd ~
mkdir networknt
```

# Create Database Demo Specification

First let's build an OpenAPI specification with several endpoints to demo database
access. You will need [swagger editor](https://networknt.github.io/light-4j/tools/swagger-editor/)
to create a specification. 

Here is the OpenAPI specification created and it can be found in 
[swagger repo](https://github.com/networknt/swagger) database sub folder.

```
swagger: '2.0'

info:
  version: "1.0.0"
  title: Light-Rest-4J Database Tutorial
  description: A demo on how to connect, query and update Oracle/Mysql/Postgres. 
  contact:
    email: <EMAIL>
  license:
    name: "Apache 2.0"
    url: "http://www.apache.org/licenses/LICENSE-2.0.html"
host: database.networknt.com
schemes:
  - http
  - https
basePath: /v1

consumes:
  - application/json
produces:
  - application/json

paths:
  /query:
    get:
      description: Single query to database table
      operationId: getQuery
      responses:
        200:
          description: "successful operation"
          schema:
            $ref: "#/definitions/RandomNumber"          
      security:
      - database_auth:
        - "database.r"
  /queries:
    get:
      description: Multiple queries to database table
      operationId: getQueries
      parameters:
      - name: "queries"
        in: "query"
        description: "Number of random numbers"
        required: false
        type: "integer"
        format: "int32"
      responses:
        200:
          description: "successful operation"
          schema:
            type: "array"
            items:
              $ref: "#/definitions/RandomNumber"
      security:
      - database_auth:
        - "database.r"
  /updates:
    get:
      description: Multiple updates to database table
      operationId: getUpdates
      parameters:
      - name: "queries"
        in: "query"
        description: "Number of random numbers"
        required: false
        type: "integer"
        format: "int32"
      responses:
        200:
          description: "successful operation"
          schema:
            type: "array"
            items:
              $ref: "#/definitions/RandomNumber"
      security:
      - database_auth:
        - "database.w"
securityDefinitions:
  database_auth:
    type: "oauth2"
    authorizationUrl: "http://localhost:8888/oauth2/code"
    flow: "implicit"
    scopes:
      database.w: "write database table"
      database.r: "read database table"
definitions:
  RandomNumber:
    type: "object"
    required:
    - "id"
    - "randomNumber"
    properties:
      id:
        type: "integer"
        format: "int32"
        description: "a unique id as primary key"
      randomNumber:
        type: "integer"
        format: "int32"
        description: "a random number"
```

Now let's clone the swagger repo to your working directory.

```
cd ~/networknt
git clone https://github.com/networknt/swagger
```

# Generate Demo Project

With the specification in place, we can generate the code with [swagger-codegen](https://github.com/networknt/swagger-codegen)

There are two different ways to generate the code:

* Local build
* Docker container

To learn how to use the tool, please refer to this [document](tools/swagger-codegen/)

### Generate code with local build

Clone and build swagger-codegen

```
cd ~/networknt
git clone <EMAIL>:networknt/swagger-codegen.git
cd swagger-codegen
mvn clean install -DskipTests
```

For this demo, I am going to generate the code into light-example-4j/database/generated
folder so that users can check the code later on from this repo. 

Let's checkout the light-example-4j repo and backup the existing database project.

```
cd ~/networknt
git clone <EMAIL>:networknt/light-example-4j.git
cd light-java-example
mv database database.bak
mkdir database
cd database
mkdir generated
```

Before generating the project, we need to create a config.json to define packages,
artifactId and groupId for the project.

Here is the content of the file and it can be found in ~/networknt/swagger/database

```
{
  "invokerPackage": "com.networknt.database",
  "apiPackage":"com.networknt.database.handler",
  "modelPackage":"com.networknt.database.model",
  "artifactId": "database",
  "groupId": "com.networknt"
}
```


Code generation

```
cd ~/networknt/swagger-codegen
java -jar modules/swagger-codegen-cli/target/swagger-codegen-cli.jar generate -c ~/networknt/swagger/database/config.json -i ~/networknt/swagger/database/swagger.yaml -l light-java -o ~/networknt/light-java-example/database/generated

```

Now you should have a project generated. Let's build it and run it.

```
cd ~/networknt
cd light-java-example/database/generated
mvn clean install exec:exec
```

Now you can access the service with curl following the step below.
<commit_msg>update docs<commit_after>---
date: 2017-01-23T09:07:32-05:00
title: Restful Database Access Tutorial
---

# Introduction

Most microservices will have to access database in order to fulfill consumer requests. 
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 the steps
will be somewhat different but the concept is the same.

* How to setup database connection pool
* How to connect to the database instance
* How to do query database tables
* How to update database tables

# Preparation

In order to follow the steps below, please make sure you have the same working 
environment.

* A computer with MacOS or Linux (Windows should work but I never tried)
* Install git
* Install Docker
* Install JDK 8 and Maven
* Install Java IDE (Intellij IDEA Community Edition is recommended)
* Create a working directory under your user directory called networknt.

```
cd ~
mkdir networknt
```

# Create Database Demo Specification

First let's build an OpenAPI specification with several endpoints to demo database
access. You will need [swagger editor](https://networknt.github.io/light-4j/tools/swagger-editor/)
to create a specification. 

Here is the OpenAPI specification created and it can be found in 
[model-config repo](https://github.com/networknt/model-config/tree/master/rest/database) 

```
swagger: '2.0'

info:
  version: "1.0.0"
  title: light-rest-4j Database Tutorial
  description: A demo on how to connect, query and update Oracle/Mysql/Postgres. 
  contact:
    email: <EMAIL>
  license:
    name: "Apache 2.0"
    url: "http://www.apache.org/licenses/LICENSE-2.0.html"
host: database.networknt.com
schemes:
  - http
  - https
basePath: /v1

consumes:
  - application/json
produces:
  - application/json

paths:
  /query:
    get:
      description: Single query to database table
      operationId: getQuery
      responses:
        200:
          description: "successful operation"
          schema:
            $ref: "#/definitions/RandomNumber"          
      security:
      - database_auth:
        - "database.r"
  /queries:
    get:
      description: Multiple queries to database table
      operationId: getQueries
      parameters:
      - name: "queries"
        in: "query"
        description: "Number of random numbers"
        required: false
        type: "integer"
        format: "int32"
      responses:
        200:
          description: "successful operation"
          schema:
            type: "array"
            items:
              $ref: "#/definitions/RandomNumber"
      security:
      - database_auth:
        - "database.r"
  /updates:
    get:
      description: Multiple updates to database table
      operationId: getUpdates
      parameters:
      - name: "queries"
        in: "query"
        description: "Number of random numbers"
        required: false
        type: "integer"
        format: "int32"
      responses:
        200:
          description: "successful operation"
          schema:
            type: "array"
            items:
              $ref: "#/definitions/RandomNumber"
      security:
      - database_auth:
        - "database.w"
securityDefinitions:
  database_auth:
    type: "oauth2"
    authorizationUrl: "http://localhost:8888/oauth2/code"
    flow: "implicit"
    scopes:
      database.w: "write database table"
      database.r: "read database table"
definitions:
  RandomNumber:
    type: "object"
    required:
    - "id"
    - "randomNumber"
    properties:
      id:
        type: "integer"
        format: "int32"
        description: "a unique id as primary key"
      randomNumber:
        type: "integer"
        format: "int32"
        description: "a random number"
```

Now let's clone the model-config repo to your working directory.

```
cd ~/networknt
git clone <EMAIL>:networknt/model-config.git
```

# Generate Demo Project

With the specification in place, we can generate the code with [light-codegen](https://github.com/networknt/light-codegen)

There are three different ways to generate the code:

* Local build
* Docker container
* Script with docker container

To learn how to use the tool, please refer to this the [README.md](https://github.com/networknt/light-codegen)

### Generate code with local build

Clone and build light-codegen

```
cd ~/networknt
git clone <EMAIL>: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/database/generated
folder so that users can check the code later on from this repo. 

Let's checkout the light-example-4j repo and backup the existing database project.

```
cd ~/networknt
git clone <EMAIL>:networknt/light-example-4j.git
cd light-example-4j/rest
mv database database.bak
```

Before generating the project, we need to create a config.json to define packages,
artifactId, groupId and other options for the project.

Here is the content of the file and it can be found in ~/networknt/model-config/rest/database

```
{
  "invokerPackage": "com.networknt.database",
  "apiPackage":"com.networknt.database.handler",
  "modelPackage":"com.networknt.database.model",
  "artifactId": "database",
  "groupId": "com.networknt"
}
```


Code generation

```
cd ~/networknt/swagger-codegen
java -jar modules/swagger-codegen-cli/target/swagger-codegen-cli.jar generate -c ~/networknt/swagger/database/config.json -i ~/networknt/swagger/database/swagger.yaml -l light-java -o ~/networknt/light-java-example/database/generated

```

Now you should have a project generated. Let's build it and run it.

```
cd ~/networknt
cd light-java-example/database/generated
mvn clean install exec:exec
```

Now you can access the service with curl following the step below.

{'fraction_non_alphanumeric': 0.09309650888091696, 'fraction_numerical': 0.010324612827018987, 'mean_word_length': 2.9867457272410185, 'pattern_counts': {'":': 10, '<': 10, '<?xml version=': 0, '>': 10, 'https://': 8, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2628307', 'n_tokens_mistral': 3405, 'n_tokens_neox': 3288, 'n_words': 1153}