ir.thinkinsql
Class Database

java.lang.Object
  ir.thinkinsql.Database

public class Database
extends java.lang.Object

Provides access to persistence services. Consumers can use Database as-is by passing a java.sql.Connection or a javax.sql.DataSource to the relevant open method. Expected practice is to enhance Database to encapsulate the open parameters and provide application-specific enhancements.

Pooling & Caching
Any connections created from a jdbc url are pooled, and the prepared statements created with them are cached as well. Type information and sql text generated via reflection are also cached.

Transactions
The underlying pooled connections created by Database are in AutoCommit mode until beginTransaction is called. Any beginTransaction call will be followed automatically by a rollback if the database is closed or is finalized before commit is called. This is a necessary precaution to avoid having a connection with an incomplete transaction returned to the pool.

Oracle
Statements for selects are not cached for Oracle, as the Oracle jdbc driver keeps the cursor open if the statement is not fully closed, alas.

Field Summary

java.lang.String

CatalogSeparator

Constructor Summary

Database()

Method Summary

void	beginTransaction() Sets the database's underlying connection's autocommit flag off.
void	close() Closes the underlying connection, or returns it to the pool, if applicable.
java.lang.String	combineNamedStatements(java.lang.String firstStmtKey, java.lang.String secondStmtKey, java.lang.String... otherStmtKeys)
void	commit() Commits any changes made since the last beginTransaction, and returns the connection to autoCommit mode.
boolean	delete(IRecord rec) Deletes the record, returns whether record was actually deleted.
int	execute(java.lang.String sql, java.lang.Object... parms) Executes an update statement, returns count of records affected.
boolean	exists(IRecord rec, java.lang.Object... keys) Indicates whether record's passed or embodied key already exists in table, does NOT populate record's non-key values.
void	finalize() Overridden to call close().
java.lang.String	generateClass(java.lang.String tblNameOrSelect, java.lang.String newClassName) If a table name or schema.table is passed, generates a BaseRecord-extending Java class.
ColDef	getColDef(java.lang.String tblName, java.lang.String colName) Retrieves a column's metadata.
int	getColSize(java.lang.String table, java.lang.String column) returns the size of the column
java.sql.Connection	getConnection()
protected java.lang.String	getDatabaseProductName()
protected int	getDriverMajorVersion()
java.lang.Object	getProperty(java.lang.Object key) Returns the value of an arbitrary property.
IReader	getReader(javax.sql.RowSet rs) Reconstitutes an IReader from a Rowset.
void	insert(IRecord rec) Inserts a record for the object.
boolean	isOpen()
void	loadNamedStatements(java.util.Map<java.lang.String,java.lang.String> m) Loads statements to internal cache for usage with any method that receives an SQL parameter.
void	loadNamedStatements(java.net.URL u) Uses XmlMapper to transform an xml file of sql statements to a Map, and loads those statements to internal cache for usage with any method that receives an SQL parameter.
void	open(java.sql.Connection conn) Wraps the passed connection to the data source.
void	open(javax.sql.DataSource ds, java.lang.String testSql) Opens a connection to the data source.
protected void	open(java.lang.String driver, java.lang.String url, java.lang.String usr, java.lang.String pwd, int poolSize, java.lang.String verify) Opens a connection from a jdbc URL.
protected java.lang.String	overrideNamedStatement(java.lang.String name, java.lang.String sql) Provides a plug point for enhancers to make special substitutions for sql loaded as a named statement, before it is used.
java.sql.CallableStatement	prepareCall(java.lang.String sql) Returns a callable statement for the sql passed.
void	rollback() Rolls back any changes made since the last beginTransaction, and returns the connection to autoCommit mode.
boolean	select(IRecord rec, java.lang.Object... keys) Retrieves and loads a record using the passed key, or if no key passed, embodied key.
boolean	selectFirst(java.lang.String sql, IRow row, java.lang.Object... parms) Loads a row from the first row of the result.
java.util.List<java.util.List<java.lang.Object>>	selectMatrix(java.lang.String sql, int maxRows, java.lang.Object... parms) Returns data as a 2d matrix of objects filled by calling ResultSet.getObject.
IReader	selectReader(java.lang.String sql, int maxRows, java.lang.Object... parms) Returns an IReader to iterate and autopopulate IRows.
<E extends IRow> java.util.List<E>	selectRows(java.lang.String sql, E returnTypeTemplate, int maxRows, java.lang.Object... parms) Returns a list of IRow instances for the passed statement.
<T> T	selectScalar(java.lang.String sql, T dft, java.lang.Object... parms) Returns the first column value of the first row selected, cast to the type of the default value passed.
<A> java.util.List<A>	selectScalarList(java.lang.String sql, int maxRows, java.lang.Class<A> returnType, java.lang.Object... parameters) Returns a List of values from column 1 of all rows retrieved, cast to the passed target class.
void	setProperty(java.lang.String key, java.lang.Object v) Sets the value of an arbitrary property.
void	setUpdateListener(UpdateListener lstnr) Sets an update listener to receive notifications of updates.
void	testNamedStatements(java.io.PrintWriter pw) Runs all statements loaded via loadNamedStatements with all null parameters within a transaction to be rolled back, reports on results to passed PrintWriter.
java.lang.String	toString() Overridden to return ClassName:Url:hashCode()
boolean	update(IRecord rec) Updates the record, returns whether update occurred
void	write(IRecord rec) Inserts a record for the object if its embodied key is not found, otherwise updates it

