用于JDBC操作数据库的公共类

/* 
 * @(#)CommonSql.java  2011-9-5 
 *  
 * Copyright 2011 Bianjing,All rights reserved. 
 */  

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

/**
 * 用于JDBC操作数据库的共通类
 * 
 * @author Bianjing
 * @version 1.0.0 2011-9-5
 */
public class CommonSql {
    /** 数据源 */
    private DataSource dataSource;

    /** 数据库连接对象 */
    private Connection connection;

    /** 数据库操作对象 */
    private PreparedStatement ps;

    /** 数据库操作对象 */
    private Statement statement;

    /** 返回的数据结果集对象 */
    private ResultSet rs;

    /** 是否自动提交事务，默认为true，如果该值为false则需要手动提交事务 */
    private boolean autoCommit = true;

    /** 数据库连接是否已经打开 */
    private boolean openConnection;

    /** JNDI名称 */
    private String jndiName;

    /** 数据库驱动 */
    private String driver;

    /** 数据库访问地址 */
    private String url;

    /** 用户名 */
    private String user;

    /** 密码 */
    private String pwd;

    public CommonSql() {
        
    }

    public CommonSql(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public CommonSql(String jndiName) {
        this.jndiName = jndiName;
    }

    public CommonSql(String driver, String url, String user, String pwd) {
        this.driver = driver;
        this.url = url;
        this.user = user;
        this.pwd = pwd;
    }

    /**
     * 打开数据库连接并创建数据库连接对象<br/>
     * 支持通过ICO注入数据源、数据库驱动、数据库驱动、JNDI名称、数据库访问地址和用户名、密码
     * 
     * @return boolean true:连接成功，false:连接失败
     */
    public boolean openConnection() {
        /**
         * 通过数据源来获取数据库连接对象
         */
        if (dataSource != null) {
            try {
                connection = dataSource.getConnection();
                // 数据库连接已经打开
                openConnection = true;
            } catch (SQLException e) {
                closeAll();
                // 所有的"System.out.println"都可以替换为"logger.error"
                System.out.println("从数据源获取数据库连接失败！");
                throw new RuntimeException(e);
            }

            return openConnection;
        }
        /**
         * 通过JNDI来获取数据库连接对象
         */
        if (jndiName != null) {
            try {
                Context initContext = new InitialContext();
                dataSource = (DataSource) initContext.lookup(jndiName);
                connection = dataSource.getConnection();
                // 数据库连接已经打开
                openConnection = true;
            } catch (Exception e) {
                closeAll();
                System.out.println("从JNDI获取数据库连接失败！");
                throw new RuntimeException(e);
            }

            return openConnection;
        }
        /**
         * 通过数据库驱动、数据库访问地址、用户名、密码来获取数据库连接对象
         */
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user, pwd);
            // 数据库连接已经打开
            openConnection = true;
        } catch (Exception e) {
            closeAll();
            System.out.println("数据库连接失败！");
            throw new RuntimeException(e);
        }

