JDBC Transport

JDBC Transport

[ Introduction ] [ Features ] [ Transactions ] [ API Documentation and Notes ] [ Runnable Examples ] [ Performance Results ] [ Configuration Reference ] [ Why Polling Requires at Least Two Threads ]

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.

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.

Feature	Summary	Mule Community	Mule Enterprise
Inbound SELECT Queries	Retrieve records using the SQL SELECT statement configured on inbound endpoints.	x	x
Large Dataset Retrieval	Enables retrieval arbitrarily large datasets by consuming records in smaller batches.		x
Acknowledgment Statements	Supports ACK SQL statements that update the source or other table after a record is read.	x	x
Basic Insert/Update/Delete Statements	Individual SQL INSERT, UPDATE, and DELETE queries specified on outbound endpoints. One statement is executed at a time.	x	x
Batch Insert/Update/Delete Statements	Support for JDBC batch INSERT, UPDATE, and DELETE statements, so that many statements can be executed together.		x
Advanced JDBC-related Transformers	XML and CSV transformers for easily converting to and from datasets in these common formats.		x
Outbound SELECT Queries	Retrieve records using SQL SELECT statement configured on outbound endpoints. Supports synchronous queries with dynamic runtime parameters.		x
Outbound Stored Procedure Support - Basic	Ability to invoke stored procedures on outbound endpoints. Supports IN parameters but not OUT parameters.	x	x
Outbound Stored Procedure Support - Advanced	Same as Basic but includes both IN and OUT parameter support. OUT parameters can be simple data types or cursors		x
Unnamed Queries	Queries that can be invoked programmatically from within service components or other Java code. This is the most flexible option, but also requires writing code.	x	x
Flexible Data Source Configuration	Support for configuration of data sources through JNDI, XAPool, or Spring.	x	x
Transactions	Support for transactions via underlying Transaction Manager.	x	x

Important Note on Namespaces

When using Mule Enterprise JDBC features, note that you must import the EE namespace for the JDBC transport, and the XSD name is mule-jdbc-ee.xsd instead of mule-jdbc.xsd. For example, in Mule 2.1:

<mule 
    xmlns:jdbc="http://www.mulesource.com/schema/mule/jdbc/2.1"
    xsi:schemaLocation="http://www.mulesource.com/schema/mule/jdbc/2.1 http://www.mulesource.com/schema/mule/jdbc/2.1/mule-jdbc-ee.xsd">

As of Mule Enterprise 2.2, the path is slightly different (.org instead of .com, addition of /ee in path):

<mule 
    xmlns:jdbc="http://www.mulesource.org/schema/mule/ee/jdbc/2.2"
    xsi:schemaLocation="http://www.mulesource.org/schema/mule/ee/jdbc/2.2 http://www.mulesource.org/schema/mule/ee/jdbc/2.2/mule-jdbc-ee.xsd">

If you are not using Mule Enterprise JDBC features, you can use the Mule community release JDBC namespace:

<mule 
    xmlns:jdbc="http://www.mulesource.org/schema/mule/jdbc/2.2"
    xsi:schemaLocation="http://www.mulesource.org/schema/mule/jdbc/2.2 http://www.mulesource.org/schema/mule/jdbc/2.2/mule-jdbc.xsd">

Inbound SELECT Queries

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

Here is an example:

<spring:bean id="jdbcDataSource" class="org.enhydra.jdbc.standard.StandardDataSource" destroy-method="shutdown">
	<spring:property name="driverName" value="oracle.jdbc.driver.OracleDriver"/>
	<spring:property name="url" value="jdbc:oracle:thin:user/pass@host:1521:db"/>
</spring:bean>
...
<jdbc:connector name="jdbcConnector" pollingFrequency="10000" dataSource-ref="jdbcDataSource">
        <jdbc:query key="selectLoadedMules"
                    value="SELECT ID, MULE_NAME, RANCH, COLOR, WEIGHT, AGE from mule_source where ID between 0 and 20"/>
