Rest API is every where now adays and it simplifies the integration between systems.

But when you only have a Relational Database, and you want to expose your DB CRUD operations to Rest API, most of the time, you will need to do some programming.

For small projects or companies that do not have enough resources, it would be hard to develop a program to do that.

Is there an easier way to do that?

The answer is yes, this demos shows how to use WSO2 to do this, WSO2 is an open source platform, it has a web admin UI and a eclipse based integration studio tool.
The version I’m using is WSO2 6.6, you can download using the link.
Please also refer to the Integration Corner’s youtube channel with detail on how to use WSO2

After download, simply unzip the file, go to /bin folder and use integrator.bat or integrator.sh t start WSO2.
You should be able to visit https://localhost:9443/carbon using admin/admin

In this demo we uses Mysql to do the test.
Simply refer to most-popular-mysql-commands to get the sql we need in this demo

Following SQL are executed in the DB to create the test table and sample data
CREATE TABLE people(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(255),age INT);
INSERT INTO people (name, age) VALUES (‘John’, 24), (‘Jane’, 22);

Now we create a Data Service for this, we name it “PeopleDataService”

In the next step, setu a Data Source, in this demo, we used MySql, so we need to setup the MySql jdbc url
Please note you will need to put a MySql connector jar file in WSO2’s lib folder
The jar I used is “mysql-connector-java-8.0.23.jar”, you can find similar ones in MySql install or official website

Get – Search Operation

Continue to setup the Query, you will need to also define the output to be a json, use column numbers and define the output mapping to the returned column number. $1 means the 1st column of the query output

Continue to the definition, we don’t need to define any operations in the DB, so we can ignore this.

We define a Resources, this will be the WSO2 url that link to the DB query, here we will use people, and the Resource Method(or commonly called http verb) will be GET

Save and finish the setup, now you should see a PeopleDataService in 1 min – refresh the page if you don’t see it

Now we can use PostMan to do a test on this Rest
You can find the end point definition on the Service Dashboard by clicking on the Data Service name

Use PostMan to do a Get operation test, and you should find it working as following

Post – Create Operation

Now we continue to create the Post/CREATE Restul API
We go inside the Service Dashboard and click the “Edit Data Service(Wizard)”, this will lead us back to the Data source definition wizard. We do not change the Data Source, but to create a new Query

Create the query as following
Please note, the parameters you passed in through Rest must have a leading ‘:’ char
After you create the query, click the “Generate Input Mappings”, it will create the Input Mappings for you
Also please change the type to INTEGER if it’s an INTEGER column in DB, as shown below

Save and continue to Add a resource to bind the Rest API to the new query

Now we can use PostMan to do the testing
For some reason, the post to insert into WSO2 is using following json payload

{"_postinscriptions": {
"name":"test",
"age":12
}
}

PostMan should show an 202 Accepted status, this means the query finished ok

Use the Get API to confirm the change, and you should see a new people is inserted

Update and Delete

Update and Delete will be similar to Create, just change the query and the Resource Method binding, and all bind to the same people resource

Following are the 2 queries for Update and Delete, please note you need to change the parameter to Integer
Update, uses PUT Method: update people set name=:name, age=:age where id=:id
Delete, uses DELETE Method: delete from people where id=:id

Starshipit is commonly used by many businesses in NZ.
So I got a small requirement, I want to automatically create a label, how can I do that?

Well, Starshipit do have its integration APIs, so we can use it to create labels.
Let’s try it out. Following is the API url just incase you need.
https://www.starshipit.com/integrations

We need to create a developer account in the website
After verifying your email, you are all good to go.

To be able to test out the API, we need an Starshipit API key, to get this, we need an Starshipit account
https://developers.starshipit.com/api-key

Use following url to start setting up an Starshipit account
How to setup a Starshipit account

Now you should be able to get the API key required to access the API
Click the Generate button in StarshipIt setup, and you should get the API key
Url: https://app.starshipit.com/Members/Settings/Api2.aspx

Also you should get your developer key
Url: https://developers.starshipit.com/developer
Click the User profile on the right top of the header bar if you don’t see it

Ok, now you should have following keys ready and handy before doing the API testing based on the Startshipit settings page and the developer profile page
API Key, Subscription Key, Primary Key, Secondary Key

Go to Create Order page on the API Reference you should see following page relate with the API
It shows the API url, and what you should put on the Request Headers

Click the “Try It” button, you should be able to try out the Create Order

Fill in your key information that’s required.
StarShip T-api-key: this is the API Key you got earlier from the Settings page
Ocp-Apim-Subscription-Key: this is the primary key you got earlier from the developer profile page
Now try to click the “Send” button at the bottom of the page, it should receive a 200 feedback

This means the API connection and call out works fine.
Now we try to do the same thing using PostMan on our own laptop
Setup PostMan Post request and the header as following, Please enter your own API key and Primary key

Setup the Body as following, and click the “Send” button
If everything works fine, you should receive a “200 OK” response from the Starshipit server

Congratulations, you have successfully create an Order in Starshipit

So now you can continue to use the same API setup and the API Reference to do other API calls to Starshipit.

Enjoy!

Following shows how can you setup a Starshipit account

Use the following url to start setting up an Startshipit account
https://app.starshipit.com/Dashboard/Wizard.aspx

Follow the instructions to setup your own account

Step1

Step2

Step3

Step4

After the registration, you should receive an validation email.
Once Validated, it’s all done.

 –  This blog shows how to Integrate Groovy into Java

Java is powerful and it can do almost everything.

However, it feels tired sometimes to spend hours on the setting before seeing a ‘Hello World’ to print out.

On the contrast, Groovy is easy to use as a scripting language on top of Java.

Sometimes it gives me an idea if we can merge these 2 together to make the life easier.

This blog shows a way to run Groovy Scripts dynamically inside Java – just do some simple change, we can make it a simple and powerful tool that can be embedded into existing Java projects as a scripting addon.

Following codes uses 2 different kinds of Groovy scripting running method, please refer to Standard Groovy documentation “Integrating Groovy into applications” for the difference.

Transformer1 uses GroovyScriptEngine

Transformer2 uses GroovyShell

import groovy.lang.Binding;
import groovy.lang.GroovyShell;
import groovy.util.GroovyScriptEngine;
import java.io.File;
import java.nio.file.Files;
import org.apache.commons.io.FileUtils;

public class GroovyTransformer {

public static void main(String[] args) throws Exception {
groovyScriptTransformer1();
// groovyScriptTransformer2();
}

public static void groovyScriptTransformer1() throws Exception {
byte[] input = Files.readAllBytes(new File("c:/test/order_sample.csv").toPath());

Binding sharedData = new Binding();
sharedData.setVariable("input", input);

String[] roots = new String[] { "c:/test" };
GroovyScriptEngine gse = new GroovyScriptEngine(roots);
gse.run("csvParser.groovy", sharedData);

byte[] output = (byte[]) sharedData.getVariable("output");
System.out.println(new String(output));

}

public static void groovyScriptTransformer2() throws Exception {

// get the source file bytes
byte[] input = Files.readAllBytes(new File("c:/test/order_sample.csv").toPath());
Binding sharedData = new Binding();
sharedData.setVariable("input", input);

// get the groovy script as a String
String script = FileUtils.readFileToString(new File("c:/test/csvParser.groovy"), "UTF-8");
System.out.println(script);

// create a groovy shell and run the script
GroovyShell shell = new GroovyShell(sharedData);
shell.evaluate(script);

// get the output of the transformation
byte[] output = (byte[]) sharedData.getVariable("output");
System.out.println(new String(output));
}

}

Following is the sample file “order_sample.csv”

Order Number,Date,To Name,
10090,29/10/15,Sharen Shirley,
10091,29/10/15,Justina Jump,
10092,29/10/15,Macie Maxon,
10093,29/10/15,Shamika Solt,
10094,29/10/15,Marcos Mccabe,

Following is the sample Groovy script “csvParser.groovy”, it did nothing but parsed the csv file into lines, it can easily be changed to transform to other file formats – xml, json or marshaling to groovy/java objects.

import java.io.ByteArrayInputStream
import java.io.Reader
import java.io.InputStreamReader
import static com.xlson.groovycsv.CsvParser.parseCsv
def sb = StringBuilder.newInstance()

Reader inputReader = new InputStreamReader(new ByteArrayInputStream(input));
for(line in parseCsv(inputReader,separator:',')){
// println line
sb.append(line)
sb.append("\n")
}

output = sb.toString().getBytes()