|
|||||||||
PREV CLASS NEXT CLASS | FRAMES NO FRAMES All Classes | ||||||||
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD |
public interface ISqlWorker
ISqlWorker interface simplifies raw access and execution of SQL queries. Hides from the user all stuff related to managing connections, statements, etc.
This interface will usually provide functions for 99% of all tasks related to db operations.
Many of the functions in this interface in addition to sql query string also take Object array
of parameters and object array of macros. The parameters array contain input parameters
for query or stored procedure. The actualy values are normal Java objects like String, Integer, Long,
Boolean, etc. When parameter is NULL, then special constants of ISqlWorker
interface should be
used in place, which is guaranteed to work on any JDBC driver. It is also safe to just use Java null (depending on driver)
Here is an example of query strings that might be used:
All ResultSet
resultset objects returned are automatically closed by SqlWorker on
ISqlWorker.release()
Nested Class Summary | |
---|---|
static class |
ISqlWorker.Null
Null class that defines type information for null object |
Field Summary | |
---|---|
static Object |
BOOLEAN_NULL
Identifies NULL Boolean |
static Object |
BYTE_NULL
Identifies NULL Byte |
static Object |
CHAR_NULL
Identifies NULL Char |
static Object |
INTEGER_NULL
Identifies NULL Integer |
static Object |
LONG_NULL
Identifies NULL Long |
static Object |
NULL
Identifies default NULL - will assume VARCHAR type |
static Object |
NUMERIC_NULL
Identifies NULL Numeric (all floats) |
static int |
SERVERTYPE_ANY
Supported database server types |
static int |
SERVERTYPE_CUSTOM
|
static String |
SERVERTYPE_CUSTOM_STR
|
static int |
SERVERTYPE_DB2
|
static String |
SERVERTYPE_DB2_STR
|
static int |
SERVERTYPE_DERBY
|
static String |
SERVERTYPE_DERBY_STR
|
static int |
SERVERTYPE_INFORMIX
|
static String |
SERVERTYPE_INFORMIX_STR
|
static int |
SERVERTYPE_MSSQL
|
static String |
SERVERTYPE_MSSQL_STR
|
static int |
SERVERTYPE_MYSQL
|
static String |
SERVERTYPE_MYSQL_STR
|
static int |
SERVERTYPE_ODBC
|
static String |
SERVERTYPE_ODBC_STR
|
static int |
SERVERTYPE_ORACLE
|
static String |
SERVERTYPE_ORACLE_STR
|
static int |
SERVERTYPE_SYBASE
|
static String |
SERVERTYPE_SYBASE_STR
|
static Object |
SHORT_NULL
Identifies NULL Short |
static Object |
VARCHAR_NULL
Identifies NULL VARCHAR type |
Method Summary | |
---|---|
void |
beginTran()
Begins transaction for encapsulated connection and increments current transaction count |
void |
commitTran()
Commits currently active transaction for encapsulated connection. |
ResultSet |
execute(String sql,
Object[] parameters,
Object[] macros)
Execute sql formatted with parameters and macros that return ResultSet. |
void |
executeBlobUpdate(String tablename,
String fieldname,
byte[] blobvalue,
String keyfield,
Object keyvalue)
Executes update for Clob type of field. |
void |
executeClobUpdate(String tablename,
String fieldname,
String clobvalue,
String keyfield,
Object keyvalue)
Executes update for Clob type of field. |
ResultSet |
executeQuery(String sql)
Execute sql that return ResultSet |
ResultSet |
executeQuery(String sql,
Object[] parameters,
Object[] macros)
Execute sql formatted with parameters and macros that return ResultSet |
ResultSet |
executeQuery(String sql,
Object[] parameters,
Object[] macros,
int resultsettype,
int resultsetconcur)
Execute sql formatted with parameters and macros that return ResultSet |
ResultSet |
executeQuery(String sql,
Object[] parameters,
Object[] macros,
int resultsettype,
int resultsetconcur,
int fetchSize)
Execute sql formatted with parameters and macros that return ResultSet |
Object[] |
executeQueryProcOut(String strProc,
Object[] parameters,
int[] outParamTypes)
Execute stored procedure that returns output parameters |
ResultSet |
executeQueryProcResult(String strProc,
Object[] parameters)
Execute stored procedure or package that returns result set |
ResultSet |
executeQueryProcResult(String strProc,
Object[] parameters,
boolean assumePackageName)
Executes stored procedure by name, returning result set Important Oracle comment: Procedure that returns Cursor (aka ResultSet) must be declared within package and when called package name should be specified if assumePackageName = false, if assumePackageName = true, the package name is assumed equals to stored proc name. |
Object[] |
executeQueryProcResultOut(String strProc,
Object[] parameters,
int[] outParamTypes)
Deprecated. doesn't work in SQL |
int |
executeUpdate(String sql)
Execute sql that doesn't return ResultSet or update statements |
int |
executeUpdate(String sql,
Object[] parameters,
Object[] macros)
Execute statement that does not return result set or an update statement |
int |
executeUpdate(String sql,
Object[] parameters,
Object[] macros,
boolean requestIdentity)
Execute statement that does not return result set or an update statement |
int |
executeUpdate(String sql,
Object[] parameters,
Object[] macros,
boolean requestIdentity,
boolean handleError)
Execute statement that does not return result set or an update statement |
int |
executeUpdateProc(String strProc,
Object[] parameters)
Execute stored procedure that doesn't return anything. |
int |
getBitsetFromObject(Object o)
Returns a bitset from object translates based on DB |
int |
getBitsetFromResult(ResultSet rs,
int index)
Returns a bitset from a resultset entry in given DB of worker. |
Object |
getBitsetObject(int bitset)
Returns a Java Object that translates a int value into the appropriate object type depending on the DB system the worker is using |
int |
getBitsetOutputType()
Returns server storage type for bit set value. |
byte[] |
getBlob(ResultSet rs,
int column)
Returnes binary array for ResultSet column. |
String |
getClob(ResultSet rs,
int column)
Returnes String value for ResultSet clob column. |
Connection |
getConnection()
Returns encapsulated sql connection |
IDatabaseRuntime |
getDatabaseRuntime()
Returns current database runtime, may be null if there is no runtime associated |
int |
getLastInsertedIdentity()
Returns last inserted identity |
int |
getServerType()
Returns server type |
void |
init(ConnectionInfo connInfo)
Initializes sql worker connection. |
boolean |
isActiveTran()
If there is an active transaction |
boolean |
isDeadlockCode(int code)
Deprecated. Use #isDeadlockError(SQLException) instead |
boolean |
isDeadlockError(Exception e)
Determines if SQLException reports deadlock error |
void |
release()
Releases all resources aquired by SqlWorker, including database connection, result sets, prepares statements |
void |
rollbackTran()
Rollbacks currently active transaction for encapsulated connection. |
void |
setDatabaseRuntime(IDatabaseRuntime databaseRuntime)
Sets database runtime for this sql worker instance |
void |
setExternalTran(boolean isExternal)
Sets transaction flag |
Field Detail |
---|
static final Object VARCHAR_NULL
static final Object INTEGER_NULL
static final Object LONG_NULL
static final Object SHORT_NULL
static final Object BYTE_NULL
static final Object BOOLEAN_NULL
static final Object CHAR_NULL
static final Object NUMERIC_NULL
static final Object NULL
static final int SERVERTYPE_ANY
static final int SERVERTYPE_MSSQL
static final int SERVERTYPE_ORACLE
static final int SERVERTYPE_ODBC
static final int SERVERTYPE_DB2
static final int SERVERTYPE_INFORMIX
static final int SERVERTYPE_SYBASE
static final int SERVERTYPE_CUSTOM
static final int SERVERTYPE_MYSQL
static final int SERVERTYPE_DERBY
static final String SERVERTYPE_MSSQL_STR
static final String SERVERTYPE_ORACLE_STR
static final String SERVERTYPE_MYSQL_STR
static final String SERVERTYPE_ODBC_STR
static final String SERVERTYPE_DB2_STR
static final String SERVERTYPE_INFORMIX_STR
static final String SERVERTYPE_SYBASE_STR
static final String SERVERTYPE_CUSTOM_STR
static final String SERVERTYPE_DERBY_STR
Method Detail |
---|
void init(ConnectionInfo connInfo) throws SQLException
connInfo
- to initialize Sql worker for
SQLException
void release()
void beginTran() throws SQLException
SQLException
void commitTran() throws SQLException
SQLException
void rollbackTran() throws SQLException
SQLException
void setExternalTran(boolean isExternal)
boolean isActiveTran()
Connection getConnection()
int getServerType()
ISqlWorker.SERVERTYPE_MSSQL
or @ISqlWorker#SERVERTYPE_ORACLE
server typebyte[] getBlob(ResultSet rs, int column) throws SQLException
rs
- is source ResultSet that contains blob type columncolumn
- is column number for blob field
SQLException
String getClob(ResultSet rs, int column) throws SQLException
rs
- is source ResultSet that contains clob type columncolumn
- is column number for clob field
SQLException
int executeUpdate(String sql) throws SQLException, ServerBusyException, DeadlockException
sql
- is sql statement to be executed
SQLException
ServerBusyException
DeadlockException
int executeUpdate(String sql, Object[] parameters, Object[] macros) throws SQLException, ServerBusyException, DeadlockException
sql
- statement to be executedparameters
- is an Object array of input parameters for query. May be nullmacros
- is an Object array of macros to be used to format original query text. May be null
SQLException
ServerBusyException
DeadlockException
int executeUpdate(String sql, Object[] parameters, Object[] macros, boolean requestIdentity) throws SQLException, ServerBusyException, DeadlockException
sql
- statement to be executedparameters
- is an Object array of input parameters for query. May be nullmacros
- is an Object array of macros to be used to format original query text. May be nullrequestIdentity
- whether to request inserted identity value from the driver. If requested
the last returned identity could be obtained using ISqlWorker.getLastInsertedIdentity()
function
SQLException
ServerBusyException
DeadlockException
int executeUpdate(String sql, Object[] parameters, Object[] macros, boolean requestIdentity, boolean handleError) throws SQLException, ServerBusyException, DeadlockException
sql
- statement to be executedparameters
- is an Object array of input parameters for query. May be nullmacros
- is an Object array of macros to be used to format original query text. May be nullrequestIdentity
- whether to request inserted identity value from the driver. If requested
the last returned identity could be obtained using ISqlWorker.getLastInsertedIdentity()
function
SQLException
ServerBusyException
DeadlockException
ResultSet executeQuery(String sql, Object[] parameters, Object[] macros) throws SQLException, ServerBusyException, DeadlockException
sql
- sql query to be executedparameters
- is an Object array of input parameters for query. May be nullmacros
- is an Object array of macros to be used to format original query text. May be null
SQLException
ServerBusyException
DeadlockException
ResultSet executeQuery(String sql) throws SQLException, ServerBusyException, DeadlockException
sql
- sql query to be executed
SQLException
ServerBusyException
DeadlockException
ResultSet execute(String sql, Object[] parameters, Object[] macros) throws SQLException, ServerBusyException, DeadlockException
sql
- sql query to be executedparameters
- is an Object array of input parameters for query. May be nullmacros
- is an Object array of macros to be used to format original query text. May be null
SQLException
ServerBusyException
DeadlockException
ResultSet executeQuery(String sql, Object[] parameters, Object[] macros, int resultsettype, int resultsetconcur) throws SQLException, ServerBusyException, DeadlockException
sql
- sql query to be executedparameters
- is an Object array of input parameters for query. May be nullmacros
- is an Object array of macros to be used to format original query text. May be nullresultsettype
- see JDBC docs for details on thisresultsetconcur
- see JDBC docs for details on this
SQLException
ServerBusyException
DeadlockException
ResultSet executeQuery(String sql, Object[] parameters, Object[] macros, int resultsettype, int resultsetconcur, int fetchSize) throws SQLException, ServerBusyException, DeadlockException
sql
- sql query to be executedparameters
- is an Object array of input parameters for query. May be nullmacros
- is an Object array of macros to be used to format original query text. May be nullresultsettype
- see JDBC docs for details on thisresultsetconcur
- see JDBC docs for details on thisfetchSize
- desired fetch size
SQLException
ServerBusyException
DeadlockException
ResultSet executeQueryProcResult(String strProc, Object[] parameters) throws SQLException, ServerBusyException, DeadlockException
strProc
- name of the stored procedure to executeparameters
- is an Object array of input parameters for query. May be null
SQLException
ServerBusyException
DeadlockException
ResultSet executeQueryProcResult(String strProc, Object[] parameters, boolean assumePackageName) throws SQLException, ServerBusyException, DeadlockException
SQLException
ServerBusyException
DeadlockException
Object[] executeQueryProcResultOut(String strProc, Object[] parameters, int[] outParamTypes) throws SQLException, ServerBusyException, DeadlockException
SQLException
ServerBusyException
DeadlockException
Object[] executeQueryProcOut(String strProc, Object[] parameters, int[] outParamTypes) throws SQLException, ServerBusyException, DeadlockException
strProc
- name of the stored procedure to executeparameters
- is an Object array of input parameters for query. May be nulloutParamTypes
- array of output parameter types. See @Types interface for list of JDBC types
SQLException
ServerBusyException
DeadlockException
int executeUpdateProc(String strProc, Object[] parameters) throws SQLException, ServerBusyException, DeadlockException
strProc
- name of the stored procedure to executeparameters
- is an Object array of input parameters for query. May be null
SQLException
ServerBusyException
DeadlockException
void executeClobUpdate(String tablename, String fieldname, String clobvalue, String keyfield, Object keyvalue) throws SQLException, ServerBusyException, DeadlockException
tablename
- - table name with clob compatible field. For MS SQL Clob maps to text/ntext
data typesfieldname
- - name of the clob fieldclobvalue
- - value to writekeyfield
- of key field for the tablename. Usually this is PKkeyvalue
- for the key field
SQLException
ServerBusyException
DeadlockException
void executeBlobUpdate(String tablename, String fieldname, byte[] blobvalue, String keyfield, Object keyvalue) throws SQLException, ServerBusyException, DeadlockException
tablename
- - table name with clob compatible field. For MS SQL Blob maps to image
data typefieldname
- - name of the clob fieldblobvalue
- - value to write to the blob fieldkeyfield
- of key field for the tablename. Usually this is PKkeyvalue
- for the key field
SQLException
ServerBusyException
DeadlockException
boolean isDeadlockCode(int code)
#isDeadlockError(SQLException)
instead
code
- error code
boolean isDeadlockError(Exception e)
e
-
Object getBitsetObject(int bitset)
bitset
- 32-bit set
int getBitsetFromResult(ResultSet rs, int index) throws SQLException
rs
- current setindex
- field index
SQLException
- if EOF or bad indexint getBitsetFromObject(Object o)
o
- Integer in SQL, String in Oracleint getBitsetOutputType()
Types.VARCHAR
for Oracle
and Types.INTEGER
for SQL Server
IDatabaseRuntime getDatabaseRuntime()
int getLastInsertedIdentity()
void setDatabaseRuntime(IDatabaseRuntime databaseRuntime)
|
|||||||||
PREV CLASS NEXT CLASS | FRAMES NO FRAMES All Classes | ||||||||
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD |