org.qbroker.jms

Class JDBCMessenger

java.lang.Object

org.qbroker.net.DBConnector

org.qbroker.jms.JDBCMessenger

All Implemented Interfaces:

Connector

public class JDBCMessenger
extends DBConnector

JDBCMessenger connects to a database specified by URI and initializes the operation of query and update for SQL statement with JMS Messages.

There are four methods, select(), query(), update() and list() for the SQL statements. The method of select() executes a fixed query statement. In case of repeated select operation, DependencyGroup has to be defined. It will be used to determine how often to rerun the select query. If KeyColumn is defined, the SQL statement has to contain a parameter as the start point to filter out old rows. The rest of methods extract the SQL statements from the incoming messages. It supports setXXX() with %%TYPE:property%% as the placeholder, where TYPE is one of the followings: INT, LONG, FLOAT, DOUBLE, STRING, DATE, TIME and TIMESTAMP. If there is no TYPE defined, the default type of STRING will be assumed. The method of query() is used for synchronous requests, whereas update() is for asynchronous persisting request. Therefore, if you what to update Database automatically, please use update(). It will keep retry upon database failures until the message expires. The method of list() has no SQL statement. It is to list all tables in a database with dynamic filter support or all columns in a specific table.

In case of BLOB or LONGVARBINARY data is queried with a BytesMessage, the method of query will only return the first row with the last BLOB or LONGVARBINARY data stored in the message body. The rest of the data will formatted into the SQLField if there is any.

In case of query with INSERT action, the method of query() 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-Increment 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 prefixed to the result string.

In case of select, it supports the standard formatter on each of the selected messages.

For charSet and encoding, you can overwrite the default encoding by starting JVM with "-Dfile.encoding=UTF8 your_class".

Author:

yannanlu@yahoo.com

Field Summary

Fields inherited from class org.qbroker.net.DBConnector

resultType, sqlExecTimeout, uri

Constructor Summary

Constructors

Constructor and Description
JDBCMessenger(java.util.Map props)

Method Summary

Modifier and Type	Method and Description
void	acknowledge(long[] state) checks in the prepared statement and removes the asset for the result set
void	close()
void	destroyObject(java.lang.Object obj)
java.lang.String	getOperation()
protected static int	getResult(int type, java.sql.ResultSet rset, java.lang.String key, byte[] buffer, javax.jms.BytesMessage msg) It retrieves BLOB data or LONGVARBINARY data as well as other data from the resultset and puts them into the BytesMessage.
void	list(XQueue xq) It listens to an XQueue for incoming JMS messages and extracts a pattern for table names or a specific table name, and content filters from each message.
void	loadReference() loads the state parameters from the reference file
void	query(XQueue xq) It listens to an XQueue for incoming JMS messages and extracts the SQL statement and data from each message.
java.lang.String	reconnect() reconnects to the DB and returns null on success or err msg otherwise
protected void	saveReference() flushes the current reference info to a given file for tracking the browser state if the ReferenceFile is defined.
void	select(XQueue xq) It executes the predfined SQL statement and puts the result to the body of a new message.
protected int	setParameters(java.lang.String sqlStr, java.sql.PreparedStatement ps, java.lang.String msgStr, javax.jms.Message msg) It sets parameters with the data from the message
void	update(XQueue xq) It listens to the XQueue for incoming JMS messages and extracts the SQL statement and data from each message.

Methods inherited from class org.qbroker.net.DBConnector

commit, copyStream, copyStream, executeQuery, getURI, isConnected, list, list, list, main, prepareStatement, rollback, select, setAutoCommit

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

JDBCMessenger

public JDBCMessenger(java.util.Map props)

Method Detail

select

public void select(XQueue xq)
            throws java.sql.SQLException,
                   javax.jms.JMSException

It executes the predfined SQL statement and puts the result to the body of a new message. The new message will be added to the XQueue as the output. The result may be a delimited text stored in a TextMessage, a delimited bytes string stored in a BytesMessage, a List with String[] as the member stored in an ObjectMessage, or a ResultSet stored in an ObjectMessage together with the ack method enabled.

Throws:

java.sql.SQLException

javax.jms.JMSException

loadReference

public void loadReference()
                   throws java.io.IOException

loads the state parameters from the reference file

Throws:

java.io.IOException

saveReference

protected void saveReference()
                      throws java.io.IOException

flushes the current reference info to a given file for tracking the browser state if the ReferenceFile is defined.

Throws:

java.io.IOException

query

public void query(XQueue xq)
           throws java.sql.SQLException,
                  TimeoutException

