to.etc.webapp.qsql
Class JdbcUtil

java.lang.Object
  extended by to.etc.webapp.qsql.JdbcUtil

public class JdbcUtil
extends java.lang.Object

Utility class for JDBC code.

Preparation for calling SP's with TYPE xx IS RECORD parameters.

Datadict table ALL_PROCEDURES contains all SP's in packages. The parameters for SPs can be glanced from ALL_ARGUMENTS; something odd so far is that doing a selection:

 select * from sys.all_arguments where owner='DECADE' and package_name='GEBRUI' and object_name='LEES100';
 
returns data that seem to indicate that the SP exists as a 2-parameter version but also an expanded version having all parameter fields.

Author:
Frits Jalvingh Created on Dec 21, 2009

Method Summary
static boolean executeStatement(java.sql.Connection dbc, java.lang.String sql, java.lang.Object... args)
           
static
<T> T
oracleSpCall(java.sql.Connection con, java.lang.Class<T> rtype, java.lang.String sp, java.lang.Object... args)
          Provides interface to call Oracle stored procedures and functions.
static boolean oracleSpCallReturningBool(java.sql.Connection con, java.lang.String sp, java.lang.Object... args)
          Similar as oracleSpCall(Connection, Class, String, Object...), adjusted to handle returning of oracle boolean type properly.
static java.util.List<JdbcAnyRecord> queryAny(java.sql.Connection dbc, java.lang.String select, java.lang.Object... parameters)
           
static java.util.List<JdbcAnyRecord> queryAny(java.lang.String tblname, java.sql.ResultSet rs)
           
static JdbcAnyRecord queryAnyOne(java.sql.Connection dbc, java.lang.String select, java.lang.Object... parameters)
           
static JdbcAnyRecord queryAnyOne(java.lang.String tblname, java.sql.ResultSet rs)
           
static void readParameters(java.sql.PreparedStatement ps, int startindex, java.lang.Object[] params)
           
static
<T> T
selectOne(java.sql.Connection connection, java.lang.Class<T> clz, java.lang.String select, java.lang.Object... params)
          Quick method to select a single value of a given type from the database.
static
<T> java.util.List<T>
selectSingleColumnList(java.sql.Connection connection, java.lang.Class<T> clz, java.lang.String select, java.lang.Object... params)
          Quick method to select a single value of a given type from the database.
static void setDateTruncated(java.sql.PreparedStatement ps, int index, java.util.Date value)
          Sets a truncated date containing only the date part and a zero time.
static void setDouble(java.sql.PreparedStatement ps, int index, java.lang.Double value)
           
static void setFK(java.sql.PreparedStatement ps, int index, ILongIdentifyable foreigner)
           
static void setInteger(java.sql.PreparedStatement ps, int index, java.lang.Integer value)
           
static void setLong(java.sql.PreparedStatement ps, int index, java.lang.Long value)
           
static void setParameters(java.sql.PreparedStatement ps, int startindex, java.lang.Object[] params)
           
static void setString(java.sql.PreparedStatement ps, int index, java.lang.String value)
           
static void setStringTruncated(java.sql.PreparedStatement ps, int index, java.lang.String value, int maxlen)
           
static void setTimestamp(java.sql.PreparedStatement ps, int index, java.util.Date value)
          Sets a TIMESTAMP value containing both TIME and DATE values.
static void setYN(java.sql.PreparedStatement ps, int index, java.lang.Boolean value)
           
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Method Detail

setLong

public static void setLong(java.sql.PreparedStatement ps,
                           int index,
                           java.lang.Long value)
                    throws java.sql.SQLException
Throws:
java.sql.SQLException

setInteger

public static void setInteger(java.sql.PreparedStatement ps,
                              int index,
                              java.lang.Integer value)
                       throws java.sql.SQLException
Throws:
java.sql.SQLException

setDouble

public static void setDouble(java.sql.PreparedStatement ps,
                             int index,
                             java.lang.Double value)
                      throws java.sql.SQLException
Throws:
java.sql.SQLException

setTimestamp

public static void setTimestamp(java.sql.PreparedStatement ps,
                                int index,
                                java.util.Date value)
                         throws java.sql.SQLException
Sets a TIMESTAMP value containing both TIME and DATE values.

Parameters:
ps -
index -
value -
Throws:
java.sql.SQLException

setDateTruncated

public static void setDateTruncated(java.sql.PreparedStatement ps,
                                    int index,
                                    java.util.Date value)
                             throws java.sql.SQLException
