Selecting Multiple Rows Using a JDBC Inbound Endpoint


The transport allows you to poll for messages in a table. However, by default the JDBC inbound endpoint splits the record set into individual MuleMessages, and the outbound endpoint only receives a single row at a time. If you want to select multiple rows at a time and process the rows in a single transaction, you can take the following steps.

First, set the transactionPerMessage property on the <jdbc:connector> element to false. For example:

<jdbc:<a href="!/?types=connector" target="_blank" rel="" title="Cloud Connectors" >connector</a> name="jdbcConnector" dataSource-ref="jdbcDataSource1" 
pollingFrequency="1000" transactionPerMessage="false">
  <jdbc:query key="read" value="SELECT id, type, data FROM test WHERE type=1"/>
  <jdbc:query key="read.ack" value="UPDATE test SET type=2 
WHERE id=#[map-payload:id]"/>
</jdbc:<a href="!/?types=connector" target="_blank" rel="" title="Cloud Connectors" >connector</a>>

Next, on the inbound endpoint, configure a <jdbc:transaction> element and set the receiveMessageInTransaction property to true. For example:

<jdbc:inbound-endpoint queryKey="read" <a href="!/?types=connector" target="_blank" rel="" title="Cloud Connectors" >connector-</a>ref="jdbcConnector">
  <jdbc:transaction action="ALWAYS_BEGIN"/>
  <property key="receiveMessageInTransaction" value="true"/>

The outbound endpoint will now receive a list of rows from the table. You can save this list to a file, to another table, etc.

We'd love to hear your opinion on this post

7 Responses to “Selecting Multiple Rows Using a JDBC Inbound Endpoint”

  1. This post really helped me. Thanks!

  2. This post really helped me! thanks

  3. Puneet, thanks a lot for the solution. It helped me a lot.

  4. Thats cool, but I got an IllegalTransactionStateException error when applying this approach:

    Root Exception stack trace:
    org.mule.transaction.IllegalTransactionStateException: A transaction is available for this session, but transaction action is “Never”
    at org.mule.transaction.TransactionTemplate.execute(
    at org.mule.routing.outbound.AbstractOutboundRouter.dispatch(
    at org.mule.routing.outbound.FilteringOutboundRouter.route(
    at org.mule.routing.outbound.OutboundPassThroughRouter.route(
    at org.mule.routing.outbound.DefaultOutboundRouterCollection$1.doInTransaction(
    at org.mule.transaction.TransactionTemplate.execute(
    at org.mule.routing.outbound.DefaultOutboundRouterCollection.route(
    at org.mule.service.AbstractService.sendToOutboundRouter(
    at org.mule.model.seda.SedaService.doSend(
    at org.mule.service.AbstractService.sendEvent(
    at org.mule.DefaultMuleSession.sendEvent(
    at org.mule.routing.inbound.DefaultInboundRouterCollection.send(
    at org.mule.routing.inbound.DefaultInboundRouterCollection.route(
    at org.mule.transport.AbstractMessageReceiver$DefaultInternalMessageListener.onMessage(
    at org.mule.transport.AbstractMessageReceiver.routeMessage(
    at org.mule.transport.AbstractMessageReceiver.routeMessage(
    at org.mule.transport.jdbc.JdbcMessageReceiver.processMessage(
    at org.mule.transport.TransactedPollingMessageReceiver$1.doInTransaction(
    at org.mule.transaction.TransactionTemplate.execute(
    at org.mule.transport.TransactedPollingMessageReceiver.poll(

    my config is:

    Shall the outbound also be in transaction?


  5. Hi
    How can i create outbound endpoint for writing list of rows from the table into file .. could you give some explanation.

    Thanks and regards

  6. I get this error:

    org.mule.transaction.IllegalTransactionStateException: Can only bind “javax.sql.DataSource/java.sql.Connection” type resources

  7. Ah – the solution is to add a transaction manager as per {url}{url}
    and use {code}
    as per