dk.netarkivet.common.utils
Class DBUtils

java.lang.Object
  dk.netarkivet.common.utils.DBUtils

public class DBUtils
extends java.lang.Object

Various database related utilities.

Constructor Summary

DBUtils()

Method Summary

static void	checkTableVersion(java.sql.Connection connection, java.lang.String tablename, int desiredVersion) Check that a database table is of the expected version.
static void	closeStatementIfOpen(java.sql.PreparedStatement s) Close a statement, if not closed already Note: This does not throw any a SQLException, because it is always called inside a finally-clause.
static void	executeSQL(java.sql.Connection connection, java.lang.String... updates) Update a database by executing all the statements in the updates String array.
static java.util.Date	getDateMaybeNull(java.sql.ResultSet rs, int columnIndex) Get a Date from a column in the resultset.
static long	getGeneratedID(java.sql.PreparedStatement s) Get the automatically generated key that was created with the just-executed statement.
static java.lang.Integer	getIntegerMaybeNull(java.sql.ResultSet rs, int i) Get an Integer from the resultSet in column i.
static java.lang.Long	getLongMaybeNull(java.sql.ResultSet rs, int i) Get a Long from the resultSet in column i.
static int	getTableVersion(java.sql.Connection connection, java.lang.String tablename) Returns the version of a table according to schemaversions, or 0 for the initial, unnumbered version.
static java.lang.String	getUsages(java.sql.Connection connection, java.lang.String select, java.lang.Object victim, java.lang.Object... args) Return a description of where an object is used elsewhere in the database, or null.
static java.lang.String	makeSQLGlob(java.lang.String glob) Translate a "normal" glob (with * and .) into SQL syntax.
static java.sql.PreparedStatement	prepareStatement(java.sql.Connection c, java.lang.String query, java.lang.Object... args) Prepare a statement given a query string and some args.
static void	rollbackIfNeeded(java.sql.Connection c, java.lang.String action, java.lang.Object o) Method to perform a rollback of complex DB updates.
static boolean	selectAny(java.sql.Connection connection, java.lang.String query, java.lang.Object... args) Execute an SQL query and return whether the result contains any rows.
static java.lang.Long	selectFirstLongValueIfAny(java.sql.Connection connection, java.lang.String query, java.lang.Object... args) Execute an SQL statement and return the first long in the result set, or null if resultset is empty.
static java.lang.Integer	selectIntValue(java.sql.Connection connection, java.lang.String query, java.lang.Object... args) Execute an SQL statement and return the single int in the result set.
static java.lang.Integer	selectIntValue(java.sql.PreparedStatement s) Execute an SQL statement and return the single integer in the result set.
static java.util.List<java.lang.Long>	selectLongList(java.sql.Connection connection, java.lang.String query, java.lang.Object... args) Execute an SQL statement and return the list of Long-objects in its result set.
static java.lang.Long	selectLongValue(java.sql.Connection connection, java.lang.String query, java.lang.Object... args) Execute an SQL statement and return the single long in the result set.
static java.lang.Long	selectLongValue(java.sql.PreparedStatement s) Execute an SQL statement and return the single long in the result set.
static java.util.List<java.lang.String>	selectStringList(java.sql.Connection connection, java.lang.String query, java.lang.Object... args) Execute an SQL statement and return the list of strings in its result set.
static java.util.Map<java.lang.String,java.lang.Long>	selectStringLongMap(java.sql.Connection connection, java.lang.String query, java.lang.Object... args) Execute an SQL statement and return the list of strings -> id mappings in its result set.
static java.lang.String	selectStringValue(java.sql.Connection connection, java.lang.String query, java.lang.Object... args) Execute an SQL statement and return the single string in the result set.
static java.lang.String	selectStringValue(java.sql.PreparedStatement s) Execute an SQL statement and return the single string in the result set.
static void	setClobMaxLength(java.sql.PreparedStatement s, int fieldNum, java.lang.String contents, long maxSize, java.lang.Object o, java.lang.String fieldName) Set the CLOB maxlength.
static void	setComments(java.sql.PreparedStatement s, int fieldNum, Named o, int maxFieldSize) Set the comments of a Named object into the given field of statement.
static void	setDateMaybeNull(java.sql.PreparedStatement s, int fieldNum, java.util.Date date) Set the Date into the given field of a statement.
static void	setIntegerMaybeNull(java.sql.PreparedStatement s, int i, java.lang.Integer value) Insert an Integer in prepared statement.
static void	setLongMaybeNull(java.sql.PreparedStatement s, int i, java.lang.Long value) Insert a long value (which could be null) into the given field of a statement.
static void	setName(java.sql.PreparedStatement s, int fieldNum, Named o, int maxFieldSize) Set the name of a Named object into the given field.
static void	setStringMaxLength(java.sql.PreparedStatement s, int fieldNum, java.lang.String contents, int maxSize, java.lang.Object o, java.lang.String fieldname) Set String Max Length.

