Simple callback framework for running SQL statements and handling the results.

ResultSetHandler.java

/*
 * Copyright 2011 Jesper de Jong
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *     http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.jesperdj.sandbox.dbutil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Handles the results of an SQL query.
 * 
 * @author Jesper de Jong
 */
public interface ResultSetHandler<T> {

    /**
     * Handles a {@code ResultSet}. This method is called by
     * {@link StatementRunner#executeQuery(Connection, String, Object[], ResultSetHandler) StatementRunner.executeQuery} to handle the results of an SQL query.
     * <p>
     * Implementations of this interface should not close the result set when finished. {@code StatementRunner} takes care of managing all JDBC objects.
     * 
     * @param rs The {@code ResultSet}.
     * @return An object that represents the processed results.
     * @throws SQLException If a database access error occurs.
     */
    T handleResultSet(ResultSet rs) throws SQLException;
}

SQLRepository.java

/*
 * Copyright 2011 Jesper de Jong
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *     http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.jesperdj.sandbox.dbutil;

import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

import javax.xml.parsers.SAXParserFactory;

import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

/**
 * Manages a set of SQL statements that are loaded from an XML file.
 * 
 * @author Jesper de Jong
 */
public final class SQLRepository {

    private final Map<String, SQLTemplate> templates = new HashMap<String, SQLTemplate>();

    /**
     * Initializes a new {@code SQLRepository} object.
     * 
     * @param resourceName The resource name of the XML file to load SQL statements from.
     * @throws SQLRepositoryException If an error occurs while initializing the object, for example when the XML file is not found or an
     *             error occurs while parsing it.
     */
    public SQLRepository(String resourceName) {
        try {
            InputStream in = SQLRepository.class.getResourceAsStream(resourceName);
            if (in == null) {
                throw new FileNotFoundException(resourceName);
            }

            SAXParserFactory.newInstance().newSAXParser().parse(in, new DefaultHandler() {

                private String statementName;
                private StringBuilder text = new StringBuilder();

                @Override
                public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
                    if (name.equals("sql")) {
                        statementName = attributes.getValue("name");
                    }

                    text = new StringBuilder();
                }

                @Override
                public void characters(char[] ch, int start, int length) throws SAXException {
                    text.append(ch, start, length);
                }

                @Override
                public void endElement(String uri, String localName, String name) throws SAXException {
                    if (name.equals("sql")) {
                        templates.put(statementName, new SQLTemplate(text.toString()));
                    }
                }
            });

            in.close();
        }
        catch (Exception e) {
            throw new SQLRepositoryException("Cannot initialise SQLRepository with resource: " + resourceName, e);
        }
    }

    /**
     * Gets the {@code SQLTemplate} with the specified name.
     * 
     * @param name The name of the {@code SQLTemplate} to get.
     * @return The {@code SQLTemplate} or {@code null} if there is no {@code SQLTemplate} with the specified name.
     */
    public SQLTemplate getTemplate(String name) {
        return templates.get(name);
    }
}

SQLRepositoryException.java

/*
 * Copyright 2011 Jesper de Jong
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *     http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.jesperdj.sandbox.dbutil;

/**
 * Thrown when an error occurs with with an {@code SQLRepository}.
 * 
 * @author Jesper de Jong
 */
@SuppressWarnings("serial")
public class SQLRepositoryException extends RuntimeException {

    /**
     * Initializes an new {@code SQLRepositoryException}.
     * 
     * @param message Message.
     * @param cause Cause.
     */
    public SQLRepositoryException(String message, Throwable cause) {
        super(message, cause);
    }
}

SQLTemplate.java

/*
 * Copyright 2011 Jesper de Jong
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *     http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.jesperdj.sandbox.dbutil;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.DataSource;

/**
 * An SQL statement with named parameters.
 * 
 * @author Jesper de Jong
 */
public final class SQLTemplate {

    private final static Pattern PARAM_NAME_PATTERN = Pattern.compile("#\\{.+?\\}");

    private final String sql;
    private final List<String> paramNames = new ArrayList<String>();

    /**
     * Initializes a new {@code SQLTemplate} with the specified SQL statement.
     * 
     * @param text An SQL statement that contains named parameters.
     */
    public SQLTemplate(String text) {
        Matcher matcher = PARAM_NAME_PATTERN.matcher(text);

        // Find named parameters and replace them by question marks
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            String group = matcher.group();
            this.paramNames.add(group.substring(2, group.length() - 1));
            matcher.appendReplacement(sb, "?");
        }
        matcher.appendTail(sb);