</jdbc:connector>
...
<model name="ExampleModel">
        <service name="InboundSelectExample">
            <inbound>
                <jdbc:inbound-endpoint queryKey="selectMules"/>
            </inbound>
            <outbound>
                <pass-through-router>
                    <vm:outbound-endpoint path="out"/>
                </pass-through-router>
            </outbound>
        </service>
</model>
...

In this example, the inboundSelectQuery 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), and this payload is sent to the VM endpoint shown above.

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

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.

Detailed usage of the large dataset retrieval feature is shown in JDBC Example #4.

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. See Example #4 and JDBC Transport Performance Benchmark Results for more details on how Mule can be used to transport millions of records an hour from one database table to another.

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.

Continuing the inbound SELECT query example above, the ACK statement would be configured as follows:

...
<jdbc:connector name="jdbcConnector" pollingFrequency="10000" dataSource-ref="jdbcDataSource">

    <jdbc:query key="selectLoadedMules"
          value="SELECT ID, MULE_NAME, RANCH, COLOR, WEIGHT, PROCESSED from mule_source where ID between 0 and 20"/>
    <jdbc:query key="selectMules.ack"
          value="update mule_source set PROCESSED='Y'  where ID = #[map-payload:ID] "/>        

</jdbc:connector>
...

Notice the 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 #[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)".

<jdbc:connector name="jdbcConnector" pollingFrequency="10000" dataSource-ref="jdbcDataSource">
    <jdbc:query key="outboundInsertStatement"
              value="INSERT INTO TEST (ID, TYPE, DATA, ACK) VALUES (#[map-payload:ID], 
                    #[map-payload:TYPE],#[map-payload:DATA], #[map-payload:ACK])"/>
</jdbc:connector>
...
<model name="ExampleModel">
    <service name="outboundInsertExample">
        <inbound>
            <inbound-endpoint address="vm://doInsert"/>
        </inbound>
        <outbound>
            <pass-through-router>
               <jdbc:outbound-endpoint queryKey="outboundInsertStatement"/>
            </pass-through-router>
        </outbound>
    </service>
</model>
...

Batch Insert/Update/Delete Statements

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.

Examples #1 and #4 demonstrate this feature. Batch statements are available in Mule Enterprise only.

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

Advanced JDBC-related Transformers

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:

Name	Class	Input	Output
XML -> Maps	org.mule.providers.jdbc.transformers.XMLToMapsTransformer	java.lang.String (XML)	java.util.List (List of Maps. Each Map corresponds to a "record" in the XML.)
Maps -> XML	org.mule.providers.jdbc.transformers.MapsToXMLTransformer	java.util.List (List of Maps. Each Map will be converted into a "record" in the XML)	java.lang.String (XML)

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

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
  <xs:element name="table">
    <xs:complexType>
      <xs:sequence>
        <xs:element ref="record"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="record">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" ref="field"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="field">
    <xs:complexType>
      <xs:simpleContent>
        <xs:extension base="xs:NMTOKEN">
          <xs:attribute name="name" use="required" type="xs:NCName"/>
          <xs:attribute name="type" use="required" type="xs:NCName"/>
        </xs:extension>
      </xs:simpleContent>
    </xs:complexType>
  </xs:element>
</xs:schema>

Here is an example of a valid XML instance:

<table>
    <record>
	<field name="id" type="java.math.BigDecimal">0</field>
	<field name="name" type="java.lang.String">hello</field>
    </record>
</table>

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

See Example #2, which uses the XML Transformer to convert results from a database query into an XML document.

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:

Name	Class	Input	Output
CSV -> Maps	org.mule.providers.jdbc.transformers.CSVToMapsTransformer	java.lang.String (CSV data)	java.util.List (List of Maps. Each Map corresponds to a "record" in the CSV)
Maps -> CSV	org.mule.providers.jdbc.transformers.MapsToCSVTransformer	java.util.List (List of Maps. Each Map will be converted into a "record" in the CSV)	java.lang.String (CSV data)

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

Property	Description
delimiter	The delimiter character used in the CSV file. Defaults to comma.
qualifier	The qualifier character used in the CSV file. Used to signify if text contains the delimiter character.Defaults to double quote.
ignoreFirstRecord	Instructs transformer to ignore the first record. Defaults to false.
mappingFile	Location of Mapping file. Required. Can either be physical file location or classpath resource name. The DTD format of the Mapping File can be found at: http://flatpack.sourceforge.net/flatpack.dtd. For examples of this format, see http://flatpack.sourceforge.net/documentation/index.html.

For an example, see Example #1, which uses the CSV Transformer to load data from a CSV file to a database table.

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:

   ...
   <jdbc:connector name="jdbcConnector" dataSource-ref="jdbcDataSource">
           <jdbc:query key="selectMules"
                       value="select * from mule_source where ID between 0 and #[map-payload:ID]"/>
   </jdbc:connector>
   ...
   <model name="ExampleModel">
       <service name="selectOutboundExample">
           <inbound>
               <inbound-endpoint address="vm://mapReceiver"/>
           </inbound>
           <outbound>
               <pass-through-router>
                  <jdbc:outbound-endpoint queryKey="selectMules"/>
               </pass-through-router>
           </outbound>
       </service>
   </model>
   
   

   In this scenario, if a Map is sent to the vm://mapReceiver endpoint containing this key/value pair:

   key=ID
   value=3
   

   The following query is executed:

   SELECT * FROM mule_source WHERE ID between 0 and 3
   

   See Examples #2 and #3 for further outbound SELECT query examples. Note that this feature is available in Mule Enterprise only.

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:

<jdbc:connector name="jdbcConnector" pollingFrequency="10000" dataSource-ref="jdbcDataSource">
    <jdbc:query key="storedProc" value="CALL addField()"/>
</jdbc:connector>

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:

<jdbc:query key="storedProc1" value="CALL addFieldWithParams(24)"/>
<jdbc:query key="storedProc2" value="CALL addFieldWithParams(#[map-payload:value])"/>

...
<pass-through-router>
    <jdbc:outbound-endpoint queryKey="storedProc1"/>
</pass-through-router>
...
<pass-through-router>
    <jdbc:outbound-endpoint address="jdbc://storedProc2?value=25"/>
</pass-through-router>
...

If you do not want to poll the database, you can write a stored procedure that uses HTTP to start a Mule service. The stored procedure can be called from an Oracle trigger. If you take this approach, make sure the Mule service is asynchronous. 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 service 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 services, 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:

Call #[<return parameter name>;<int | float | double | string | resultSet>;<out>] := 
<Oracle package name>.<stored procedure/function name>($PARAM1, $PARAM2, ...)

where $PARAMn is specified using the following syntax:

#[<parameter name>;<int | float | double | string | resultSet>;<in | out | inout>]

For example:

<jdbc:query key="SingleCursor"  value="call MULEPACK.TEST_CURSOR(#[mules;resultSet;out])"/>

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:

<jdbc:query key="itcCheckMsgProcessedOrNot"

value="call #[mules;int;out] := ITCPACK.CHECK_IF_MSG_IS_HANDLED_FNC(487568,#[mules1;string;out],
#[mules2;string;out],#[mules3;int;out],#[mules4;string;out])"/>

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

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:

<jdbc:query key="storedProc1" value="CALL myProc(#[a], #[b;int;inout], #[c;string;out])"/>

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.

See Example #3 for more examples.

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" service 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" service below, which uses both the "MapLookup" and "ResultSet2Maps" transformers to achieve this result.