Methods inherited from class java.lang.Object

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

Field Detail

CatalogSeparator

public java.lang.String CatalogSeparator

See Also:

DatabaseMetaData.getCatalogSeparator()

Constructor Detail

Database

public Database()

Method Detail

beginTransaction

public void beginTransaction()
                      throws java.lang.Exception

Sets the database's underlying connection's autocommit flag off. Any beginTransaction call will be followed automatically by a rollback if the database is closed or goes out of scope before commit is called. This is a necessary precaution to avoid having a connection with an incomplete transaction returned to the pool.

Throws:

java.lang.Exception

close

public void close()

Closes the underlying connection, or returns it to the pool, if applicable.

combineNamedStatements

public java.lang.String combineNamedStatements(java.lang.String firstStmtKey,
                                               java.lang.String secondStmtKey,
                                               java.lang.String... otherStmtKeys)
                                        throws java.lang.Exception

Parameters:

firstStmtKey -

secondStmtKey -

otherStmtKeys -

Returns:

values retrieved from statement names passed with a space between each

Throws:

java.lang.Exception

commit

public void commit()
            throws java.lang.Exception

Commits any changes made since the last beginTransaction, and returns the connection to autoCommit mode.

Throws:

java.lang.Exception

delete

public boolean delete(IRecord rec)
               throws java.lang.Exception

Deletes the record, returns whether record was actually deleted.

Throws:

java.lang.Exception

execute

public int execute(java.lang.String sql,
                   java.lang.Object... parms)
            throws java.lang.Exception

Executes an update statement, returns count of records affected.

Throws:

java.lang.Exception

exists

public boolean exists(IRecord rec,
                      java.lang.Object... keys)
               throws java.lang.Exception

Indicates whether record's passed or embodied key already exists in table, does NOT populate record's non-key values.

Throws:

java.lang.Exception

finalize

public void finalize()

Overridden to call close().

Overrides:

finalize in class java.lang.Object

generateClass

public java.lang.String generateClass(java.lang.String tblNameOrSelect,
                                      java.lang.String newClassName)
                               throws java.lang.Exception

If a table name or schema.table is passed, generates a BaseRecord-extending Java class. If a select statement is passed, generates a BaseRow-extending Java class.

Throws:

java.lang.Exception

getColDef

public ColDef getColDef(java.lang.String tblName,
                        java.lang.String colName)
                 throws java.lang.Exception

Retrieves a column's metadata.

Parameters:

tblName - can be table name or schema.table

Returns:

column definition requested or null if not found

Throws:

java.lang.Exception

getColSize

public int getColSize(java.lang.String table,
                      java.lang.String column)
               throws java.lang.Exception

returns the size of the column

Throws:

java.lang.Exception

getConnection

public java.sql.Connection getConnection()

getDatabaseProductName

protected java.lang.String getDatabaseProductName()

See Also:

DatabaseMetaData.getDatabaseProductName()

getDriverMajorVersion

protected int getDriverMajorVersion()

See Also:

DatabaseMetaData.getDriverMajorVersion()

getProperty

public java.lang.Object getProperty(java.lang.Object key)

Returns the value of an arbitrary property.

getReader

public IReader getReader(javax.sql.RowSet rs)
                  throws java.lang.Exception

Reconstitutes an IReader from a Rowset.

Throws:

java.lang.Exception

insert

public void insert(IRecord rec)
            throws java.lang.Exception

Inserts a record for the object. Throws an exception if insert fails.

Throws:

java.lang.Exception

isOpen

public boolean isOpen()

loadNamedStatements

public void loadNamedStatements(java.util.Map<java.lang.String,java.lang.String> m)
                         throws java.lang.Exception

Loads statements to internal cache for usage with any method that receives an SQL parameter.

Throws:

java.lang.Exception

loadNamedStatements

public void loadNamedStatements(java.net.URL u)
                         throws java.lang.Exception

Uses XmlMapper to transform an xml file of sql statements to a Map, and loads those statements to internal cache for usage with any method that receives an SQL parameter.

Throws:

java.lang.Exception

open

public void open(java.sql.Connection conn)
          throws java.lang.Exception

Wraps the passed connection to the data source. Note that no pooling will be provided by the framework for the passed connection - it is expected that the container will do so.