        // Replace sequences of white space by a single white space; store SQL statement
        this.sql = sb.toString().replaceAll("\\s+", " ").trim();
    }

    /**
     * Executes a query without parameters.
     * 
     * @param ds DataSource to get the database connection from.
     * @param handler Handler to handle the results of the query.
     * @return The object returned by the result set handler.
     * @throws SQLException If a database access error occurs.
     */
    public <T> T executeQuery(DataSource ds, ResultSetHandler<T> handler) throws SQLException {
        return StatementRunner.executeQuery(ds, sql, handler);
    }

    /**
     * Executes a query without parameters.
     * 
     * @param cn Database connection.
     * @param handler Handler to handle the results of the query.
     * @return The object returned by the result set handler.
     * @throws SQLException If a database access error occurs.
     */
    public <T> T executeQuery(Connection cn, ResultSetHandler<T> handler) throws SQLException {
        return StatementRunner.executeQuery(cn, sql, handler);
    }

    /**
     * Executes a query with parameters.
     * 
     * @param ds DataSource to get the database connection from.
     * @param sql SQL query statement to execute.
     * @param params A {@code Map} containing the parameters for the SQL statement (maps parameter names to values).
     * @param handler Handler to handle the results of the query.
     * @return The object returned by the result set handler.
     * @throws SQLException If a database access error occurs.
     */
    public <T> T executeQuery(DataSource ds, Map<String, Object> params, ResultSetHandler<T> handler) throws SQLException {
        return StatementRunner.executeQuery(ds, sql, getParamArray(params), handler);
    }

    /**
     * Executes a query with parameters.
     * 
     * @param cn Database connection.
     * @param params A {@code Map} containing the parameters for the SQL statement (maps parameter names to values).
     * @param handler Handler to handle the results of the query.
     * @return The object returned by the result set handler.
     * @throws SQLException If a database access error occurs.
     */
    public <T> T executeQuery(Connection cn, Map<String, Object> params, ResultSetHandler<T> handler) throws SQLException {
        return StatementRunner.executeQuery(cn, sql, getParamArray(params), handler);
    }

    /**
     * Execute a batch of queries. The same SQL statement is used for each of the queries, but with a different set of parameters.
     * The {@code ResultSetHandler} is called once for each query to handle the result.
     * 
     * @param ds DataSource to get the database connection from.
     * @param iter An {@code Iterator} that provides the parameters for each query.
     * @param handler Handler to handle the results of the query.
     * @return A {@code List} containing the objects that the {@code ResultSetHandler} returned for each of the queries.
     * @throws SQLException If a database access error occurs.
     */
    public <T> List<T> executeBatchQuery(DataSource ds, Iterator<Map<String, Object>> iter, ResultSetHandler<T> handler)
            throws SQLException {
        return StatementRunner.executeBatchQuery(ds, sql, new MapToArrayIterator(iter), handler);
    }

    /**
     * Execute a batch of queries. The same SQL statement is used for each of the queries, but with a different set of parameters.
     * The {@code ResultSetHandler} is called once for each query to handle the result.
     * 
     * @param cn Database connection.
     * @param iter An {@code Iterator} that provides the parameters for each query.
     * @param handler Handler to handle the results of the query.
     * @return A {@code List} containing the objects that the {@code ResultSetHandler} returned for each of the queries.
     * @throws SQLException If a database access error occurs.
     */
    public <T> List<T> executeBatchQuery(Connection cn, Iterator<Map<String, Object>> iter, ResultSetHandler<T> handler)
            throws SQLException {
        return StatementRunner.executeBatchQuery(cn, sql, new MapToArrayIterator(iter), handler);
    }

    /**
     * Executes an update or insert without parameters.
     * 
     * @param ds DataSource to get the database connection from.
     * @return The number of rows updated or inserted in the database.
     * @throws SQLException If a database access error occurs.
     */
    public int executeUpdate(DataSource ds) throws SQLException {
        return StatementRunner.executeUpdate(ds, sql);
    }

    /**
     * Executes an update or insert without parameters.
     * 
     * @param cn Database connection.
     * @return The number of rows updated or inserted in the database.
     * @throws SQLException If a database access error occurs.
     */
    public int executeUpdate(Connection cn) throws SQLException {
        return StatementRunner.executeUpdate(cn, sql);
    }

    /**
     * Executes an update or insert with parameters.
     * 
     * @param ds DataSource to get the database connection from.
     * @param params A {@code Map} containing the parameters for the SQL statement (maps parameter names to values).
     * @return The number of rows updated or inserted in the database.
     * @throws SQLException If a database access error occurs.
     */
    public int executeUpdate(DataSource ds, Map<String, Object> params) throws SQLException {
        return StatementRunner.executeUpdate(ds, sql, getParamArray(params));
    }

    /**
     * Executes an update or insert with parameters.
     * 
     * @param cn Database connection.
     * @param params A {@code Map} containing the parameters for the SQL statement (maps parameter names to values).
     * @return The number of rows updated or inserted in the database.
     * @throws SQLException If a database access error occurs.
     */
    public int executeUpdate(Connection cn, Map<String, Object> params) throws SQLException {
        return StatementRunner.executeUpdate(cn, sql, getParamArray(params));
    }

    /**
     * Executes a batch update or insert.
     * 
     * @param ds DataSource to get the database connection from.
     * @param iter An {@code Iterator} that provides the data for each row.
     * @return An array of update counts containing one element for each command in the batch. The elements of the array are ordered
     *         according to the order in which commands were added to the batch.
     * @throws SQLException If a database access error occurs.
     */
    public int[] executeBatchUpdate(DataSource ds, final Iterator<Map<String, Object>> iter) throws SQLException {
        return StatementRunner.executeBatchUpdate(ds, sql, new MapToArrayIterator(iter));
    }

    /**
     * Executes a batch update or insert.
     * 
     * @param cn Database connection.
     * @param iter An {@code Iterator} that provides the data for each row.
     * @return An array of update counts containing one element for each command in the batch. The elements of the array are ordered
     *         according to the order in which commands were added to the batch.
     * @throws SQLException If a database access error occurs.
     */
    public int[] executeBatchUpdate(Connection cn, final Iterator<Map<String, Object>> iter) throws SQLException {
        return StatementRunner.executeBatchUpdate(cn, sql, new MapToArrayIterator(iter));
    }

    /**
     * Converts a parameter map to an {@code Object[]} matching the parameters in the prepared statement.
     * 
     * @param params Parameter map (maps parameter names to values).
     * @return An {@code Object[]} for setting the parameters of the prepared statement.
     */
    private Object[] getParamArray(Map<String, Object> params) {
        Object[] array = new Object[paramNames.size()];

        int i = 0;
        for (String paramName : paramNames) {
            array[i++] = params.get(paramName);
        }

        return array;
    }

    private class MapToArrayIterator implements Iterator<Object[]> {

        private Iterator<Map<String, Object>> iter;

        public MapToArrayIterator(Iterator<Map<String, Object>> iter) {
            this.iter = iter;
        }

        public boolean hasNext() {
            return iter.hasNext();
        }

        public Object[] next() {
            return getParamArray(iter.next());
        }

        public void remove() {
            throw new UnsupportedOperationException();
        }
    }
}

