com.rli.util.djava

Class JDBCOperations

java.lang.Object

com.rli.util.djava.JDBCOperations

public class JDBCOperations
extends java.lang.Object

Field Summary

Fields

Modifier and Type	Field and Description
static int	DEFAULTFETCHSIZE
static int	NUMBEROFRETRIES
static long	TIME2WAITAFTERERR

Constructor Summary

Constructors

Constructor and Description
JDBCOperations(ConnString2 cs2)
JDBCOperations(ConnString2 cs2, GenericLogger logger) Use parameters from ConnString2
JDBCOperations(java.lang.String connectionString)
JDBCOperations(java.lang.String connectionString, boolean usepool, GenericLogger logger)
JDBCOperations(java.lang.String connectionString, GenericLogger logger)
JDBCOperations(java.lang.String driverName, java.lang.String url, java.lang.String user, java.lang.String password, int sqlHelperDbType)
JDBCOperations(java.lang.String driverName, java.lang.String url, java.lang.String user, java.lang.String password, int sqlHelperDbType, boolean usepool, GenericLogger logger)
JDBCOperations(java.lang.String driverName, java.lang.String url, java.lang.String user, java.lang.String password, int sqlHelperDbType, GenericLogger logger)

Method Summary

Modifier and Type	Method and Description
boolean	checkConnection(java.lang.String tableName) check if a connection is valid by accessing a given table used mainly to check if a pooled connection is still alive....
void	commit()
void	commitWithExceptionSupport()
java.sql.Statement	createStatement() Creates Statement using conn.
void	doConnect()
void	doConnectThrowException() perform connection, throws SQLException if error
void	doConnectWithExceptionSupport() doConnect with throws Exception if connection error
void	doDisconnect()
long	executeInsertAutokey(java.lang.String sqlDML, boolean commit)
long	executeInsertAutokey(java.lang.String sqlDML, boolean commit, java.util.List values)
java.sql.ResultSet	executeQuery(java.lang.String sqlCommand)
java.sql.ResultSet	executeQuery(java.lang.String sqlCommand, int maxRows)
java.sql.ResultSet	executeQuery(java.lang.String sqlCommand, java.util.List valuesNTypes)
java.sql.ResultSet	executeQuery(java.lang.String sqlDML, java.util.List<java.lang.String[]> valuesNTypes, int maxRows) execute query, will retry (x numretries) in case of connection error.
java.sql.ResultSet	executeQuery(java.lang.String sqlDML, java.util.List<java.lang.String[]> valuesNTypes, int maxRows, boolean isUsePst) centralized method to execute query.
java.sql.ResultSet	executeQueryAsStatement(java.lang.String sqlDML, java.util.List<java.lang.String[]> valuesNTypes, int maxRows)
boolean	executeScript(java.lang.String sql) cautious errors occur if ';' is used in a sql value do an executeUpdate on each sql separated with ';'
boolean	executeUpdate(java.lang.String sqlDML)
boolean	executeUpdate(java.lang.String sqlCommand, java.util.List valuesNtypes)
boolean	executeUpdate(java.lang.String sqlDML, java.util.List valuesNtypes, int maxRows)
boolean	executeUpdateAutoCommit(java.lang.String sqlDML, boolean autoCommit)
boolean	executeUpdateAutoCommit(java.lang.String sqlCommand, java.util.List valuesNtypes, boolean autoCommit)
boolean	executeUpdateAutoCommit(java.lang.String sqlDML, java.util.List valuesNtypes, int maxRows, boolean autoCommit)
boolean	executeUpdateWithExceptionSupport(java.lang.String sqlDML, java.util.List<java.lang.String[]> valuesNtypes, int maxRows)
boolean	executeUpdateWithExceptionSupport(java.lang.String sqlDML, java.util.List<java.lang.String[]> valuesNtypes, int maxRows, boolean isUsePst) centralized method to execute update
boolean	executeUpdateWithExceptionSupportAutoCommit(java.lang.String sqlDML, java.util.List<java.lang.String[]> valuesNtypes, int maxRows, boolean isUsePst, boolean autoCommit) centralized method to execute update
void	forceDisconnect()
void	forceDisconnectAndReleaseResources()
protected static java.lang.String	generateSqlForNullValues(java.lang.String sql, java.util.List<java.lang.String[]> valuesNTypes, boolean updateParameters) replace null value with "IS NULL" in SQL so prepared statement will work with null value in where clause.
boolean	getAutoCommit()
java.sql.Connection	getConn()
int	getDbType()
java.lang.String	getDriverName()
java.lang.Exception	getLastError()
java.lang.String	getLastUsedDriverName()
java.lang.String	getLastUsedPassword()
java.lang.String	getLastUsedUrl()
java.lang.String	getLastUsedUser()
java.sql.DatabaseMetaData	getMetaData()
java.lang.String	getParameterForStatement(java.lang.String type, java.lang.String value)
static java.lang.String	getSqlCmdWithParameterValues(java.lang.String sqlDML, java.util.List valuesNTypes) return full SQL command with attribute values provided.
int	getTransactionIsolation()
void	initializeConnection()
boolean	isConnected()
static boolean	isConnectionError(java.lang.Exception ex)
static boolean	isConnectionErrorWithCause(java.lang.Exception ex)
boolean	isForOracleAdministration()
boolean	isNoLogging()
protected void	log(java.lang.String message, int level)
protected void	log(java.lang.String message, java.lang.Throwable t)
protected void	log(java.lang.Throwable e)
static void	main(java.lang.String[] args)
static java.lang.String	queryWithValuesToString(java.lang.String query, java.util.List<java.lang.String[]> valuesNtypes) Get executed query with values of parameters
byte[]	readblob(java.lang.String sqlCommand, java.lang.String column)
void	reSetConn()
void	rollback()
void	setAutoCommit(boolean autocommit)
void	setDefaultPsCacheSize(int defaultPsCacheSize)
void	setForOracleAdministration(boolean forOracleAdministration)
void	setNoLogging(boolean noLogging)
void	setRetriesParam(int numberOfretries, long time2sleepBetweenretries)
void	setTransactionIsolation(int level)
void	setUsePool(boolean set)
static void	setValuesForPreparedStatement(java.lang.String driverName, java.sql.CallableStatement c, java.util.List<java.lang.String[]> valuesNtypes)
void	simpleClosePs()
void	simpleCloseStmt()
void	simpleDoConnect()
void	simpleDoConnectThrowException() perform connection, throws SQLException if error
void	simpleDoConnectWithExceptionSupport() doConnect with throws Exception if connection error
void	simpleDoDisconnect()
java.sql.ResultSet	simpleExecuteQuery(java.lang.String sqlCommand)
java.sql.ResultSet	simpleExecuteQuery(java.lang.String sqlCommand, int maxRows)
java.sql.ResultSet	simpleExecuteQuery(java.lang.String sqlCommand, java.util.List<java.lang.String[]> valuesNTypes)
java.sql.ResultSet	simpleExecuteQuery(java.lang.String sqlDML, java.util.List<java.lang.String[]> valuesNTypes, int maxRows)
java.sql.ResultSet	simpleExecuteQuery(java.lang.String sqlDML, java.util.List<java.lang.String[]> valuesNTypes, int maxRows, boolean isUsePst) centralized method to execute query.
boolean	supportsBatchUpdates() Deterimines if DB supports batch updates.
boolean	writeBinaryStream(java.lang.String sqlDML, java.io.InputStream fis, long size)

