org.sakaiproject.component.framework.sql
Class BasicSqlService

java.lang.Object
  extended byorg.sakaiproject.component.framework.sql.BasicSqlService
All Implemented Interfaces:
SqlService

public class BasicSqlService
extends Object
implements SqlService

BasicSqlService implements the SqlService to support (mostly Legacy) Sakai code.

Version:
$Revision: 1340 $
Author:
University of Michigan, Sakai Software Development Team

Nested Class Summary
 class BasicSqlService.StreamWithConnection
           StreamWithConnection is a cover over a stream that comes from a statmenet result in a connection, holding all these until closed.
 
Field Summary
protected  DataSource defaultDataSource
          The "shared", "common" database connection pool
protected  DataSource longDataSource
          The "slow" connection pool for file uploads/downloads
protected  boolean m_autoDdl
          Configuration: to run the ddl on init or not.
protected  GregorianCalendar m_cal
          Used to work with dates in GMT in the db.
protected  boolean m_commitAfterRead
          Should we do a commit after a single statement read?
protected  boolean m_showSql
          if true, debug each sql command with timing.
protected  String m_vendor
          Database vendor used; possible values are oracle, mysql, hsqldb (default).
 
Fields inherited from interface org.sakaiproject.service.framework.sql.SqlService
DEFAULT
 
Constructor Summary
BasicSqlService()
           
 
Method Summary
 Connection borrowConnection()
          Access an available or newly created Connection from the default pool. Will wait a while until one is available.
 void dbCancel(Connection conn)
          Cancel the update that was locked on this connection.
 List dbRead(Connection callerConn, String sql, Object[] fields, SqlReader reader)
          Process a query, filling in with fields, and return the results as a List, one per record read.
 List dbRead(String sql)
          Read a single field from the db, from multiple records, returned as string[], one per record.
 List dbRead(String sql, Object[] fields, SqlReader reader)
          Process a query, filling in with fields, and return the results as a List, one per record read.
 void dbReadBinary(Connection callerConn, String sql, Object[] fields, byte[] value)
          Read a single field from the db, from multiple record - concatenating the binary values into value.
 InputStream dbReadBinary(String sql, Object[] fields, boolean big)
          Read a single field / record from the db, returning a stream on the result record / field.
 void dbReadBinary(String sql, Object[] fields, byte[] value)
          Read a single field from the db, from multiple record - concatenating the binary values into value.
 void dbReadBlobAndUpdate(String sql, byte[] content)
          Read a single field BLOB from the db from one record, and update it's bytes with content.
 Connection dbReadLock(String sql, StringBuffer field)
          Read a single field from the db, from a single record, return the value found, and lock for update.
 void dbUpdateCommit(String sql, Object[] fields, String var, Connection conn)
          Commit the update that was locked on this connection.
 boolean dbWrite(Connection connection, String sql, Object[] fields)
          Execute the "write" sql - no response, using a set of fields from an array and a given connection.
 boolean dbWrite(String sql)
          Execute the "write" sql - no response.
 boolean dbWrite(String sql, Object[] fields)
          Execute the "write" sql - no response, using a set of fields from an array plus one more as params.
 boolean dbWrite(String sql, Object[] fields, String lastField)
          Execute the "write" sql - no response, using a set of fields from an array plus one more as params.
protected  boolean dbWrite(String sql, Object[] fields, String lastField, Connection callerConnection, boolean failQuiet)
          Execute the "write" sql - no response, using a set of fields from an array plus one more as params and connection.
 boolean dbWrite(String sql, String var)
          Execute the "write" sql - no response. a long field is set to "?"
 boolean dbWriteBinary(String sql, Object[] fields, byte[] var, int offset, int len)
          Execute the "write" sql - no response. a long binary field is set to "?"
 boolean dbWriteFailQuiet(Connection connection, String sql, Object[] fields)
          Execute the "write" sql - no response, using a set of fields from an array and a given connection logging no errors on failure.
 void ddl(ClassLoader loader, String resource)
          Load and run the named file using the given class loader, as a ddl check / create. The first non-comment ('--') line will be run, and if successfull, all other non-comment lines will be run. SQL statements must be on a single line, and may have ';' terminators.
