com.bea.p13n.util.jdbc
Class JdbcHelper

java.lang.Object
  |
  +--com.bea.p13n.util.jdbc.JdbcHelper

public class JdbcHelper

extends java.lang.Object

Method Summary

static void	close(java.sql.Connection con) Close a connection, ignoring any errors.
static void	close(java.sql.ResultSet rs) Close a result set, ignoring any errors.
static void	close(java.sql.ResultSet rs, java.sql.Statement stmt, java.sql.Connection con) Close a connection, statement and resultset, ignoring errors.
static void	close(java.sql.Statement stmt) Close a statement, ignoring any errors.
static void	commitConnection(java.sql.Connection con) Commits the java.sql.Connection that you have been using for CLOB operations.
static java.lang.String	getClob(java.sql.ResultSet rs, int index) Get the value of a CLOB column as a string.
static java.sql.Clob	getClobLocator(java.sql.Connection con, java.lang.String tableName, java.lang.String clobColumnName, java.lang.String whereClause) Retrieve the LOB locator for a CLOB.
static java.sql.Connection	getConnection(javax.sql.DataSource src, int retries) Get a connection from a DataSource with retry logic.
static java.sql.Connection	getConnection(javax.sql.DataSource src, int retries, long waitTime) Get a connection from a DataSource with retry and timeout logic.
static java.lang.String	getEmptyClobInitializer() Return the database specific keyword that must be used when inserting a row with an empty CLOB.
static JdbcHelper	getInstance()
static boolean	setClob(java.sql.PreparedStatement stmt, int index, java.sql.Clob clob, java.lang.String str) If there is a delegate, this method uses JdbcHelperDelegate.writeClobData()
static void	setClob(java.sql.PreparedStatement stmt, int index, java.lang.String str) Set the value of a CLOB column with a string.

Methods inherited from class java.lang.Object

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

Method Detail

getInstance

public static JdbcHelper getInstance()

getEmptyClobInitializer

public static java.lang.String getEmptyClobInitializer()
                                                throws java.sql.SQLException

Return the database specific keyword that must be used when inserting a row with an empty CLOB. This method uses the JdbcHelperDelegate. If no JdbcHelperDelegate is specified in application-config.xml then this method will return the empty String.

For example, the method returns EMPTY_CLOB() when an Oracle delegate is used.

Returns:

keyword required to insert an empty CLOB, return empty String if not supported, return empty String if no JdbcHelperDelegate is specified in application-config.xml

Throws:

java.sql.SQLException - on an error

getClobLocator

public static java.sql.Clob getClobLocator(java.sql.Connection con,
                                           java.lang.String tableName,
                                           java.lang.String clobColumnName,
                                           java.lang.String whereClause)
                                    throws java.sql.SQLException

Retrieve the LOB locator for a CLOB. The method creates a PreparedStatement from the specified parameters and gets the LOB locator from the ResultSet.

This method only returns a single CLOB locator. If the WHERE clause results in mutiple rows, only the CLOB from the first row is returned.

If no JdbcHelperDelegate is specified in application-config.xml then this method will return null.

WARNING: if using a delegate to stream data to a CLOB, then you should use Connection.setAutoCommit(false) before calling this method. This will make the CLOB available across multiple SQL statements.

Parameters:

con - the database connection

tableName - the name of the database table

clobColumnName - the name of the column holding the CLOB

whereClause - the where clause (without WHERE keyword)

Returns:

a CLOB locator, return null if the ResultSet is empty, return null if no JdbcHelperDelegate is specified in application-config.xml

Throws:

java.sql.SQLException - on an error

getConnection

public static java.sql.Connection getConnection(javax.sql.DataSource src,
                                                int retries,
                                                long waitTime)
                                         throws java.sql.SQLException

Get a connection from a DataSource with retry and timeout logic.

This will retry upto maxRetries. Additionally, it will only retry for waitTime number of milliseconds (as best as possible -- it might be longer, but will not be less).

This will always try at least once to get a connection. Use retries=0 for only a single attempt.

This works with both transactional and non-transaction DataSources tied to WLS connection pools. In WLS, transactional DataSources always internally wait 5 seconds for a new connection; non-transaction DataSources don't wait at all.

NOTE: The connection pool referenced by the given DataSource needs to be configured with the testConnOnReserve property set to true. This is necessary to ensure that in the event a SQLException is thrown, the cause of the exception is the unavailability of a pooled connection.

Parameters:

