oracle.ifs.core.util

Class DatabaseUtilities

java.lang.Object

oracle.ifs.core.util.DatabaseUtilities

public class DatabaseUtilities
extends Object

DatabaseUtilities contains utilities that perform various SQL operations.

Author:

Dave Long

(Published)

Method Summary

Modifier and Type	Method and Description
static void	addColumnIfNecessary(IfsConnection conn, String tableName, String columnName, String rdbmsDataType) Alter the specified table to add a new column.
static void	analyze(IfsConnection conn, String schemaName) Analyze a schema.
static void	analyzeTable(IfsConnection conn, String schemaName, String tableName) Analyze a table in a schema.
static void	analyzeTable(LibrarySession session, String tableName) Analyze a single table in the IFS schema.
static void	closeResultSet(ResultSet rset) Close a ResultSet, ignoring any Exceptions.
static void	closeStatement(StatementWrapper stmt) Close a StatementWrapper, ignoring any Exceptions.
static boolean	columnExists(IfsConnection conn, String tableName, String columnName) Return true if the specified column exists in the specified table.
static boolean	columnExists(LibrarySession session, String tableName, String columnName) Return true if the specified column exists in the specified table.
static void	commit(IfsConnection conn) Issue a commit.
static void	createTable(IfsConnection conn, String tableName, String createStmt) Create a table.
static void	createTable(IfsConnection conn, String tableName, String createStmt, boolean dropFirst) Create a table, optionally attempting to drop the table first.
static void	createTable(LibrarySession session, String tableName, String createStmt) Create a table.
static void	createTable(LibrarySession session, String tableName, String createStmt, boolean dropFirst) Create a table, optionally attempting to drop the table first.
static void	dropTable(IfsConnection conn, String tableName) Drop a table.
static void	dropTable(LibrarySession session, String tableName) Drop a table.
static void	executeCall(LibrarySession session, String sql) Execute a PL/SQL call.
static void	executeStatement(IfsConnection conn, String stmtString) Execute any SQL statement
static void	executeStatement(LibrarySession session, String stmtString) Execute any SQL statement.
static int	executeUpdate(IfsConnection conn, String stmtString) Execute an update statement.
static int	executeUpdate(LibrarySession session, String stmtString) Execute an update, delete, or insert statement.
static String	getColumnList(String[] cols) Return the concatenation of the specified columns as a comma-separated column list.
static String	getUpdateSql(String tableName, String[] pkCols, String updateClause) Return the SQL used to update row that can be identified uniquely.
static String	getUpdateWhereClause(String[] cols) Return the clause to use in the SQL update's where clause to uniquely identify the row to be updated.
static boolean	isValidConnection(IfsConnection conn) Test validity of a IfsConnection
static void	performAnalyze(LibrarySession session) Perform ANALYZE on the IFS schema.
static void	performAnalyzeOracle(LibrarySession session) Execute the ANALYZE action for Oracle.
static void	performAnalyzePostgres(LibrarySession session) Execute the ANALYZE action for Postgres.
static void	renameColumn(IfsConnection conn, String tableName, String columnName, String newColumnName) Alter the specified table to rename a column.
static void	rollback(IfsConnection conn) Issue a rollback.
static boolean	rowExists(IfsConnection conn, String tableName, String condition) Return true if there is a row that meets the specified condition for the specified table.
static boolean	rowExists(LibrarySession session, String tableName, String condition) Return true if there is a row that meets the specified condition for the specified table.
static int	selectCount(IfsConnection conn, String tableName, String condition) Selects the row count that meets the specified condition for the specified table.
static int	selectCount(LibrarySession session, String tableName, String condition) Selects the row count that meets the specified condition for the specified table.
static long	selectMinOrMax(IfsConnection conn, String selClause, String tableName, String condition) Selects the min or max doc ID from the specified table.
static long	selectMinOrMax(LibrarySession session, String selClause, String tableName, String condition) Selects the min or max doc ID from the specified table.
static boolean	tableExists(IfsConnection conn, String tableName) Return true if the specified table exists.
static boolean	tableExists(LibrarySession session, String tableName) Return true if the specified table exists.

