JDBC Connector
Enterprise, CloudHub
|
While the JDBC transport continues to be supported, as of Anypoint Studio May 2014 with 3.5.0 Runtime, the JDBC connector is deprecated, and the Database connector takes on JDBC connection capabilities. Applications that use the JDBC connector and underlying JDBC transport continue to work, but the JDBC 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. |
The JDBC Endpoint 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. |
Installation
You can install a connector in Anypoint Studio using the instructions in To Install a Connector from Anypoint Exchange.
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 building blocks 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 building block 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 |
|---|---|
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 |
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 building block 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. |
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:
|
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:
To set this property to |
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
-
See Also
For details on setting the properties for a JDBC endpoint using an XML editor, consult the JDBC Transport Reference.