Package xbird.util.jdbc

Source Code of xbird.util.jdbc.JDBCUtils

/*
* @(#)$Id$
*
* Copyright 2003-2004 The Apache Software Foundation
* Copyright 2006-2008 Makoto YUI
*
* 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.
*
* Contributors:
*     Makoto YUI - ported from jakarta commons DBUtils
*/
package xbird.util.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Arrays;

import javax.annotation.Nonnull;
import javax.annotation.Nullable;

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

import xbird.config.Settings;

/**
*
* <DIV lang="en"></DIV>
* <DIV lang="ja"></DIV>
*
* @author Makoto YUI (yuin405+xbird@gmail.com)
*/
public final class JDBCUtils {

    private static final Log LOG = LogFactory.getLog(JDBCUtils.class);
    private static final boolean showSQL = "true".equalsIgnoreCase(Settings.get("xbird.showsql"));

    private JDBCUtils() {}

    public static Connection getConnection(@Nonnull String connectUrl, @Nullable String driverClassName, @Nullable String userName, @Nullable String password)
            throws ClassNotFoundException, SQLException {
        if(driverClassName != null) {
            Class.forName(driverClassName);
        }
        final Connection conn;
        if(userName == null) {
            conn = DriverManager.getConnection(connectUrl);
        } else {
            conn = DriverManager.getConnection(connectUrl, userName, password);
        }
        return conn;
    }

    public static Connection getConnection(@Nonnull String connectUrl, @Nullable String userName, @Nullable String password)
            throws SQLException {
        final Connection conn;
        if(userName == null) {
            conn = DriverManager.getConnection(connectUrl);
        } else {
            conn = DriverManager.getConnection(connectUrl, userName, password);
        }
        return conn;
    }

    /**
     * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
     *
     * @param conn The Connection to use to run the query.  The caller is
     * responsible for closing this Connection.
     * @param sql The SQL to execute.
     * @param params An array of query replacement parameters.  Each row in
     * this array is one set of batch replacement values.
     * @return The number of rows updated per statement.
     * @throws SQLException
     */
    public static int[] batch(Connection conn, String sql, Object[][] params) throws SQLException {
        final boolean autoCommit = conn.getAutoCommit();
        if(autoCommit) {
            conn.setAutoCommit(false);
        }
        PreparedStatement stmt = null;
        int[] rows = null;
        try {
            stmt = conn.prepareStatement(sql);
            for(int i = 0; i < params.length; i++) {
                fillStatement(stmt, params[i]);
                stmt.addBatch();
            }
            verboseQuery(sql, (Object[]) params);
            rows = stmt.executeBatch();
        } catch (SQLException e) {
            rethrow(e, sql, (Object[]) params);
        } finally {
            close(stmt);
        }
        return rows;
    }

    public static int[] batch(Connection conn, String... sqls) throws SQLException {
        final boolean autoCommit = conn.getAutoCommit();
        if(autoCommit) {
            conn.setAutoCommit(false);
        }
        Statement stmt = null;
        int[] rows = null;
        try {
            stmt = conn.createStatement();
            for(int i = 0; i < sqls.length; i++) {
                verboseQuery(sqls[i], (Object[]) null);
                stmt.addBatch(sqls[i]);
            }
            rows = stmt.executeBatch();
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            rethrow(e, sqls);
        } finally {
            close(stmt);
        }
        // change back commit mode.
        if(autoCommit) {
            conn.setAutoCommit(true);
        }
        return rows;
    }

    /**
     * Fill the <code>PreparedStatement</code> replacement parameters with
     * the given objects.
     *
     * @param params Query replacement parameters; <code>null</code> is a valid value to pass in.
     */
    public static void fillStatement(PreparedStatement stmt, Object[] params) throws SQLException {
        if(params == null) {
            return;
        }
        for(int i = 0; i < params.length; i++) {
            if(params[i] != null) {
                stmt.setObject(i + 1, params[i]);
            } else {
                // VARCHAR works with many drivers regardless
                // of the actual column type.  Oddly, NULL and
                // OTHER don't work with Oracle's drivers.
                stmt.setNull(i + 1, Types.VARCHAR);
            }
        }
    }