Methods inherited from class java.lang.Object

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

Constructor Detail

DBUtils

public DBUtils()

Method Detail

selectIntValue

public static java.lang.Integer selectIntValue(java.sql.PreparedStatement s)

Execute an SQL statement and return the single integer in the result set.

Parameters:

s - A prepared statement

Returns:

The integer result, or null if the result value was null.

Throws:

IOFailure - if the statement didn't result in exactly one integer.

selectIntValue

public static java.lang.Integer selectIntValue(java.sql.Connection connection,
                                               java.lang.String query,
                                               java.lang.Object... args)

Execute an SQL statement and return the single int in the result set. This variant takes a query string and a single string arg and combines them to form a normal query. NB: the method does not close the provided connection.

Parameters:

connection - connection to database.

query - a query with ? for parameters (must not be null or empty string)

args - parameters of type string, int, long or boolean

Returns:

The integer result

Throws:

IOFailure - if the statement didn't result in exactly one integer

selectLongValue

public static java.lang.Long selectLongValue(java.sql.PreparedStatement s)

Execute an SQL statement and return the single long in the result set.

Parameters:

s - A prepared statement

Returns:

The long result, or null if the result was a null value Note that a null value is not the same as no result rows.

Throws:

IOFailure - if the statement didn't result in exactly one row with a long or null value

selectLongValue

public static java.lang.Long selectLongValue(java.sql.Connection connection,
                                             java.lang.String query,
                                             java.lang.Object... args)

Execute an SQL statement and return the single long in the result set. This variant takes a query string and a single string arg and combines them to form a normal query. NB: the provided connection is not closed.

Parameters:

connection - connection to database.

query - a query with ? for parameters (must not be null or empty string)

args - parameters of type string, int, long or boolean

Returns:

The long result

Throws:

IOFailure - if the statement didn't result in exactly one long value

selectFirstLongValueIfAny

public static java.lang.Long selectFirstLongValueIfAny(java.sql.Connection connection,
                                                       java.lang.String query,
                                                       java.lang.Object... args)

Execute an SQL statement and return the first long in the result set, or null if resultset is empty. NB: the provided connection is not closed.

Parameters:

connection - connection to database.

query - a query with ? for parameters (must not be null or empty string)

args - parameters of type string, int, long or boolean

Returns:

The long result, or will return null in one of the two following cases: There is no results, or the first result is a null-value.

Throws:

IOFailure - on SQL errors.

prepareStatement

public static java.sql.PreparedStatement prepareStatement(java.sql.Connection c,
                                                          java.lang.String query,
                                                          java.lang.Object... args)
                                                   throws java.sql.SQLException

Prepare a statement given a query string and some args. NB: the provided connection si not closed.

Parameters:

c - a Database connection

query - a query string (must not be null or empty)

args - some args to insert into this query string (must not be null)

Returns:

a prepared statement

Throws:

java.sql.SQLException - If unable to prepare a statement

ArgumentNotValid - If unable to handle type of one the args, or the arguments are either null or an empty String.

selectStringList

public static java.util.List<java.lang.String> selectStringList(java.sql.Connection connection,
                                                                java.lang.String query,
                                                                java.lang.Object... args)

Execute an SQL statement and return the list of strings in its result set. This uses specifically the harvester database. NB: the provided connection is not closed.

Parameters:

connection - connection to the database.

query - the given sql-query (must not be null or empty)

args - The arguments to insert into this query (must not be null)

Returns:

the list of strings in its result set

Throws:

IOFailure - If this query fails

selectStringLongMap

public static java.util.Map<java.lang.String,java.lang.Long> selectStringLongMap(java.sql.Connection connection,
                                                                                 java.lang.String query,
                                                                                 java.lang.Object... args)
                                                                          throws java.sql.SQLException

Execute an SQL statement and return the list of strings -> id mappings in its result set. NB: the provided connection si not closed.

Parameters:

connection - connection to the database.

query - the given sql-query (must not be null or empty string)

args - The arguments to insert into this query

Returns:

the list of strings -> id mappings

Throws:

java.sql.SQLException - If this query fails

selectLongList

public static java.util.List<java.lang.Long> selectLongList(java.sql.Connection connection,
                                                            java.lang.String query,
                                                            java.lang.Object... args)

Execute an SQL statement and return the list of Long-objects in its result set. NB: the provided connection is not closed.

Parameters:

connection - connection to the database.

query - the given sql-query (must not be null or empty string)

args - The arguments to insert into this query

Returns:

the list of Long-objects in its result set

getGeneratedID

public static long getGeneratedID(java.sql.PreparedStatement s)
                           throws java.sql.SQLException

Get the automatically generated key that was created with the just-executed statement.

Parameters:

s - A statement created with Statement.RETURN_GENERATED_KEYS

Returns:

The single generated key

Throws:

java.sql.SQLException - If a database access error occurs or the PreparedStatement is closed, or the JDBC driver does not support the setGeneratedKeys() method

getTableVersion

public static int getTableVersion(java.sql.Connection connection,
                                  java.lang.String tablename)
                           throws IOFailure

Returns the version of a table according to schemaversions, or 0 for the initial, unnumbered version. NB: the provided connection is not closed

Parameters:

connection - connection to the database.

tablename - The name of a table in the database.

Returns:

Version of the given table.

Throws:

IOFailure - if DB table schemaversions does not exist

setStringMaxLength

public static void setStringMaxLength(java.sql.PreparedStatement s,
                                      int fieldNum,
                                      java.lang.String contents,
                                      int maxSize,
                                      java.lang.Object o,
                                      java.lang.String fieldname)
                               throws java.sql.SQLException

Set String Max Length. If contents.length() > maxSize, contents is truncated to contain the first maxSize characters of the contents, and a warning is logged.

Parameters:

s - a Prepared Statement

fieldNum - a index into the above statement

contents - the contents

maxSize - the maximum size of field: fieldName

o - the Object, which assumedly have a field named fieldName

fieldname - the name of a given field

Throws:

java.sql.SQLException - if set operation fails

setComments

public static void setComments(java.sql.PreparedStatement s,
                               int fieldNum,
                               Named o,
                               int maxFieldSize)
                        throws java.sql.SQLException

Set the comments of a Named object into the given field of statement.

Parameters:

s - a prepared statement

fieldNum - the index of the given field to be set

o - the Named object

maxFieldSize - max size of the comments field

Throws:

java.sql.SQLException - If any trouble accessing the database during the operation

PermissionDenied - If length of o.getComments() is larger than Constants.MAX_COMMENT_SIZE

setName

public static void setName(java.sql.PreparedStatement s,
                           int fieldNum,
                           Named o,
                           int maxFieldSize)
                    throws java.sql.SQLException

