org.athenasource.framework.unidbi
Class JDBCUtilities

java.lang.Object
  extended by org.athenasource.framework.unidbi.JDBCUtilities

public class JDBCUtilities
extends Object

JDBC Utilities.

Sample usage:

 // List of databases:

 


Constructor Summary
JDBCUtilities()
           
 
Method Summary
static void close(Connection connection)
          Closes the given connection.
static void close(ResultSet resultSet)
          Closes the given result set.
static void close(ResultSet resultSet, Statement statement)
          Closes the given result set and statement in order.
static void close(ResultSet resultSet, Statement statement, Connection connection)
          Closes the given result set, statement and connection in order.
static void close(Statement statement)
          Closes the given statement.
static String escapeSql(String str)
          Escapes the characters in a String to be suitable to pass to an SQL query.
static void execute(String[] sqls, Connection connection)
          Execute the given SQL statements in a transaction and commit.
static void execute(String[] sqls, Connection connection, boolean commitAfterExecution)
          Execute the given SQL statements in a transaction.
static int execute(String sql, Connection connection)
          Executes the given sql in the specified connection.
static Object execute(String sql, Statement stmt)
          Executes the given sql in the specified statement object.
static void executeMultipleStatements(String sqls, Connection connection)
          Execute the given string containing multiple SQL statements in a transaction and commit.
static void executeMultipleStatements(String sqls, Connection connection, boolean commitAfterExecution)
          Execute the given string containing multiple SQL statements in a transaction.
static Object executeStoredProc(String sql, Connection conn)
          Executes store procedure.
static long getOracleSequenceNextVal(Connection connection, String sequence)
          Gets the nextval of given oracle sequence as long.
static String[] parseMultiStatements(Reader reader)
          Parses multiple sql statements into individual sqls in an array.
static void setParameters(PreparedStatement pstmt, Object... values)
          Sets multiple parameters in bulk.
static int setParametersAndUpdate(PreparedStatement pstmt, Object... values)
          Sets the parameters and executeUpdate.
static String toString(ResultSet resultSet)
          Returns a string representation of the given result set.
static String toStringCurrentRow(ResultSet resultSet)
          Returns a string representation of the given result set.
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Constructor Detail

JDBCUtilities

public JDBCUtilities()
Method Detail

escapeSql

public static String escapeSql(String str)

Escapes the characters in a String to be suitable to pass to an SQL query.

For example,

statement.executeQuery("SELECT * FROM MOVIES WHERE TITLE='" +
   StringEscapeUtils.escapeSql("McHale's Navy") +
   "'");

At present, this method only turns single-quotes into doubled single-quotes ("McHale's Navy" => "McHale''s Navy"). It does not handle the cases of percent (%) or underscore (_) for use in LIKE clauses.

see http://www.jguru.com/faq/view.jsp?EID=8881

Parameters:
str - the string to escape, may be null
Returns:
a new String, escaped for SQL, null if null string input

getOracleSequenceNextVal

public static long getOracleSequenceNextVal(Connection connection,
                                            String sequence)
                                     throws SQLException
Gets the nextval of given oracle sequence as long.

This method is useful for INSERT into oracle table with mimic auto-increment primary key.

Parameters:
connection - the connection to be used - it will not be closed by this method.
sequence - the name of the sequence.
Returns:
the nextval of the sequence as long.
Throws:
SQLException - in case of any error.

toString

public static String toString(ResultSet resultSet)
                       throws SQLException
Returns a string representation of the given result set. The result set will be iterated to generated the string representation. It will not be closed. This method may consume considerable memory as it loads all the items in the result set into memory.

Parameters:
resultSet - the result set.
Returns:
a String table representing the result set.
Throws:
SQLException

toStringCurrentRow

public static String toStringCurrentRow(ResultSet resultSet)
                                 throws SQLException
Returns a string representation of the given result set. The result set will be iterated to generated the string representation. It will not be closed. This method may consume considerable memory as it loads all the items in the result set into memory.

Parameters:
resultSet - the result set.
Returns:
a String table representing the result set.
Throws:
SQLException

executeMultipleStatements

public static void executeMultipleStatements(String sqls,
                                             Connection connection)
                                      throws SQLException
Execute the given string containing multiple SQL statements in a transaction and commit.

Parameters:
sqls -
connection - the connection - it will not be closed.
commitAfterExecution - Whether the connection should be committed after the sqls have been executed.
Throws:
SQLException - will be logged and re-thrown.

executeMultipleStatements

public static void executeMultipleStatements(String sqls,
                                             Connection connection,
                                             boolean commitAfterExecution)
                                      throws SQLException
Execute the given string containing multiple SQL statements in a transaction.

Parameters:
sqls -
connection - the connection - it will not be closed.
commitAfterExecution - Whether the connection should be committed after the sqls have been executed.
Throws:
SQLException - will be logged and re-thrown.

execute

public static void execute(String[] sqls,
                           Connection connection)
                    throws SQLException
Execute the given SQL statements in a transaction and commit. If there is SQLException, it will be logged and re-thrown.

Parameters:
sqls -
connection - the connection - it will not be closed.
Throws:
SQLException - will be logged and re-thrown.

execute

public static void execute(String[] sqls,
                           Connection connection,
                           boolean commitAfterExecution)
                    throws SQLException
Execute the given SQL statements in a transaction. If there is SQLException, it will be logged and re-thrown.

Parameters:
sqls -
connection - the connection - it will not be closed.
commitAfterExecution - Whether the connection should be committed after the sqls have been executed.
Throws:
SQLException - will be logged and re-thrown.

execute

public static Object execute(String sql,
                             Statement stmt)
                      throws SQLException
Executes the given sql in the specified statement object. If there is SQLException, it will be logged and re-thrown. This method does not return anything, which only suits for executing updates.

Parameters:
sql -
stmt - the statement will not be closed.
Returns:
update count, result set or null depending on the query type.
Throws:
SQLException - will be logged and re-thrown.

execute

public static int execute(String sql,
                          Connection connection)
                   throws SQLException
Executes the given sql in the specified connection. A statement object will be created to execute the sql. This method does not return anything, which only suits for executing updates.

Parameters:
sql -
connection - the connection will not be closed.
Returns:
the udpate count or -1 if not applicable.
Throws:
SQLException - will be logged and re-thrown.

setParameters

public static void setParameters(PreparedStatement pstmt,
                                 Object... values)
                          throws SQLException
Sets multiple parameters in bulk.

Parameters:
pstmt - the prepare statement.
values - the values for each parameter.
Throws:
SQLException

setParametersAndUpdate

public static int setParametersAndUpdate(PreparedStatement pstmt,
                                         Object... values)
                                  throws SQLException
Sets the parameters and executeUpdate.

Parameters:
pstmt - the prepare statement.
values - the values for each parameter.
Returns:
the return from PreparedStatement.executeUpdate()
Throws:
SQLException

executeStoredProc

public static Object executeStoredProc(String sql,
                                       Connection conn)
                                throws SQLException
Executes store procedure. A store procedure begins with 'CALL' or '{ CALL' and with param declarations in [], e.g.: CALL SYSPROC.ALTOBJ('APPLY_CONTINUE_ON_ERROR', 'CREATE TABLE T1 (CL1 VARCHAR(5))', ?, ?) [1:Types.INTEGER:IN:-1, 1:Types.INTEGER:OUT, 2:Types.VARCHAR:OUT]

Returns:
update count, result set or null.
Throws:
SQLException
NumberFormatException

close

public static void close(ResultSet resultSet,
                         Statement statement,
                         Connection connection)
Closes the given result set, statement and connection in order. If any of the arguments is null, it will be skipped.

Parameters:
resultSet - the result set to be closed, or null.
statement - the statement to be closed, or null.
connection - the connection to be close, or null.

close

public static void close(ResultSet resultSet,
                         Statement statement)
Closes the given result set and statement in order. If any of the arguments is null, it will be skipped.

Parameters:
resultSet - the result set to be closed, or null.
statement - the statement to be closed, or null.

close

public static void close(ResultSet resultSet)
Closes the given result set.

Parameters:
resultSet - the result set to be closed, or null.

close

public static void close(Statement statement)
Closes the given statement.

Parameters:
statement - the statement to be closed, or null.

close

public static void close(Connection connection)
Closes the given connection.

Parameters:
connection - the connection to be closed, or null.

parseMultiStatements

public static String[] parseMultiStatements(Reader reader)
                                     throws IOException
Parses multiple sql statements into individual sqls in an array.

Parameters:
reader -
Returns:
Throws:
IOException


Copyright © 2008 - 2011 Athena Source. All Rights Reserved.