Methods inherited from class java.lang.Object

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

Field Detail

NUMBEROFRETRIES

public static int NUMBEROFRETRIES

TIME2WAITAFTERERR

public static long TIME2WAITAFTERERR

DEFAULTFETCHSIZE

public static int DEFAULTFETCHSIZE

Constructor Detail

JDBCOperations

public JDBCOperations(java.lang.String connectionString)

JDBCOperations

public JDBCOperations(java.lang.String connectionString,
                      GenericLogger logger)

Parameters:

connectionString -

JDBCOperations

public JDBCOperations(ConnString2 cs2)

JDBCOperations

public JDBCOperations(ConnString2 cs2,
                      GenericLogger logger)

Use parameters from ConnString2

Parameters:

cs2 -

JDBCOperations

public JDBCOperations(java.lang.String driverName,
                      java.lang.String url,
                      java.lang.String user,
                      java.lang.String password,
                      int sqlHelperDbType)

JDBCOperations

public JDBCOperations(java.lang.String driverName,
                      java.lang.String url,
                      java.lang.String user,
                      java.lang.String password,
                      int sqlHelperDbType,
                      GenericLogger logger)

Parameters:

driverName -

url -

user -

password -

sqlHelperDbType -

JDBCOperations

public JDBCOperations(java.lang.String connectionString,
                      boolean usepool,
                      GenericLogger logger)

Parameters:

connectionString -

usepool -

JDBCOperations

