How to use IBM App Connect with JDBC

The Java™ Database Connectivity (JDBC) API is a 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. You can use this connector to connect to a number of supported databases to run custom SQL (create, retrieve, update, and delete) statements.

You can use App Connect to pass key data from an application into a JDBC custom query that calls out to a database endpoint. The data that is returned from the database server can then be passed into other applications. The following information describes how to use App Connect to establish JDBC connections to database servers, and how to construct and pass SQL queries.

What to consider first

Before you use JDBC to connect to a database server, take note of the following considerations.

  • App Connect supports JDBC connections to the following databases: IBM Db2® Database editions that run on premises on either Linux®, UNIX, and Windows (LUW) or IBM i, IBM Db2 on Cloud, Oracle, and PostgreSQL.
  • Make sure that your database server is running and that a database connection exists for the database that you want to use with App Connect.

Connecting to JDBC

To create an integration flow that passes key data between your database server and other applications, you must connect App Connect to each app in the flow. You can add an account to connect to the database either from the App Connect Designer Connect > Applications and APIs page (previously the Catalog page), or when you add a JDBC action to a flow in the flow editor.

To connect, you need the following database connection details. If you're not the owner or administrator of your database instance, you can obtain this information from your database administrator.

Database type

Select the type of database to connect to.

  • IBM Db2 Linux, UNIX, or Windows (LUW) - client managed (the default)
  • IBM Db2 Linux, UNIX, or Windows (LUW) - IBM Cloud
  • IBM Db2 for i
  • Microsoft SQL Server
  • Oracle

    Supports Oracle Database (non-RAC) and Oracle Real Application Clusters (RAC).

  • PostgreSQL
Database name

Specify the name of the database, as set up in your system.

Database hostname

Specify the fully qualified hostname of the database server, or its IP address, for example, myserver.abc.com or 192.0.2.24.

Database port

Specify the port on which the database server listens.

Username

Specify a username that is authorized to access the database.

Password

Specify a password for the username.

Maximum pool size

Specify the maximum number of database connections that are allowed within a single connection pool. If unspecified, the value defaults to 0 (zero).

Additional parameters

Specify extra parameters to control how to connect to the database. Use name-value pairs (with a semicolon separator) in the following format:
name1=value1;name2=value2.

For example, autoCommit=true;maxActive=20

Mandatory extra parameters are needed for the following database types.
Database type Mandatory extra parameters

Microsoft SQL Server

By default, SQL Server Authentication is used as the authentication mode, and requires no additional parameters.

To connect through Windows Authentication, you must specify either of these additional parameters to indicate which NTLM version the endpoint supports.

  • AuthenticationMethod=ntlm2java
  • AuthenticationMethod=ntlmjava

Oracle

If you're using Oracle Real Application Clusters (RAC), the following extra parameter is needed to define the Oracle RAC service name.

ServiceName=oracleRACserviceName
Private network connection (optional)

Select the name of a private network agent that App Connect uses to connect to your private network. This list is populated with the names of private network agents that are created on the Private networks page. For more information, see Connecting to a private network.

The following example shows the completed connection fields for a JDBC connector.
Example of completed fields for connecting using JDBC

To connect to a JDBC endpoint from the App Connect Designer Applications and APIs page for the first time, expand JDBC, then click Connect. For more information, see Managing accounts.

Tip:

Before you use the account that is created in App Connect in a flow, rename the account to something meaningful that helps you to identify it. To rename the account on the Applications and APIs page, select the account, open its options menu (⋮), then click Rename Account.

General considerations for using JDBC in App Connect

The JDBC connector provides a single Custom query action that contains a single Custom SQL query multi-line field for specifying your custom SQL. Notice that the Custom SQL query field uses a database query notation SQL icon to indicate the type of input that's needed.

Custom SQL query field for the JDBC Custom query action
  • You can write your own custom SQL query, so you can use a single action to run a query against multiple schemas and database objects (such as tables) in the database.
  • The custom SQL can be any Data Query Language (DQL) or Data Manipulation Language (DML) statement. Make sure that the username and password you connect with has the necessary privileges for the type of queries that you want to run.
  • You can write complex queries that include join queries and subqueries.
  • An SQL injection of forbidden strings or malicious code might corrupt the database. To eliminate the possibility of SQL injections in your custom queries, a special type of mapping is provided to convert your query to a parameterized query (or prepared statement). In the parameterized query, placeholders are used for the parameters and the parameter values are supplied at execution time.
    • If you're typing the query, you can choose a mapping from a previous node as the value of a parameter value in a filter clause, either by entering the first few characters of the field name or by clicking the mapping icon Insert a mapping icon to open the Available mappings list. The mapping, which is typically inserted in this sample format Sample of a typical mapping, is instead converted to a mapping in parameterized form: Sample mapping from question mark.
    • When you type the query, you can also use a question mark ? to select a mapping for any value that you want to set for a parameter in the WHERE clause. After you type the comparison operator (for example, the equal sign =), add a space character and then type ? to open the list of available mappings and make a selection.

      Using a question mark to select a mapping

    • You can copy and paste a query into the Custom SQL query field. In the query that you copy, you can use a ? (with a leading space character) to denote a potential mapping, as shown in the following example.
      update contact set firstName= ? , lastName= ? , email= ? , title= ? , company= ? ,  contact_created_date= CURRENT DATE Where contactid= ?

      When you paste the query in the Custom SQL query field, all the ? characters (except for the last one) are highlighted within red boxes for your attention. The focus is on the last ? in the query, which is shown within a blue box, with an expanded list of mappings for you to choose from. You can then click the remaining ? in turn to replace them with mappings in parameterized form.

      Result of pasting a query

The JDBC Toolkit flow

The JDBC connector comprises a standard LoopBack® component, and a Java component that is implemented as an App Connect Enterprise Toolkit flow for the JDBC connector. In App Connect Designer, flows and integration runtimes that include a JDBC action incorporate this JDBC Toolkit flow.

  • When the first JDBC account is created in your App Connect Designer instance, a JDBC Toolkit flow is created internally and deployed as an integration runtime. If the deployment of this internal integration runtime fails for any reason, errors are displayed, and the logs can be collected and sent to IBM Support for investigation.
  • When you deploy an exported BAR file as an integration runtime, a JDBC Toolkit flow is packaged automatically with that BAR file for deployment, and runs in the integration runtime when the deployment completes.

    If you view the policy projects for the integration, you can also see a tile that is named gen.jdbcConnectorPolicies for the generated JDBC connector policy project.

Events and actions

JDBC events

These events are for changes in this application that trigger a flow to start completing the actions in the flow.

Note: Events are not available for changes in this application. You can trigger a flow in other ways, such as at a scheduled interval or at specific dates and times.

JDBC actions

Your flow completes these actions on this application.

Custom query