dk.netarkivet.common.utils.DBUtils.java Source code

Java tutorial

Introduction

Here is the source code for dk.netarkivet.common.utils.DBUtils.java

Source

/* File:        $Id$
 * Revision:    $Revision$
 * Author:      $Author$
 * Date:        $Date$
 *
 * The Netarchive Suite - Software to harvest and preserve websites
 * Copyright 2004-2012 The Royal Danish Library, the Danish State and
 * University Library, the National Library of France and the Austrian
 * National Library.
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 */

package dk.netarkivet.common.utils;

import java.io.StringReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import dk.netarkivet.common.exceptions.ArgumentNotValid;
import dk.netarkivet.common.exceptions.IOFailure;
import dk.netarkivet.common.exceptions.PermissionDenied;

/**
 * Various database related utilities.
 *
 */
public final class DBUtils {

    /** default constructor. Is private to avoid initialization. */
    private DBUtils() {

    }

    /** The logger. */
    private static final Log log = LogFactory.getLog(DBUtils.class);

    /** Execute an SQL statement and return the single integer
     * in the result set.
     *
     * @param s A prepared statement
     * @return The integer result, or null if the result value was null.
     * @throws IOFailure if the statement didn't result in exactly one integer.
     */
    public static Integer selectIntValue(PreparedStatement s) {
        ArgumentNotValid.checkNotNull(s, "PreparedStatement s");
        try {
            ResultSet res = s.executeQuery();
            if (!res.next()) {
                throw new IOFailure("No results from " + s);
            }
            Integer resultInt = res.getInt(1);
            if (res.wasNull()) {
                resultInt = null;
            }
            if (res.next()) {
                throw new IOFailure("Too many results from " + s);
            }
            return resultInt;
        } catch (SQLException e) {
            throw new IOFailure(
                    "SQL error executing statement " + s + "\n" + ExceptionUtils.getSQLExceptionCause(e), e);
        }
    }

    /** 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.
     *
     * @param connection connection to database.
     * @param query a query with ? for parameters (must not be null or
     * empty string)
     * @param args parameters of type string, int, long or boolean
     * @return The integer result
     * @throws IOFailure if the statement didn't result in exactly one integer
     */
    public static Integer selectIntValue(Connection connection, String query, Object... args) {
        ArgumentNotValid.checkNotNull(connection, "Connection connection");
        ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
        ArgumentNotValid.checkNotNull(args, "Object... args");
        PreparedStatement s = null;
        try {
            s = DBUtils.prepareStatement(connection, query, args);
            // We do not test for 0-values here, already tested in
            // selectIntValue(s)
            return selectIntValue(s);
        } catch (SQLException e) {
            throw new IOFailure("SQL error preparing statement " + query + " args " + Arrays.toString(args) + "\n"
                    + ExceptionUtils.getSQLExceptionCause(e), e);
        } finally {
            closeStatementIfOpen(s);
        }
    }

    /** Execute an SQL statement and return the single long in the result set.
     *
     * @param s A prepared statement
     * @return 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
     */
    public static Long selectLongValue(PreparedStatement s) {
        ArgumentNotValid.checkNotNull(s, "PreparedStatement s");
        try {
            ResultSet res = s.executeQuery();
            if (!res.next()) {
                throw new IOFailure("No results from " + s);
            }
            Long resultLong = res.getLong(1);
            if (res.wasNull()) {
                resultLong = null;
            }
            if (res.next()) {
                throw new IOFailure("Too many results from " + s);
            }
            return resultLong;
        } catch (SQLException e) {
            throw new IOFailure(
                    "SQL error executing statement " + s + "\n" + ExceptionUtils.getSQLExceptionCause(e), e);
        }
    }

    /** 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.
     *
     * @param connection connection to database.
     * @param query a query with ? for parameters (must not be null or
     * empty string)
     * @param args parameters of type string, int, long or boolean
     * @return The long result
     * @throws IOFailure if the statement didn't result in exactly one long
     * value
     */
    public static Long selectLongValue(Connection connection, String query, Object... args) {
        ArgumentNotValid.checkNotNull(connection, "Connection connection");
        ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
        ArgumentNotValid.checkNotNull(args, "Object... args");
        PreparedStatement s = null;
        try {
            s = DBUtils.prepareStatement(connection, query, args);
            // We do not test for 0-values here, already tested in
            // selectLongValue(s)
            return selectLongValue(s);
        } catch (SQLException e) {
            throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n"
                    + ExceptionUtils.getSQLExceptionCause(e), e);
        } finally {
            closeStatementIfOpen(s);
        }
    }

    /** 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.
     *
     * @param connection connection to database.
     * @param query a query with ? for parameters (must not be null
     * or empty string)
     * @param args parameters of type string, int, long or boolean
     * @return 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.
     */
    public static Long selectFirstLongValueIfAny(Connection connection, String query, Object... args) {
        ArgumentNotValid.checkNotNull(connection, "Connection connection");
        ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
        ArgumentNotValid.checkNotNull(args, "Object... args");
        PreparedStatement s = null;
        try {
            s = DBUtils.prepareStatement(connection, query, args);
            ResultSet rs = s.executeQuery();
            if (rs.next()) {
                return DBUtils.getLongMaybeNull(rs, 1);
            } else {
                return null;
            }
        } catch (SQLException e) {
            String message = "SQL error executing '" + query + "'" + "\n" + ExceptionUtils.getSQLExceptionCause(e);
            log.warn(message, e);
            throw new IOFailure(message, e);
        } finally {
            closeStatementIfOpen(s);
        }
    }

    /**
     * Prepare a statement given a query string and some args.
     *
     * NB: the provided connection is not closed.
     *
     * @param c a Database connection
     * @param query a query string  (must not be null or empty)
     * @param args some args to insert into this query string (must not be null)
     * @return a prepared statement
     * @throws SQLException If unable to prepare a statement
     * @throws ArgumentNotValid If unable to handle type of one the args, or
     * the arguments are either null or an empty String.
     */
    public static PreparedStatement prepareStatement(Connection c, String query, Object... args)
            throws SQLException {
        ArgumentNotValid.checkNotNull(c, "Connection c");
        ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
        ArgumentNotValid.checkNotNull(args, "Object... args");
        PreparedStatement s = c.prepareStatement(query);
        int i = 1;
        for (Object arg : args) {
            if (arg instanceof String) {
                s.setString(i, (String) arg);
            } else if (arg instanceof Integer) {
                s.setInt(i, (Integer) arg);
            } else if (arg instanceof Long) {
                s.setLong(i, (Long) arg);
            } else if (arg instanceof Boolean) {
                s.setBoolean(i, (Boolean) arg);
            } else if (arg instanceof Date) {
                s.setTimestamp(i, new Timestamp(((Date) arg).getTime()));
            } else {
                throw new ArgumentNotValid("Cannot handle type '" + arg.getClass().getName()
                        + "'. We can only handle string, " + "int, long, date or boolean args for query: " + query);
            }
            i++;
        }
        return s;
    }

    /**
     * Prepare a statement for iteration given a query string, fetch size
     * and some args.
     *
     * NB: the provided connection is not closed.
     *
     * @param c a Database connection
     * @param fetchSize hint to JDBC driver on number of results to cache
     * @param query a query string  (must not be null or empty)
     * @param args some args to insert into this query string (must not be null)
     * @return a prepared statement
     * @throws SQLException If unable to prepare a statement
     * @throws ArgumentNotValid If unable to handle type of one the args, or
     * the arguments are either null or an empty String.
     */
    public static PreparedStatement prepareStatement(Connection c, int fetchSize, String query, Object... args)
            throws SQLException {
        ArgumentNotValid.checkNotNull(c, "Connection c");
        ArgumentNotValid.checkPositive(fetchSize, "int fetchSize");
        ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
        ArgumentNotValid.checkNotNull(args, "Object... args");
        c.setAutoCommit(false);
        PreparedStatement s = c.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        s.setFetchSize(fetchSize);
        int i = 1;
        for (Object arg : args) {
            if (arg instanceof String) {
                s.setString(i, (String) arg);
            } else if (arg instanceof Integer) {
                s.setInt(i, (Integer) arg);
            } else if (arg instanceof Long) {
                s.setLong(i, (Long) arg);
            } else if (arg instanceof Boolean) {
                s.setBoolean(i, (Boolean) arg);
            } else if (arg instanceof Date) {
                s.setTimestamp(i, new Timestamp(((Date) arg).getTime()));
            } else {
                throw new ArgumentNotValid("Cannot handle type '" + arg.getClass().getName()
                        + "'. We can only handle string, " + "int, long, date or boolean args for query: " + query);
            }
            i++;
        }
        return s;
    }

    /** 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.
     *
     * @param connection connection to the database.
     * @param query the given sql-query (must not be null or empty)
     * @param args The arguments to insert into this query (must not be null)
     * @throws IOFailure If this query fails
     * @return the list of strings in its result set
     */
    public static List<String> selectStringList(Connection connection, String query, Object... args) {
        ArgumentNotValid.checkNotNull(connection, "Connection connection");
        ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
        ArgumentNotValid.checkNotNull(args, "Object... args");
        PreparedStatement s = null;
        try {
            s = prepareStatement(connection, query, args);
            ResultSet result = s.executeQuery();
            List<String> results = new ArrayList<String>();
            while (result.next()) {
                if (result.getString(1) == null) {
                    String warning = "NULL pointer found in resultSet from query: " + query;
                    log.warn(warning);
                    throw new IOFailure(warning);
                }
                results.add(result.getString(1));
            }
            return results;
        } catch (SQLException e) {
            throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n"
                    + ExceptionUtils.getSQLExceptionCause(e), e);
        } finally {
            closeStatementIfOpen(s);
        }
    }

    /** Execute an SQL statement and return the list of strings -> id mappings
     * in its result set.
     *
     * NB: the provided connection is not closed.
     *
     * @param connection connection to the database.
     * @param query the given sql-query (must not be null or empty string)
     * @param args The arguments to insert into this query
     * @throws SQLException If this query fails
     * @return the list of strings -> id mappings
     */
    public static Map<String, Long> selectStringLongMap(Connection connection, String query, Object... args)
            throws SQLException {
        ArgumentNotValid.checkNotNull(connection, "Connection connection");
        ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
        ArgumentNotValid.checkNotNull(args, "Object... args");
        PreparedStatement s = null;
        try {
            s = prepareStatement(connection, query, args);
            ResultSet result = s.executeQuery();
            Map<String, Long> results = new HashMap<String, Long>();
            while (result.next()) {
                String resultString = result.getString(1);
                long resultLong = result.getLong(2);
                if ((resultString == null) || (resultLong == 0L && result.wasNull())) {
                    String warning = "NULL pointers found in entry (" + resultString + "," + resultLong
                            + ") in resultset from query: " + query;
                    log.warn(warning);
                }
                results.put(resultString, resultLong);
            }
            return results;
        } finally {
            closeStatementIfOpen(s);
        }

    }

    /**
     * Execute an SQL statement and return the list of Long-objects
     * in its result set.
     *
     * NB: the provided connection is not closed.
     *
     * @param connection connection to the database.
     * @param query the given sql-query (must not be null or empty string)
     * @param args The arguments to insert into this query
     * @return the list of Long-objects in its result set
     */
    public static List<Long> selectLongList(Connection connection, String query, Object... args) {
        ArgumentNotValid.checkNotNull(connection, "Connection connection");
        ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
        ArgumentNotValid.checkNotNull(args, "Object... args");
        PreparedStatement s = null;
        try {
            s = prepareStatement(connection, query, args);
            ResultSet result = s.executeQuery();
            List<Long> results = new ArrayList<Long>();
            while (result.next()) {
                if (result.getLong(1) == 0L && result.wasNull()) {
                    String warning = "NULL value encountered in query: " + query;
                    log.warn(warning);
                }
                results.add(result.getLong(1));
            }
            return results;
        } catch (SQLException e) {
            throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n"
                    + ExceptionUtils.getSQLExceptionCause(e), e);
        } finally {
            closeStatementIfOpen(s);
        }
    }

    /**
     * Return an iterator to a list of Longs.
     * @param connection an open connection to the database
     * @param query The given sql-query (must not be null or empty string)
     * @param args The arguments to insert into this query
     * @return an iterator to a list of Longs.
     */
    public static Iterator<Long> selectLongIterator(Connection connection, String query, Object... args) {
        ArgumentNotValid.checkNotNull(connection, "Connection connection");
        ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
        ArgumentNotValid.checkNotNull(args, "Object... args");
        PreparedStatement s = null;
        try {
            s = prepareStatement(connection, 8192, query, args);
            ResultSet result = s.executeQuery();
            Iterator<Long> results = new ResultSetIterator<Long>(s, result) {
                @Override
                public Long filter(ResultSet result) {
                    try {
                        return result.getLong(1);
                    } catch (SQLException e) {
                        log.warn("Error retrieving long from resultset\n" + ExceptionUtils.getSQLExceptionCause(e),
                                e);
                        return 0L;
                    }
                }
            };
            return results;
        } catch (SQLException e) {
            closeStatementIfOpen(s);
            throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n"
                    + ExceptionUtils.getSQLExceptionCause(e), e);
        }
    }

    /**
     * Execute an SQL statement and return the set of Long-objects
     * in its result set.
     *
     * NB: the provided connection is not closed.
     *
     * @param connection connection to the database.
     * @param query the given sql-query (must not be null or empty string)
     * @param args The arguments to insert into this query
     * @return the set of Long-objects in its result set
     */
    public static Set<Long> selectLongSet(Connection connection, String query, Object... args) {
        ArgumentNotValid.checkNotNull(connection, "Connection connection");
        ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
        ArgumentNotValid.checkNotNull(args, "Object... args");
        PreparedStatement s = null;
        try {
            s = prepareStatement(connection, query, args);
            ResultSet result = s.executeQuery();
            Set<Long> results = new TreeSet<Long>();
            while (result.next()) {
                if (result.getLong(1) == 0L && result.wasNull()) {
                    String warning = "NULL value encountered in query: " + query;
                    log.warn(warning);
                }
                results.add(result.getLong(1));
            }
            return results;
        } catch (SQLException e) {
            throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n"
                    + ExceptionUtils.getSQLExceptionCause(e), e);
        } finally {
            closeStatementIfOpen(s);
        }
    }

    /** Get the automatically generated key that was created with the
     * just-executed statement.
     *
     * @param s A statement created with Statement.RETURN_GENERATED_KEYS
     * @return 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
     */
    public static long getGeneratedID(PreparedStatement s) throws SQLException {
        ArgumentNotValid.checkNotNull(s, "PreparedStatement s");
        ResultSet res = s.getGeneratedKeys();
        if (!res.next()) {
            throw new IOFailure("No keys generated by " + s);
        }
        return res.getLong(1);
    }

    /** Returns the version of a table according to schemaversions, or 0
     * for the initial, unnumbered version.
     *
     * NB: the provided connection is not closed
     *
     * @param connection connection to the database.
     * @param tablename The name of a table in the database.
     * @return Version of the given table.
     * @throws IOFailure if DB table schemaversions does not exist
     */
    public static int getTableVersion(Connection connection, String tablename) throws IOFailure {
        ArgumentNotValid.checkNotNull(connection, "Connection connection");
        ArgumentNotValid.checkNotNullOrEmpty(tablename, "String tablenname");
        PreparedStatement s = null;
        int version = 0;
        try {
            s = connection.prepareStatement("SELECT version FROM schemaversions" + " WHERE tablename = ?");
            s.setString(1, tablename);
            ResultSet res = s.executeQuery();
            if (!res.next()) {
                log.warn("As yet unknown tablename '" + tablename + "' in table schemaversions. The table"
                        + " should be automatically created in the database " + "when it is first needed.");
            } else {
                version = res.getInt(1);
                if (res.wasNull()) {
                    log.warn("Null table version for '" + tablename + "'");
                }
            }
            return version;
        } catch (SQLException e) {
            String msg = "SQL Error checking version of table " + tablename + "\n"
                    + ExceptionUtils.getSQLExceptionCause(e);
            log.warn(msg, e);
            throw new IOFailure(msg, e);
        } finally {
            closeStatementIfOpen(s);
        }
    }

    /**
     * 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.
     * @param s a Prepared Statement
     * @param fieldNum a index into the above statement
     * @param contents the contents
     * @param maxSize the maximum size of field: fieldName
     * @param o the Object, which is assumed to have a field named fieldName
     * @param fieldname the name of a given field
     * @throws SQLException if set operation fails
     */
    public static void setStringMaxLength(PreparedStatement s, int fieldNum, String contents, int maxSize, Object o,
            String fieldname) throws SQLException {
        ArgumentNotValid.checkNotNull(s, "PreparedStatement s");
        ArgumentNotValid.checkNotNegative(fieldNum, "int fieldNum");

        if (contents != null) {
            if (contents.length() > maxSize) {
                log.warn(fieldname + " of " + o + " is longer than the allowed " + maxSize
                        + " characters. The contents is truncated to length " + maxSize
                        + ". The untruncated contents was: " + contents);
                // truncate to length maxSize
                contents = contents.substring(0, maxSize);
            }
            s.setString(fieldNum, contents);
        } else {
            s.setNull(fieldNum, Types.VARCHAR);
        }
    }

    /** Set the comments of a Named object into the given field of statement.
     *
     * @param s a prepared statement
     * @param fieldNum the index of the given field to be set
     * @param o the Named object
     * @param maxFieldSize max size of the comments field
     * @throws SQLException If any trouble accessing the database during 
     * the operation
     * @throws PermissionDenied If length of o.getComments() is larger than
     * Constants.MAX_COMMENT_SIZE
     */
    public static void setComments(PreparedStatement s, int fieldNum, Named o, int maxFieldSize)
            throws SQLException {
        ArgumentNotValid.checkNotNull(s, "PreparedStatement s");
        ArgumentNotValid.checkNotNegative(fieldNum, "int fieldNum");
        ArgumentNotValid.checkNotNull(o, "Named o");
        ArgumentNotValid.checkNotNegative(maxFieldSize, "int maxFieldSize");

        if (o.getComments().length() > maxFieldSize) {
            throw new PermissionDenied("Length of comments (" + o.getComments().length()
                    + ") is larger than allowed. Max length is " + maxFieldSize);
        }
        setStringMaxLength(s, fieldNum, o.getComments(), maxFieldSize, o, "comments");
    }

    /** Set the name of a Named object into the given field.
     * @param s a prepared statement
     * @param fieldNum the index of the given field to be set
     * @param o the Named object
     * @param maxFieldSize max size of the name field
     * @throws SQLException  If any trouble accessing the database during 
     * the operation
     * @throws PermissionDenied If length of o.getName() is larger than
     * Constants.MAX_NAME_SIZE
     */
    public static void setName(PreparedStatement s, int fieldNum, Named o, int maxFieldSize) throws SQLException {
        ArgumentNotValid.checkNotNull(s, "PreparedStatement s");
        ArgumentNotValid.checkNotNegative(fieldNum, "int fieldNum");
        ArgumentNotValid.checkNotNull(o, "Named o");
        ArgumentNotValid.checkNotNegative(maxFieldSize, "int maxFieldSize");

        if (o.getName().length() > maxFieldSize) {
            throw new PermissionDenied("Length of name (" + o.getName().length()
                    + ") is larger than allowed. Max length is " + maxFieldSize);
        }
        setStringMaxLength(s, fieldNum, o.getName(), maxFieldSize, o, "name");
    }

    /** Set the Date into the given field of a statement.
     *
     * @param s a prepared statement
     * @param fieldNum the index of the given field to be set
     * @param date the date (may be null)
     * @throws SQLException  If any trouble accessing the database during 
     * the operation
     */
    public static void setDateMaybeNull(PreparedStatement s, int fieldNum, Date date) throws SQLException {
        ArgumentNotValid.checkNotNull(s, "PreparedStatement s");
        ArgumentNotValid.checkNotNegative(fieldNum, "int fieldNum");

        if (date != null) {
            s.setTimestamp(fieldNum, new Timestamp(date.getTime()));
        } else {
            s.setNull(fieldNum, Types.DATE);
        }
    }

    /**
     * Get a Date from a column in the resultset.
     * Returns null, if the value in the column is NULL.
     * @param rs the resultSet
     * @param columnIndex The given column, where the Date resides
     * @return 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
     */
    public static Date getDateMaybeNull(ResultSet rs, final int columnIndex) throws SQLException {
        ArgumentNotValid.checkNotNull(rs, "ResultSet rs");
        ArgumentNotValid.checkNotNegative(columnIndex, "int columnIndex");

        final Timestamp startTimestamp = rs.getTimestamp(columnIndex);
        if (rs.wasNull()) {
            return null;
        }
        Date startdate;
        if (startTimestamp != null) {
            startdate = new Date(startTimestamp.getTime());
        } else {
            startdate = null;
        }
        return startdate;
    }

    /**
     * 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.
     *
     * @param c the db-connection
     * @param action The action going on, before calling this method
     * @param o The Object being acted upon by this action
     */
    public static void rollbackIfNeeded(Connection c, String action, Object o) {
        ArgumentNotValid.checkNotNull(c, "Connection c");
        try {
            if (!c.getAutoCommit()) {
                c.rollback();
                c.setAutoCommit(true);
            }
        } catch (SQLException e) {
            log.warn("SQL error doing rollback after " + action + " " + o + "\n"
                    + ExceptionUtils.getSQLExceptionCause(e), e);
            // Can't throw here, we want the real exception
        }
    }

    /**
     * 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.
     * @param s a prepared statement
     * @param fieldNum the field-index, where the contents are inserted
     * @param contents the contents
     * @param maxSize the maxsize for this contents
     * @param o the Object, which is assumed to have a field named fieldName
     * @param 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
     */
    public static void setClobMaxLength(PreparedStatement s, int fieldNum, String contents, long maxSize, Object o,
            String fieldName) throws SQLException {
        ArgumentNotValid.checkNotNull(s, "PreparedStatement s");
        if (contents != null) {
            if (contents.length() > maxSize) {
                log.warn("The field '" + fieldName + "' is " + contents.length() + " characters long, which is "
                        + (contents.length() - maxSize) + " longer than the allowed " + maxSize
                        + " characters. The contents is now truncated to " + "length " + maxSize);
                // This caused OOM if both the 'contents' and o.toString() was large
                // (See NAS-2015).
                // It is therefore omitted from this log-entry.

                // truncate to length maxSize (if maxSize <= Integer.MAX_VALUE)
                // else truncate to length Integer.MAX_VALUE
                if (maxSize > Integer.MAX_VALUE) {
                    log.warn("The maxSize is larger than maxint (" + Integer.MAX_VALUE
                            + "), which is not allowed. MaxSize changed to maxint");
                    maxSize = Integer.MAX_VALUE;
                }
                contents = contents.substring(0, (int) maxSize);
            }
            s.setCharacterStream(fieldNum, new StringReader(contents), contents.length());
            s.setString(fieldNum, contents);
        } else {
            s.setNull(fieldNum, Types.CLOB);
        }
    }

    /**
     * Insert a long value (which could be null) into
     * the given field of a statement.
     * @param s a prepared Statement
     * @param i the number of a given field in the prepared statement
     * @param 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
     */
    public static void setLongMaybeNull(PreparedStatement s, int i, Long value) throws SQLException {
        ArgumentNotValid.checkNotNull(s, "PreparedStatement s");
        if (value != null) {
            s.setLong(i, value);
        } else {
            s.setNull(i, Types.BIGINT);
        }
    }

    /**
     * Insert an Integer in prepared statement.
     * @param s a prepared statement
     * @param i the index of the statement, where the Integer should be inserted
     * @param 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
     */
    public static void setIntegerMaybeNull(PreparedStatement s, int i, Integer value) throws SQLException {
        ArgumentNotValid.checkNotNull(s, "PreparedStatement s");

        if (value != null) {
            s.setInt(i, value);
        } else {
            s.setNull(i, Types.INTEGER);
        }
    }

    /**
     * Get an Integer from the resultSet in column i.
     * @param rs the resultset
     * @param i the column where the wanted Integer resides
     * @return 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
     */
    public static Integer getIntegerMaybeNull(ResultSet rs, int i) throws SQLException {
        ArgumentNotValid.checkNotNull(rs, "ResultSet rs");
        Integer res = rs.getInt(i);
        if (rs.wasNull()) {
            return null;
        }
        return res;
    }

    /**
     * Get a Long from the resultSet in column i.
     * @param rs the resultset
     * @param i the column where the wanted Long resides
     * @return 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
     */
    public static Long getLongMaybeNull(ResultSet rs, int i) throws SQLException {
        ArgumentNotValid.checkNotNull(rs, "ResultSet rs");
        Long res = rs.getLong(i);
        if (rs.wasNull()) {
            return null;
        }
        return res;
    }

    /** Return a description of where an object is used elsewhere in the
     * database, or null.
     *
     * NB: the provided connection is not closed.
     *
     * @param connection connection to the database.
     * @param select A select statement finding the names of other uses.  The
     * statement should result in exactly one column of string values.
     * @param victim The object being used.
     * @param args Any objects that may be used to prepare the select statement.
     * @return A string describing the usages, or null if no usages were found.
     */
    public static String getUsages(Connection connection, String select, Object victim, Object... args) {
        ArgumentNotValid.checkNotNull(connection, "Connection connection");
        PreparedStatement s = null;
        try {
            s = prepareStatement(connection, select, args);
            ResultSet res = s.executeQuery();
            if (res.next()) {
                List<String> usedIn = new ArrayList<String>();
                do {
                    usedIn.add(res.getString(1));
                } while (res.next());
                return usedIn.toString();
            }
            return null;
        } catch (SQLException e) {
            final String message = "SQL error checking for usages of " + victim + "\n"
                    + ExceptionUtils.getSQLExceptionCause(e);
            log.warn(message, e);
            throw new IOFailure(message, e);
        } finally {
            closeStatementIfOpen(s);
        }
    }

    /** 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.
     *
     * @param connection connection to the database.
     * @param query a query with ? for parameters (must not be null or
     * an empty string)
     * @param args parameters of type string, int, long or boolean
     * @return The string result
     * @throws IOFailure if the statement didn't result in exactly one string
     * value
     */
    public static String selectStringValue(Connection connection, String query, Object... args) {
        ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
        ArgumentNotValid.checkNotNull(args, "Object... args");
        ArgumentNotValid.checkNotNull(connection, "Connection connection");

        PreparedStatement s = null;
        try {
            s = prepareStatement(connection, query, args);
            // We do not test for null-values here, already tested in
            // selectStringValue(s)
            return DBUtils.selectStringValue(s);
        } catch (SQLException e) {
            throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n"
                    + ExceptionUtils.getSQLExceptionCause(e), e);
        } finally {
            closeStatementIfOpen(s);
        }
    }

    /** Execute an SQL statement and return the single string in the result set.
     *
     * @param s A prepared statement
     * @return 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
     */
    public static String selectStringValue(PreparedStatement s) {
        ArgumentNotValid.checkNotNull(s, "PreparedStatement s");
        try {
            ResultSet res = s.executeQuery();
            if (!res.next()) {
                throw new IOFailure("No results from " + s);
            }
            String resultString = res.getString(1);
            if (res.wasNull()) {
                resultString = null;
            }
            if (res.next()) {
                throw new IOFailure("Too many results from " + s);
            }
            return resultString;
        } catch (SQLException e) {
            throw new IOFailure(
                    "SQL error executing statement " + s + "\n" + ExceptionUtils.getSQLExceptionCause(e), e);
        }
    }

    /** Execute an SQL query and return whether the result contains any rows.
     *
     * NB: the provided connection is not closed.
     *
     * @param connection connection to the database.
     * @param query a query with ? for parameters (must not be null or
     * an empty String)
     * @param args parameters of type string, int, long or boolean
     * @return True if executing the query resulted in at least one row.
     * @throws IOFailure if there were problems with the SQL query
     */
    public static boolean selectAny(Connection connection, String query, Object... args) {
        ArgumentNotValid.checkNotNull(connection, "Connection connection");
        ArgumentNotValid.checkNotNullOrEmpty(query, "String query");
        ArgumentNotValid.checkNotNull(args, "Object... args");

        PreparedStatement s = null;
        try {
            s = prepareStatement(connection, query, args);
            return s.executeQuery().next();
        } catch (SQLException e) {
            throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n"
                    + ExceptionUtils.getSQLExceptionCause(e), e);
        } finally {
            closeStatementIfOpen(s);
        }
    }

    /** Translate a "normal" glob (with * and .) into SQL syntax.
     *
     * @param glob A shell-like glob string (must not be null)
     * @return A string that implements glob in SQL "LIKE" constructs.
     */
    public static String makeSQLGlob(String glob) {
        ArgumentNotValid.checkNotNull(glob, "String glob");
        return glob.replace("*", "%").replace("?", "_");
    }

    /** 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.
     *
     * @param connection connection to the database.
     * @param updates The SQL statements that makes the necessary
     * updates.
     * @throws IOFailure in case of problems in interacting with the database
     */
    public static void executeSQL(Connection connection, final String... updates) {
        ArgumentNotValid.checkNotNull(updates, "String... updates");
        PreparedStatement st = null;
        String s = "";

        try {
            connection.setAutoCommit(false);
            for (String update : updates) {
                s = update;
                log.debug("Executing SQL-statement: " + update);
                st = prepareStatement(connection, update);
                st.executeUpdate();
                st.close();
            }
            connection.setAutoCommit(true);
            log.debug("Updated database using updates '" + StringUtils.conjoin(";", updates) + "'.");
        } catch (SQLException e) {
            String msg = "SQL error updating database with sql: " + s + "\n"
                    + ExceptionUtils.getSQLExceptionCause(e);
            log.warn(msg, e);
            throw new IOFailure(msg, e);
        } finally {
            rollbackIfNeeded(connection, "updating table with SQL: ", StringUtils.conjoin(";", updates) + "'.");
        }
    }

    /**
     * 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.
     * @param s a statement
     */
    public static void closeStatementIfOpen(Statement s) {
        if (s != null) {
            try {
                s.close();
            } catch (SQLException e) {
                log.warn("Error closing SQL statement " + s + "\n" + ExceptionUtils.getSQLExceptionCause(e), e);
            }
        }
    }
}