public JDBCOperations(java.lang.String driverName,
                      java.lang.String url,
                      java.lang.String user,
                      java.lang.String password,
                      int sqlHelperDbType,
                      boolean usepool,
                      GenericLogger logger)

Parameters:

driverName -

url -

user -

password -

sqlHelperDbType -

usepool - Oct 25, 2006

Method Detail

setRetriesParam

public void setRetriesParam(int numberOfretries,
                            long time2sleepBetweenretries)

checkConnection

public boolean checkConnection(java.lang.String tableName)

check if a connection is valid by accessing a given table used mainly to check if a pooled connection is still alive....

Parameters:

tableName -

Returns:

true- connection ok false- connection not ok (or wrong table name)

executeQuery

public java.sql.ResultSet executeQuery(java.lang.String sqlCommand)
                                throws java.sql.SQLException

Parameters:

sqlCommand -

Returns:

Throws:

java.lang.Exception

java.sql.SQLException

setAutoCommit

public void setAutoCommit(boolean autocommit)

Parameters:

autocommit -

getAutoCommit

public boolean getAutoCommit()

Parameters:

autocommit -

commit

public void commit()

commitWithExceptionSupport

public void commitWithExceptionSupport()
                                throws java.lang.Exception

Throws:

java.lang.Exception

rollback

public void rollback()

executeInsertAutokey

public long executeInsertAutokey(java.lang.String sqlDML,
                                 boolean commit)

executeInsertAutokey

public long executeInsertAutokey(java.lang.String sqlDML,
                                 boolean commit,
                                 java.util.List values)

executeQuery

public java.sql.ResultSet executeQuery(java.lang.String sqlCommand,
                                       int maxRows)
                                throws java.sql.SQLException

Parameters:

sqlCommand -

maxRows -

Returns:

Throws:

java.lang.Exception

java.sql.SQLException

executeQuery

public java.sql.ResultSet executeQuery(java.lang.String sqlCommand,
                                       java.util.List valuesNTypes)
                                throws java.sql.SQLException

Throws:

java.sql.SQLException

getSqlCmdWithParameterValues

public static java.lang.String getSqlCmdWithParameterValues(java.lang.String sqlDML,
                                                            java.util.List valuesNTypes)

return full SQL command with attribute values provided.

Parameters:

sqlDML -

valuesNTypes -

Returns:

executeQuery

public java.sql.ResultSet executeQuery(java.lang.String sqlDML,
                                       java.util.List<java.lang.String[]> valuesNTypes,
                                       int maxRows)
                                throws java.sql.SQLException

execute query, will retry (x numretries) in case of connection error. no retry for other exception.

Parameters:

sqlDML -

valuesNTypes -

maxRows -

Returns:

Throws:

java.sql.SQLException

java.sql.SQLException

executeQuery

public java.sql.ResultSet executeQuery(java.lang.String sqlDML,
                                       java.util.List<java.lang.String[]> valuesNTypes,
                                       int maxRows,
                                       boolean isUsePst)
                                throws java.sql.SQLException

centralized method to execute query. execute query, will retry (x numretries) in case of connection error. no retry for other exception.

Parameters:

sqlDML -

valuesNTypes -

maxRows -

isUsePst - do use PST (warning if some valuesNtypes is not empty, will use PST anyway)

Returns:

Throws:

java.sql.SQLException

isConnectionError

public static boolean isConnectionError(java.lang.Exception ex)

Parameters:

ex -

Returns:

isConnectionErrorWithCause

public static boolean isConnectionErrorWithCause(java.lang.Exception ex)

executeUpdate

public boolean executeUpdate(java.lang.String sqlDML)
                      throws java.sql.SQLException

Parameters:

sqlDML -

Returns:

Throws:

java.sql.SQLException

executeUpdate

public boolean executeUpdate(java.lang.String sqlCommand,
                             java.util.List valuesNtypes)
                      throws java.sql.SQLException

Throws:

java.sql.SQLException

executeUpdate

public boolean executeUpdate(java.lang.String sqlDML,
                             java.util.List valuesNtypes,
                             int maxRows)
                      throws java.sql.SQLException

Throws:

java.sql.SQLException

setValuesForPreparedStatement

public static void setValuesForPreparedStatement(java.lang.String driverName,
                                                 java.sql.CallableStatement c,
                                                 java.util.List<java.lang.String[]> valuesNtypes)
                                          throws java.sql.SQLException

Throws:

java.sql.SQLException

writeBinaryStream

public boolean writeBinaryStream(java.lang.String sqlDML,
                                 java.io.InputStream fis,
                                 long size)

Parameters:

sqlDML -

fis -

size -

Returns:

readblob

public byte[] readblob(java.lang.String sqlCommand,
                       java.lang.String column)
                throws java.sql.SQLException

Parameters:

sqlCommand -

column -

Returns:

Throws:

java.lang.Exception

java.sql.SQLException

initializeConnection

public void initializeConnection()

reSetConn

public void reSetConn()

doConnectWithExceptionSupport

public void doConnectWithExceptionSupport()
                                   throws java.lang.Exception

doConnect with throws Exception if connection error

Throws:

java.lang.Exception

doConnectThrowException

public void doConnectThrowException()
                             throws java.sql.SQLException

perform connection, throws SQLException if error

Throws:

java.sql.SQLException

doConnect

public void doConnect()

getLastUsedDriverName

public java.lang.String getLastUsedDriverName()

getLastUsedUrl

public java.lang.String getLastUsedUrl()

getLastUsedUser

public java.lang.String getLastUsedUser()

getLastUsedPassword

public java.lang.String getLastUsedPassword()

getMetaData

public java.sql.DatabaseMetaData getMetaData()
                                      throws java.sql.SQLException

Returns:

Throws:

java.sql.SQLException

doDisconnect

public void doDisconnect()

forceDisconnect

public void forceDisconnect()

forceDisconnectAndReleaseResources

public void forceDisconnectAndReleaseResources()

getLastError

public java.lang.Exception getLastError()

Parameters:

sqlCommand -

returnRS -

Throws:

java.lang.Exception

main

public static void main(java.lang.String[] args)

setDefaultPsCacheSize

public void setDefaultPsCacheSize(int defaultPsCacheSize)

executeUpdateWithExceptionSupport

public boolean executeUpdateWithExceptionSupport(java.lang.String sqlDML,
                                                 java.util.List<java.lang.String[]> valuesNtypes,
                                                 int maxRows,
                                                 boolean isUsePst)
                                          throws java.sql.SQLException

centralized method to execute update

Parameters:

sqlDML -

valuesNtypes -

maxRows -

isUsePst - do use PST (warning if some valuesNtypes is not empty, will use PST anyway)

Returns:

Throws:

java.sql.SQLException

executeUpdateWithExceptionSupport

public boolean executeUpdateWithExceptionSupport(java.lang.String sqlDML,
                                                 java.util.List<java.lang.String[]> valuesNtypes,
                                                 int maxRows)
                                          throws java.sql.SQLException

Throws:

java.sql.SQLException

getConn

public java.sql.Connection getConn()
                            throws java.lang.Exception

Throws:

java.lang.Exception

isConnected

public boolean isConnected()

executeScript

public boolean executeScript(java.lang.String sql)
                      throws java.lang.Exception

cautious errors occur if ';' is used in a sql value do an executeUpdate on each sql separated with ';'

Parameters:

sql -

Throws:

java.lang.Exception

generateSqlForNullValues

protected static java.lang.String generateSqlForNullValues(java.lang.String sql,
                                                           java.util.List<java.lang.String[]> valuesNTypes,
                                                           boolean updateParameters)

replace null value with "IS NULL" in SQL so prepared statement will work with null value in where clause. (not well supported) If values are removed see: http://forums.sun.com/thread.jspa?threadID=540698

Parameters:

sql - sql for prepared statement with '?' for parameters

valuesNTypes - list if values for pst String[value,sqlType]

updateParameters - remove null value replaced with "IS NULL " from valuesNTypes

Returns:

isForOracleAdministration

public boolean isForOracleAdministration()

setForOracleAdministration

public void setForOracleAdministration(boolean forOracleAdministration)

setUsePool

public void setUsePool(boolean set)

executeUpdateAutoCommit

public boolean executeUpdateAutoCommit(java.lang.String sqlDML,
                                       boolean autoCommit)
                                throws java.sql.SQLException

Throws:

java.sql.SQLException

executeUpdateAutoCommit

public boolean executeUpdateAutoCommit(java.lang.String sqlCommand,
                                       java.util.List valuesNtypes,
                                       boolean autoCommit)
                                throws java.sql.SQLException

Throws:

java.sql.SQLException

executeUpdateAutoCommit

public boolean executeUpdateAutoCommit(java.lang.String sqlDML,
                                       java.util.List valuesNtypes,
                                       int maxRows,
                                       boolean autoCommit)
                                throws java.sql.SQLException

Throws:

java.sql.SQLException

executeUpdateWithExceptionSupportAutoCommit

