org.opensubsystems.core.util
Class DatabaseUtils

java.lang.Object
  org.opensubsystems.core.util.DatabaseUtils

public final class DatabaseUtils
extends java.lang.Object

Set of common utility methods related to database access.

Version:

$Id: DatabaseUtils.java,v 1.23 2007/01/07 06:14:01 bastafidli Exp $

Author:

Miro Halas

Code reviewer:

Miro Halas

Code reviewed:

1.21 2006/01/17 22:45:35 jlegeny

Field Summary

protected static java.util.Map

s_mpDependencyCache Hashed tables-columns existance information for dependency checking.

Method Summary

static void	closeResultSet(java.sql.ResultSet results) Gracefully close result set so that no error is generated.
static void	closeResultSetAndStatement(java.sql.ResultSet results, java.sql.Statement jdbcstatement) Gracefully close result set and statement so that no error is generated.
static void	closeStatement(java.sql.Statement jdbcstatement) Gracefully close statement so that no error is generated.
protected static int	estimateResultSetSize(java.sql.ResultSet rsQueryResults) Estimate the size of the result set so that data structures can be allocated efficiently.
static int	executeUpdateAndClose(java.sql.PreparedStatement jdbcstatement) Execute given statement and close the statement after it is done.
static java.util.List	getInListWithSafeLength(java.util.Collection idList, boolean bQuote) Because there is limitation for sql statement length (in sap db is default 64 kb) and in() expression can contain lot of members, this function will construct list of strings with maximum number of allowed members (representing ids) each.
static boolean	hasRelations(java.lang.String[][] arTableColumn, int iId) This method is for relation checking and for hashing table-column existance for optimalization.
static DataObject	loadAtMostOneData(DatabaseFactory factory, java.sql.PreparedStatement pstmQuery, java.lang.String strErrorMessage) Execute statement and load at most one data object from the result set and if the result set contains more than one item announce error.
static int	loadAtMostOneInt(java.sql.PreparedStatement pstmQuery, int iDefault, java.lang.String strErrorMessage) Execute statement and load at most one data object from the result set and if the result set contains more than one item announce error.
static java.lang.String	loadAtMostOneString(java.sql.PreparedStatement pstmQuery, java.lang.String strDefault, java.lang.String strErrorMessage) Execute statement and load at most one data object from the result set and if the result set contains more than one item announce error.
static java.util.List	loadMultipleData(DatabaseFactory factory, java.sql.PreparedStatement pstmQuery) Execute statement and load multiple data objects from the result set.
static java.util.Set	loadMultipleDataToSet(DatabaseFactory factory, java.sql.PreparedStatement pstmQuery, boolean bLoadSpecific) Execute statement and load multiple data objects from the result set.
static int[]	loadMultipleIntsAsArray(java.sql.PreparedStatement pstmQuery) Execute statement and load multiple int's from the result set.
static java.util.List	loadMultipleStrings(java.sql.PreparedStatement pstmQuery) Execute statement and load multiple strings from the result set.
static TwoIntStruct[]	loadMultipleTwoIntStruct(java.sql.PreparedStatement pstmQuery) Execute statement and load multiple TwoIntStruct objects from the result set.
static int[]	mergeColumnsSafely(int[] arrOriginalColumns, int[] arrExtraColumns) Merge new columns to the list of existing columns safely so if they already exist in the original list, they won't be added.
static void	rollbackAndIgnoreException(java.sql.Connection cntConnection) Rollback transaction and ingore any exception if it occurs.

Methods inherited from class java.lang.Object

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

Field Detail

s_mpDependencyCache

protected static java.util.Map s_mpDependencyCache

Hashed tables-columns existance information for dependency checking.

Method Detail

executeUpdateAndClose

public static int executeUpdateAndClose(java.sql.PreparedStatement jdbcstatement)
                                 throws java.sql.SQLException

Execute given statement and close the statement after it is done.

Parameters:

jdbcstatement - - statement to execute

Returns:

int - number of affected rows

Throws:

java.sql.SQLException - - only if the update fails, not in any other case

closeResultSet

public static void closeResultSet(java.sql.ResultSet results)

Gracefully close result set so that no error is generated. This method NEVER throws any exception therefore it is safe to call it in finally before returning connection.

