JDBC Connector

With the release of Anypoint Studio May 2014 with 3.5.0 Runtime, and its new Database Connector, the JDBC Connector has been deprecated. Applications that use the JDBC Connector will continue to work with the 3.5.0 Enterprise Runtime, but the connector is no longer available in the Studio palette to add to applications.

Access the documentation for the Database Connector to take advantage of the newly-designed functionality.

CE Schema

The JDBC Connector implements a transport channel to connect your Mule application to any JDBC-compatible database. You can implement the JDBC endpoint as an inbound endpoint with a one-way exchange pattern, or as an outbound endpoint configured for either a one-way or request-response exchange pattern.

Use the JDBC Endpoint to perform create, read, update, and delete operations (often referred to by the acronym CRUD) on database records. If you are using the EE version, it can also invoke a procedure stored in a database, such as creating new tables dynamically.

The JDBC connector supports multi-resource transactions. Multi-resource transactions are also supported by the Database Connector. Consider also the use of XA Transactions to handle this use case.

To Install this Connector

In Anypoint Studio, click the Exchange icon in the Studio taskbar.
Click Login in Anypoint Exchange.
Search for the connector and click Install.
Follow the prompts to install the connector.

When Studio has an update, a message displays in the lower right corner, which you can click to install the update.

Configuration

JDBC Endpoint configuration consists of three stages:

Decide where you want to place the JDBC endpoint within your Mule flow, then drag it from the Palette to the appropriate place in the sequence of processors that make up your Mule flow.

If you place the JDBC endpoint at the beginning of the flow, it acts as a polling inbound endpoint (such as, message source), triggering the flow by executing a query at intervals specified through the polling frequency field. This necessarily corresponds to a one-way exchange pattern for Inbound Endpoints.

JDBC Inbound Endpoints can only be set to a one-way exchange pattern. JDBC Outbound Endpoints can be set to either a one-way or request-response exchange pattern. For details about exchange patterns, see Anypoint Studio Essentials.

If you place the JDBC processor in the middle of or at the end of the flow, it serves as an outbound endpoint, which is to say, as a processor that executes SQL code. Different SQL statements return different data types. For example, queries return a list of maps, Store Procedures (available only for EE) return a map, while other update statements return nothing.

Configure the JDBC endpoint by providing values for the fields on the various tabs on the Properties pane, which you open by double-clicking on the JDBC icon on the Message Flow canvas.

The properties editor contains five tabs, as detailed below.

General Tab

Property Description

Property	Description
Display Name	Defaults to the generic endpoint name, which in this case is JDBC. Change the display name (which must be alpha-numeric) to reflect the endpoint’s specific role, such as `Order Entry Endpoint`
Exchange-Pattern	If set to one-way (the default) for either an inbound or outbound endpoint, the JDBC endpoint sends incoming data to the next processor in the flow. If set to request-response, which is only possible for outbound JDBC endpoints, the endpoint waits for a response before sending the data.
Query Key	Specifies which query to use when accessing the database.
SQL Statement	See SQL Statement for details.
Transaction Type	Select from a drop-down list the way to access database resources. The available options are No Transactions, XA Transactions, and JDBC Transactions.
Transaction Action	Select from a drop-down list how the transaction accesses the database.
Transaction Timeout	Enter a timeout in ms to wait for the Action to complete.
Interact with External	Check the box to allow Mule to interact with transactions begun outside of Mule. As an example, if an external transaction is active, then BEGIN_OR_JOIN joins in, and ALWAYS_BEGIN causes an exception to be thrown.

Display Name

Defaults to the generic endpoint name, which in this case is JDBC. Change the display name (which must be alpha-numeric) to reflect the endpoint’s specific role, such as Order Entry Endpoint

Exchange-Pattern

If set to one-way (the default) for either an inbound or outbound endpoint, the JDBC endpoint sends incoming data to the next processor in the flow. If set to request-response, which is only possible for outbound JDBC endpoints, the endpoint waits for a response before sending the data.

Query Key

Specifies which query to use when accessing the database.

SQL Statement

See SQL Statement for details.

Transaction Type

Select from a drop-down list the way to access database resources. The available options are No Transactions, XA Transactions, and JDBC Transactions.

Transaction Action

Select from a drop-down list how the transaction accesses the database.

Transaction Timeout

Enter a timeout in ms to wait for the Action to complete.

Interact with External

Check the box to allow Mule to interact with transactions begun outside of Mule. As an example, if an external transaction is active, then BEGIN_OR_JOIN joins in, and ALWAYS_BEGIN causes an exception to be thrown.

SQL Statement

This panel (below) replaces the Query panel on the General Tab only when the Mule Enterprise Edition runtime has been specified during flow configuration.