StatementRunner.java

/*
 * Copyright 2011 Jesper de Jong
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *     http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.jesperdj.sandbox.dbutil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;

import javax.sql.DataSource;

/**
 * Utility methods for executing SQL statements.
 * <p>
 * The main purpose of this class is to make it easy to execute SQL statements without the need to write lots of "plumbing code"; this class
 * takes care of opening and closing JDBC objects (connections, statements, result sets).
 * 
 * @author Jesper de Jong
 */
public final class StatementRunner {

    private StatementRunner() {
        // Private constructor to prevent instantiation
    }

    /**
     * Executes a query without parameters.
     * 
     * @param ds {@code DataSource} to get the database connection from.
     * @param sql SQL query statement to execute.
     * @param handler Handler to handle the results of the query.
     * @return The object returned by the result set handler.
     * @throws SQLException If a database access error occurs.
     */
    public static <T> T executeQuery(DataSource ds, String sql, ResultSetHandler<T> handler) throws SQLException {
        return executeQuery(ds, sql, null, handler);
    }

    /**
     * Executes a query without parameters.
     * 
     * @param cn Database connection.
     * @param sql SQL query statement to execute.
     * @param handler Handler to handle the results of the query.
     * @return The object returned by the result set handler.
     * @throws SQLException If a database access error occurs.
     */
    public static <T> T executeQuery(Connection cn, String sql, ResultSetHandler<T> handler) throws SQLException {
        return executeQuery(cn, sql, null, handler);
    }

