Java tutorial
/* * @(#)$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 gridool.util.jdbc; import gridool.Settings; 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; /** * * <DIV lang="en"></DIV> * <DIV lang="ja"></DIV> * * @author Makoto YUI (yuin405@gmail.com) */ public final class JDBCUtils { private static final Log LOG = LogFactory.getLog(JDBCUtils.class); private static final boolean showSQL = "true".equalsIgnoreCase(Settings.get("gridool.db.show_sql")); 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; } public static Connection getConnection(@Nonnull String connectUrl) throws SQLException { return DriverManager.getConnection(connectUrl); } /** * 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(); } } }