How to connect to the 3rd party database such as MySQL at Nimbella (example in Java)

Nov 23, 2020 | Technology

copied

With Nimbella, we can easily integrate any DB into any language like the way a beginner learns JDBC, PyMongo,etc. Implementation through their IDE.

This blog is a part 1 on illustrating basic DB Integration with Nimbella. Also note that there will be performance issues during high traffic since MySQL is not designed to scale by default.

Let’s create a Table named "Runs" which contains the data of Cricket Players as shown.

Create a Database in MySQL

Please consider that we are going to create an API in the Nimbella Platform which returns player data as per the player Name in the Request.

Create a Maven Project (getplayerstats) and then Create a package (playerdata) under src/main/java and then Create the Following Files.

Create a playerdata package

  1. DBUtil.java: — to facilitate DB Operations which contain below methods

DBUtil.java

  1. GetPlayerData.java — contains a main method, which returns the Json Object as Response and receives the Client request in the Form of JsonObject as arguments. Client Request lifecycle starts and ends in this main method.

i.e, when a client sends a request mapped to this function, Nimbella receives the request and forwards it to this function and returns the response of the main method to the client as Response.

GetPlayerData.java

  1. Player.java — Data model class for the Player Object

i.e, it contains the getter,setter for Player Object.

Player.java

  1. PlayerDao — DAO for the Player object. The Data Access Object (DAO) is an object or an interface that provides access to an underlying database,

i.e., it provides an abstraction between database and business logic.

PlayerDao

  1. PlayerDaoImplementation.java — This class will be used as a DAO to perform CRUD operations.

PlayerDaoImplementation.java

  1. Build.sh File: Nimbella creates a jar file containing all the libraries mentioned in the pom.xml. To do so, we need to create a build.sh file in the root directory of the project.

Build.sh File

  1. Pom.xml

Pom.xml

Set up Nimbella for Deployment:

  1. Create an account in Nimbella and install nim CLI and then log in to it, as below

    Set up Nimbella for Deployment 1-1

    Set up Nimbella for Deployment 1-2

    (or ) Execute below command in CLI. On successful login, it would look like below

    nim login

    Set up Nimbella for Deployment 1-3

  2. Get Base URL/workspace By Executing below command:

     nim auth current

Set up Nimbella for Deployment 2

Deployment Procedure:

Everything is set properly. Now it's time to deploy our Maven project into Nimbella. To Deploy we should follow a specific folder structure as per Nimbella’s recommendation.

We have created a maven project named "getplayerstats". Now we are going to do the below.

  1. Create a Folder named "CricketStatsApp" and create a folder named "packages" inside it.
  2. Inside "CricketStatsApp" folder we are going to create another file named project.yml, which is used as a Deployment configuration file in Nimbella.

Deployment Procedure 2

  1. Now we are going to create a folder named "cricket" and paste the created Maven project directory inside it. Our Project Directory looks as below.

Deployment Procedure 3

Deployed URL:

As per the above folder structure, the URL for Our Function will be

https://pathitha-4mpb1smu66t-apigcp.nimbella.io/api/cricket/getplayerstats

https://pathitha-4mpb1smu66t-apigcp.nimbella.io/api/ →BaseURL/Nimbellaworkspace

cricket/getplayerstats →End point as per Folder Creation

How to Configure Environment Variables in Nimbella for deployment:

  1. In the Project.yml file specify the environment variables to be used for deployment under the "environment" section inside the respective function block, as below.

Configure Environment Variables in Nimbella for deployment 1

  1. Create a .env file and mention the values of the environment variables in this file, as below.

Configure Environment Variables in Nimbella for deployment 2

Once done, we can run Nimbella’s deployment command by passing the path of the environment (.env) file as an argument to set the environment variables for the Serverless function.

Deployment Command:

 nim project deploy CricketStatsApp --env={path of environment file}

Deployment Command

Access API using Postman:

Once deployment is over, API can be accessed through Postman to retrieve the stored Player data from 3rd Party DB(MySQL).

Access API using Postman 1

Access API using Postman 2

Access API using Postman 3

In Brief:

  1. Create a Nimbella account and install Nimbella CLI
  2. Login to the CLI with "nim login"
  3. Create a Maven project and write your logic to connect & retrieve data from an external database and return a JSON object, having the data as a response inside the "body" attribute.
  4. Create build.sh and provide artifact id from pom.xml in this.
  5. Create a packages folder which encloses all these Maven project files.
  6. Create project.yml and specify the file where the main method is written.
  7. Configure Environment Variables for the deployment through the project.yml file.
  8. Deploy the project and hit Postman with the deployed URL once it's deployed to get the response.

GitHub Source: https://github.com/boltathi24/nimbella-sql-java

By - Athithan Raj P. Follow him on Twitter: @raj_athithan

Recent Posts