org.sakaiproject.service.framework.sql
Interface SqlService

All Known Implementing Classes:
BasicSqlService

public interface SqlService

SqlService provides access to pooled Connections to Sql databases.

The Connection objects managed by this service are standard java.sql.Connection objects.

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

Field Summary
static String DEFAULT
          The poolId for which can be used to get at the default pool.
 
Method Summary
 Connection borrowConnection()
          Access an available or newly created Connection from the default pool.
 void dbCancel(Connection conn)
          Cancel the update that was locked on this connection.
 List dbRead(Connection conn, 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 conn, 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.
 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.
 boolean dbWrite(String sql, String var)
          Execute the "write" sql - no response.
 boolean dbWriteBinary(String sql, Object[] fields, byte[] var, int offset, int len)
          Execute the "write" sql - no response.
 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.
 GregorianCalendar getCal()
          Access the calendar used in processing Time objects for Sql.
 String getVendor()
           
 void returnConnection(Connection conn)
          Release a database connection.
 

Field Detail

DEFAULT

public static final String DEFAULT
The poolId for which can be used to get at the default pool.

See Also:
Constant Field Values
Method Detail

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.

Returns:
The Connection object.
Throws:
SQLException - if a connection cannot be delivered.

returnConnection

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

Parameters:
conn - The connetion to release. If null or not one of ours, ignored.

dbRead

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

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.

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 conn,
                   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.

Parameters:
conn - The db connection object to use.
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.

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 conn,
                         String sql,
                         Object[] fields,
                         byte[] value)
Read a single field from the db, from multiple record - concatenating the binary values into value.

Parameters:
conn - 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!

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.

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

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

Parameters:
conn - The database connection on which the lock was gained.

getCal

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

Returns:
The calendar used in processing Time objects for Sql.

getVendor

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

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.

Parameters:
loader - The ClassLoader used to load the resource.
resource - The path name to the resource - vender string and .sql will be added