This panel facilitates the specification of SQL queries as well as stored procedures. To select a stored procedure, complete the following steps:

Click the green "plus" icon to the right of the text box labeled Key in the SQL Statement panel of the General tab on the properties editor of the JDBC Endpoint.
After the Query dialog box (below) pops up, type the key of the stored procedure you want to identify into the text box labeled Query Key.
In the large text box labeled Query, type the SQL statement you want to execute.
When you are satisfied with you entries in the fields, click Finish to commit them.

Advanced Tab

Property	Description
Address	Enter the address for this endpoint, such as jdbc://getTest?type=1.
Response Timeout	Specify how long the endpoint must wait for a response (in ms). The default is 1000 ms.
Encoding	Choose from a drop-down list the character set used for message data. (i.e. UTF-8).
Disable Transport Transformer	Check this box if you do not want to use the endpoint’s default response transport.
MIME Type	Select from the dropdown list one of the formats this endpoint supports.
Query Timeout	(Applies to outbound JDBC endpoints only.) Specify how long (in ms) the JDBC endpoint waits for the SQL statement to return a response.
Polling Frequency	(Applies to inbound JDBC endpoints only.) Specify how often to check for incoming messages. Default value is 1000 ms.

Property

Description

Address

Enter the address for this endpoint, such as jdbc://getTest?type=1.

Response Timeout

Specify how long the endpoint must wait for a response (in ms). The default is 1000 ms.

Encoding

Choose from a drop-down list the character set used for message data. (i.e. UTF-8).

Disable Transport Transformer

Check this box if you do not want to use the endpoint’s default response transport.

MIME Type

Select from the dropdown list one of the formats this endpoint supports.

Query Timeout

(Applies to outbound JDBC endpoints only.) Specify how long (in ms) the JDBC endpoint waits for the SQL statement to return a response.

Polling Frequency

(Applies to inbound JDBC endpoints only.) Specify how often to check for incoming messages. Default value is 1000 ms.

References Tab

Property	Description
Endpoint Reference	Use the drop-down list to select a previously configured global endpoint reference. If you have not created a global element for this type of endpoint, you can do so from this window by clicking Add. Click Edit to modify a previously created global element.
Connector Reference	Use the dropdown list to select a previously configured connector for this endpoint. If you have not created a connector for this type of endpoint, you can do so from this window by clicking Add. Click Edit to modify a previously created global element. The following lists the available types of global JDBC connectors: DB2 Data Source Derby Data Source MS SQL Data Source MySQL Data Source Oracle Data Source PostgreSQL Data Source
Request Transformer References	Enter a list of synchronous transformers to apply to the request before it is sent to the transport.
Response Transformer References	Enter a list of synchronous transformers to apply to the response before it is returned from the transport.

Property

Description

Endpoint Reference

Use the drop-down list to select a previously configured global endpoint reference. If you have not created a global element for this type of endpoint, you can do so from this window by clicking Add. Click Edit to modify a previously created global element.

Connector Reference

Use the dropdown list to select a previously configured connector for this endpoint. If you have not created a connector for this type of endpoint, you can do so from this window by clicking Add. Click Edit to modify a previously created global element. The following lists the available types of global JDBC connectors:

DB2 Data Source
Derby Data Source
MS SQL Data Source
MySQL Data Source
Oracle Data Source
PostgreSQL Data Source

Request Transformer References

Enter a list of synchronous transformers to apply to the request before it is sent to the transport.

Response Transformer References

Enter a list of synchronous transformers to apply to the response before it is returned from the transport.

This connector has one extra property that is currently only configurable at the global level in Studio, when you configure a Connector Reference:

Property Default Value Description

handleOutputResultSets

false

If set to true, returns a result set from calls to stored procedures.

To set this property to true, create or edit your Connector Reference, open the Advanced tab of the Global Element Configuration, and check the box next to Handle Output Result Sets.

Queries Tab

You can enter SQL queries for this endpoint, which consist of the following:

A key
A value
A value reference (optional)

Tips

DataMapper and iterative execution of SQL Statement: If you use a DataMapper to feed an ArrayList into a JDBC endpoint in your application, note that Mule executes your JDBC statement once for every item in the ArrayList that emerged from the DataMapper. This is expected behavior: when the payload is a List and the SQL statement contains parameters, Mule assumes the list contains the values to be inserted and employs the BatchUpdateSqlStatementStrategy. To more closely examine this behavior, access the following classes:
- com.mulesoft.mule.transport.jdbc.sqlstrategy.EESqlStatementStrategyFactory (creates a SqlStatementStrategy based on the type of SQL and the payload)
- com.mulesoft.mule.transport.jdbc.sqlstrategy.BatchUpdateSqlStatementStrategy