Methods inherited from class java.lang.Object

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

Method Detail

executeStatement

public static void executeStatement(LibrarySession session,
                                    String stmtString)
                             throws IfsException

Execute any SQL statement.

Parameters:

session - the session context

stmtString - the SQL to execute

Throws:

IfsException - if the operation fails

(Unpublished)

executeUpdate

public static int executeUpdate(LibrarySession session,
                                String stmtString)
                         throws IfsException

Execute an update, delete, or insert statement.

Returns the number of rows affected by the update, delete, or insert.

Parameters:

session - the session context

stmtString - the SQL to execute

Returns:

the number of rows affected

Throws:

IfsException - if the operation fails

(Unpublished)

executeCall

public static void executeCall(LibrarySession session,
                               String sql)
                        throws IfsException

Execute a PL/SQL call.

Parameters:

session - the session context

session - the SQL to execute in a CallableStatement

Throws:

IfsException - if operation fails

(Unpublished)

createTable

public static void createTable(LibrarySession session,
                               String tableName,
                               String createStmt)
                        throws IfsException

Create a table.

Parameters:

session - the session context

tableName - the name of the table

createStmt - the CREATE TABLE sql string

Throws:

IfsException - if the operation fails

(Unpublished)

createTable

public static void createTable(LibrarySession session,
                               String tableName,
                               String createStmt,
                               boolean dropFirst)
                        throws IfsException

Create a table, optionally attempting to drop the table first.

Parameters:

session - the session context

tableName - the name of the table

createStmt - the CREATE TABLE sql string

dropFirst - whether to attempt to drop the table first

Throws:

IfsException - if the operation fails

(Unpublished)

dropTable

public static void dropTable(LibrarySession session,
                             String tableName)
                      throws IfsException

Drop a table.

Does not throw an exception if the attempt fails.

Parameters:

session - the session context

tableName - the name of the table

Throws:

IfsException - if the operation fails

(Unpublished)

rowExists

public static boolean rowExists(LibrarySession session,
                                String tableName,
                                String condition)
                         throws IfsException

Return true if there is a row that meets the specified condition for the specified table. Throws if the table does not exist.

Parameters:

session - the session context

tableName - the table name

condition - the where clause

Returns:

true if the row exists; false otherwise

Throws:

IfsException - if the operation fails

(Unpublished)

selectCount

public static int selectCount(LibrarySession session,
                              String tableName,
                              String condition)
                       throws IfsException

Selects the row count that meets the specified condition for the specified table.

Throws if the table does not exist.

Parameters:

session - the session context

tableName - the table name

condition - the where clause

Throws:

IfsException - if the operation fails

(Unpublished)

selectMinOrMax

public static long selectMinOrMax(LibrarySession session,
                                  String selClause,
                                  String tableName,
                                  String condition)
                           throws IfsException

Selects the min or max doc ID from the specified table.

Parameters:

session - the session context

selClause - the min or max clause to be selected

tableName - the table name

condition - the where clause

Returns:

the min or max value

Throws:

IfsException - if the operation fails

(Unpublished)

performAnalyze

public static void performAnalyze(LibrarySession session)
                           throws IfsException

Perform ANALYZE on the IFS schema.

Parameters:

session - the session context

Throws:

IfsException - if operation fails

(Published)

performAnalyzeOracle

public static void performAnalyzeOracle(LibrarySession session)
                                 throws IfsException

Execute the ANALYZE action for Oracle.

Parameters:

session - the session context

Throws:

IfsException - if operation fails

(Published)

performAnalyzePostgres

public static void performAnalyzePostgres(LibrarySession session)
                                   throws IfsException

Execute the ANALYZE action for Postgres.

