Selecting Multiple Rows Using a JDBC Inbound Endpoint

The JDBC 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="https://www.mulesoft.com/exchange#!/?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="https://www.mulesoft.com/exchange#!/?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="https://www.mulesoft.com/exchange#!/?types=connector" target="_blank" rel="" title="Cloud Connectors" >connector-</a>ref="jdbcConnector">
  <jdbc:transaction action="ALWAYS_BEGIN"/>
  <property key="receiveMessageInTransaction" value="true"/>
</jdbc:inbound-endpoint>

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.