    /**
     * Executes a query with parameters.
     * 
     * @param ds {@code DataSource} to get the database connection from.
     * @param sql SQL query statement to execute.
     * @param params Parameters for the SQL statement.
     * @param handler Handler to handle the results of the query.
     * @return The object returned by the result set handler.
     * @throws SQLException If a database access error occurs.
     */
    public static <T> T executeQuery(DataSource ds, String sql, Object[] params, ResultSetHandler<T> handler) throws SQLException {
        Connection cn = null;

        try {
            cn = ds.getConnection();
            return executeQuery(cn, sql, params, handler);
        }
        finally {
            closeQuietly(cn);
        }
    }

    /**
     * Executes a query with parameters.
     * 
     * @param cn Database connection.
     * @param sql SQL query statement to execute.
     * @param params Parameters for the SQL statement.
     * @param handler Handler to handle the results of the query.
     * @return The object returned by the result set handler.
     * @throws SQLException If a database access error occurs.
     */
    public static <T> T executeQuery(Connection cn, String sql, Object[] params, ResultSetHandler<T> handler) throws SQLException {
        T result = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = cn.prepareStatement(sql);
            setParams(ps, params);
            rs = ps.executeQuery();
            result = handler.handleResultSet(rs);
        }
        catch (SQLException sqle) {
            rethrow(sqle, sql, params);
        }
        finally {
            closeQuietly(rs);
            closeQuietly(ps);
        }

        return result;
    }

    /**
     * Execute a batch of queries. The same SQL statement is used for each of the queries, but with a different set of parameters. The
     * {@code ResultSetHandler} is called once for each query to handle the result.
     * 
     * @param ds {@code DataSource} to get the database connection from.
     * @param sql SQL query statement to execute.
     * @param iter An {@code Iterator} that provides the parameters for each query.
     * @param handler Handler to handle the results of the query.
     * @return A {@code List} containing the objects that the {@code ResultSetHandler} returned for each of the queries.
     * @throws SQLException If a database access error occurs.
     */
    public static <T> List<T> executeBatchQuery(DataSource ds, String sql, Iterator<Object[]> iter, ResultSetHandler<T> handler)
            throws SQLException {
        Connection cn = null;

        try {
            cn = ds.getConnection();
            return executeBatchQuery(cn, sql, iter, handler);
        }
        finally {
            closeQuietly(cn);
        }
    }

    /**
     * Execute a batch of queries. The same SQL statement is used for each of the queries, but with a different set of parameters. The
     * {@code ResultSetHandler} is called once for each query to handle the result.
     * 
     * @param cn Database connection.
     * @param sql SQL query statement to execute.
     * @param iter An {@code Iterator} that provides the parameters for each query.
     * @param handler Handler to handle the results of the query.
     * @return A {@code List} containing the objects that the {@code ResultSetHandler} returned for each of the queries.
     * @throws SQLException If a database access error occurs.
     */
    public static <T> List<T> executeBatchQuery(Connection cn, String sql, Iterator<Object[]> iter, ResultSetHandler<T> handler)
            throws SQLException {
        List<T> result = new ArrayList<T>();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = cn.prepareStatement(sql);

            while (iter.hasNext()) {
                setParams(ps, iter.next());
                rs = ps.executeQuery();
                result.add(handler.handleResultSet(rs));

                closeQuietly(rs);
                rs = null;
            }
        }
        catch (SQLException sqle) {
            rethrow(sqle, sql, null);
        }
        finally {
            closeQuietly(rs);
            closeQuietly(ps);
        }

        return result;
    }

    /**
     * Executes an update or insert without parameters.
     * 
     * @param ds {@code DataSource} to get the database connection from.
     * @param sql SQL update or insert statement to execute.
     * @return The number of rows updated or inserted in the database.
     * @throws SQLException If a database access error occurs.
     */
    public static int executeUpdate(DataSource ds, String sql) throws SQLException {
        return executeUpdate(ds, sql, null);
    }

    /**
     * Executes an update or insert without parameters.
     * 
     * @param cn Database connection.
     * @param sql SQL update or insert statement to execute.
     * @return The number of rows updated or inserted in the database.
     * @throws SQLException If a database access error occurs.
     */
    public static int executeUpdate(Connection cn, String sql) throws SQLException {
        return executeUpdate(cn, sql, null);
    }

    /**
     * Executes an update or insert with parameters.
     * 
     * @param ds {@code DataSource} to get the database connection from.
     * @param sql SQL update or insert statement to execute.
     * @param params Parameters for the SQL statement.
     * @return The number of rows updated or inserted in the database.
     * @throws SQLException If a database access error occurs.
     */
    public static int executeUpdate(DataSource ds, String sql, Object[] params) throws SQLException {
        Connection cn = null;

        try {
            cn = ds.getConnection();
            return executeUpdate(cn, sql, params);
        }
        finally {
            closeQuietly(cn);
        }
    }

    /**
     * Executes an update or insert with parameters.
     * 
     * @param cn Database connection.
     * @param sql SQL update or insert statement to execute.
     * @param params Parameters for the SQL statement.
     * @return The number of rows updated or inserted in the database.
     * @throws SQLException If a database access error occurs.
     */
    public static int executeUpdate(Connection cn, String sql, Object[] params) throws SQLException {
        int result = 0;
        PreparedStatement ps = null;

        try {
            ps = cn.prepareStatement(sql);
            setParams(ps, params);
            result = ps.executeUpdate();
        }
        catch (SQLException sqle) {
            rethrow(sqle, sql, params);
        }
        finally {
            closeQuietly(ps);
        }

        return result;
    }

    /**
     * Executes a batch update or insert.
     * 
     * @param ds {@code DataSource} to get the database connection from.
     * @param sql SQL update or insert statement to execute.
     * @param iter An {@code Iterator} that provides the data for each row.
     * @return An array of update counts containing one element for each command in the batch. The elements of the array are ordered
     *         according to the order in which commands were added to the batch.
     * @throws SQLException If a database access error occurs.
     */
    public static int[] executeBatchUpdate(DataSource ds, String sql, Iterator<Object[]> iter) throws SQLException {
        Connection cn = null;

        try {
            cn = ds.getConnection();
            return executeBatchUpdate(cn, sql, iter);
        }
        finally {
            closeQuietly(cn);
        }
    }

    /**
     * Executes a batch update or insert.
     * 
     * @param cn Database connection.
     * @param sql SQL update or insert statement to execute.
     * @param iter An {@code Iterator} that provides the data for each row.
     * @return An array of update counts containing one element for each command in the batch. The elements of the array are ordered
     *         according to the order in which commands were added to the batch.
     * @throws SQLException If a database access error occurs.
     */
    public static int[] executeBatchUpdate(Connection cn, String sql, Iterator<Object[]> iter) throws SQLException {
        int[] result = null;
        PreparedStatement ps = null;

        try {
            ps = cn.prepareStatement(sql);

            while (iter.hasNext()) {
                setParams(ps, iter.next());
                ps.addBatch();
            }

            result = ps.executeBatch();
        }
        catch (SQLException sqle) {
            rethrow(sqle, sql, null);
        }
        finally {
            closeQuietly(ps);
        }

        return result;
    }

    /**
     * Sets the parameters in a prepared statement.
     * 
     * @param ps The prepared statement.
     * @param params The parameters to set.
     * @throws SQLException If a JDBC API method throws this exception.
     */
    private static void setParams(PreparedStatement ps, Object[] params) throws SQLException {
        if (params != null) {
            for (int i = 0; i < params.length; ++i) {
                if (params[i] != null) {
                    ps.setObject(i + 1, params[i]);
                }
                else {
                    ps.setNull(i + 1, Types.NULL);
                }
            }
        }
    }

    /**
     * Adds the SQL statement and parameters to the message of the exception and wraps the exception in an {@code SQLException}.
     * 
     * @param cause The original exception.
     * @param sql The SQL statement.
     * @param params The parameters of the statement.
     * @throws SQLException The exception with the SQL statement and parameters added to the message.
     */
    private static void rethrow(SQLException cause, String sql, Object[] params) throws SQLException {
        StringBuilder msg = new StringBuilder(cause.getMessage());

        msg.append("\nStatement: ");
        msg.append(sql);

        if (params != null) {
            msg.append("\nParameters: ");
            msg.append(Arrays.asList(params).toString());
        }

        throw new SQLException(msg.toString(), cause);
    }

    // Close a connection without throwing exceptions
    private static void closeQuietly(Connection cn) {
        if (cn != null) {
            try {
                cn.close();
            }
            catch (SQLException sqle) {
                // Ignore exception
            }
        }
    }

    // Close a statement without throwing exceptions
    private static void closeQuietly(Statement st) {
        if (st != null) {
            try {
                st.close();
            }
            catch (SQLException sqle) {
                // Ignore exception
            }
        }
    }

    // Close a result set without throwing exceptions
    private static void closeQuietly(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            }
            catch (SQLException sqle) {
                // Ignore exception
            }
        }
    }
}