protected  void debug(String str, String sql, Object[] fields)
          Append a message about this SQL statement to the DEBUG string in progress, if any
protected  String debugFields(Object[] fields)
           
 void destroy()
          Final cleanup.
 GregorianCalendar getCal()
          Access the calendar used in processing Time objects for Sql.
 String getVendor()
           
 void init()
          Final initialization, once all dependencies are set.
protected  int prepareStatement(PreparedStatement pstmt, Object[] fields)
          Prepare a prepared statement with fields.
 void returnConnection(Connection conn)
          Release a database connection.
 void setAutoDdl(String value)
          Configuration: to run the ddl on init or not.
 void setCommitAfterRead(String value)
          Configuration: should we do a commit after each single SQL read?
 void setDefaultDataSource(DataSource defaultDataSource)
           
 void setLongDataSource(DataSource slowDataSource)
           
 void setShowSql(String value)
          Configuration: to show each sql command in the logs or not.
 void setVendor(String value)
          Configuration: Database vendor used; possible values are oracle, mysql, hsqldb.
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

defaultDataSource

protected DataSource defaultDataSource
The "shared", "common" database connection pool


longDataSource

protected DataSource longDataSource
The "slow" connection pool for file uploads/downloads


m_commitAfterRead

protected boolean m_commitAfterRead
Should we do a commit after a single statement read?


m_vendor

protected String m_vendor
Database vendor used; possible values are oracle, mysql, hsqldb (default).


m_showSql

protected boolean m_showSql
if true, debug each sql command with timing.


m_autoDdl

protected boolean m_autoDdl
Configuration: to run the ddl on init or not.


m_cal

protected final GregorianCalendar m_cal
Used to work with dates in GMT in the db.

Constructor Detail

BasicSqlService

public BasicSqlService()
Method Detail

setCommitAfterRead

public void setCommitAfterRead(String value)
Configuration: should we do a commit after each single SQL read?

Parameters:
value - the setting (true of false) string.

setVendor

public void setVendor(String value)
Configuration: Database vendor used; possible values are oracle, mysql, hsqldb.

Parameters:
value - the Database vendor used.

setShowSql

public void setShowSql(String value)
Configuration: to show each sql command in the logs or not.

Parameters:
value - the showSql setting.

setAutoDdl

public void setAutoDdl(String value)
Configuration: to run the ddl on init or not.

Parameters:
value - the auto ddl value.

getVendor

public String getVendor()
Specified by:
getVendor in interface SqlService
Returns:
a string indicating the database vendor - "oracle" or "mysql" or "hsqldb".

init

public void init()
Final initialization, once all dependencies are set.


destroy

public void destroy()
Final cleanup.


borrowConnection

public Connection borrowConnection()
                            throws SQLException
Access an available or newly created Connection from the default pool. Will wait a while until one is available.

Specified by:
borrowConnection in interface SqlService
Returns:
The Connection object.
Throws:
SQLException - if a connection cannot be delivered.

returnConnection

public void returnConnection(Connection conn)
Release a database connection.

Specified by:
returnConnection in interface SqlService
Parameters:
conn - The connetion to release. If null or not one of ours, ignored.

getCal

public GregorianCalendar getCal()
Access the calendar used in processing Time objects for Sql.

Specified by:
getCal in interface SqlService
Returns:
The calendar used in processing Time objects for Sql.

dbRead

public List dbRead(String sql)
Read a single field from the db, from multiple records, returned as string[], one per record.

Specified by:
dbRead in interface SqlService
Parameters:
sql - The sql statement.
Returns:
The List of Strings of single fields of the record found, or empty if none found.

dbRead

public List dbRead(String sql,
                   Object[] fields,
                   SqlReader reader)