Throws:

java.lang.Exception

open

public void open(javax.sql.DataSource ds,
                 java.lang.String testSql)
          throws java.lang.Exception

Opens a connection to the data source. Note that no pooling will be provided by the framework for connections created from a data source - it is expected that the container will do so. Pass a non-blank testSql to verify connection before use.

Throws:

java.lang.Exception

open

protected void open(java.lang.String driver,
                    java.lang.String url,
                    java.lang.String usr,
                    java.lang.String pwd,
                    int poolSize,
                    java.lang.String verify)
             throws java.lang.Exception

Opens a connection from a jdbc URL. For this type of connection, the framework will pool connections and prepared statements. The verify parameter should be an SQL select statement to be used to test connections as they are checked out of the pool, such as "select 1 from dual"

Throws:

java.lang.Exception

overrideNamedStatement

protected java.lang.String overrideNamedStatement(java.lang.String name,
                                                  java.lang.String sql)
                                           throws java.lang.Exception

Provides a plug point for enhancers to make special substitutions for sql loaded as a named statement, before it is used. Return statement as-is for no override.

Throws:

java.lang.Exception

prepareCall

public java.sql.CallableStatement prepareCall(java.lang.String sql)
                                       throws java.lang.Exception

Returns a callable statement for the sql passed.

Throws:

java.lang.Exception

rollback

public void rollback()
              throws java.lang.Exception

Rolls back any changes made since the last beginTransaction, and returns the connection to autoCommit mode.

Throws:

java.lang.Exception

select

public boolean select(IRecord rec,
                      java.lang.Object... keys)
               throws java.lang.Exception

Retrieves and loads a record using the passed key, or if no key passed, embodied key.

Throws:

java.lang.Exception

selectFirst

public boolean selectFirst(java.lang.String sql,
                           IRow row,
                           java.lang.Object... parms)
                    throws java.lang.Exception

Loads a row from the first row of the result. Returns whether a row was found.

Throws:

java.lang.Exception

selectMatrix

public java.util.List<java.util.List<java.lang.Object>> selectMatrix(java.lang.String sql,
                                                                     int maxRows,
                                                                     java.lang.Object... parms)
                                                              throws java.lang.Exception

Returns data as a 2d matrix of objects filled by calling ResultSet.getObject. Any cell could be null. An empty result set would be an empty array. Use -1 as maxRows to select all.

Throws:

java.lang.Exception

selectReader

public IReader selectReader(java.lang.String sql,
                            int maxRows,
                            java.lang.Object... parms)
                     throws java.lang.Exception

Returns an IReader to iterate and autopopulate IRows. Use -1 as maxRows to select all.

Throws:

java.lang.Exception

selectRows

public <E extends IRow> java.util.List<E> selectRows(java.lang.String sql,
                                                     E returnTypeTemplate,
                                                     int maxRows,
                                                     java.lang.Object... parms)
                                          throws java.lang.Exception

Returns a list of IRow instances for the passed statement. Passed template object must have a default constructor. Use -1 as maxRows to select all.

Throws:

java.lang.Exception

selectScalar

public <T> T selectScalar(java.lang.String sql,
                          T dft,
                          java.lang.Object... parms)
               throws java.lang.Exception

Returns the first column value of the first row selected, cast to the type of the default value passed. If no rows are found, the default value is returned.
eg.
String sql = "select sum(f1) from tTbl where parm1=? and parm2=?";
int res = db.selectScalar(sql,0,74,"fred");

Throws:

java.lang.Exception

selectScalarList

public <A> java.util.List<A> selectScalarList(java.lang.String sql,
                                              int maxRows,
                                              java.lang.Class<A> returnType,
                                              java.lang.Object... parameters)
                                   throws java.lang.Exception

Returns a List of values from column 1 of all rows retrieved, cast to the passed target class.

Throws:

java.lang.Exception

setProperty

public void setProperty(java.lang.String key,
                        java.lang.Object v)

Sets the value of an arbitrary property.

setUpdateListener

public void setUpdateListener(UpdateListener lstnr)

Sets an update listener to receive notifications of updates.

testNamedStatements

public void testNamedStatements(java.io.PrintWriter pw)
                         throws java.lang.Exception

Runs all statements loaded via loadNamedStatements with all null parameters within a transaction to be rolled back, reports on results to passed PrintWriter.

Throws:

java.lang.Exception

toString

public java.lang.String toString()

Overridden to return ClassName:Url:hashCode()

Overrides:

toString in class java.lang.Object

update

public boolean update(IRecord rec)
               throws java.lang.Exception

Updates the record, returns whether update occurred

Throws:

java.lang.Exception

write

public void write(IRecord rec)
           throws java.lang.Exception

Inserts a record for the object if its embodied key is not found, otherwise updates it

Throws:

java.lang.Exception