It listens to an XQueue for incoming JMS messages and extracts the SQL statement and data from each message. After compiling and setting data on the statement, it executes the SQL statement to query the database. Based on the result type, the query result can be formatted into the customized content. Or the ResultSet of the query can be put into the message before it is removed from the XQueue. The requester will be able to get the result content or ResultSet out of the message. In case of non-select SQL statement, it will execute the statement and store the return code into the RCField.

Since the query operation relies on the request, this method will intercept the request and processes it. The incoming message is required to be writeable. The method will set a String property of the message specified by the RCField with the return code, indicating the status of the process to fulfill the request. The requester is required to check the value of the property once it gets the message back. If the return code is 0, the query is successful. Otherwise, the message body will not contain the content out of query operation.

If the SQL statement is an INSERT action and its execution is successful, this method will also check the extra SQL statement stored in the ExtraSQLField of the message. If the statement is defined, it will be executed immediately. Its result will be put into the same field as the return. The requester is supposed to check the content in ExtraSQLField. This extra SQL can be used to get Auto-Increment id or something else.

If the MaxIdleTime is set to none-zero, it will monitor the idle time and will throw TimeoutException once the idle time exceeds MaxIdleTime. it is up to the caller to handle this exception.

Throws:

java.sql.SQLException

TimeoutException

update

public void update(XQueue xq)
            throws java.sql.SQLException,
                   TimeoutException,
                   javax.jms.JMSException

It listens to the XQueue for incoming JMS messages and extracts the SQL statement and data from each message. After the compiling and setting data on the statement, it executes the SQL statement for the database operation of update, insert or delete.

If the MaxIdleTime is set to none-zero, it will monitor the idle time and will throw TimeoutException once the idle time exceeds MaxIdleTime. It is up to the caller to handle this exception.

The original message will not be modified. If the XQueue has enabled the EXTERNAL_XA bit, it will also acknowledge the messages. It is NOT MT-Safe due to the cached SQL statements.

Throws:

java.sql.SQLException

TimeoutException

javax.jms.JMSException

list

public void list(XQueue xq)
          throws java.sql.SQLException,
                 TimeoutException

It listens to an XQueue for incoming JMS messages and extracts a pattern for table names or a specific table name, and content filters from each message. In case of a pattern, it gets a list of tables in the database and queries the total count of records in each table hit by the filters. If the request has no wildcards, it will be treated as a specific table name. The method will get info for each of the columns. Based on the result type, the query result for tables will be formatted into the customized content with 4 columns, TABLE_NAME, TABLE_TYPE, COUNT, and REMARK. The result will be put into the message before it is removed from the XQueue. The requester will be able to get the result content out of the message.

Since the list operation relies on the request, this method will intercept the request and processes it. The incoming message is required to be writeable. The method will set a String property of the message specified by the RCField with the return code, indicating the status of the process to fulfill the request. The requester is required to check the value of the property once it gets the message back. If the return code is 0, the query is successful. Otherwise, the message body will not contain the content out of query operation.

If the MaxIdleTime is set to none-zero, it will monitor the idle time and will throw TimeoutException once the idle time exceeds MaxIdleTime. it is up to the caller to handle this exception.

Throws:

java.sql.SQLException

TimeoutException

setParameters

protected int setParameters(java.lang.String sqlStr,
                            java.sql.PreparedStatement ps,
                            java.lang.String msgStr,
                            javax.jms.Message msg)
                     throws java.sql.SQLException,
                            javax.jms.JMSException

It sets parameters with the data from the message

Throws:

java.sql.SQLException

javax.jms.JMSException

getResult

protected static int getResult(int type,
                               java.sql.ResultSet rset,
                               java.lang.String key,
                               byte[] buffer,
                               javax.jms.BytesMessage msg)
                        throws java.sql.SQLException,
                               javax.jms.JMSException

It retrieves BLOB data or LONGVARBINARY data as well as other data from the resultset and puts them into the BytesMessage. Upon success, it returns the number of records retrieved. Otherwise, it returns -1 indicating there is no BLOB data or LONGVARBINARY data found.

The BLOB data or the LONGVARBINARY data in the last position will be put into the message body. The rest will be formatted into the field specified by the given key.

Throws:

java.sql.SQLException

javax.jms.JMSException

reconnect

public java.lang.String reconnect()

reconnects to the DB and returns null on success or err msg otherwise

Specified by:

reconnect in interface Connector

Overrides:

reconnect in class DBConnector

acknowledge

public void acknowledge(long[] state)
                 throws javax.jms.JMSException

checks in the prepared statement and removes the asset for the result set

Throws:

javax.jms.JMSException

destroyObject

public void destroyObject(java.lang.Object obj)

close

public void close()

Specified by:

close in interface Connector

Overrides:

close in class DBConnector

getOperation

public java.lang.String getOperation()