JDBCPersister is a JMS message consumer that retrieves the SQL statement from the message and sends the query to a JDBC data source for query or update. Once the query result is returned, it will put the result into the message body and removes the message from the internal XQueue. JDBCPersister supports all JDBC implentations via DBDriver. There are two operations, query and update, supported for now. For the query operation, the upstream node is supposed to pick up the query result. For the update operation, the upstream node is supposed to check the return code. It also supports flow control and XA. Fault tolerance with auto reconnections is built in.

In case of query with INSERT action, the persister will also check the extra SQL statement stored in the ExtraSQLField of the message. If the statement is defined, it will be executed only if the first statement is successful. The extra SQL statement can be used to get Auto-Incremental id or something else. The result of the extra statement will be put into the ExtraSQLField as a string for return. In case of error, "ERROR: " will be prepended to the result string.

Apart from the common properties, there are many implementation specific properties for JDBCPersister.

Property Name	Data Type	Requirement	Description	Examples
DBDriver	string	mandatory	full classname of JDBC driver	oracle.jdbc.driver.OracleDriver
SQLField	string	optional	field name for SQL query statement	(default: SQLField)
RCField	string	optional	field name to store return code (0 for success)	(default: ReturnCode)
ResultField	string	optional	field name to store the number of rows returned	(default: SQLResult)
ResultType	integer	optional	type of the result	4 for XML (default: 1)
TemplateFile	string	optional	filename containing a template to build the SQL statement
Template	string	optional	template to build the SQL statement
ExtraSQLField	string	optional	field name for the extra SQL statement

where ResultType specifies what type of format is used for the query result. 1 is for TEXT, 2 for BYTES, 4 for XML, 8 for JSON, 16 for List and 32 for ResultSet. In the first 4 cases, the query result will be stored in the message body. Otherwise, the request message is required to be ObjectMessage for List or Resultset.

In case of query, the query result in JSON will look like as follows:

{
  "Record": [{
     "column1": "value1",
     "column2": "value2",
     ...,
     "columnN": "valueN"
  },{
     "column1": "value1",
     "column2": "value2",
     ...,
     "columnN": "valueN"
  },{
  ...
  },{
     "column1": "value1",
     "column2": "value2",
     ...,
     "columnN": "valueN"
  }]
}

Here is an example of JDBCPersister:

{
  "Name": "pstr_db",
  "ClassName": "org.qbroker.persister.JDBCPersister",
  "URI": "jdbc:oracle:thin:@devdb1:5000:devdb",
  "DBDriver": "oracle.jdbc.driver.OracleDriver",
  "Username": "qbadm",
  "Password": "xxxx",
  "ResultType": "8",
  "Operation": "query",
  "LinkName": "query",
  "Tolerance": "0",
  "MaxRetry": "2",
  "DisplayMask": "6",
  "StringProperty"{ "
    "ReturnCode": "",
    "SQL": ""
  }
}