    /**
     * Execute an SQL SELECT query with a single replacement parameter. The
     * caller is responsible for closing the connection.
     *
     * @param conn The connection to execute the query in.
     * @param sql The query to execute.
     * @param param The replacement parameter.
     * @param rsh The handler that converts the results into an object.
     * @return The object returned by the handler.
     */
    public static Object query(Connection conn, String sql, Object param, ResultSetHandler rsh)
            throws SQLException {
        return query(conn, sql, new Object[] { param }, rsh);
    }

    /**
     * Execute an SQL SELECT query with replacement parameters.  The
     * caller is responsible for closing the connection.
     *
     * @param conn The connection to execute the query in.
     * @param sql The query to execute.
     * @param params The replacement parameters.
     * @param rsh The handler that converts the results into an object.
     * @return The object returned by the handler.
     */
    public static Object query(Connection conn, String sql, Object[] params, ResultSetHandler rsh)
            throws SQLException {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Object result = null;
        try {
            stmt = conn.prepareStatement(sql);
            fillStatement(stmt, params);
            verboseQuery(sql, params);
            rs = stmt.executeQuery();
            result = rsh.handle(rs);
        } catch (SQLException e) {
            rethrow(e, sql, params);
        } finally {
            try {
                close(rs);
            } finally {
                close(stmt);
            }
        }
        return result;
    }

    /**
     * Execute an SQL SELECT query without any replacement parameters.  The
     * caller is responsible for closing the connection.
     *
     * @param conn The connection to execute the query in.
     * @param sql The query to execute.
     * @param rsh The handler that converts the results into an object.
     * @return The object returned by the handler.
     */
    public static Object query(Connection conn, String sql, ResultSetHandler rsh)
            throws SQLException {
        return query(conn, sql, (Object[]) null, rsh);
    }

    /**
     * Execute an SQL SELECT query with a single replacement parameter. The
     * caller is responsible for closing the connection.
     *
     * @param conn The connection to execute the query in.
     * @param sql The query to execute.
     * @param param The replacement parameter.
     * @return The object represents ResultSet.
     */
    public static ResultSet fetch(Connection conn, String sql, Object param) throws SQLException {
        return fetch(conn, sql, new Object[] { param });
    }

    /**
     * Execute an SQL SELECT query with replacement parameters.  The
     * caller is responsible for closing the connection.
     *
     * @param conn The connection to execute the query in.
     * @param sql The query to execute.
     * @param params The replacement parameters.
     * @return The object represents ResultSet.
     */
    public static ResultSet fetch(Connection conn, String sql, Object[] params) throws SQLException {
        ResultSet rs = null;
        try {
            PreparedStatement stmt = conn.prepareStatement(sql);
            fillStatement(stmt, params);
            verboseQuery(sql, params);
            rs = stmt.executeQuery();
        } catch (SQLException e) {
            rethrow(e, sql, params);
        }
        return rs;
    }

    /**
     * Execute an SQL SELECT query without any replacement parameters.  The
     * caller is responsible for closing the connection.
     *
     * @param conn The connection to execute the query in.
     * @param sql The query to execute.
     * @return The object represents ResultSet.
     */
    public static ResultSet fetch(Connection conn, String sql) throws SQLException {
        ResultSet rs = null;
        try {
            Statement stmt = conn.createStatement();
            verboseQuery(sql, (Object[]) null);
            rs = stmt.executeQuery(sql);
        } catch (SQLException e) {
            rethrow(e, sql, (Object[]) null);
        }
        return rs;
    }

    /**
     * Throws a new exception with a more informative error message.
     *
     * @param cause The original exception that will be chained to the new
     * exception when it's rethrown.
     * @param sql The query that was executing when the exception happened.    
     * @param params The query replacement paramaters; <code>null</code> is a
     * valid value to pass in.
     */
    private static void rethrow(SQLException cause, String sql, Object[] params)
            throws SQLException {
        StringBuilder msg = new StringBuilder(cause.getMessage());
        msg.append(" Query: ");
        msg.append(sql);
        msg.append(" Parameters: ");
        if(params == null) {
            msg.append("[]");
        } else {
            msg.append(Arrays.asList(params));
        }
        SQLException e = new SQLException(msg.toString());
        e.setNextException(cause);
        throw e;
    }

    private static void rethrow(SQLException cause, String[] sqls) throws SQLException {
        StringBuilder msg = new StringBuilder(cause.getMessage());
        msg.append(" Query: ");
        if(sqls == null) {
            msg.append("[]");
        } else {
            msg.append(Arrays.asList(sqls));
        }
        SQLException e = new SQLException(msg.toString());
        e.setNextException(cause);
        throw e;
    }