public boolean executeUpdateWithExceptionSupportAutoCommit(java.lang.String sqlDML,
                                                           java.util.List<java.lang.String[]> valuesNtypes,
                                                           int maxRows,
                                                           boolean isUsePst,
                                                           boolean autoCommit)
                                                    throws java.sql.SQLException

centralized method to execute update

Parameters:

sqlDML -

valuesNtypes -

maxRows -

isUsePst - do use PST (warning if some valuesNtypes is not empty, will use PST anyway)

Returns:

Throws:

java.sql.SQLException

getDriverName

public java.lang.String getDriverName()

getDbType

public int getDbType()

setTransactionIsolation

public void setTransactionIsolation(int level)

getTransactionIsolation

public int getTransactionIsolation()

simpleExecuteQuery

public java.sql.ResultSet simpleExecuteQuery(java.lang.String sqlDML,
                                             java.util.List<java.lang.String[]> valuesNTypes,
                                             int maxRows,
                                             boolean isUsePst)
                                      throws java.sql.SQLException

centralized method to execute query. execute query, will retry (x numretries) in case of connection error. no retry for other exception.

Parameters:

sqlDML -

valuesNTypes -

maxRows -

isUsePst - - ignored

Returns:

Throws:

java.sql.SQLException

simpleExecuteQuery

public java.sql.ResultSet simpleExecuteQuery(java.lang.String sqlCommand)
                                      throws java.sql.SQLException

Throws:

java.sql.SQLException

simpleExecuteQuery

public java.sql.ResultSet simpleExecuteQuery(java.lang.String sqlCommand,
                                             int maxRows)
                                      throws java.sql.SQLException

Throws:

java.sql.SQLException

simpleExecuteQuery

public java.sql.ResultSet simpleExecuteQuery(java.lang.String sqlCommand,
                                             java.util.List<java.lang.String[]> valuesNTypes)
                                      throws java.sql.SQLException

Throws:

java.sql.SQLException

simpleExecuteQuery

public java.sql.ResultSet simpleExecuteQuery(java.lang.String sqlDML,
                                             java.util.List<java.lang.String[]> valuesNTypes,
                                             int maxRows)
                                      throws java.sql.SQLException

Throws:

java.sql.SQLException

simpleDoConnectWithExceptionSupport

public void simpleDoConnectWithExceptionSupport()
                                         throws java.lang.Exception

doConnect with throws Exception if connection error

Throws:

java.lang.Exception

simpleDoConnectThrowException

public void simpleDoConnectThrowException()
                                   throws java.sql.SQLException

perform connection, throws SQLException if error

Throws:

java.sql.SQLException

simpleDoConnect

public void simpleDoConnect()

simpleDoDisconnect

public void simpleDoDisconnect()

simpleCloseStmt

public void simpleCloseStmt()

simpleClosePs

public void simpleClosePs()

executeQueryAsStatement

public java.sql.ResultSet executeQueryAsStatement(java.lang.String sqlDML,
                                                  java.util.List<java.lang.String[]> valuesNTypes,
                                                  int maxRows)
                                           throws java.sql.SQLException

Throws:

java.sql.SQLException

getParameterForStatement

public java.lang.String getParameterForStatement(java.lang.String type,
                                                 java.lang.String value)
                                          throws java.sql.SQLException

Throws:

java.sql.SQLException

log

protected void log(java.lang.Throwable e)

log

protected void log(java.lang.String message,
                   java.lang.Throwable t)

log

protected void log(java.lang.String message,
                   int level)

supportsBatchUpdates

public boolean supportsBatchUpdates()
                             throws java.sql.SQLException

Deterimines if DB supports batch updates. Based on DbBulkOperation.saveFirstChnunk(). Before calling this method, connect to DB using doConnect*() methods or exception will be thrown.

Returns:

true if DB supports batch updates; false - otherwise

Throws:

java.sql.SQLException

queryWithValuesToString

public static java.lang.String queryWithValuesToString(java.lang.String query,
                                                       java.util.List<java.lang.String[]> valuesNtypes)

Get executed query with values of parameters

Parameters:

query - - SQL query

valuesNtypes - - values and types for SQL query as required in execute*(String sql, List valuesNTypes, ...) methods of the class

createStatement

public java.sql.Statement createStatement()
                                   throws java.sql.SQLException

Creates Statement using conn. If conn is null, tries to establish connection. Returned Statement should be properly closed where createStatement() is called!

Returns:

Throws:

java.sql.SQLException

isNoLogging

public boolean isNoLogging()

setNoLogging

public void setNoLogging(boolean noLogging)