Exposing JDBC Data over a JSON API

Version: 17.07

Supported Since: 17.07

Use Case Description

QikBuy is a small retail shop that is planning to open a new website and mobile app for advertising their products. It hopes to establish a RESTful API for product display and search which can be shared between the site and the app, and can be maintained independently. As it is not planning to allow purchasing of products via the API yet, it needs only a read-only JSON API that can be implemented very quickly, as the project deadline is approaching.

Proposed Solution

The JSON Data Service processing element is specialized for scenarios like the one described above, and can be set up with minimal effort. Each required API endpoint can be set up simply by specifying a new URL-to-SQL qurery mappings on the element configuration.

QikBuy will specify the following two endpoints for its API (the longer pattern taking precedence in the list):

/product/search? q={name}&maxPrice={price}

searches for products based on name fragments and maximum price, and returns a JSON array containing a list of matches; note that the input for maxPrice will always be suffixed with "USD" (to ensure that the caller is aware that the price comparison is being done in USD), but the backend database contains only numeric values for the price column (without the "USD" suffix)

/product/{id}

returns a JSON object (single match) representing the product for the specified id

Implementation of the Solution

Prerequisite

In order to implement above use case you must first select following dependencies when you are creating a new Ultra project.

NIO HTTP Connector from the connector list
JSON Data Service from the processor list

If you have already created a project, you can add above dependencies via Component Registry. From Tools menu, select Ultra Studio → Component Registry and from the Connectors list and Processors list, select above dependencies.

For our scenario we shall assume that the data source for QikBuy’s products is a product table matching the following schema definition, under a MySQL database shop running on localhost (you may use a different schema or DBMS, with appropriate changes to the query mappings):

CREATE TABLE product (
  id smallint(6)
  name varchar(25),
  price smallint(6),
);

Implementation

Before implementing the API (integration flow) we need to specify the data source from which the backend data would be retrieved. Open project.xpml and add the following resource definition under the <x:resources> tag:

<x:resource id="ds-resource">
    <bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="url" value="${db.url}"/>
        <property name="username" value="${db.username}"/>
        <property name="password" value="${db.password}"/>
    </bean>
</x:resource>

All database parameters have been externalized and can be customized via the default.properties configuration file at src/main/resources, when testing the flow within UltraStudio. Ensure that you specify proper values for each parameter if the defaults defined therein are not compatible with your set-up.

To implement the use case, first let’s create our integration flow named json_data_service and then add required processing components by going through following steps in order.

Add a NIO HTTP Ingress Connector from the Connectors → Ingress Connectors list, to accept processing requests from external application. The ingress connector should be configured to expose a single web service on HTTP port 8280 and under /product/.+ Service path regular expression pattern, to accept only API-bound requests.

Add a JSON Data Service processing element from the Processors → Data Service list, and configure it with:

ds-resource (defined above) as the Data Source, and

the following mappings under the Query Map, in the same order:

/product/search?q={name:VARCHAR} &maxPrice={price:INTEGER}USD

SELECT * FROM product WHERE name LIKE CONCAT('%',:name,'%') AND price ⇐ :price

/product/{id:INTEGER}

SINGLE: SELECT * FROM product WHERE id = :id

Note the maxPrice={price:INTEGER}USD pattern whereby we can retain certain prefixes and suffixes (USD) in the query or path parameter (maxPrice) but discard them from the actual SQL query parameter (price).

Connect the Processor out port of the ingress connector to the Input of the JSON data service processing element, and the Next out port of the latter to the Input (bottommost) port of the former, to complete the HTTP loop.

The completed integration flow should resemble the following:

Now you can run the Ultra Project and check the functionality of the integration flow. Create and start an UltraESB Server run configuration.

Property Configuration

When running the sample in the UltraESB-X distribution, you need to override the following properties in-order for the sample to work. The properties file is located at $ULTRA_HOME/conf/projects/json-data-service/default.properties.

Refer to Managing Project Properties documentation on how to override properties.

db.url

JDBC URL for the external database (e.g. jdbc:mysql://localhost:3306/shop); note that the database must contain a product table matching the schema given previously

db.username

username for authenticating to the above database

db.password

password for authenticating to the above database

You will also have to add the mysql-connector-java-5.1.38.jar dependency (downloadable from here) to $ULTRA_HOME/lib/custom directory.

After that, navigate to $ULTRA_HOME/bin directory. Next you can run the UltraESB-X distribution with following command to start the engine with this sample project deployed.

./ultraesbx.sh -sample json-data-service

Testing the Integration Project

Before testing the flow, ensure that you have a few data entries in the shop.product table.

Open up the HTTP Client tool shipped with Ultra Studio Toolbox and send a GET request to the URL http://localhost:8280/product/1 (assuming that a product with ID 1 exists in the table; otherwise, use a different ID). You would receive a 200 response with a JSON payload containing a single product:
```
HTTP/1.1 200 OK
...
Content-Type: application/json
Content-Length: 50
Connection: close

{"name":"product1","price":1000,"id":1}
```
Retry the request, but this time with a non-existent product ID (e.g. 10000). You would receive a 404 response indicating that no product was found:
```
HTTP/1.1 404 Not Found
...
Content-Length: 0
Content-Type: text/plain; charset=UTF-8
Connection: close
```
Send a request to http://localhost:8280/product/search?maxPrice=1000USD (assuming some of the products in the table have prices below 1000; note the "USD" prefix). You would receive a 404 (client error) response stating that the q query parameter value is missing:
```
HTTP/1.1 400 Bad Request
...
Content-Length: 35
Content-Type: text/plain; charset=UTF-8
Connection: Close

Missing value for query parameter q
```
Repeat the same query by adding a value for q (e.g. http://localhost:8280/product/search?maxPrice=1000USD&q=phone). You would receive a 200 response, with a JSON array containing matching products (or an empty array if no matches were found).
```
HTTP/1.1 200 OK
...
Content-Type: application/json
Content-Length: 84
Connection: close

[{"name":"Redmi Note 2","price":450,"id":3},{"name":"Galaxy S5","price":400,"id":4}]
```

In case 2 we received a 404 error because we were specifically looking for a single product (using the SINGLE: prefix of the query configuration) whereas in case 4 we received an empty response because we were looking for a collection and an empty array is still a valid collection.

Terms of Use