Process a query, filling in with fields, and return the results as a List, one per record read. If a reader is provided, it will be called for each record to prepare the Object placed into the List. Otherwise, the first field of each record, as a String, will be placed in the list.

Specified by:
dbRead in interface SqlService
Parameters:
sql - The sql statement.
fields - The array of fields for parameters.
reader - The reader object to read each record.
Returns:
The List of things read, one per record.

dbRead

public List dbRead(Connection callerConn,
                   String sql,
                   Object[] fields,
                   SqlReader reader)
Process a query, filling in with fields, and return the results as a List, one per record read. If a reader is provided, it will be called for each record to prepare the Object placed into the List. Otherwise, the first field of each record, as a String, will be placed in the list.

Specified by:
dbRead in interface SqlService
Parameters:
callerConn - The db connection object to use (if not null).
sql - The sql statement.
fields - The array of fields for parameters.
reader - The reader object to read each record.
Returns:
The List of things read, one per record.

dbReadBinary

public void dbReadBinary(String sql,
                         Object[] fields,
                         byte[] value)
Read a single field from the db, from multiple record - concatenating the binary values into value.

Specified by:
dbReadBinary in interface SqlService
Parameters:
sql - The sql statement.
fields - The array of fields for parameters.
value - The array of bytes to fill with the value read from the db.

dbReadBinary

public void dbReadBinary(Connection callerConn,
                         String sql,
                         Object[] fields,
                         byte[] value)
Read a single field from the db, from multiple record - concatenating the binary values into value.

Specified by:
dbReadBinary in interface SqlService
Parameters:
callerConn - The optional db connection object to use.
sql - The sql statement.
fields - The array of fields for parameters.
value - The array of bytes to fill with the value read from the db.

dbReadBinary

public InputStream dbReadBinary(String sql,
                                Object[] fields,
                                boolean big)
                         throws ServerOverloadException
Read a single field / record from the db, returning a stream on the result record / field. The stream holds the conection open - so it must be closed or finalized quickly!

Specified by:
dbReadBinary in interface SqlService
Parameters:
sql - The sql statement.
fields - The array of fields for parameters.
big - If true, the read is expected to be potentially large.
Throws:
ServerOverloadException - if the read cannot complete due to lack of a free connection (if wait is false)

dbWrite

public boolean dbWrite(String sql)
Execute the "write" sql - no response.

Specified by:
dbWrite in interface SqlService
Parameters:
sql - The sql statement.
Returns:
true if successful, false if not.

dbWrite

public boolean dbWrite(String sql,
                       String var)
Execute the "write" sql - no response. a long field is set to "?" - fill it in with var

Specified by:
dbWrite in interface SqlService
Parameters:
sql - The sql statement.
var - The value to bind to the first parameter in the sql statement.
Returns:
true if successful, false if not.

dbWriteBinary

public boolean dbWriteBinary(String sql,
                             Object[] fields,
                             byte[] var,
                             int offset,
                             int len)
Execute the "write" sql - no response. a long binary field is set to "?" - fill it in with var

Specified by:
dbWriteBinary in interface SqlService
Parameters:
sql - The sql statement.
fields - The array of fields for parameters.
var - The value to bind to the last parameter in the sql statement.
offset - The start within the var to write
len - The number of bytes of var, starting with index, to write
Returns:
true if successful, false if not.

dbWrite

public boolean dbWrite(String sql,
                       Object[] fields)
Execute the "write" sql - no response, using a set of fields from an array plus one more as params.

Specified by:
dbWrite in interface SqlService
Parameters:
sql - The sql statement.
fields - The array of fields for parameters.
Returns:
true if successful, false if not.

dbWrite

public boolean dbWrite(Connection connection,
                       String sql,
                       Object[] fields)
Execute the "write" sql - no response, using a set of fields from an array and a given connection.

Specified by:
dbWrite in interface SqlService
Parameters:
connection - The connection to use.
sql - The sql statement.
fields - The array of fields for parameters.
Returns:
true if successful, false if not.