    /**
     * Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement
     * parameter.
     *
     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @param param The replacement parameter.
     * @return The number of rows updated.
     */
    public static int update(Connection conn, String sql, Object param) throws SQLException {
        return update(conn, sql, new Object[] { param });
    }

    /**
     * Execute an SQL INSERT, UPDATE, or DELETE query.
     *
     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @param params The query replacement parameters.
     * @return The number of rows updated.
     */
    public static int update(Connection conn, String sql, Object[] params) throws SQLException {
        PreparedStatement stmt = null;
        int rows = 0;
        try {
            stmt = conn.prepareStatement(sql);
            fillStatement(stmt, params);
            verboseQuery(sql, params);
            rows = stmt.executeUpdate();
        } catch (SQLException e) {
            rethrow(e, sql, params);
        } finally {
            close(stmt);
        }
        return rows;
    }

    /**
     * Execute an SQL INSERT, UPDATE, or DELETE query without replacement
     * parameters.
     *
     * @param conn The connection to use to run the query.
     * @param sql The SQL to execute.
     * @return The number of rows updated.
     */
    public static int update(Connection conn, String sql) throws SQLException {
        Statement stmt = null;
        int rows = 0;
        try {
            stmt = conn.createStatement();
            verboseQuery(sql, (Object[]) null);
            rows = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            rethrow(e, sql, (Object[]) null);
        } finally {
            close(stmt);
        }
        return rows;
    }

    public static boolean call(Connection conn, String sql, Object[] params) throws SQLException {
        CallableStatement proc = null;
        try {
            proc = conn.prepareCall(sql);
            fillStatement(proc, params);
            verboseQuery(sql, params);
            return proc.execute();
        } catch (SQLException e) {
            rethrow(e, sql, params);
        } finally {
            close(proc);
        }
        return false;
    }

    public static boolean call(Connection conn, String sql, Object params) throws SQLException {
        return call(conn, sql, new Object[] { params });
    }

    public static boolean call(Connection conn, String sql) throws SQLException {
        return call(conn, sql, (Object[]) null);
    }

    private static void verboseQuery(String sql, Object[] params) {
        if(showSQL) {
            StringBuilder msg = new StringBuilder(128);
            msg.append("Executing: ").append(sql);
            if(params != null) {
                msg.append(" Parameters: ");
                msg.append(Arrays.asList(params));
            }
            LOG.info(msg.toString());
        }
    }

    /**
     * Close a <code>Connection</code>, avoid closing if null.
     */
    public static void closeQuietly(Connection conn) {
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                LOG.error("failed closing a connection.", e);
            }
        }
    }

    /**
     * Close a <code>ResultSet</code>, avoid closing if null.
     */
    public static void close(ResultSet rs) throws SQLException {
        if(rs != null) {
            rs.close();
        }
    }

    /**
     * Close a <code>ResultSet</code>, avoid closing if null.
     */
    public static void closeAll(ResultSet rs) throws SQLException {
        if(rs != null) {
            Statement stmt = rs.getStatement();
            if(stmt != null) {
                Connection conn = stmt.getConnection();
                stmt.close();
                closeQuietly(conn);
            } else {
                rs.close();
            }
        }
    }

    /**
     * Close a <code>Statement</code>, avoid closing if null.
     */
    public static void close(Statement stmt) throws SQLException {
        if(stmt != null) {
            stmt.close();
        }
    }

    /**
     * Close a <code>Statement</code>, avoid closing if null.
     */
    public static void closeAll(Statement stmt) throws SQLException {
        if(stmt != null) {
            Connection conn = stmt.getConnection();
            stmt.close();
            closeQuietly(conn);
        }
    }

    /**
     * Commits a <code>Connection</code> then closes it, avoid closing if null.
     */
    public static void commitAndClose(Connection conn) throws SQLException {
        if(conn == null) {
            throw new IllegalArgumentException("Given connection is null although trying to commit the transaction.");
        }
        try {
            conn.commit();
        } finally {
            conn.close();
        }
    }
}
TOP

Related Classes of xbird.util.jdbc.JDBCUtils

TOP
Copyright © 2015 www.massapi.com. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.