        return openConnection;
    }

    /**
     * 执行数据库的更新操作
     * 
     * @param sql
     *            要执行的SQL语句
     * @return boolean true:执行成功，false:执行失败
     */
    public boolean execUpdate(String sql, Object... args) {
        boolean isPassed = false;
        // 判断连接数据库是否成功
        if (openConnection) {
            try {
                ps = connection.prepareStatement(sql);
                // 设置参数
                if (args != null && args.length > 0) {
                    for (int i = 0; i < args.length; i++) {
                        ps.setObject(i + 1, args[i]);
                    }
                }
                ps.executeUpdate();

                isPassed = true;
            } catch (SQLException e) {
                try {
                    if (autoCommit) {
                        connection.rollback();
                    }
                } catch (SQLException e1) {
                    throw new RuntimeException(e1);
                }
                System.out.println("SQL:" + sql);
                throw new RuntimeException(e);
            } finally {
                if (autoCommit) {
                    closeAll();
                }
            }
        } else {
            System.out.println("数据库连接对象没有打开！");
        }

        return isPassed;
    }
    
    /**
     * 执行数据库的更新操作
     * 
     * @param sql
     *            要执行的SQL语句
     * @return boolean true:执行成功，false:执行失败
     */
    public boolean execUpdate(String sql, List<?> args) {
        return execUpdate(sql, args.toArray());
    }

    /**
     * 执行批量更新数据库操作
     * 
     * @param sql
     *            要执行的SQL语句的字符串数组
     * @return boolean true:执行成功，false:执行失败
     */
    public boolean execUpdate(Object[] sql) {
        boolean flag = false;
        // 判断连接数据库是否成功
        if (openConnection) {
            try {
                statement = connection.createStatement();
                for (int i = 0; i < sql.length; i++) {
                    statement.addBatch((String) sql[i]);
                }
                statement.executeBatch();

                flag = true;
            } catch (SQLException e) {
                try {
                    if (autoCommit) {
                        connection.rollback();
                    }
                } catch (SQLException e1) {
                    throw new RuntimeException(e1);
                }
                for (int i = 0; i < sql.length; i++) {
                    System.out.println("SQL " + (i + 1) + ":" + sql[i]);
                }
                throw new RuntimeException(e);
            } finally {
                if (autoCommit) {
                    closeAll();
                }
            }
        } else {
            System.out.println("数据库连接对象没有打开！");
        }

        return flag;
    }

    /**
     * 执行批量更新数据库操作
     * 
     * @param sql
     *            要执行的SQL语句的集合
     * @return boolean true:执行成功，false:执行失败
     */
    public boolean execUpdate(List<?> sql) {
        return execUpdate(sql.toArray());
    }

    /**
     * 执行数据库查询操作
     * 
     * @param sql
     *            要执行的SQL语句
     * @param args
     *            查询参数列表
     * @return ResultSet 返回查询的结果集对象
     */
    public ResultSet execQuery(String sql, Object... args) {
        rs = null;
        // 判断连接数据库是否成功
        if (openConnection) {
            try {
                ps = connection.prepareStatement(sql);
                // 设置参数
                if (args != null && args.length > 0) {
                    for (int i = 0; i < args.length; i++) {
                        ps.setObject(i + 1, args[i]);
                    }
                }
                
                rs = ps.executeQuery();
            } catch (SQLException e) {
                if (autoCommit) {
                    closeAll();
                }
                System.out.println("SQL:" + sql);
                throw new RuntimeException(e);
            }
        } else {
            System.out.println("数据库连接对象没有打开！");
        }

        return rs;
    }

    /**
     * 执行数据库查询操作
     * 
     * @param sql
     *            要执行的SQL语句
     * @param args
     *            查询参数列表
     * @return ResultSet 返回查询的结果集对象
     */
    public ResultSet execQuery(String sql, List<?> args) {
        return execQuery(sql, args.toArray());
    }

    /**
     * 根据标准SQL查询数据库，返回一个int值
     * 
     * @param sql
     *            要执行的SQL语句
     * @param args
     *            查询参数列表
     * @return int值，如果出错则返回-1
     */
    public int findForInt(String sql, Object... args) {
        ResultSet rs = execQuery(sql, args);
        int count = -1;
        try {
            if (rs != null && rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (autoCommit) {
                closeAll();
            }
        }

        return count;
    }
    
    /**
     * 根据标准SQL查询数据库，返回一个int值
     * 
     * @param sql
     *            要执行的SQL语句
     * @param args
     *            查询参数列表
     * @return int值，如果出错则返回-1
     */
    public int findForInt(String sql, List<?> args) {
        return findForInt(sql, args.toArray());
    }

    /**
     * 关闭所有数据库连接对象
     */
    public void closeAll() {
        if (rs != null || ps != null || statement != null || connection != null) {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                rs = null;
                if (ps != null || statement != null || connection != null) {
                    try {
                        if (ps != null && !ps.isClosed()) {
                            ps.close();
                        }
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    } finally {
                        ps = null;
                        if (statement != null || connection != null) {
                            try {
                                if (statement != null && !statement.isClosed()) {
                                    statement.close();
                                }
                            } catch (SQLException e) {
                                throw new RuntimeException(e);
                            } finally {
                                statement = null;
                                try {
                                    if (connection != null
                                            && !connection.isClosed()) {
                                        connection.close();
                                    }
                                } catch (SQLException e) {
                                    throw new RuntimeException(e);
                                } finally {
                                    connection = null;
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * 提交事务并关闭数据库连接
     */
    public void commit() {
        try {
            if (!autoCommit) {
                connection.commit();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            autoCommit = true;
            closeAll();
        }
    }

    /**
     * 回滚事务并关闭数据库连接
     */
    public void rollback() {
        try {
            if (!autoCommit) {
                connection.rollback();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            autoCommit = true;
            closeAll();
        }
    }

    public DataSource getDataSource() {
        return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public String getDriver() {
        return driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public boolean getAutoCommit() {
        return autoCommit;
    }

    public void setAutoCommit(boolean autoCommit) {
        try {
            connection.setAutoCommit(autoCommit);
        } catch (SQLException e) {
            closeAll();
            throw new RuntimeException(e);
        }
        this.autoCommit = autoCommit;
    }

    public boolean getOpenConnection() {
        return openConnection;
    }

    public String getJndiName() {
        return jndiName;
    }

    public void setJndiName(String jndiName) {
        this.jndiName = jndiName;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    public Connection getConnection() {
        return connection;
    }

    /**
     * 测试数据库连接是否成功
     * 
     * @param args
     * @throws SQLException
     */
    public static void main(String[] args) throws SQLException {
        String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        String url = "jdbc:sqlserver://127.0.0.1:1433;databaseName=demo";
        String user = "sa";
        String pwd = "sa";

        CommonSql commonSql = new CommonSql(driver, url, user, pwd);
        if (commonSql.openConnection()) {
            System.out.println("数据库连接成功！");
            DatabaseMetaData dbMetaData = commonSql.getConnection()
                    .getMetaData();
            System.out
                    .print("当前连接的数据库是:" + dbMetaData.getDatabaseProductName());
            System.out.println(" " + dbMetaData.getDatabaseProductVersion());
        } else {
            System.out.println("数据库连接失败！");
        }

        commonSql.closeAll();
    }
}