util之 oracle SQLUtil，java JDBC 实现sql语句execute执行插入、更新、删除dml操作，查询queryList数据库单列List数据，查询queryObject单记录...

本文摘自

http://www.xwood.net/_site_domain_/_root/5870/5874/t_c277906.html

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
 
import javax.sql.DataSource;
 
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
 
public final class SQLUtil {
 
    private static Log logger = LogFactory.getLog(SQLUtil.class);
 
    /**
     * 执行插入、更新、删除操作
     * 
     * @param dataSource
     * @param sql
     * @param params
     * @throws SQLException
     */
    public static int execute(DataSource dataSource, String sql, Object... params) throws SQLException {
 
        logger.debug("start execute sql:" + sql);
        long startTime = System.currentTimeMillis();
        Connection conn = null;
        Statement stat = null;
        int updateCount = 0;
 
        try {
            conn = dataSource.getConnection();
            if (params == null || params.length == 0) {
                stat = conn.createStatement();
                stat.execute(sql);
            } else {
                stat = conn.prepareStatement(sql);
                PreparedStatement pstat = (PreparedStatement) stat;
                int parameterIndex = 1;
                for (Object param : params) {
                    pstat.setObject(parameterIndex, param);
                    parameterIndex++;
                }
                pstat.execute();
            }
 
            updateCount = stat.getUpdateCount();
            logger.debug("end execute sql:" + sql + " UpdateCount:" + updateCount + " " + (System.currentTimeMillis() - startTime) + "ms");
        } finally {
            close(stat);
            close(conn);
        }
 
        return updateCount;
    }
 
    /**
     * 查询数据库单列List数据
     * 
     * @param dataSource
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static List<Object> queryList(DataSource dataSource, String sql, Object... params) throws SQLException {
        logger.debug("start execute sql:" + sql);
 
        long startTime = System.currentTimeMillis();
 
        Connection conn = null;
        Statement stat = null;
        List<Object> resultList = new ArrayList<Object>();
 
        try {
            conn = dataSource.getConnection();
            stat = conn.createStatement();
            if (params == null || params.length == 0) {
                stat = conn.createStatement();
                ResultSet rs = stat.executeQuery(sql);
                int i = 1;
                while (rs.next()) {
                    resultList.add(rs.getObject(i));
                    i++;
                }
                return resultList;
            } else {
                stat = conn.prepareStatement(sql);
                PreparedStatement pstat = (PreparedStatement) stat;
                int parameterIndex = 1;
                for (Object param : params) {
                    pstat.setObject(parameterIndex, param);
                    parameterIndex++;
                }
 
                ResultSet rs = pstat.executeQuery();

                int i = 1;
                while (rs.next()) {
                    resultList.add(rs.getObject(i));
                    i++;
                }
 
            }
 
            logger.debug("end query sql:" + sql + " Result:" + resultList + " "
                    + (System.currentTimeMillis() - startTime) + "ms");
 
        } finally {
            stat.close();
            conn.close();
        }
 
        return resultList;
    }
 
    public static Object queryObject(DataSource dataSource, String sql, Object... params) throws SQLException {
        List<Object> resultList = queryList(dataSource, sql, params);
        if (resultList == null || resultList.size() == 0)
            return null;
        return resultList.get(0);
    }
 
    public static void close(Connection con) {
        if (con != null)
            try {
                con.close();
            } catch (SQLException ex) {
                logger.debug("Could not close JDBC Connection", ex);
            } catch (Throwable ex) {
                logger.debug("Unexpected exception on closing JDBC Connection", ex);
            }
    }
 
    public static void close(Statement stmt) {
        if (stmt != null)
            try {
                stmt.close();
            } catch (SQLException ex) {
                logger.trace("Could not close JDBC Statement", ex);
            } catch (Throwable ex) {
                logger.trace("Unexpected exception on closing JDBC Statement", ex);
            }
    }
 
    public static void close(ResultSet rs) {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException ex) {
                logger.trace("Could not close JDBC ResultSet", ex);
            } catch (Throwable ex) {
                logger.trace("Unexpected exception on closing JDBC ResultSet", ex);
            }
    }
 
}