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.