Sometimes; you’ll want to capture data from an in-flight message and write it out to a JDBC compliant database. There are 3 standard ways you can do this : jdbc-data-capture-service, jdbc-raw-data-capture-service and jdbc-stored-procedure-producer. The first 2 are Service implementations which can write to any database table; the last is only used when executing a stored procedure.

JDBC Data Capture Service

jdbc-data-capture-service assumes that the payload of the message is text based (often XML). Data can be extracted from the message and stored in a database. There is the ability to optionally iterate over an XPath insert multiple rows into the database as part of the same service. The configuration for this service closely mirrors that of jdbc-data-query-service in that it will have a number of jdbc-statement-parameter parameters configured and an SQL Statement.

If we consider the following example document:

<document>
  <subject>Pangrams</subject>
  <sample>
    <data>The quick brown fox jumps over the lazy dog.</data>
  </sample>
  <sample>
    <data>Pack my box with a dozen liqour jugs.</data>
  </sample>
  <sample>
    <data>Quick zephyrs blow, vexing daft Jim.</data>
  </sample>
  <sample>
    <data>How quickly daft jumping zebras vex.</data>
  </sample>
</document>

Then our jdbc-data-capture-service to insert all the sample pangrams into a database table called PANGRAMS might be:

<jdbc-data-capture-service>
 <connection class="jdbc-connection">
  <driver-imp>com.mysql.jdbc.Driver</driver-imp>
  <connect-url>jdbc:mysql://localhost:3306/mydatabase</connect-url>
 </connection>
 <statement>INSERT INTO PANGRAMS (pangram) values (?);</statement>
 <parameter-applicator class="sequential-parameter-applicator"/>
 <iteration-xpath>/document/sample</iteration-xpath>
 <iterates>true</iterates>
 <jdbc-statement-parameter>
  <query-string>./data</query-string>
  <query-class>java.lang.String</query-class>
  <query-type>xpath</query-type>
 </jdbc-statement-parameter>
</jdbc-data-capture-service>
  • This results in 4 new rows in the database table PANGRAMS
  • We get the nodelist returned by /document/sample and for each node in the list we resolve the the xpath ./data to find the parameter to pass into the insert statement.
    • This results in 4 INSERT statements being executed.

JDBC Raw Data Capture Service

jdbc-raw-data-capture-service makes no assumptions about the payload itself; if you configure a jdbc-statement-parameter that is xpath based and the document is not XML; then results are undefined. It is designed for the use-cases where the message contains binary data, and you need to store the entire payload into a database table; optionally capturing some non-payload information as well. Again it will have a number of jdbc-statement-parameter parameters configured and an SQL Statement.

Taking the example document above, we want to insert the entire document as a new row in the database table PANGRAMS capturing the message-id as well.

<jdbc-raw-data-capture-service>
 <connection class="jdbc-connection">
  <driver-imp>com.mysql.jdbc.Driver</driver-imp>
  <connect-url>jdbc:mysql://localhost:3306/mydatabase</connect-url>
 </connection>
 <statement>INSERT INTO PANGRAMS (uniqueId, pangram) values (?, ?);</statement>
 <parameter-applicator class="sequential-parameter-applicator"/>
 <jdbc-statement-parameter>
  <query-class>java.lang.String</query-class>
  <query-type>id</query-type>
 </jdbc-statement-parameter>
 <jdbc-character-stream-statement-parameter/>
</jdbc-raw-data-capture-service>
  • This results in a single new row in the database table PANGRAMS containing the message id and the entire document as two columns.
Tags: cookbook