JDBC Transport Reference

Introduction

The JDBC Transport allows you to send and receive messages with a database using the JDBC protocol. Common usage includes retrieving, inserting, updating, and deleting database records, as well as invoking stored procedures (e.g., to create new tables dynamically).

Some features are available only with the Mule Enterprise version of the JDBC transport, which is available with version 1.6 and later of Mule Enterprise. These enterprise-only features are noted below.

Transport Info

Namespace and Syntax

XML namespace (CE version):

XML namespace (EE version):

XML Schema location (CE version):

XML Schema location (EE version):

Connector syntax (CE version):

Connector syntax (EE version):

Endpoint syntax:
Inbound endpoints:
You can define your endpoints 2 different ways:

1. Prefixed endpoint (valid for both CE and EE jdbc endpoints):

2. Non-prefixed URI:

Outbound endpoints:

1. Prefixed endpoint (valid for both CE and EE jdbc endpoints):

2. Non-prefixed URI:

Considerations

Using the JDBC transport is a good idea if you don't already have a database abstraction layer defined for your application. It saves you trouble of writing your own database client code and will be more portable if you decide to change databases in the future. If your application uses a database abstraction layer, then it is usually preferable to use that instead of the JDBC transport.

Features

The Mule Enterprise JDBC Transport provides key functionality, performance improvements, transformers, and examples not available in the Mule community release. The following table summarizes the feature differences.

Within this features section, items identified by theEnterprise Edition marker indicate features available only in the Enterprise Edition.

Inbound SELECT Queries

Inbound SELECT queries are queries that are executed periodically (according to the pollingFrequency set on the connector).

Here is an example:

In this example, the selectLoadedMules ❶ would be invoked every 10 seconds (pollingFrequency=10000 ms) ❷. Each record from the result set is converted into a Map (consisting of column/value pairs).

Inbound SELECT queries are limited because (1) generally, they cannot be called synchronously (unnamed queries are an exception), and (2) they do not support runtime parameters.

Large Dataset Retrieval

Enterprise Edition

Overview

Large dataset retrieval is a strategy for retrieving large datasets by fetching records in smaller, more manageable batches. Mule Enterprise provides the key components and transformers needed to implement a wide range of these strategies.

When To Use It

• When the dataset to be retrieved is large enough to overwhelm memory and connection resources.
• When preserving the order of messages is important.
• When resumable processing is desired (that is, retrieval of the dataset can pick up where it left off, even after service interruption).
• When load balancing the data retrieval among clustered Mule nodes.

How It Works

Large dataset retrieval does not use conventional inbound SELECT queries to retrieve data. Instead, it uses a Batch Manager component to compute ID ranges for the next batch of records to be retrieved. An outbound SELECT query uses this range to actually fetch the records. The Batch Manager also controls batch processing flow to make sure that it does not process the next batch until the previous batch has finished processing.

Here is an example:

First you set up the file which will hold the starting point id for the next batch of records ❶. Next you define your BatchManager and set the idStore, batchSize and starting point ❷. Then you define a 'noArgsWrapper' spring bean and set a reference to the batch manager ❸. ❹ is where you define the component which will be called after the inbound endpoint is triggered. Your outbound endpoints can use

#[map-payload:lowerId]

and

#[map-payload:upperId]

to reference a batch of database rows.

Important Limitations

Large dataset retrieval requires that:

1. The source data contains a unique, sequential numeric ID. Records should also be fetched in ascending order with respect to this ID.
2. There are no large gaps in these IDs (no larger than the configured batch size).

In Combination with Batch Inserts

Combining large dataset retrieval with batch inserts can support simple but powerful ETL use cases.

Acknowledgment (ACK) Statements

ACK statements are optional SQL statements that are paired with inbound SELECT queries. When an inbound SELECT query is invoked by Mule, the ACK statement is invoked for each record returned by the query. Typically, the ACK statement is an UPDATE, INSERT, or DELETE.

An ACK statement would be configured as follows:

Notice the required convention of appending an ".ack" extension to the query name. This convention lets Mule know which inbound SELECT query to pair with the ACK statement.