Parameters:

results - - result set to close, if null it is ignored

closeResultSetAndStatement

public static void closeResultSetAndStatement(java.sql.ResultSet results,
                                              java.sql.Statement jdbcstatement)

Gracefully close result set and statement so that no error is generated. This method NEVER throws any exception therefore it is safe to call it in finally before returning connection.

Parameters:

results - - result set to close, if null it is ignored

jdbcstatement - - jdbc statement to close, if null it is ignored

closeStatement

public static void closeStatement(java.sql.Statement jdbcstatement)

Gracefully close statement so that no error is generated. This method NEVER throws any exception therefore it is safe to call it in finally before returning connection.

Parameters:

jdbcstatement - - jdbc statement to close, if null it is ignored

loadAtMostOneData

public static DataObject loadAtMostOneData(DatabaseFactory factory,
                                           java.sql.PreparedStatement pstmQuery,
                                           java.lang.String strErrorMessage)
                                    throws java.sql.SQLException,
                                           OSSException

Execute statement and load at most one data object from the result set and if the result set contains more than one item announce error. Note: Since the caller constructed the prepared statement, it is responsible for closing it.

Parameters:

factory - - factory used to load data

pstmQuery - - query to execute

strErrorMessage - - error message to announce if there is more than one item

Returns:

DataObject - loaded data object or null if the result set was empty

Throws:

java.sql.SQLException - - an error has occured

OSSException - - an error has occured

loadAtMostOneInt

public static int loadAtMostOneInt(java.sql.PreparedStatement pstmQuery,
                                   int iDefault,
                                   java.lang.String strErrorMessage)
                            throws java.sql.SQLException,
                                   OSSException

Execute statement and load at most one data object from the result set and if the result set contains more than one item announce error. Note: Since the caller constructed the prepared statement, it is responsible for closing it.

Parameters:

pstmQuery - - query to execute

iDefault - - default value to return if the result set doesn't contain any value

strErrorMessage - - error message to announce if there is more than one item

Returns:

int - loaded int or null if the result set was empty

Throws:

java.sql.SQLException - - an error has occured

OSSException - - an error has occured

loadAtMostOneString

public static java.lang.String loadAtMostOneString(java.sql.PreparedStatement pstmQuery,
                                                   java.lang.String strDefault,
                                                   java.lang.String strErrorMessage)
                                            throws java.sql.SQLException,
                                                   OSSException

Execute statement and load at most one data object from the result set and if the result set contains more than one item announce error. Note: Since the caller constructed the prepared statement, it is responsible for closing it.

Parameters:

pstmQuery - - query to execute

strDefault - - default value to return if the result set doesn't contain any value

strErrorMessage - - error message to announce if there is more than one item

Returns:

String - loaded string or null if the result set was empty

Throws:

java.sql.SQLException - - an error has occured

OSSException - - an error has occured

loadMultipleData

public static java.util.List loadMultipleData(DatabaseFactory factory,
                                              java.sql.PreparedStatement pstmQuery)
                                       throws java.sql.SQLException,
                                              OSSException

Execute statement and load multiple data objects from the result set. If there are no items null will be returned. Note: Since the caller constructed the prepared statement, it is responsible for closing it.

Parameters:

factory - - factory used to load data

pstmQuery - - query to execute

Returns:

List - list of loaded data objects or null if there were no results

Throws:

java.sql.SQLException - - an error has occured

OSSException - - an error has occured

loadMultipleDataToSet

public static java.util.Set loadMultipleDataToSet(DatabaseFactory factory,
                                                  java.sql.PreparedStatement pstmQuery,
                                                  boolean bLoadSpecific)
                                           throws java.sql.SQLException,
                                                  OSSException

Execute statement and load multiple data objects from the result set. If there are no items null will be returned. Note: Since the caller constructed the prepared statement, it is responsible for closing it.

Parameters:

factory - - factory used to load data

pstmQuery - - query to execute

bLoadSpecific - - flag signaling if there will be loaded specific data false ... all columns will be loaded from resultset and set up whole object to the HashSet true ... 1st column will be loaded from resultset and set up only particular data to the HashSet

Returns:

Set - set of loaded data objects or null if there were no results

Throws:

java.sql.SQLException - - an error has occured

OSSException - - an error has occured

