de.uhilger.baselink

Class PersistenceManager

java.lang.Object

de.uhilger.baselink.PersistenceManager

public class PersistenceManager
extends java.lang.Object

Class PersistenceManager stores and retrieves Java objects to and from a relational database. Any object that implements interface Record can be read from or written to a database using this class.

In addition, any other class can be used directly as PersistenceManager wraps such other objects into GenericRecord objects implicitly.

Major aim of class PersistenceManager is to encapsulate the management of database related objects such as Connection, Statement and ResultSet to make sure proper opening and closing especially when used in conjunction with a connection pool.

In addition PersistenceManager provides methods that allow for an abundance of database read/write operations for convenience providing a base class to extend for individual needs.

Version:

3, March 22, 2011

Author:

Copyright (c) Ulrich Hilger, http://uhilger.de, Published under the terms and conditions of the GNU General Public License

Field Summary

Fields

Modifier and Type	Field and Description
protected java.lang.String	dburl reference to JDBC url to use for connections in development mode
protected javax.sql.DataSource	ds reference to data source to use for connections in production mode
static java.lang.String	NULL_STR

Constructor Summary

Constructors

Constructor and Description
PersistenceManager() Create a new object of class PersistenceManager

Method Summary

Modifier and Type	Method and Description
java.sql.PreparedStatement	buildQuery(java.sql.Connection c, java.lang.String sql, java.lang.Object[] params) Create an SQL statement from an SQL string and an array of parameters
void	closeConnectionFinally(java.sql.Connection c) close a given connection
void	closeResultSetFinally(java.sql.ResultSet rs) Close a given result set
void	closeStatementFinally(java.sql.Statement s) Close a given statement
void	commit(java.sql.Connection c)
java.lang.Object	delete(java.sql.Connection c, java.lang.Object o) Delete a given object from the database Use this method for single deletes.
java.lang.Object	delete(java.sql.Connection c, java.lang.Object o, Record record) Delete a given object from the database
java.lang.Object	delete(java.lang.Object o) Delete a given object from the database Use this method for single deletes.
java.lang.Object	delete(java.lang.Object o, Record record) Delete a given object from the database
int	execute(java.sql.Connection c, java.lang.String sql) Execute an SQL statement
int	execute(java.sql.Connection c, java.lang.String sql, java.lang.Object... params) Execute an SQL statement
int	execute(java.lang.String sql) Execute an SQL statement
int	execute(java.lang.String sql, java.lang.Object... params) Execute an SQL statement
int[]	executeScript(java.sql.Connection c, java.lang.String sqlScript) Execute an SQL script
int[]	executeScript(java.lang.String sqlScript) Execute an SQL script
java.util.List<java.util.Map<java.lang.String,java.lang.Object>>	executeWithKeys(java.sql.Connection c, java.lang.String sql) Execute an SQL statement and return keys generated in the database
java.util.List<java.util.Map<java.lang.String,java.lang.Object>>	executeWithKeys(java.lang.String sql) Execute an SQL statement and return keys generated in the database
java.sql.Connection	getConnection() Get a database connection
java.lang.Object	insert(java.sql.Connection c, java.lang.Object o) Add an object to the database Use this method for single inserts.
java.lang.Object	insert(java.sql.Connection c, java.lang.Object o, Record record) Add an object to the database
java.lang.Object	insert(java.lang.Object o) Add an object to the database Use this method for single inserts.
java.lang.Object	insert(java.lang.Object o, Record record) Add an object to the database
void	rollback(java.sql.Connection c)
java.util.List<java.util.Map<java.lang.String,java.lang.Object>>	select(java.sql.Connection c, java.lang.String sql) Select a list of objects that match a given SQL statement
java.util.List<java.util.List<java.lang.String>>	select(java.sql.Connection c, java.lang.String sql, boolean includeBlobs) Select a list of objects that match a given SQL statement
java.util.List<java.util.List<java.lang.String>>	select(java.sql.Connection c, java.lang.String sql, boolean includeBlobs, java.lang.Object... params) Select a list of objects that match a given SQL statement
java.util.List<java.lang.Object>	select(java.sql.Connection c, java.lang.String sql, Record record, boolean includeBlobs) Select a list of objects that match a given SQL statement
java.util.List<java.lang.Object>	select(java.sql.Connection c, java.lang.String sql, Record record, boolean includeBlobs, java.lang.Object... params) Select a list of objects that match a given SQL statement
java.util.List<java.util.Map<java.lang.String,java.lang.Object>>	select(java.lang.String sql) Select a list of objects that match a given SQL statement
java.util.List<java.util.List<java.lang.String>>	select(java.lang.String sql, boolean includeBlobs) Select a list of objects that match a given SQL statement
java.util.List<java.util.List<java.lang.String>>	select(java.lang.String sql, boolean includeBlobs, java.lang.Object... params) Select a list of objects that match a given SQL statement
java.util.List<java.lang.Object>	select(java.lang.String sql, Record record) Select a list of objects through a given SQL statement
java.util.List<java.lang.Object>	select(java.lang.String sql, Record record, boolean includeBlobs) Select a list of objects through a given SQL statement
java.util.List<java.lang.Object>	select(java.lang.String sql, Record record, boolean includeBlobs, java.lang.Object... params) Select a list of objects through a given SQL statement
void	setDatabase(java.lang.String url) Set the database Use either this method together with method setDriverName or method setDataSourceName to indicate the connection type
void	setDatabase(java.lang.String driverName, java.lang.String url) Set the database Use either this method or method setDataSourceName to indicate the connection type
void	setDataSourceName(java.lang.String dataSourceName) Set name of DataSource Use either this method or method setDatabase to indicate the connection type
void	setDriverName(java.lang.String driverName) Set the JDBC driver class name
void	startTransaction(java.sql.Connection c)
java.util.List<java.util.List<java.lang.String>>	toList(java.sql.ResultSet rs, boolean includeBlobs) Helper method that converts a ResultSet into a list of lists, one per row, each row is a list of strings
java.lang.Object	update(java.sql.Connection c, java.lang.Object o) update an object in the database Use this method for single updates.
java.lang.Object	update(java.sql.Connection c, java.lang.Object o, Record record) update an object in the database
java.lang.Object	update(java.lang.Object o) update an object in the database Use this method for single updates.
java.lang.Object	update(java.lang.Object o, Record record) update an object in the database

Methods inherited from class java.lang.Object

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

Field Detail

NULL_STR

public static final java.lang.String NULL_STR

See Also:

Constant Field Values

ds

protected javax.sql.DataSource ds

reference to data source to use for connections in production mode

dburl

protected java.lang.String dburl

reference to JDBC url to use for connections in development mode

Constructor Detail

PersistenceManager

public PersistenceManager()

Create a new object of class PersistenceManager

Method Detail

setDriverName

public void setDriverName(java.lang.String driverName)
                   throws java.lang.ClassNotFoundException

Set the JDBC driver class name

Parameters:

driverName - name of JDBC driver class name

Throws:

java.lang.ClassNotFoundException - when driver is not found

setDatabase

public void setDatabase(java.lang.String url)

Set the database

Use either this method together with method setDriverName or method setDataSourceName to indicate the connection type

Parameters:

url - JDBC url

setDatabase

public void setDatabase(java.lang.String driverName,
                        java.lang.String url)
                 throws java.lang.ClassNotFoundException

Set the database

Use either this method or method setDataSourceName to indicate the connection type

Parameters:

driverName - class name of JDBC driver

url - JDBC url

Throws:

java.lang.ClassNotFoundException - when driver is not found

setDataSourceName

public void setDataSourceName(java.lang.String dataSourceName)
                       throws javax.naming.NamingException

Set name of DataSource

Use either this method or method setDatabase to indicate the connection type

Parameters:

dataSourceName - name of DataSource

Throws:

javax.naming.NamingException - when JNDI lookup fails

getConnection

public java.sql.Connection getConnection()

Get a database connection

Returns:

a database connection

delete

public java.lang.Object delete(java.lang.Object o)

Delete a given object from the database

Use this method for single deletes. In cases where several subsequent deletes for objects of the same class are required the use of method delete(Object, Record) is recommended instead to minimise instantiation overhead.

Parameters:

o - object to delete

Returns:

the deleted object

delete

public java.lang.Object delete(java.sql.Connection c,
                               java.lang.Object o)

Delete a given object from the database

Use this method for single deletes. In cases where several subsequent deletes for objects of the same class are required the use of method delete(Connection, Object, Record) is recommended instead to minimise instantiation overhead.

Parameters:

c - the connection to use, expected to be open and established

o - object to delete

Returns:

the deleted object

delete

public java.lang.Object delete(java.lang.Object o,
                               Record record)

Delete a given object from the database

Parameters:

o - object to delete

record - reference to object to use to map between object and database

Returns:

the deleted object

delete

public java.lang.Object delete(java.sql.Connection c,
                               java.lang.Object o,
                               Record record)

Delete a given object from the database

Parameters:

c - the connection to use, expected to be open and established

o - object to delete

record - reference to object to use to map between object and database

Returns:

the deleted object

insert

public java.lang.Object insert(java.lang.Object o)

Add an object to the database

Use this method for single inserts. In cases where several subsequent inserts for objects of the same class are required the use of method insert(Object, Record) is recommended instead to minimise instantiation overhead.

Parameters:

o - the object to add

Returns:

the added object

insert

public java.lang.Object insert(java.sql.Connection c,
                               java.lang.Object o)

Add an object to the database

Use this method for single inserts. In cases where several subsequent inserts for objects of the same class are required the use of method insert(Connection, Object, Record) is recommended instead to minimise instantiation overhead.

Parameters:

c - the connection to use, expected to be open and established

o - the object to add

Returns:

the object added to the database

insert

public java.lang.Object insert(java.lang.Object o,
                               Record record)

Add an object to the database

Parameters:

o - object to add

record - reference to object to use to map between object and database

Returns:

the added object

insert

public java.lang.Object insert(java.sql.Connection c,
                               java.lang.Object o,
                               Record record)

Add an object to the database

Parameters:

c - the connection to use, expected to be open and established

o - object to add

record - reference to object to use to map between object and database

Returns:

the object that was added

update

public java.lang.Object update(java.lang.Object o)

update an object in the database

Use this method for single updates. In cases where several subsequent updates for objects of the same class are required the use of method update(Object, Record) is recommended instead to minimise instantiation overhead.

Parameters:

o - object to update

Returns:

the object that was updated

update

public java.lang.Object update(java.sql.Connection c,
                               java.lang.Object o)

update an object in the database

Use this method for single updates. In cases where several subsequent updates for objects of the same class are required the use of method update(Connection, Object, Record) is recommended instead to minimise instantiation overhead.

Parameters:

c - the connection to use, expected to be open and established

o - object to update

Returns:

the object that was updated

update

public java.lang.Object update(java.lang.Object o,
                               Record record)

update an object in the database

Parameters:

o - object to update

record - reference to object to use to map between object and database

Returns:

the object that was updated

update

public java.lang.Object update(java.sql.Connection c,
                               java.lang.Object o,
                               Record record)

update an object in the database

Parameters:

c - the connection to use, expected to be open and established

o - object to update

record - reference to object to use to map between object and database

Returns:

the object that was updated

select

public java.util.List<java.lang.Object> select(java.lang.String sql,
                                               Record record)

Select a list of objects through a given SQL statement

Parameters:

sql - sql query string that designates the requested objects

record - object to use to map db records to objects

Returns:

a list of objects that match the given query

select

public java.util.List<java.lang.Object> select(java.lang.String sql,
                                               Record record,
                                               boolean includeBlobs)

Select a list of objects through a given SQL statement

Parameters:

sql - sql query string that designates the requested objects

record - object to use to map db records to objects

includeBlobs - true when BLOB contents are to be retrieved, false if not

Returns:

a list of objects that match the given query

select

public java.util.List<java.lang.Object> select(java.lang.String sql,
                                               Record record,
                                               boolean includeBlobs,
                                               java.lang.Object... params)

Select a list of objects through a given SQL statement

Parameters:

sql - sql query string that designates the requested objects

record - object to use to map db records to objects

includeBlobs - true when BLOB contents are to be retrieved, false if not

params - list of parameters in the order they appear in the SQL string

Returns:

a list of objects that match the given query

select

public java.util.List<java.lang.Object> select(java.sql.Connection c,
                                               java.lang.String sql,
                                               Record record,
                                               boolean includeBlobs)

Select a list of objects that match a given SQL statement

Parameters:

c - the database connection to use for this query, expected to be established and open already

sql - sql query string that designates the requested objects

record - object to use to map db records to objects

includeBlobs - true when BLOB contents are to be retrieved, false if not

Returns:

a list of objects that match the given query

select

public java.util.List<java.lang.Object> select(java.sql.Connection c,
                                               java.lang.String sql,
                                               Record record,
                                               boolean includeBlobs,
                                               java.lang.Object... params)

Select a list of objects that match a given SQL statement

Parameters:

c - the database connection to use for this query, expected to be established and open already

sql - sql query string that designates the requested objects

record - object to use to map db records to objects

includeBlobs - true when BLOB contents are to be retrieved, false if not

params - list of parameters in the order they appear in the SQL string

Returns:

a list of objects that match the given query

select

public java.util.List<java.util.Map<java.lang.String,java.lang.Object>> select(java.lang.String sql)

Select a list of objects that match a given SQL statement

Parameters:

sql - sql query string that designates the requested objects

Returns:

a list of map objects, one for each record. An element in the list can be accessed with list.get(recordno).get("fieldname")

select

public java.util.List<java.util.List<java.lang.String>> select(java.lang.String sql,
                                                               boolean includeBlobs)

Select a list of objects that match a given SQL statement

Parameters:

sql - sql query string that designates the requested objects

includeBlobs - true when content of blob coloumns should be returned, false if not

Returns:

a list of list objects, one for each record. An element in the list can be accessed with list.get(recordno).get(fieldno), each element is of type String

select

public java.util.List<java.util.Map<java.lang.String,java.lang.Object>> select(java.sql.Connection c,
                                                                               java.lang.String sql)

Select a list of objects that match a given SQL statement

Parameters:

c - the database connection to use for this query, expected to be established and open already

sql - sql query string that designates the requested objects

Returns:

a list of map objects, one for each record. An element in the list can be accessed with list.get(recordno).get("fieldname")

select

public java.util.List<java.util.List<java.lang.String>> select(java.sql.Connection c,
                                                               java.lang.String sql,
                                                               boolean includeBlobs)

Select a list of objects that match a given SQL statement

Parameters:

c - the database connection to use for this query, expected to be established and open already

sql - sql query string that designates the requested objects

includeBlobs - true when content of blob coloumns should be returned, false if not

Returns:

a list of list objects, one for each record. An element in the list can be accessed with list.get(recordno).get(fieldno), each element is of type String

select

public java.util.List<java.util.List<java.lang.String>> select(java.lang.String sql,
                                                               boolean includeBlobs,
                                                               java.lang.Object... params)

Select a list of objects that match a given SQL statement

Parameters:

sql - sql query string that designates the requested objects with ? at the position of params

includeBlobs - true when content of blob coloumns should be returned, false if not

params - list of parameters in the order they appear in the SQL string

Returns:

a list of list objects, one for each record. An element in the list can be accessed with list.get(recordno).get(fieldno), each element is of type String

select

public java.util.List<java.util.List<java.lang.String>> select(java.sql.Connection c,
                                                               java.lang.String sql,
                                                               boolean includeBlobs,
                                                               java.lang.Object... params)

Select a list of objects that match a given SQL statement

Parameters:

c - the database connection to use for this query, expected to be established and open already

sql - sql query string that designates the requested objects with ? at the position of params

includeBlobs - true when content of blob coloumns should be returned, false if not

params - list of parameters in the order they appear in the SQL string

Returns:

a list of list objects, one for each record. An element in the list can be accessed with list.get(recordno).get(fieldno), each element is of type String

executeWithKeys

public java.util.List<java.util.Map<java.lang.String,java.lang.Object>> executeWithKeys(java.lang.String sql)

Execute an SQL statement and return keys generated in the database

Parameters:

sql - the statement to execute

Returns:

a list of generated keys

executeWithKeys

public java.util.List<java.util.Map<java.lang.String,java.lang.Object>> executeWithKeys(java.sql.Connection c,
                                                                                        java.lang.String sql)

Execute an SQL statement and return keys generated in the database

Parameters:

c - database connection to use

sql - the statement to execute

Returns:

a list of generated keys

execute

public int execute(java.lang.String sql)

Execute an SQL statement

Parameters:

sql - the statement to execute

Returns:

the number of records affected by this statement or -1 if none

execute

public int execute(java.sql.Connection c,
                   java.lang.String sql)

Execute an SQL statement

Parameters:

c - database connection to use

sql - the statement to execute

Returns:

the number of records affected by this statement or -1 if none

execute

public int execute(java.lang.String sql,
                   java.lang.Object... params)

Execute an SQL statement

Parameters:

sql - the SQL string with ? at the position of params

params - list of parameters in the order they appear in the SQL string

Returns:

the number of records affected by this statement or -1 if none

execute

public int execute(java.sql.Connection c,
                   java.lang.String sql,
                   java.lang.Object... params)

Execute an SQL statement

Parameters:

c - database connection to use

sql - the SQL string with ? at the position of params

params - list of parameters in the order they appear in the SQL string

Returns:

the number of records affected by this statement or -1 if none

executeScript

public int[] executeScript(java.lang.String sqlScript)

Execute an SQL script

Parameters:

sqlScript - the SQL script to execute

Returns:

an array of row counts referring to the number of affected rows of each sql statement in the script in the order of SQL commands in the script Statement.EXECUTE_FAILED indicates failure of single steps in the script Statement.SUCCESS_NO_INFO indicates successful execution without information about the number of affected rows

executeScript

public int[] executeScript(java.sql.Connection c,
                           java.lang.String sqlScript)

Execute an SQL script

Parameters:

c - the Connection object to use

sqlScript - the SQL script to execute

Returns:

an array of row counts referring to the number of affected rows of each sql statement in the script in the order of SQL commands in the script Statement.EXECUTE_FAILED indicates failure of single steps in the script Statement.SUCCESS_NO_INFO indicates successful execution without information about the number of affected rows

startTransaction

public void startTransaction(java.sql.Connection c)

commit

public void commit(java.sql.Connection c)

rollback

public void rollback(java.sql.Connection c)

closeResultSetFinally

public void closeResultSetFinally(java.sql.ResultSet rs)

Close a given result set

Parameters:

rs - the result set to close

closeStatementFinally

public void closeStatementFinally(java.sql.Statement s)

Close a given statement

Parameters:

s - the statement to close

closeConnectionFinally

public void closeConnectionFinally(java.sql.Connection c)

close a given connection

Parameters:

c - the connection to close

buildQuery

public java.sql.PreparedStatement buildQuery(java.sql.Connection c,
                                             java.lang.String sql,
                                             java.lang.Object[] params)
                                      throws java.lang.Exception

Create an SQL statement from an SQL string and an array of parameters

Parameters:

sql - the SQL string with ? at the position of params

params - list of parameters in the order they appear in the SQL string

Returns:

the SQL statement with given parameters applied

Throws:

java.lang.Exception

toList

public java.util.List<java.util.List<java.lang.String>> toList(java.sql.ResultSet rs,
                                                               boolean includeBlobs)
                                                        throws java.sql.SQLException

Helper method that converts a ResultSet into a list of lists, one per row, each row is a list of strings

Parameters:

rs - result set to convert

includeBlobs - true when blob columns should be returned, false if not

Returns:

a list of list objects, one for each record. An element in the list can be accessed with list.get(recordno).get(fieldno), each element is of type String. This first row has the field names

Throws:

java.sql.SQLException