Also, note that the ACK statement supports parameters. These parameters are bound to any of the column values from the inbound SELECT query (such as #[map-payload:ID] in the case above).

ACK statements are useful when you want an inbound SELECT query to retrieve records from a source table no more than once. Be careful, however, when using ACK statements with larger result sets. As mentioned earlier, an ACK statement gets issued for each record retrieved, and this can be very resource-intensive for even a modest number of records per second (> 100).

Basic Insert/Update/Delete Statements

SQL INSERT, UPDATE, and DELETE statements are specified on outbound endpoints. These statements are typically configured with parameters, which are bound with values passed along to the outbound endpoint from an upstream component.

Basic statements execute just one statement at a time, as opposed to batch statements, which execute multiple statements at a time. Basic statements are appropriate for low-volume record processing (<20 records per second), while batch statements are appropriate for high-volume record processing (thousands of records per second).

For example, when a message with a java.util.Map payload is sent to a basic insert/update/delete endpoint, the parameters in the statement are bound with corresponding entries in the Map. In the configuration below, if the message contains a Map payload with {ID=1,TYPE=1,DATA=hello,ACK=0}, the following insert will be issued: "INSERT INTO TEST (ID,TYPE,DATA,ACK) values (1,1,'hello',0)".

Batch Insert/Update/Delete Statements

Enterprise Edition

As mentioned above, batch statements represent a significant performance improvement over their basic counterparts. Records can be inserted at a rate of thousands per second with this feature.

Usage of batch INSERT, UPDATE, and DELETE statements is the same as for basic statements, except the payload sent to the VM endpoint should be a List of Maps, instead of just a single Map.

Batch Callable Statements are also supported. Usage is identical to Batch Insert/Update/Delete.

Advanced JDBC-related Transformers

Enterprise Edition

Common integration use cases involve moving CSV and XML data from files to databases and back. This section describes the transformers that perform these actions. These transformers are available in Mule Enterprise only.

XML-JDBC Transformer

The XML Transformer converts between XML and JDBC-format Maps. The JDBC-format Maps can be used by JDBC outbound endpoints (for select, insert, update, or delete operations).

Transformer Details:

Also, the XML message payload (passed in or out as a String) must adhere to a particular schema format:

Here is an example of a valid XML instance:

The transformer converts each "record" element to a Map of column/value pairs using "fields". The collection of Maps is returned in a List.

The following will return any processed rows in xml format when you go to 'http://localhost:8080/first20' in your browser:

CSV-JDBC Transformer

The CSV Transformer converts between CSV data and JDBC-format Maps. The JDBC-format Maps can be used by JDBC outbound endpoints (for select, insert, update, or delete operations).

Transformer Details:

The following table summarizes the properties that can be set on this transformer:

This configuration loads a csv file in the 'mule_source' table of a database

Outbound SELECT Queries

An inbound SELECT query is invoked on an inbound endpoint according to a specified polling frequency. A major improvement to the inbound SELECT query is the outbound SELECT query, which can be invoked on an outbound endpoint. As a result, the outbound SELECT query can do many things that the inbound SELECT query cannot, such as:

1. Support synchronous invocation of queries. For example, you can implement the classic use case of a web page that serves content from a database using an HTTP inbound endpoint and an outbound SELECT query endpoint.

2. Allows parameters so that values can be bound to the query at runtime. This requires that the message contain a Map payload containing key names that match the parameter names. For example, the following configuration could be used to retrieve an outbound SELECT query:

In this scenario, if hit the 'http://localhost:8080/getMules?max=3' url, then the following query is executed:

The database rows are transformed into xml which you will see in your browser.

Outbound Stored Procedure Support - Basic

Stored procedures are supported on outbound endpoints in Mule. Like any other query, stored procedure queries can be listed in the queries map. Following is an example of how stored procedure queries could be defined:

To denote that we are going to execute a stored procedure and not a simple SQL query, we must start off the query by the text CALL followed by the name of the stored procedure.

Parameters to stored procedures can be forwarded by either passing static parameters in the configuration or using the same syntax as for SQL queries (see "Passing in Parameters" below). For example:

If you do not want to poll the database, you can write a stored procedure that uses HTTP to start a Mule flow. The stored procedure can be called from an Oracle trigger. If you take this approach, make sure the exchange pattern is 'one-way'. Otherwise, the trigger/transaction won't commit until the HTTP post returns.

Note that stored procedures are only supported on outbound endpoints. If you want to set up a flow that calls a stored procedure at a regular interval, you can define a Quartz inbound endpoint and then define the stored procedure call in the outbound endpoint. For information on using Quartz to trigger flows, see the following blog post.

Passing in Parameters

To pass in parameter values and get returned values to/from stored procedures or stored functions in Oracle, you declare the parameter name, direction, and type in the JDBC query key/value pairs on JDBC connectors using the following syntax:

where $PARAMn is specified using the following syntax:

For example:

This SQL statement calls a stored procedure TEST_CURSOR in the package of MULEPACK, specifying an out parameter whose name is "mules" of type java.sql.ResultSet.

Here is another example:

This SQL statement calls a stored function CHECK_IF_MSG_IS_HANDLED_FNC in the package of ITCPACK, assigning a return value of integer to the parameter whose name is "mules" while specifying other parameters, e.g., parameter "mules2" is a out string parameter.

Stored procedures/functions can only be called on JDBC outbound endpoints. Once the values are returned from the database, they are put in a java.util.HashMap with key/value pairs. The keys are the parameter names, e.g., "mules2", while the values are the Java data values (Integer, String, etc.). This hash map is the payload of MuleMessage either returned to the caller or sent to the next endpoint depending on the Mule configuration.

Outbound Stored Procedure Support - Advanced

Enterprise Edition

Mule Enterprise provides advanced stored procedure support for outbound endpoints beyond what is available in the Mule community release. This section describes the advanced support.

OUT Parameters

In Mule Enterprise, you can execute your stored procedures with out and inout scalar parameters. The syntax for such parameters is:

You must specify the type of each output parameter (OUT, INOUT) and its data type (int, string, etc.). The result of such stored procedures is a map containing (out parameter name, value) entries.

Oracle Cursor Support

For Oracle databases only, an OUT parameter can return a cursor. The following example shows how this works.

If you want to handle the cursor as a java.sql.ResultSet, see the "cursorOutputAsResultSet" flow below, which uses the "MapLookup" transformer to return the ResultSet.

If you want to handle the cursor by fetching the java.sql.ResultSet to a collection of Map objects, see the "cursorOutputAsMaps" flow below, which uses both the "MapLookup" and "ResultSet2Maps" transformers to achieve this result.

In the above example, note that it is also possible to call a function that returns a cursor ref. For example, if TEST_CURSOR2() returns a cursor ref, the following statement could be used to get that cursor as a ResultSet:

Important note on transactions: When calling stored procedures or functions that return cursors (ResultSet), it is recommended that you process the ResultSet within a transaction.

Unnamed Queries

SQL statements can also be executed without configuring queries in the Mule configuration file. For a given endpoint, the query to execute can be specified as the address of the URI.

Flexible Data Source Configuration

You can use any JDBC data source library with the JDBC Connector. The "myDataSource" reference below refers to a DataSource bean created in Spring:

You can also create a JDBC connection pool so that you don't create a new connection to the database for each message. You can easily create a pooled data source in Spring using xapool. The following example shows how to create the Spring bean right in the Mule configuration file.

If you need more control over the configuration of the pool, you can use the standard JDBC classes. For example, you could create the following bean in the Spring configuration file (you could also create them in the Mule configuration file by prefixing everything with the Spring namespace):

You could then reference the c3p0DataSource bean in your Mule configuration:

Or you could call it from your application as follows:

To retrieve the data source from a JNDI repository, you would configure the connector as follows:

Transactions

Transactions are supported on JDBC endpoints. See Transaction Management for details.

Usage

Copy your JDBC client jar to the <MULE_HOME>/lib/user directory of your installation.

If you want to include the JDBC transport in your configuration, these are the namespaces you need to define:

For the enterprise version of the JDBC transport:

Then you need to define a connector:

Finally, you define an inbound or outbound endpoint.

• Use an inbound endpoint if you want changes to your database to trigger a Mule flow
• Use an outbound endpoint to make changes to the database data or to return database data to an inbound endpoint, such as using an http endpoint to display database data.

Endpoints look like this:
Inbound endpoints:

Outbound endpoints:

If you are using Mule Enterprise edition, then you must use the EE version of the JDBC transport. Therefore, if you are migrating from CE to EE, you will need to update the namespace and schemaLocation declarations to the EE versions as described above.

Exchange patterns

one-way and request-response exchange patterns are supported. If an exchange pattern is not defined, 'one-way' is the default.

Polling transport

The inbound endpoint for JDBC transport uses polling to look for new data. The default is to check every second, but it can be changed via the 'pollingFrequency' attribute on the connector.

Features supported by this module: Transactions, reconnect, expressions, etc.

Most standard transport features are supported for the jdbc transport: transactions, retry, expressions, etc. Streaming is not supported for the JDBC transport.

Example Configurations

The following example demonstrates how you would write rows in a database to their own files.

The database authentication information is stored in a properties file named 'db.properties' ❶. For a MySQL database, the file would look similar to this:
database.driver=com.mysql.jdbc.Driver
database.connection=jdbc:mysql://localhost/test?user=<user>&password=<password>

The values in the property file are used in ❷ and ❸ to configure the data source bean. The jdbc connector references the data source ❹ and defines a couple of queries (❺ and ❻) which the inbound endpoint will use. The 'read' query checks the database for rows which have a 'type' column set to 1. The 'read.ack' query is automatically run for every new record found and sets the 'type' column to 2 so it will not be picked up again by the indound endpoint. A file connector is defined at ❼ to write each row found to a file with a date stamp name. Next, the flow is defined which calls the jdbc 'read' query on the inbound endpoint ❽. New database rows are then processed by the object-to-string transformer ❾ and finally written to the '/tmp/rows' directory ❿.

This example shows how to display database rows in a browser:

This example requires Mule Enterprise Edition to run. ❶ defines a select database query using the 'max' parameter which will be passed in the requesting url. We define some transformers at ❷ and ❸ to turn the database row into xml and set the correct Content-type for the browser to display it correctly. ❹ declares the http inbound endpoint with a url of 'http://localhost:8080/rows'. Since we are using an inbound parameter in the select query, we also need to include the 'max' parameter on the requesting url, i.e. http://localhost:8080/rows?max=5. ❺ is where the jdbc outbound endpoint will call the 'selectRows' query after the http endpoint is triggered.

Configuration Reference

Community edition:

Connector

Attributes of <connector...>

Child Elements of <connector...>

Inbound endpoint Receives or fetches data from a database. You can reference SQL select statements or call stored procedures on inbound endpoints. Statements on the inbound endpoint get invoked periodically according to the pollingInterval. Statements that contain an insert, update, or delete are not allowed.

Attributes of <inbound-endpoint...>

Child Elements of <inbound-endpoint...>

Outbound endpoint You can reference any SQL statement or call a stored procedure on outbound endpoints. Statements on the outbound endpoint get invoked synchronously. SQL select statements or stored procedures may return output that is handled by the ResultSetHandler and then attached to the message as the payload.

Attributes of <outbound-endpoint...>

Child Elements of <outbound-endpoint...>

Enterprise edition:

Connector

Attributes of <connector...>

Child Elements of <connector...>

Inbound endpoint

Child Elements of <inbound-endpoint...>

Outbound endpoint

Child Elements of <outbound-endpoint...>

NOTE: The XSLT has been modified so that you can set the top level wiki header. In this example, the topstylelevel is set to 3, enabling you to generate element docs from here without rupturing the logic of your other header styles.

Transformers

The following transformers can be found in the enterprise version of the jdbc transport:

Maps to xml transformer Converts a List of Map objects to XML. The Map List is the same as what you get from using the default ResultSetHandler. The XML schema format is provided in the documentation.

Child Elements of <maps-to-xml-transformer...>

Xml to maps transformer Converts XML to a List of Map objects. The Map List is the same as what you get from using the default ResultSetHandler. The XML schema format is provided in the documentation.

Child Elements of <xml-to-maps-transformer...>

Maps to csv transformer Converts a List of Map objects to a CSV file. The Map List is the same as what you get from using the default ResultSetHandler.

Attributes of <maps-to-csv-transformer...>

Child Elements of <maps-to-csv-transformer...>

Csv to maps transformer Converts a CSV file to a List of Map objects. The Map List is the same as what you get from using the default ResultSetHandler.

Attributes of <csv-to-maps-transformer...>

Child Elements of <csv-to-maps-transformer...>

Resultset to maps transformer Transforms a java.sql.ResultSet to a List of Map objects just like the default ResultSetHandler. Useful with Oracle stored procedures that return cursors (ResultSets).

Child Elements of <resultset-to-maps-transformer...>

Filters

Others

NOTE: Transports may have associated transformers, filters, etc. Provide listings for all of these as well.

Schema

You can view the full schema for the JDBC transport here . The enterprise version of the schema docs is not available.

Javadoc API Reference

Refer to the EE distrobution for the enterprise version of the jdbc transport javadocs.

Maven

The JDBC transport is implemented by the mule-transport-jdbc module. You can find the source for the jdbc transport under transports/jdbc.

If you are using maven to build your application, use the following dependency snippet to include the JDBC transport in your project:
Community version:

Enterprise version:

Read more about Mule-Maven dependencies.

Best Practices

• Put your database connection and credential information in a separate properties file. This allows your port you configuration file to different environments. See Example Configurations or the JDBC Transport Example for an example on how this is done

Data Source Configuration

Data source configuration has become much simpler. Previously, a data source had to be configured with Spring:

Now this is greatly simplified:

Data sources

The following elements can be used with all the database-specific data sources listed below:

Derby

Derby data sources are created as embedded data sources. So the definition of user and password is not required.

Tip Use the jdbc:derby-data-source configuration element to configure Derby. If you use a regular bean, you may receive errors when undeploying or redeploying the application.

Example:

The following attributes are available on the derby-data-source element:

MySQL

Example:

The following attributes are available on the mysql-data-source element:

Oracle

Example:

The following attributes are available on the oracle-data-source element:

Postgresql

Example:

The following attributes are available on the mysql-data-source element: