JDBCWrapper.java

/*
    This file is part of Cyclos (www.cyclos.org).
    A project of the Social Trade Organisation (www.socialtrade.org).

    Cyclos is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.

    Cyclos 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 General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with Cyclos; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

 */
package nl.strohalm.cyclos.utils;

import java.math.BigDecimal;
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.util.Calendar;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import nl.strohalm.cyclos.utils.conversion.CoercionHelper;

import org.apache.commons.lang.ArrayUtils;

/**
 * Contains helper methods for JDBC access
 * @author luis
 */
public class JDBCWrapper {

    /**
     * Closes the given connection, ignoring any exceptions
     */
    public static void closeQuietly(final Connection conn) {
        try {
            conn.close();
        } catch (final Exception e) {
            // Ignore
        }
    }

    /**
     * Closes the given result set AND it's statement, ignoring any exceptions
     */
    public static void closeQuietly(final ResultSet rs) {
        Statement st = null;
        try {
            st = rs.getStatement();
        } catch (final Exception e) {
            // Ignore
        }
        try {
            rs.close();
        } catch (final Exception e) {
            // Ignore
        }
        closeQuietly(st);
    }

    /**
     * Closes the given statement, ignoring any exceptions
     */
    public static void closeQuietly(final Statement st) {
        try {
            st.close();
        } catch (final Exception e) {
            // Ignore
        }
    }

    /**
     * Set the given positional parameters on a prepared statement, guessing the argument types
     */
    private static void setParameters(final PreparedStatement ps, final Object... parameters) throws SQLException {
        if (ps == null || ArrayUtils.isEmpty(parameters)) {
            return;
        }
        for (int i = 0; i < parameters.length; i++) {
            final Object object = parameters[i];
            final int index = i + 1;
            if (object instanceof Number) {
                ps.setBigDecimal(index, CoercionHelper.coerce(BigDecimal.class, object));
            } else if ((object instanceof Calendar) || (object instanceof Date)) {
                final Calendar cal = CoercionHelper.coerce(Calendar.class, object);
                ps.setTimestamp(index, new Timestamp(cal.getTimeInMillis()));
            } else if (object instanceof Boolean) {
                ps.setBoolean(index, (Boolean) object);
            } else {
                ps.setString(index, CoercionHelper.coerce(String.class, object));
            }
        }
    }

    private final Connection connection;

    public JDBCWrapper(final Connection connection) {
        this.connection = connection;
    }

    /**
     * Closes the underlying connection
     */
    public void close() {
        closeQuietly(connection);
    }

    /**
     * Commits the underlying connection
     */
    public void commit() throws SQLException {
        connection.commit();
    }

    /**
     * Execute a single statement
     */
    public int execute(final String statement, final Object... parameters) throws SQLException {
        final PreparedStatement ps = connection.prepareStatement(statement);
        try {
            setParameters(ps, parameters);
            return ps.executeUpdate();
        } finally {
            closeQuietly(ps);
        }
    }

    public Connection getConnection() {
        return connection;
    }

    public boolean isHSQLDB() throws SQLException {
        return connection.getMetaData().getDatabaseProductName().toLowerCase().startsWith("hsql");
    }

    /**
     * Execute a sql query, returning the open result set
     */
    public ResultSet query(final String sql, final Object... parameters) throws SQLException {
        return doQuery(sql, ResultSet.CONCUR_READ_ONLY, parameters);
    }

    /**
     * Execute a sql query, returning the first row / column as Long
     */
    public Long readScalarAsLong(final String sql, final Object... parameters) throws SQLException {
        final PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = null;
        try {
            setParameters(ps, parameters);
            rs = ps.executeQuery();
            if (rs.next()) {
                final long data = rs.getLong(1);
                if (rs.wasNull()) {
                    return null;
                }
                return data;
            } else {
                return null;
            }
        } finally {
            closeQuietly(rs);
        }
    }

    /**
     * Execute a sql query, returning the first column as long in a list
     */
    public List<Long> readScalarAsLongList(final String sql, final Object... parameters) throws SQLException {
        final PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = null;
        try {
            setParameters(ps, parameters);
            rs = ps.executeQuery();
            final List<Long> list = new LinkedList<Long>();
            while (rs.next()) {
                list.add(rs.getLong(1));
            }
            return list;
        } finally {
            closeQuietly(rs);
        }
    }

    /**
     * Execute a sql query, returning the first row / column as string
     */
    public String readScalarAsString(final String sql, final Object... parameters) throws SQLException {
        final PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = null;
        try {
            setParameters(ps, parameters);
            rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getString(1);
            } else {
                return null;
            }
        } finally {
            closeQuietly(rs);
        }
    }

    /**
     * Execute a sql query, returning the first column as string in a list
     */
    public List<String> readScalarAsStringList(final String sql, final Object... parameters) throws SQLException {
        final PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = null;
        try {
            setParameters(ps, parameters);
            rs = ps.executeQuery();
            final List<String> list = new LinkedList<String>();
            while (rs.next()) {
                list.add(rs.getString(1));
            }
            return list;
        } finally {
            closeQuietly(rs);
        }
    }

    /**
     * Rollbacks the underlying connection
     */
    public void rollback() throws SQLException {
        connection.rollback();
    }

    /**
     * Execute a sql query, returning an updatable, open result set
     */
    public ResultSet updatableQuery(final String sql, final Object... parameters) throws SQLException {
        return doQuery(sql, ResultSet.CONCUR_UPDATABLE, parameters);
    }

    private ResultSet doQuery(final String sql, final int concurrency, final Object... parameters) throws SQLException {
        final PreparedStatement ps = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, concurrency);
        setParameters(ps, parameters);
        return ps.executeQuery();
    }
}