src - the data source.

retries - the maximum number of times to retry (cannot exceed maxRetries).

waitTime - the maximum number of milliseconds to try (cannot exceed maxWaitTime).

Returns:

a connection from the data source on success.

Throws:

java.sql.SQLException - thrown on an error getting a connection.

getConnection

public static java.sql.Connection getConnection(javax.sql.DataSource src,
                                                int retries)
                                         throws java.sql.SQLException

Get a connection from a DataSource with retry logic.

Parameters:

src - the data source.

retries - the maximum number of times to retry (cannot exceed maxRetries).

Returns:

a connection from the data source on success.

Throws:

java.sql.SQLException - thrown on an error getting a connection.

close

public static void close(java.sql.Connection con)

Close a connection, ignoring any errors.

Parameters:

con - The Connection object to be closed.

close

public static void close(java.sql.Statement stmt)

Close a statement, ignoring any errors.

Parameters:

stmt - The Statement object to be closed.

close

public static void close(java.sql.ResultSet rs)

Close a result set, ignoring any errors.

Parameters:

rs - The ResultSet object to be closed.

close

public static void close(java.sql.ResultSet rs,
                         java.sql.Statement stmt,
                         java.sql.Connection con)

Close a connection, statement and resultset, ignoring errors.

Parameters:

rs - The ResultSet object to be closed.

stmt - The Statement object to be closed.

con - The Connection object to be closed.

getClob

public static java.lang.String getClob(java.sql.ResultSet rs,
                                       int index)
                                throws java.sql.SQLException

Get the value of a CLOB column as a string.

This method will only return the value of the CLOB in the current row of the ResultSet. This method does not increment the ResultSet cursor. You must use ResultSet.next() to increment the cursor before calling this method. This allows the method to be used to process multi-row ResultSets.

If no JdbcHelperDelegate is specified in the console (configured via JdbcHelperMBean) then this method will return a string obtained using the default method that was used before a delegate model was implemented for this class. That method uses Clob.getCharacterStream() to construct a BufferedReader that is read in with the readLine() method.

Parameters:

rs - the result set.

index - the column index in the result set.

Returns:

the column as a string.

Throws:

java.sql.SQLException - thrown on an error.

setClob

public static void setClob(java.sql.PreparedStatement stmt,
                           int index,
                           java.lang.String str)
                    throws java.sql.SQLException

Set the value of a CLOB column with a string.

Parameters:

stmt - the prepared statement.

index - the column index.

str - the CLOB string.

Throws:

java.sql.SQLException - thrown on an error.

setClob

public static boolean setClob(java.sql.PreparedStatement stmt,
                              int index,
                              java.sql.Clob clob,
                              java.lang.String str)
                       throws java.sql.SQLException

If there is a delegate, this method uses JdbcHelperDelegate.writeClobData()

Parameters:

stmt - the prepared statement.

index - the column index.

clob - the CLOB locator (ignored if no delegate).

str - the String to be set in the CLOB (if delegate) or in the PreparedStatement (if no delegate)

Returns:

boolean executeRequired. If false then a delegate was used to directly set the CLOB in the database without using the PreparedStatement. If true then no delegate was used, or the delegate set the Clob value on the statement and the PreparedStatement must be executed to update the database.

Throws:

java.sql.SQLException - thrown on an error.

See Also:

If this is done, then the PreparedStatement does not need to be used and the return value, executeRequired, is false. The CLOB should be first located with getClobLocator(), If there is no delegate, then this method ignores the Clob argument and sets the value of the String into the CLOB placeholder in the PreparedStatement with setClob(PreparedStatement stmt, int index, String str) and returns executeRequired = true.,

WARNING: if using a delegate to stream data to a CLOB, then you should have used Connection.setAutoCommit(false) before calling the CLOB locator method that provided the Clob you are using in this method call. This will make the CLOB available across multiple SQL statements.

commitConnection

public static void commitConnection(java.sql.Connection con)
                             throws java.sql.SQLException

Commits the java.sql.Connection that you have been using for CLOB operations. A CLOB becomes inactive as soon as a transaction is committed. You must have auto-commit=false on your connection if you need to have a CLOB available across multiple SQL statements.

The delegate may implement this as a no-op method if it is illegal to do a Connection.commit() for the driver (e.g., jDriver for Oracle XA)

If there is no delegate, this method does a Connection.commit()

Parameters:

con - the Connection

Throws:

java.sql.SQLException - on an error