Parameters:

session - the session context

Throws:

IfsException - if operation fails

(Published)

analyzeTable

public static void analyzeTable(LibrarySession session,
                                String tableName)
                         throws IfsException

Analyze a single table in the IFS schema.

This is currently only supported on Oracle.

Parameters:

session - the session context

tableName - the name of the table to analyze

Throws:

IfsException - if the operation fails

(Unpublished)

tableExists

public static boolean tableExists(LibrarySession session,
                                  String tableName)
                           throws IfsException

Return true if the specified table exists.

Parameters:

session - the session context

tableName - the table name

Returns:

true if the table exists

Throws:

IfsException - if the operation fails

(Unpublished)

columnExists

public static boolean columnExists(LibrarySession session,
                                   String tableName,
                                   String columnName)
                            throws IfsException

Return true if the specified column exists in the specified table.

Parameters:

session - the session context

tableName - the table name

columnName - the column name

Returns:

true if the column exists in the specified table

Throws:

IfsException - if the operation fails

(Unpublished)

executeStatement

public static void executeStatement(IfsConnection conn,
                                    String stmtString)
                             throws SQLException

Execute any SQL statement

Parameters:

conn - the database connection

stmtString - the SQL to execute

Throws:

SQLException - if the operation fails

(Unpublished)

executeUpdate

public static int executeUpdate(IfsConnection conn,
                                String stmtString)
                         throws SQLException

Execute an update statement.

Returns the number of rows affected by the update.

Parameters:

conn - the database connection

stmtString - the SQL to execute

Returns:

the number of rows affected

Throws:

SQLException - if the operation fails

(Unpublished)

createTable

public static void createTable(IfsConnection conn,
                               String tableName,
                               String createStmt)
                        throws SQLException

Create a table.

Parameters:

conn - the database connection

tableName - the name of the table

createStmt - the CREATE TABLE sql string

Throws:

SQLException - if the operation fails

(Unpublished)

createTable

public static void createTable(IfsConnection conn,
                               String tableName,
                               String createStmt,
                               boolean dropFirst)
                        throws SQLException

Create a table, optionally attempting to drop the table first.

Parameters:

conn - the database connection

tableName - the name of the table

createStmt - the CREATE TABLE sql string

dropFirst - whether to attempt to drop the table first

Throws:

SQLException - if the operation fails

(Unpublished)

dropTable

public static void dropTable(IfsConnection conn,
                             String tableName)
                      throws SQLException

Drop a table.

Does not throw an exception if the attempt fails.

Parameters:

conn - the database connection

tableName - the name of the table

Throws:

SQLException - if the operation fails

(Unpublished)

rowExists

public static boolean rowExists(IfsConnection conn,
                                String tableName,
                                String condition)
                         throws SQLException

Return true if there is a row that meets the specified condition for the specified table. Throws if the table does not exist.

Parameters:

conn - the database connection

tableName - the table name

condition - the where clause

Returns:

true if the row exists; false otherwise

Throws:

SQLException - if the operation fails

(Unpublished)

selectCount

public static int selectCount(IfsConnection conn,
                              String tableName,
                              String condition)
                       throws SQLException

Selects the row count that meets the specified condition for the specified table.

Throws if the table does not exist.

Parameters:

conn - the database connection

tableName - the table name

condition - the where clause

Throws:

SQLException - if the operation fails

(Unpublished)

selectMinOrMax

public static long selectMinOrMax(IfsConnection conn,
                                  String selClause,
                                  String tableName,
                                  String condition)
                           throws SQLException

Selects the min or max doc ID from the specified table.

Parameters:

conn - the database connection

selClause - the min or max clause to be selected

tableName - the table name

condition - the where clause

Returns:

the min or max value

Throws:

SQLException - if the operation fails

(Unpublished)

tableExists

public static boolean tableExists(IfsConnection conn,
                                  String tableName)
                           throws SQLException