Sets a truncated date containing only the date part and a zero time.

Parameters:
ps -
index -
value -
Throws:
java.sql.SQLException

setString

public static void setString(java.sql.PreparedStatement ps,
                             int index,
                             java.lang.String value)
                      throws java.sql.SQLException
Throws:
java.sql.SQLException

setStringTruncated

public static void setStringTruncated(java.sql.PreparedStatement ps,
                                      int index,
                                      java.lang.String value,
                                      int maxlen)
                               throws java.sql.SQLException
Throws:
java.sql.SQLException

setYN

public static void setYN(java.sql.PreparedStatement ps,
                         int index,
                         java.lang.Boolean value)
                  throws java.sql.SQLException
Throws:
java.sql.SQLException

setFK

public static void setFK(java.sql.PreparedStatement ps,
                         int index,
                         ILongIdentifyable foreigner)
                  throws java.sql.SQLException
Throws:
java.sql.SQLException

selectOne

public static <T> T selectOne(java.sql.Connection connection,
                              java.lang.Class<T> clz,
                              java.lang.String select,
                              java.lang.Object... params)
                   throws java.sql.SQLException
Quick method to select a single value of a given type from the database. Returns null if not found AND if the value was null...

Parameters:
connection -
clz -
select -
Returns:
Throws:
java.sql.SQLException

selectSingleColumnList

public static <T> java.util.List<T> selectSingleColumnList(java.sql.Connection connection,
                                                           java.lang.Class<T> clz,
                                                           java.lang.String select,
                                                           java.lang.Object... params)
                                                throws java.sql.SQLException
Quick method to select a single value of a given type from the database. Returns null if not found AND if the value was null...

Parameters:
connection -
clz -
select -
Returns:
Throws:
java.sql.SQLException

setParameters

public static void setParameters(java.sql.PreparedStatement ps,
                                 int startindex,
                                 java.lang.Object[] params)
                          throws java.sql.SQLException
Throws:
java.sql.SQLException

readParameters

public static void readParameters(java.sql.PreparedStatement ps,
                                  int startindex,
                                  java.lang.Object[] params)
                           throws java.sql.SQLException
Throws:
java.sql.SQLException

queryAny

public static java.util.List<JdbcAnyRecord> queryAny(java.sql.Connection dbc,
                                                     java.lang.String select,
                                                     java.lang.Object... parameters)
                                              throws java.sql.SQLException
Throws:
java.sql.SQLException

queryAny

public static java.util.List<JdbcAnyRecord> queryAny(java.lang.String tblname,
                                                     java.sql.ResultSet rs)
                                              throws java.sql.SQLException
Throws:
java.sql.SQLException

queryAnyOne

public static JdbcAnyRecord queryAnyOne(java.sql.Connection dbc,
                                        java.lang.String select,
                                        java.lang.Object... parameters)
                                 throws java.sql.SQLException
Throws:
java.sql.SQLException

queryAnyOne

public static JdbcAnyRecord queryAnyOne(java.lang.String tblname,
                                        java.sql.ResultSet rs)
                                 throws java.sql.SQLException
Throws:
java.sql.SQLException

executeStatement

public static boolean executeStatement(java.sql.Connection dbc,
                                       java.lang.String sql,
                                       java.lang.Object... args)
                                throws java.sql.SQLException
Throws:
java.sql.SQLException

oracleSpCall

public static <T> T oracleSpCall(java.sql.Connection con,
                                 java.lang.Class<T> rtype,
                                 java.lang.String sp,
                                 java.lang.Object... args)
                      throws java.sql.SQLException
Provides interface to call Oracle stored procedures and functions. In case that called function returns boolean use oracleSpCallReturningBool(Connection, String, Object...) Support all three type of parameters:

Type Parameters:
T - Oracle function return value type
Parameters:
con - Db connection
rtype - Oracle function return type
sp - Stored procedure / function name
args - Stored procedure / function parameters
Returns:
Oracle function return value in case of rtype != Void.class
Throws:
java.sql.SQLException

oracleSpCallReturningBool

public static boolean oracleSpCallReturningBool(java.sql.Connection con,
                                                java.lang.String sp,
                                                java.lang.Object... args)
                                         throws java.sql.SQLException
Similar as oracleSpCall(Connection, Class, String, Object...), adjusted to handle returning of oracle boolean type properly.

Parameters:
con -
sp -
args - For hanlding IN/OUT/IN OUT params see oracleSpCall(Connection, Class, String, Object...)
Returns:
Throws:
java.sql.SQLException