Package dk.netarkivet.common.utils
Class DBUtils
- java.lang.Object
-
- dk.netarkivet.common.utils.DBUtils
-
public final class DBUtils extends Object
Various database related utilities.
-
-
Method Summary
All Methods Static Methods Concrete Methods Modifier and Type Method Description static void
closeStatementIfOpen(Statement 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(Connection connection, String... updates)
Update a database by executing all the statements in the updates String array.static Date
getDateMaybeNull(ResultSet rs, int columnIndex)
Get a Date from a column in the resultset.static long
getGeneratedID(PreparedStatement s)
Get the automatically generated key that was created with the just-executed statement.static Integer
getIntegerMaybeNull(ResultSet rs, int i)
Get an Integer from the resultSet in column i.static Long
getLongMaybeNull(ResultSet rs, int i)
Get a Long from the resultSet in column i.static int
getTableVersion(Connection connection, String tablename)
Returns the version of a table according to schemaversions, or 0 for the initial, unnumbered version.static String
getUsages(Connection connection, String select, Object victim, Object... args)
Return a description of where an object is used elsewhere in the database, or null.static String
makeSQLGlob(String glob)
Translate a "normal" glob (with * and .) into SQL syntax.static PreparedStatement
prepareStatement(Connection c, int fetchSize, String query, Object... args)
Prepare a statement for iteration given a query string, fetch size and some args.static PreparedStatement
prepareStatement(Connection c, String query, Object... args)
Prepare a statement given a query string and some args.static void
rollbackIfNeeded(Connection c, String action, Object o)
Method to perform a rollback of complex DB updates.static boolean
selectAny(Connection connection, String query, Object... args)
Execute an SQL query and return whether the result contains any rows.static Long
selectFirstLongValueIfAny(Connection connection, String query, Object... args)
Execute an SQL statement and return the first long in the result set, or null if resultset is empty.static Integer
selectIntValue(Connection connection, String query, Object... args)
Execute an SQL statement and return the single int in the result set.static Integer
selectIntValue(PreparedStatement s)
Execute an SQL statement and return the single integer in the result set.static Iterator<Long>
selectLongIterator(Connection connection, String query, Object... args)
Return an iterator to a list of Longs.static List<Long>
selectLongList(Connection connection, String query, Object... args)
Execute an SQL statement and return the list of Long-objects in its result set.static Set<Long>
selectLongSet(Connection connection, String query, Object... args)
Execute an SQL statement and return the set of Long-objects in its result set.static Long
selectLongValue(Connection connection, String query, Object... args)
Execute an SQL statement and return the single long in the result set.static Long
selectLongValue(PreparedStatement s)
Execute an SQL statement and return the single long in the result set.static List<String>
selectStringList(Connection connection, String query, Object... args)
Execute an SQL statement and return the list of strings in its result set.static Map<String,Long>
selectStringLongMap(Connection connection, String query, Object... args)
Execute an SQL statement and return the list of strings -> id mappings in its result set.static String
selectStringValue(Connection connection, String query, Object... args)
Execute an SQL statement and return the single string in the result set.static String
selectStringValue(PreparedStatement s)
Execute an SQL statement and return the single string in the result set.static void
setClobMaxLength(PreparedStatement s, int fieldNum, String contents, long maxSize, Object o, String fieldName)
Set the CLOB maxlength.static void
setComments(PreparedStatement s, int fieldNum, Named o, int maxFieldSize)
Set the comments of a Named object into the given field of statement.static void
setDateMaybeNull(PreparedStatement s, int fieldNum, Date date)
Set the Date into the given field of a statement.static void
setIntegerMaybeNull(PreparedStatement s, int i, Integer value)
Insert an Integer in prepared statement.static void
setLongMaybeNull(PreparedStatement s, int i, Long value)
Insert a long value (which could be null) into the given field of a statement.static void
setName(PreparedStatement s, int fieldNum, Named o, int maxFieldSize)
Set the name of a Named object into the given field.static void
setStringMaxLength(PreparedStatement s, int fieldNum, String contents, int maxSize, Object o, String fieldname)
Set String Max Length.
-
-
-
Method Detail
-
selectIntValue
public static Integer selectIntValue(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 Integer selectIntValue(Connection connection, String query, 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 Long selectLongValue(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 Long selectLongValue(Connection connection, String query, 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 Long selectFirstLongValueIfAny(Connection connection, String query, 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 PreparedStatement prepareStatement(Connection c, String query, Object... args) throws SQLException
Prepare a statement given a query string and some args.NB: the provided connection is not closed.
- Parameters:
c
- a Database connectionquery
- 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:
SQLException
- If unable to prepare a statementArgumentNotValid
- If unable to handle type of one the args, or the arguments are either null or an empty String.
-
prepareStatement
public static PreparedStatement prepareStatement(Connection c, int fetchSize, String query, Object... args) throws SQLException
Prepare a statement for iteration given a query string, fetch size and some args.NB: the provided connection is not closed.
- Parameters:
c
- a Database connectionfetchSize
- hint to JDBC driver on number of results to cachequery
- 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:
SQLException
- If unable to prepare a statementArgumentNotValid
- If unable to handle type of one the args, or the arguments are either null or an empty String.
-
selectStringList
public static List<String> selectStringList(Connection connection, String query, 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 Map<String,Long> selectStringLongMap(Connection connection, String query, Object... args) throws SQLException
Execute an SQL statement and return the list of strings -> id mappings 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 strings -> id mappings
- Throws:
SQLException
- If this query fails
-
selectLongList
public static List<Long> selectLongList(Connection connection, String query, Object... args)
Execute an SQL statement and return the list of Long-objects in its result set.- 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
-
selectLongIterator
public static Iterator<Long> selectLongIterator(Connection connection, String query, Object... args)
Return an iterator to a list of Longs.- Parameters:
connection
- an open connection to the databasequery
- The given sql-query (must not be null or empty string)args
- The arguments to insert into this query- Returns:
- an iterator to a list of Longs.
-
selectLongSet
public static Set<Long> selectLongSet(Connection connection, String query, Object... args)
Execute an SQL statement and return the set 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 set of Long-objects in its result set
-
getGeneratedID
public static long getGeneratedID(PreparedStatement s) throws 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:
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(Connection connection, 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(PreparedStatement s, int fieldNum, String contents, int maxSize, Object o, String fieldname) throws 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 StatementfieldNum
- a index into the above statementcontents
- the contentsmaxSize
- the maximum size of field: fieldNameo
- the Object, which is assumed to have a field named fieldNamefieldname
- the name of a given field- Throws:
SQLException
- if set operation fails
-
setComments
public static void setComments(PreparedStatement s, int fieldNum, Named o, int maxFieldSize) throws SQLException
Set the comments of a Named object into the given field of statement.- Parameters:
s
- a prepared statementfieldNum
- the index of the given field to be seto
- the Named objectmaxFieldSize
- max size of the comments field- Throws:
SQLException
- If any trouble accessing the database during the operationPermissionDenied
- If length of o.getComments() is larger than Constants.MAX_COMMENT_SIZE
-
setName
public static void setName(PreparedStatement s, int fieldNum, Named o, int maxFieldSize) throws SQLException
Set the name of a Named object into the given field.- Parameters:
s
- a prepared statementfieldNum
- the index of the given field to be seto
- the Named objectmaxFieldSize
- max size of the name field- Throws:
SQLException
- If any trouble accessing the database during the operationPermissionDenied
- If length of o.getName() is larger than Constants.MAX_NAME_SIZE
-
setDateMaybeNull
public static void setDateMaybeNull(PreparedStatement s, int fieldNum, Date date) throws SQLException
Set the Date into the given field of a statement.- Parameters:
s
- a prepared statementfieldNum
- the index of the given field to be setdate
- the date (may be null)- Throws:
SQLException
- If any trouble accessing the database during the operation
-
getDateMaybeNull
public static Date getDateMaybeNull(ResultSet rs, int columnIndex) throws SQLException
Get a Date from a column in the resultset. Returns null, if the value in the column is NULL.- Parameters:
rs
- the resultSetcolumnIndex
- The given column, where the Date resides- Returns:
- a Date from a column in the resultset
- Throws:
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(Connection c, String action, 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. If autoCommit is true then no action is taken. This method 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 is not closed.
- Parameters:
c
- the db-connectionaction
- The action going on, before calling this methodo
- The Object being acted upon by this action
-
setClobMaxLength
public static void setClobMaxLength(PreparedStatement s, int fieldNum, String contents, long maxSize, Object o, String fieldName) throws 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 statementfieldNum
- the field-index, where the contents are insertedcontents
- the contentsmaxSize
- the maxsize for this contentso
- the Object, which is assumed to have a field named fieldNamefieldName
- a given field (Assumed to present in Object o)- Throws:
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(PreparedStatement s, int i, Long value) throws SQLException
Insert a long value (which could be null) into the given field of a statement.- Parameters:
s
- a prepared Statementi
- the number of a given field in the prepared statementvalue
- the long value to insert (maybe null)- Throws:
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(PreparedStatement s, int i, Integer value) throws SQLException
Insert an Integer in prepared statement.- Parameters:
s
- a prepared statementi
- the index of the statement, where the Integer should be insertedvalue
- The Integer to insert (maybe null)- Throws:
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 Integer getIntegerMaybeNull(ResultSet rs, int i) throws SQLException
Get an Integer from the resultSet in column i.- Parameters:
rs
- the resultseti
- the column where the wanted Integer resides- Returns:
- an Integer object located in column i in the resultset
- Throws:
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 Long getLongMaybeNull(ResultSet rs, int i) throws SQLException
Get a Long from the resultSet in column i.- Parameters:
rs
- the resultseti
- the column where the wanted Long resides- Returns:
- a Long object located in column i in the resultset
- Throws:
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 String getUsages(Connection connection, String select, Object victim, 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.
-
selectStringValue
public static String selectStringValue(Connection connection, String query, 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 String selectStringValue(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(Connection connection, String query, 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 String makeSQLGlob(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(Connection connection, 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(Statement 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
-
-