dbWriteFailQuiet

public boolean dbWriteFailQuiet(Connection connection,
                                String sql,
                                Object[] fields)
Execute the "write" sql - no response, using a set of fields from an array and a given connection logging no errors on failure.

Specified by:
dbWriteFailQuiet in interface SqlService
Parameters:
connection - The connection to use.
sql - The sql statement.
fields - The array of fields for parameters.
Returns:
true if successful, false if not.

dbWrite

public boolean dbWrite(String sql,
                       Object[] fields,
                       String lastField)
Execute the "write" sql - no response, using a set of fields from an array plus one more as params.

Specified by:
dbWrite in interface SqlService
Parameters:
sql - The sql statement.
fields - The array of fields for parameters.
lastField - The value to bind to the last parameter in the sql statement.
Returns:
true if successful, false if not.

dbWrite

protected boolean dbWrite(String sql,
                          Object[] fields,
                          String lastField,
                          Connection callerConnection,
                          boolean failQuiet)
Execute the "write" sql - no response, using a set of fields from an array plus one more as params and connection.

Parameters:
sql - The sql statement.
fields - The array of fields for parameters.
lastField - The value to bind to the last parameter in the sql statement.
callerConnection - The connection to use.
failQuiet - If true, don't log errors from statement failure
Returns:
true if successful, false if not.

dbReadBlobAndUpdate

public void dbReadBlobAndUpdate(String sql,
                                byte[] content)
Read a single field BLOB from the db from one record, and update it's bytes with content.

Specified by:
dbReadBlobAndUpdate in interface SqlService
Parameters:
sql - The sql statement to select the BLOB.
content - The new bytes for the BLOB.

dbReadLock

public Connection dbReadLock(String sql,
                             StringBuffer field)
Read a single field from the db, from a single record, return the value found, and lock for update.

Specified by:
dbReadLock in interface SqlService
Parameters:
sql - The sql statement.
field - A StringBuffer that will be filled with the field.
Returns:
The Connection holding the lock.

dbUpdateCommit

public void dbUpdateCommit(String sql,
                           Object[] fields,
                           String var,
                           Connection conn)
Commit the update that was locked on this connection.

Specified by:
dbUpdateCommit in interface SqlService
Parameters:
sql - The sql statement.
fields - The array of fields for parameters.
var - The value to bind to the last parameter in the sql statement.
conn - The database connection on which the lock was gained.

dbCancel

public void dbCancel(Connection conn)
Cancel the update that was locked on this connection.

Specified by:
dbCancel in interface SqlService
Parameters:
conn - The database connection on which the lock was gained.

ddl

public void ddl(ClassLoader loader,
                String resource)
Load and run the named file using the given class loader, as a ddl check / create. The first non-comment ('--') line will be run, and if successfull, all other non-comment lines will be run. SQL statements must be on a single line, and may have ';' terminators.

Specified by:
ddl in interface SqlService
Parameters:
loader - The ClassLoader used to load the resource.
resource - The path name to the resource - vender string and .sql will be added

prepareStatement

protected int prepareStatement(PreparedStatement pstmt,
                               Object[] fields)
                        throws SQLException,
                               UnsupportedEncodingException
Prepare a prepared statement with fields.

Parameters:
pstmt - The prepared statement to fill in.
fields - The Object array of values to fill in.
Returns:
the next pos that was not filled in.
Throws:
UnsupportedEncodingException
SQLException

debug

protected void debug(String str,
                     String sql,
                     Object[] fields)
Append a message about this SQL statement to the DEBUG string in progress, if any

Parameters:
str - The SQL statement.
fields - The bind fields.

debugFields

protected String debugFields(Object[] fields)

setDefaultDataSource

public void setDefaultDataSource(DataSource defaultDataSource)
Parameters:
defaultDataSource - The defaultDataSource to set.

setLongDataSource

public void setLongDataSource(DataSource slowDataSource)
Parameters:
slowDataSource - The slowDataSource to set.