Set the name of a Named object into the given field.

Parameters:

s - a prepared statement

fieldNum - the index of the given field to be set

o - the Named object

maxFieldSize - max size of the name field

Throws:

java.sql.SQLException - If any trouble accessing the database during the operation

PermissionDenied - If length of o.getName() is larger than Constants.MAX_NAME_SIZE

setDateMaybeNull

public static void setDateMaybeNull(java.sql.PreparedStatement s,
                                    int fieldNum,
                                    java.util.Date date)
                             throws java.sql.SQLException

Set the Date into the given field of a statement.

Parameters:

s - a prepared statement

fieldNum - the index of the given field to be set

date - the date (may be null)

Throws:

java.sql.SQLException - If any trouble accessing the database during the operation

getDateMaybeNull

public static java.util.Date getDateMaybeNull(java.sql.ResultSet rs,
                                              int columnIndex)
                                       throws java.sql.SQLException

Get a Date from a column in the resultset. Returns null, if the value in the column is NULL.

Parameters:

rs - the resultSet

columnIndex - The given column, where the Date resides

Returns:

a Date from a column in the resultset

Throws:

java.sql.SQLException - If columnIndex does not correspond to a parameter marker in the ResultSet, or a database access error occurs or this method is called on a closed ResultSet

rollbackIfNeeded

public static void rollbackIfNeeded(java.sql.Connection c,
                                    java.lang.String action,
                                    java.lang.Object o)

Method to perform a rollback of complex DB updates. If no commit has been performed, this will undo the entire transaction, otherwise nothing will happen. This should be called in a finally block with no DB updates after the last commit. Thus exceptions while closing are ignored, but logged as warnings. NB: the provided connection si not closed.

Parameters:

c - the db-connection

action - The action going on, before calling this method

o - The Object being acted upon by this action

setClobMaxLength

public static void setClobMaxLength(java.sql.PreparedStatement s,
                                    int fieldNum,
                                    java.lang.String contents,
                                    long maxSize,
                                    java.lang.Object o,
                                    java.lang.String fieldName)
                             throws java.sql.SQLException

Set the CLOB maxlength. If contents.length() > maxSize, contents is truncated to contain the first maxSize characters of the contents, and a warning is logged.

Parameters:

s - a prepared statement

fieldNum - the field-index, where the contents are inserted

contents - the contents

maxSize - the maxsize for this contents

o - the Object, which assumedly have a field named fieldName

fieldName - a given field (Assumedly in Object o)

Throws:

java.sql.SQLException - If fieldNum does not correspond to a parameter marker in the PreparedStatement, or a database access error occurs or this method is called on a closed PreparedStatement

setLongMaybeNull

public static void setLongMaybeNull(java.sql.PreparedStatement s,
                                    int i,
                                    java.lang.Long value)
                             throws java.sql.SQLException

Insert a long value (which could be null) into the given field of a statement.

Parameters:

s - a prepared Statement

i - the number of a given field in the prepared statement

value - the long value to insert (maybe null)

Throws:

java.sql.SQLException - If i does not correspond to a parameter marker in the PreparedStatement, or a database access error occurs or this method is called on a closed PreparedStatement

setIntegerMaybeNull

public static void setIntegerMaybeNull(java.sql.PreparedStatement s,
                                       int i,
                                       java.lang.Integer value)
                                throws java.sql.SQLException

Insert an Integer in prepared statement.

Parameters:

s - a prepared statement

i - the index of the statement, where the Integer should be inserted

value - The Integer to insert (maybe null)

Throws:

java.sql.SQLException - If i does not correspond to a parameter marker in the PreparedStatement, or a database access error occurs or this method is called on a closed PreparedStatement

getIntegerMaybeNull

public static java.lang.Integer getIntegerMaybeNull(java.sql.ResultSet rs,
                                                    int i)
                                             throws java.sql.SQLException

Get an Integer from the resultSet in column i.

