Co-author - Mohammed Asif Kundgol
The Java Database Connectivity (JDBC) API is Java-based technology that provides access to data sources such as database management systems and files. IBM App Connect provides a JDBC connector that offers database-independent connectivity.
The JDBC connector introduces Custom SQL Query execution capabilities in App Connect to accelerate the speed of development while working with databases, shorten time to value, and improve overall user experience. You can use this connector to connect to a number of supported databases to execute custom SQL (CRUD) statements.
The JDBC connector offers the following capabilities:
- Supports Data Query Language (DQL) or Data Manipulation Language (DML) statements.
- Supports complex queries including JOINs, and subqueries etc
- Supports valid SQL commands, for example, select, insert, update and delete queries. These include all SQL Logical Operators, Comparison Operators, aggregate functions, and clauses etc
- Supports complex queries spanning multiple schemas and database objects.
- Handles any unintended SQL Injection vulnerabilities because the implementation uses PreparedStatements at the back-end.
Building custom SQL queries and SQL injection prevention:
You can use App Connect to map fields using the traditional mappings, that is, by typing a keyword or via the mapper icon. In addition to this, you can now supply a query by using a question mark '?'. With every entry of a '?', you can map from the list of available fields. The mapped fields are treated as secure fields which are translated into a parameterized query in the back-end. A parameterized query is a query in which placeholders are used for parameters and the parameter values are supplied at execution time. The most important reason to use parameterized queries is to avoid SQL injection attacks.
The following images demonstrate how mapping works in the Custom SQL query field.
1. Type a keyword to see the list of matched fields in the mapper. In this image we have started typing the letter "or" and "ORDERID" is displayed.
Figure 1: Image to show how parameters are displayed
2. Click the mapping icon (highlighted in blue) to open the Available mappings list.
3. Query with a question mark '?' which will then display fields from the mapper.
Figure 3: Image to show the parameter value bubble
If you have a pre-written query you can copy and paste it directly into the Custom SQL query field. Use a question mark to map to a parameter value. Each question mark will be highlighted in a blue or red bubble. Click each bubble to map to the fields you want.
Figure 4: Image to show mapping in copied SQL query
You are prevented from running an incomplete query or a query with incorrect mappings. The node will throw an error it will not allow the API to start.
Connecting to JDBC:
To connect to a Database from the App Connect Designer Catalog page for the first time, expand JDBC, then click Connect.
Figure 6: Image to show available Database Types List
Enter the required database details and then click Connect .
Tip: Before you use the account, rename the account to something meaningful that helps you to identify it. To rename the account on the “Catalog” page, select the account, open its options menu (⋮), then click Rename Account.
Use-case scenario:
In any enterprise, business data is spread across several departments and stored in multiple databases. For example, a company named Acme Corp, a supplier of computer accessories, receives orders from their e-commerce website and the orders need to be added to a 'Purchase Order' table in a database in a different department. Next, in order for the shipping team to process the orders and dispatch to customers, the individual line items need to be updated in a 'Purchase Order Line' table.
Acme Corp can use the JDBC application node in App Connect to build an API to interact with these databases spread across different departments to query an order and insert or update an order if necessary. The integration flow can be exposed as an API that can be invoked by other applications be it their e-commerce website widgets or mobile applications. The API will update an order if any changes are made, such as adding new items or updating the quantity of existing items. If the order does not exist then it will create a new one.
Figure 10 below displays the flow for a POST API that we created in App Connect with the JDBC nodes. The following tables/schemas are used in this scenario:
- PORDER (Purchase Order)
- POLINE (Purchase Order Line)
- ITEMS
Here is an example of the ITEMS table with some entries
Figure 9: ITEMS table values
The first JDBC Custom query node in the image below is used to check whether the order already exists. This is done by writing a SELECT query in the Custom SQL Query field on the PORDER table. Based on the response of the first query we can then decide whether the incoming order needs to be updated or inserted. If the order does not exist we will use an INSERT query on the PORDER and POLINE tables to create an order. If the order already exists we need to update the order details as well as the corresponding purchase order lines which will need to be updated or inserted based on their existence in the database .
Figure 10: The flow for an API to insert/update purchase order and order lines
Check out the following video to see how we implemented this flow in App Connect.
You can also read more about the JDBC connector here - How to use IBM App Connect with JDBC
For more information about other supported connectors, see Connectors .
#AppConnect#JDBI
#JDBC#connectapplications