<jdbc:connector name="jdbcConnector" pollingFrequency="1000" cursorTypeConstant="-10"
      dataSource-ref="jdbcDataSource">
    <jdbc:query key="SingleCursor"  value="call TEST_CURSOR(#[mules;resultSet;out])"/>
</jdbc:connector>
    
<custom-transformer class="org.mule.transformer.simple.MapLookup" name="MapLookup">
    <spring:property name="key" value="mules"/>    
</custom-transformer>
    
<jdbc:resultset-to-maps-transformer name="ResultSet2Maps"/>
        
    <model name="SPModel">       
        <service name="cursorOutputAsMaps">
            <inbound>
                <vm:inbound-endpoint path="returns.maps" responseTransformer-refs="ResultSet2Maps MapLookup"/>
            </inbound>
            <outbound>
                <pass-through-router>
                    <jdbc:outbound-endpoint queryKey="SingleCursor"/>
                </pass-through-router>
            </outbound>
        </service>
        
        <service name="cursorOutputAsResultSet">
            <inbound>
                <vm:inbound-endpoint  path="returns.resultset"  responseTransformer-refs="MapLookup"/>
            </inbound>
            <outbound>
                <pass-through-router>
                    <jdbc:outbound-endpoint queryKey="SingleCursor"/>
                </pass-through-router>
            </outbound>
        </service>       
    </model>    

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:

    <jdbc:query key="SingleCursor"  value="call #[mules;resultSet;out] := TEST_CURSOR2()"/>

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.

MuleMessage msg = eventContext.requestEvent("jdbc://SELECT * FROM TEST", 0);

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:

<jdbc:connector name="jdbcConnector" pollingFrequency="10000" dataSource-ref="myDataSource">
        ...
</jdbc:connector>

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.

<spring:bean id="pooledDS" class="org.enhydra.jdbc.standard.StandardDataSource" destroy-method="shutdown">
  <spring:property name="driverName" value="oracle.jdbc.driver.OracleDriver"/>
  <spring:property name="url" value="jdbc:oracle:thin:user/pass@host:1521:db"/>
</spring:bean>

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):

<bean id="c3p0DataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"> 
        <property name="driverClass"> 
            <value>oracle.jdbc.driver.OracleDriver</value> 
        </property> 
        <property name="jdbcUrl"> 
            <value>jdbc:oracle:thin:@MyUrl:MySID</value> 
        </property> 

        <property name="user"> 
            <value>USER</value> 
        </property> 
        <property name="password"> 
            <value>PWD</value> 
        </property> 

        <property name="properties"> 
            <props> 
                <prop key="c3p0.acquire_increment">5</prop> 
                <prop key=" c3p0.idle_test_period">100</prop> 
                <prop key="c3p0.max_size">100</prop> 
                <prop key="c3p0.max_statements">1</prop> 
                <prop key=" c3p0.min_size">10</prop> 
                <prop key="user">USER</prop> 
                <prop key="password">PWD</prop> 
            </props> 
        </property> 
    </bean>

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

<connector name="C3p0Connector" className="org.mule.providers.jdbc.JdbcConnector"> 
  <properties> 
    <container-property name="dataSource" reference="c3p0DataSource"/> 
      <map name="queries"> 
        <property name="test1" value="select * from Tablel"/> 
        <property name="test2" value="call testd(1)"/> 
      </map> 
  </properties> 
</connector> 

Or you could call it from your application as follows:

JdbcConnector jdbcConnector = (JdbcConnector) MuleServer.getMuleContext().getRegistry().lookupConnector("C3p0Connector"); 
ComboPooledDataSource datasource = (ComboPooledDataSource)jdbcConnector.getDataSource(); 
Connection connection = (Connection)datasource.getConnection(); 

String query = "select * from Table1"; //any query
Statement stat = connection.createStatement();
ResultSet rs = stat.executeQuery(query);

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

<spring:beans>
  <jee:jndi-lookup id="myDataSource" jndi-name="yourJndiName" environment-ref="yourJndiEnv" />
  <util:map id="jndiEnv">
    <spring:entry key="java.naming.factory.initial" value="yourJndiFactory" />
   </util:map>
</spring:beans>

If you create the Spring bean right in the Mule configuration file, you must include the following namespaces:

<mule xmlns="http://www.mulesource.org/schema/mule/core/2.2"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:spring="http://www.springframework.org/schema/beans"
       xmlns:jee="http://www.springframework.org/schema/jee"
       xmlns:util="http://www.springframework.org/schema/util"
       xmlns:jdbc="http://www.mulesource.org/schema/mule/jdbc/2.2"
       xsi:schemaLocation="
       http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
       http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-2.0.xsd
       http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-2.0.xsd
       http://www.mulesource.org/schema/mule/core/2.2 http://www.mulesource.org/schema/mule/core/2.2/mule.xsd
       http://www.mulesource.org/schema/mule/jdbc/2.2 http://www.mulesource.org/schema/mule/jdbc/2.2/mule-jdbc.xsd">

Transactions

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

API Documentation and Notes

Connector Properties

You can set the following properties on the JDBC connector.

Property	Description	Default	Required
dataSource-ref	Reference to the JDBC data source to use. When using XA transactions, an XADataSource object must be provided.		Yes
pollingFrequency	The delay in milliseconds that will be used during two subsequent polls to the database		No
resultSetHandler-ref	Reference to ResultSetHandler used to pass back query results. For more information about this object, see the examples.	org.apache.commons.dbutils. handlers.MapListHandler	No
queryRunner-ref	Reference to the QueryRunner to use when executing a Query. For more information about this object, see the examples.	org.apache.commons.dbutils. QueryRunner	no
transactionPerMessage	Whether each database record should be received in a separate transaction. If false, there will be a single transaction for the entire result set.	true	No

Extending the SQL Strategy

As of Mule 2.2, you can use the <sqlStatementStrategyFactory> child element to override the default SqlStatementStrategyFactory using the class or ref attribute. It determines the execution strategy based on the SQL provided. For example:

<jdbc:connector name="jdbcConnector4" dataSource-ref="testDS2">
  <jdbc:sqlStatementStrategyFactory class="org.mule.transport.jdbc.config.JdbcNamespaceHandlerTestCase$TestSqlStatementStrategyFactory"/>
</jdbc:connector>

<jdbc:connector name="jdbcConnector5" dataSource-ref="testDS2">
  <jdbc:sqlStatementStrategyFactory ref="sqlStatementStrategyFactory"/>
</jdbc:connector>

Notes on Configuring Queries

SQL queries are used by endpoints and should be configured on the connector using the "query" element:

<jdbc:query key="myQuery" value="select * from blah"/>

The queries can be parameterized using the #[...] pattern that follows the Expression Evaluation Framework.

Runnable Examples

For examples of using the Mule Enterprise version of the JDBC transport, see JDBC Transport Examples.

Performance Results

For information on performance testing on the Mule Enterprise version of the JDBC transport, see JDBC Transport Performance Benchmark Results.

Configuration Reference

For a list of the elements available in the JDBC transport schema, see JDBC Transport Configuration Reference.

Why Polling Requires at Least Two Threads

While polling happens from its own thread (created in AbstractPollingMessageReceiver.poll() ), the actual processing of the message receiver happens on the receiver's thread pool. Each poll() consumes one slot in the receiver thread pool. The poll() method in TransactedPollingMessageReceiver schedules work to the same receiver thread pool if results are not received in a transaction, which is the case here. Since the receiver thread pool's size is only 1 and the poll() method that's currently running already uses that slot, the wait policy kicks in and eventually fails adding the work item.

By specifying the receiveMessageInTransaction and receiveMessagesInXaTransaction endpoint properties and configuring the connector to not use a transaction per message you get the desired behavior