Parameters:

rs - the resultset

i - the column where the wanted Integer resides

Returns:

an Integer object located in column i in the resultset

Throws:

java.sql.SQLException - If the columnIndex is not valid, or a database access error occurs or this method is called on a closed result set

getLongMaybeNull

public static java.lang.Long getLongMaybeNull(java.sql.ResultSet rs,
                                              int i)
                                       throws java.sql.SQLException

Get a Long from the resultSet in column i.

Parameters:

rs - the resultset

i - the column where the wanted Long resides

Returns:

a Long object located in column i in the resultset

Throws:

java.sql.SQLException - If the columnIndex is not valid, or a database access error occurs or this method is called on a closed result set

getUsages

public static java.lang.String getUsages(java.sql.Connection connection,
                                         java.lang.String select,
                                         java.lang.Object victim,
                                         java.lang.Object... args)

Return a description of where an object is used elsewhere in the database, or null. NB: the provided connection is not closed.

Parameters:

connection - connection to the database.

select - A select statement finding the names of other uses. The statement should result in exactly one column of string values.

victim - The object being used.

args - Any objects that may be used to prepare the select statement.

Returns:

A string describing the usages, or null if no usages were found.

checkTableVersion

public static void checkTableVersion(java.sql.Connection connection,
                                     java.lang.String tablename,
                                     int desiredVersion)

Check that a database table is of the expected version. NB: the provided connection is not closed.

Parameters:

connection - connection to the database.

tablename - The table to check.

desiredVersion - The version it should be.

Throws:

IllegalState - if the version isn't as expected.

selectStringValue

public static java.lang.String selectStringValue(java.sql.Connection connection,
                                                 java.lang.String query,
                                                 java.lang.Object... args)

Execute an SQL statement and return the single string in the result set. This variant takes a query string and a single string arg and combines them to form a normal query. This assumes the connection is to the harvester database.

Parameters:

connection - connection to the database.

query - a query with ? for parameters (must not be null or an empty string)

args - parameters of type string, int, long or boolean

Returns:

The string result

Throws:

IOFailure - if the statement didn't result in exactly one string value

selectStringValue

public static java.lang.String selectStringValue(java.sql.PreparedStatement s)

Execute an SQL statement and return the single string in the result set.

Parameters:

s - A prepared statement

Returns:

The string result, or null if the result was a null value Note that a null value is not the same as no result rows.

Throws:

IOFailure - if the statement didn't result in exactly one row with a string or null value

selectAny

public static boolean selectAny(java.sql.Connection connection,
                                java.lang.String query,
                                java.lang.Object... args)

Execute an SQL query and return whether the result contains any rows. NB: the provided connection is not closed.

Parameters:

connection - connection to the database.

query - a query with ? for parameters (must not be null or an empty String)

args - parameters of type string, int, long or boolean

Returns:

True if executing the query resulted in at least one row.

Throws:

IOFailure - if there were problems with the SQL query

makeSQLGlob

public static java.lang.String makeSQLGlob(java.lang.String glob)

Translate a "normal" glob (with * and .) into SQL syntax.

Parameters:

glob - A shell-like glob string (must not be null)

Returns:

A string that implements glob in SQL "LIKE" constructs.

executeSQL

public static void executeSQL(java.sql.Connection connection,
                              java.lang.String... updates)

Update a database by executing all the statements in the updates String array. NOTE: this must NOT be used for tables under version control It must only be used in connection with temporary tables e.g. used for backup. NB: the method does not close the provided connection.

Parameters:

connection - connection to the database.

updates - The SQL statements that makes the necessary updates.

Throws:

IOFailure - in case of problems in interacting with the database

closeStatementIfOpen

public static void closeStatementIfOpen(java.sql.PreparedStatement s)

Close a statement, if not closed already Note: This does not throw any a SQLException, because it is always called inside a finally-clause. Exceptions are logged as warnings, though.

Parameters:

s - a statement