loadMultipleStrings

public static java.util.List loadMultipleStrings(java.sql.PreparedStatement pstmQuery)
                                          throws java.sql.SQLException,
                                                 OSSException

Execute statement and load multiple strings from the result set. If there are no results null will be returned. Note: Since the caller constructed the prepared statement, it is responsible for closing it.

Parameters:

pstmQuery - - query to execute

Returns:

List - list of loaded strings or null if there were no results

Throws:

java.sql.SQLException - - an error has occured

OSSException - - an error has occured

loadMultipleIntsAsArray

public static int[] loadMultipleIntsAsArray(java.sql.PreparedStatement pstmQuery)
                                     throws java.sql.SQLException,
                                            OSSException

Execute statement and load multiple int's from the result set. If there are no results null will be returned. Note: Since the caller constructed the prepared statement, it is responsible for closing it.

Parameters:

pstmQuery - - query to execute

Returns:

int[] - list of loaded int's or null if there were no results

Throws:

java.sql.SQLException - - an error has occured

OSSException - - an error has occured

loadMultipleTwoIntStruct

public static TwoIntStruct[] loadMultipleTwoIntStruct(java.sql.PreparedStatement pstmQuery)
                                               throws java.sql.SQLException,
                                                      OSSException

Execute statement and load multiple TwoIntStruct objects from the result set. If there are no results null will be returned. Note: Since the caller constructed the prepared statement, it is responsible for closing it.

Parameters:

pstmQuery - - query to execute

Returns:

TwoIntStruct[] - list of loaded int's or null if there were no results

Throws:

java.sql.SQLException - - an error has occured

OSSException - - an error has occured

hasRelations

public static boolean hasRelations(java.lang.String[][] arTableColumn,
                                   int iId)
                            throws OSSException

This method is for relation checking and for hashing table-column existance for optimalization. Basically what it can do for you is if you pass it a set of tables and column names which can refer to your table it will check if the specified id (your id) is in any of those tables (and therefore if there is anyone refering to you). If some of the tables doesn't exist, this method will remember it and it won't check the table anymore. This can be used to break dependency on database level when you can configure in configuration file tables/columns which refer to some object and then the object can load this information from configuration file and check if there are any data refering to it. If those tables do not exist (e.g. because components containing those tables are not deployed than this method will correctly handle it)

Parameters:

arTableColumn - - array of {table name, column name} arrays to check relation to

iId - - id of the object to check relations

Returns:

boolean - true if relations exist

Throws:

OSSException - - error during check

rollbackAndIgnoreException

public static void rollbackAndIgnoreException(java.sql.Connection cntConnection)

Rollback transaction and ingore any exception if it occurs. The rollback will be performed only if this is a single operation which is not managed as part of multiconnection transaction.

Parameters:

cntConnection - - connection to rollback if necessary

mergeColumnsSafely

public static int[] mergeColumnsSafely(int[] arrOriginalColumns,
                                       int[] arrExtraColumns)

Merge new columns to the list of existing columns safely so if they already exist in the original list, they won't be added.

Parameters:

arrOriginalColumns - - list of original columns

arrExtraColumns - - new columns to add to the list

Returns:

int[] - if the list already contained the columns, it will return the same list otherwise it will return new list with extra columns

getInListWithSafeLength

public static java.util.List getInListWithSafeLength(java.util.Collection idList,
                                                     boolean bQuote)

Because there is limitation for sql statement length (in sap db is default 64 kb) and in() expression can contain lot of members, this function will construct list of strings with maximum number of allowed members (representing ids) each. Strings do not contain ().

Parameters:

idList - - collection with ids (as Objects)

bQuote - - flag signaling if there will be used quotes when string items will be served - true = there will be used quotes; false = otherwise

Returns:

List - list of strings with safe length

estimateResultSetSize

protected static int estimateResultSetSize(java.sql.ResultSet rsQueryResults)
                                    throws java.sql.SQLException,
                                           OSSException

Estimate the size of the result set so that data structures can be allocated efficiently.

Parameters:

rsQueryResults - - resultset of size of which to estimate

Returns:

int - if the size can be estimated it returns the size, otherwise it will return -1

Throws:

java.sql.SQLException - - an error has occured

OSSException - - an error has occured