Return true if the specified table exists.

Parameters:

conn - the database connection

tableName - the table name

Returns:

true if the table exists

Throws:

SQLException - if the operation fails

(Unpublished)

columnExists

public static boolean columnExists(IfsConnection conn,
                                   String tableName,
                                   String columnName)
                            throws SQLException

Return true if the specified column exists in the specified table.

Parameters:

conn - the database connection

tableName - the table name

columnName - the column name

Returns:

true if the column exists

Throws:

SQLException - if the operation fails

(Unpublished)

addColumnIfNecessary

public static void addColumnIfNecessary(IfsConnection conn,
                                        String tableName,
                                        String columnName,
                                        String rdbmsDataType)
                                 throws SQLException

Alter the specified table to add a new column.

Parameters:

conn - database connection

tableName - the table name

columnName - the name of the new column to add

rdbmsDataType - the DBMS data type string (e.g. "NUMBER(20)" )

Throws:

SQLException - if the operation fails

renameColumn

public static void renameColumn(IfsConnection conn,
                                String tableName,
                                String columnName,
                                String newColumnName)
                         throws SQLException

Alter the specified table to rename a column.

Parameters:

conn - database connection

tableName - the table name

columnName - the name of the existing column

newColumnName - the new name for the column

Throws:

SQLException - if the operation fails

analyze

public static void analyze(IfsConnection conn,
                           String schemaName)
                    throws SQLException

Analyze a schema.

Parameters:

conn - the database connection

schemaName - the name of the schema to analyze

Throws:

SQLException - if the operation fails

(Unpublished)

analyzeTable

public static void analyzeTable(IfsConnection conn,
                                String schemaName,
                                String tableName)
                         throws SQLException

Analyze a table in a schema.

Parameters:

conn - the database connection

schemaName - the name of the schema containing the table

tableName - the name of the table to analyze

Throws:

SQLException - if the operation fails

(Unpublished)

commit

public static void commit(IfsConnection conn)
                   throws SQLException

Issue a commit.

Parameters:

conn - the database connection

Throws:

SQLException - if the operation fails

(Unpublished)

rollback

public static void rollback(IfsConnection conn)
                     throws SQLException

Issue a rollback.

Parameters:

conn - the database connection

Throws:

SQLException - if the operation fails

(Unpublished)

getColumnList

public static String getColumnList(String[] cols)

Return the concatenation of the specified columns as a comma-separated column list.

Parameters:

cols - the columns

Returns:

the column list as a comma separated String

getUpdateWhereClause

public static String getUpdateWhereClause(String[] cols)

Return the clause to use in the SQL update's where clause to uniquely identify the row to be updated.

Returns a String like: "COLX = ? and COLY = ? and COLZ = ?"

Parameters:

pkCols - the column names

Returns:

the Update where clause

getUpdateSql

public static String getUpdateSql(String tableName,
                                  String[] pkCols,
                                  String updateClause)

Return the SQL used to update row that can be identified uniquely.

Returns a String like: UPDATE tablename x set NEWCOLa = 0, NEWCOLb = 0 where PKCOL1 = ? and PKCOL2 = ?

Parameters:

tableName - the table name

pkCols - the primary key columns

updColClause - the update clause list; e.g. "NEWCOLa = 0, NEWCOLb = 0"

Returns:

the SQL string to use for the update

isValidConnection

public static boolean isValidConnection(IfsConnection conn)

Test validity of a IfsConnection

Parameters:

conn - the database connection

Returns:

true iff the connection is valid

(Unpublished)

closeResultSet

public static void closeResultSet(ResultSet rset)

Close a ResultSet, ignoring any Exceptions.

Parameters:

rset - the ResultSet to close

(Unpublished)

closeStatement

public static void closeStatement(StatementWrapper stmt)

Close a StatementWrapper, ignoring any Exceptions.

Parameters:

stmt - the statement to close

(Unpublished)