com.hp.hpl.mesa.rdf.jena.rdb
Class SQLCache

java.lang.Object
  |
  +--com.hp.hpl.mesa.rdf.jena.rdb.SQLCache

public class SQLCache

extends java.lang.Object

Stores a set of sql statements loaded from a resource file. Caches prepared versions of the statements for a given db connection.

The resource file is located on the classpath and has the format:

 # comment at start of line
 operationName1
 sql code line 1
 ...
 sql code last line

 operationName2
 ...
 

where the blank lines delimit one sql block from the next.

The sql code is typically a single SQL statement but some operations, specifically database initialization and cleanup may require a variable number of statments. To cater for this terminate each statement in those groups with the string ";;". Note that a single ";" is not used because these compound statements are often stored procedure definitions which end to have ";" line terminators!

Version:

$Revision: 1.4 $ on $Date: 2001/12/05 17:56:50 $

Author:

Dave Reynolds

Constructor Summary

SQLCache(java.lang.String sqlFile, java.util.Properties defaultOps, IDBConnection connection, java.lang.String idType)
Constructor.

Method Summary

void	close() Close all prepared statements
void	flushPreparedStatementCache() Flush the cache of all currently prepared statements
boolean	getCachePreparedStatements() Return true if cache of pre-prepared statements is enabled
java.sql.Connection	getConnection() Return the associated jdbc connection
java.sql.PreparedStatement	getPreparedSQLStatement(java.lang.String opname) Return a prepared SQL statement corresponding to the named operation.
java.sql.PreparedStatement	getPreparedSQLStatement(java.lang.String opname, java.lang.String attr) Variant on getPreparedSQLStatement which access the attribte variant correspond to the given attribute suffix.
java.lang.String	getSQLStatement(java.lang.String opname) Return the raw SQL statement corresponding to the named operation.
java.lang.String	getSQLStatement(java.lang.String opname, java.lang.String attr) Return the raw SQL statement corresponding to the named operation.
java.util.Collection	getSQLStatementGroup(java.lang.String opname) Return a set of raw SQL statements corresponding to the named operation.
static java.util.Properties	loadSQLFile(java.lang.String sqlFile, java.util.Properties defaultOps, java.lang.String idType) Load in a defined set of sql statements - see class comment for format.
static java.io.BufferedReader	openResourceFile(java.lang.String filename) Open a resource file for reading.
void	returnPreparedSQLStatement(java.sql.PreparedStatement ps, java.lang.String opname) Return a prepared statement to the statement pool for reuse by another caller.
void	runSQLGroup(java.lang.String opname) Run a group of sql statements - normally used for db formating and clean up.
ResultSetIterator	runSQLQuery(java.lang.String opname, java.lang.Object[] args) Execute a named pre-prepared SQL query statement taking a set of arguments and return a set of results as an iterator (probably a subclass of ResultSetIterator.
ResultSetIterator	runSQLQuery(java.lang.String opname, java.lang.Object[] args, ResultSetIterator iterator) Execute a named pre-prepared SQL query statement taking a set of arguments and return a set of results as an iterator (probably a subclass of ResultSetIterator.
void	setCachePreparedStatements(boolean state) Set to true to enable cache of pre-prepared statements
void	setConnection(IDBConnection connection) Set the associated jdbc connection
static java.lang.String	substitute(java.lang.String line, java.lang.String macro, java.lang.String subs) Helper function substitute all occurances of macro with subs

Methods inherited from class java.lang.Object

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

Constructor Detail

SQLCache

public SQLCache(java.lang.String sqlFile,
                java.util.Properties defaultOps,
                IDBConnection connection,
                java.lang.String idType)
         throws java.io.IOException

Constructor. Creates a new cache sql statements for interfacing to a specific database.

Parameters:

sqlFile - the name of the file of sql statements to load, this is loaded from the classpath.

defaultOps - Properties table which provides the default sql statements, any definitions of a given operation in the loaded file will override the default.

connection - the jdbc connection to the database itself

idType - the sql string to use for id types (substitutes for $id in files)

Method Detail

setCachePreparedStatements

public void setCachePreparedStatements(boolean state)

Set to true to enable cache of pre-prepared statements

getCachePreparedStatements

public boolean getCachePreparedStatements()

Return true if cache of pre-prepared statements is enabled

flushPreparedStatementCache

public void flushPreparedStatementCache()
                                 throws RDFRDBException

Flush the cache of all currently prepared statements

getConnection

public java.sql.Connection getConnection()
                                  throws java.sql.SQLException

Return the associated jdbc connection

setConnection

public void setConnection(IDBConnection connection)

Set the associated jdbc connection

getSQLStatement

public java.lang.String getSQLStatement(java.lang.String opname)

Return the raw SQL statement corresponding to the named operation.

getSQLStatement

public java.lang.String getSQLStatement(java.lang.String opname,
                                        java.lang.String attr)

Return the raw SQL statement corresponding to the named operation. Attribute version - substitute the ${a} attribute macro for the current attribute number.

getSQLStatementGroup

public java.util.Collection getSQLStatementGroup(java.lang.String opname)
                                          throws java.sql.SQLException

Return a set of raw SQL statements corresponding to the named operation. This is used for compound operations where more than one SQL command is needed to implement the operation (e.g. database formating and clean up). The individual statements should be separated by double-semicolons at the end of the line.

Needs refactoring to clarify what operations are and are not compound but for now it is assumed the caller knows which is correct. Compound statements are not called repeatedly so don't currently cache the parsed statement set.

getPreparedSQLStatement

public java.sql.PreparedStatement getPreparedSQLStatement(java.lang.String opname)
                                                   throws java.sql.SQLException

Return a prepared SQL statement corresponding to the named operation. The statement should either be closed after use or returned to the prepared statement pool using returnPreparedSQLStatement .

Only works for single statements, not compound statements.

Parameters:

con - the jdbc connection to use for preparing statements

opname - the name of the sql operation to locate

Returns:

a prepared SQL statement appropriate for the JDBC connection used when this SQLCache was constructed or null if there is no such operation or no such connection

getPreparedSQLStatement

public java.sql.PreparedStatement getPreparedSQLStatement(java.lang.String opname,
                                                          java.lang.String attr)
                                                   throws java.sql.SQLException

Variant on getPreparedSQLStatement which access the attribte variant correspond to the given attribute suffix.

returnPreparedSQLStatement

public void returnPreparedSQLStatement(java.sql.PreparedStatement ps,
                                       java.lang.String opname)

Return a prepared statement to the statement pool for reuse by another caller. Files the returned statement under the given operation name. Any close problems logged rather than raising exception so that iterator close() operations can be silent so that they can meet the ClosableIterator signature.

runSQLQuery

public ResultSetIterator runSQLQuery(java.lang.String opname,
                                     java.lang.Object[] args)
                              throws java.sql.SQLException

Execute a named pre-prepared SQL query statement taking a set of arguments and return a set of results as an iterator (probably a subclass of ResultSetIterator. Returns null if they query is an update (as opposed to an empty iterator for a true query which happens to return no answers).

Not sure this is a good design. Reducing this to a general interface leads to lots of clunky wrapping and unwrapping of primitive types, coercions and lack of compile-time type checking. On the other hand letting the clients do this themselves with direct jdbc calls leaves us up to the mercy of the client to correctly use returnPreparedSQLStatement and on average seems to lead to more duplication of boiler plate code. Currently the client can chose either approach.

The calling arguments are passed in as an array.

runSQLQuery

public ResultSetIterator runSQLQuery(java.lang.String opname,
                                     java.lang.Object[] args,
                                     ResultSetIterator iterator)
                              throws java.sql.SQLException

Execute a named pre-prepared SQL query statement taking a set of arguments and return a set of results as an iterator (probably a subclass of ResultSetIterator. Returns null if they query is an update (as opposed to an empty iterator for a true query which happens to return no answers).

Not sure this is a good design. Reducing this to a general interface leads to lots of clunky wrapping and unwrapping of primitive types, coercions and lack of compile-time type checking. On the other hand letting the clients do this themselves with direct jdbc calls leaves us up to the mercy of the client to correctly use returnPreparedSQLStatement and on average seems to lead to more duplication of boiler plate code. Currently the client can chose either approach.

Parameters:

opname - the name of the SQL operation to perform

args - the arguments to pass to the SQL operation as an array of Objects

iterator - the iterator to use to return the results

runSQLGroup

public void runSQLGroup(java.lang.String opname)
                 throws java.sql.SQLException

Run a group of sql statements - normally used for db formating and clean up. All statements are executed even if one raises an error then the error is reported at the end.

close

public void close()
           throws java.sql.SQLException

Close all prepared statements

loadSQLFile

public static java.util.Properties loadSQLFile(java.lang.String sqlFile,
                                               java.util.Properties defaultOps,
                                               java.lang.String idType)
                                        throws java.io.IOException

Load in a defined set of sql statements - see class comment for format. The loaded file is return as a Property table. This call is static to support the loading of a default sql mapping.

Parameters:

sqlFile - the name of the file of sql statements to load, this is loaded from the classpath.

defaultOps - a Properties table of default sql definitions.

idType - the sql string to use for id types (substitutes for $id in files)

substitute

public static java.lang.String substitute(java.lang.String line,
                                          java.lang.String macro,
                                          java.lang.String subs)

Helper function substitute all occurances of macro with subs

openResourceFile

public static java.io.BufferedReader openResourceFile(java.lang.String filename)
                                               throws java.io.IOException

Open a resource file for reading. The file is found on the classpath.