Contact Us 1-800-596-4880
  1. Homepage
  2. Mule Runtime (3.3)

  3. Database (JDBC) Endpoint Reference (3.3)

Database (JDBC) Endpoint Reference

Overview

The Database Endpoint, which is sometimes referred to as the "JDBC Endpoint," implements a transport channel to connect your Mule application to any JDBC-compatible database. You can implement the Database 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 Database 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.

Configuration

Database Endpoint configuration consists of three stages:

  1. Decide where you want to place the Database 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.

    1. If you place the Database endpoint at the beginning of the flow, it will act as a polling inbound endpoint (i.e., 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.

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

    2. If you place the JDBC building block in the middle of or at the end of the flow, it will serve 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.

  2. Configure the Database 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 Database Endpoint Properties pane contains five tabs, as detailed below:

General Tab

DatabaseEndpointGen
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, i.e. Order Entry Endpoint

Exchange-Pattern

If set to one-way (the default) for either an inbound or outbound endpoint, the Database endpoint sends incoming data to the next building block in the flow. If set to request-response, which is only possible for outbound Database 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 database resources will be accessed. The available options are No Transactions, XA Transactions, and JDBC Transactions.

Transaction Action

Select from a drop-down list how the transaction will access 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 will join in, and ALWAYS_BEGIN will cause an exceprion 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.

JDBC_SQLStatement

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

  1. 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 pane of the Database (JDBC) Endpoint.

  2. 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.

  3. In the large text box labeled Query, type the SQL statement you want to execute.

  4. (Optional) In the text box marked Query Reference, type ???

  5. When you are satisfied with you entries in the fields, click Finish to commit them.

    JDBC_Query

Advanced Tab

DatabaseEndpointAdv
Property Description

Address

Enter the address for this endpoint, i.e. 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 Database endpoints only). Specify how long (in ms) the Database endpoint waits for the SQL statement to return a response.

Polling Frequency

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

References Tab

DatabaseEndpointRef
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.

Request Transformer References

Enter a list of synchronous transformers that will be applied to the request before it is sent to the transport.

Response Transformer References

Enter a list of synchronous transformers that will be applied to the response before it is returned from the transport.

Queries Tab

DatabaseEndpointQue

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

  • a key

  • a value

  • a value reference (optional)

Documentation Tab

The Documentation tab lets you add optional descriptive documentation for an endpoint. Every endpoint component has a Documentation tab and optional Description field.

DatabaseEndpointDoc
Field Description

Documentation

Enter a detailed description of this Database endpoint for display in a yellow help balloon that pops up when you hover your mouse over the endpoint icon.

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 Database endpoint using an XML editor, consult the JDBC Transport Reference