Class DBUtils


  • public final class DBUtils
    extends Object
    Various database related utilities.
    • 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 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:
        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.
      • 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 connection
        fetchSize - hint to JDBC driver on number of results to cache
        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:
        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 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 database
        query - 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 Statement
        fieldNum - a index into the above statement
        contents - the contents
        maxSize - the maximum size of field: fieldName
        o - the Object, which is assumed to have a field named fieldName
        fieldname - 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 statement
        fieldNum - the index of the given field to be set
        o - the Named object
        maxFieldSize - max size of the comments field
        Throws:
        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​(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 statement
        fieldNum - the index of the given field to be set
        o - the Named object
        maxFieldSize - max size of the name field
        Throws:
        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​(PreparedStatement s,
                                            int fieldNum,
                                            Date date)
                                     throws 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:
        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 resultSet
        columnIndex - 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-connection
        action - The action going on, before calling this method
        o - 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 statement
        fieldNum - the field-index, where the contents are inserted
        contents - the contents
        maxSize - the maxsize for this contents
        o - the Object, which is assumed to have a field named fieldName
        fieldName - 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 Statement
        i - the number of a given field in the prepared statement
        value - 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 statement
        i - the index of the statement, where the Integer should be inserted
        value - 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 resultset
        i - 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 resultset
        i - 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