Untitled

package common.database;

import com.sun.rowset.CachedRowSetImpl;
import common.config.GlobalConfig;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import javax.sql.rowset.CachedRowSet;
import java.sql.*;

/**
 * 基础数据库类
 * Created by qijia on 2016/1/14.
 */
public class Database {

    private final Logger logger = LoggerFactory.getLogger(Database.class);

//    驱动注册被移动到tomcat启动监听器中
//    static {
//        try {
//            Class.forName("com.mysql.jdbc.Driver");
//        } catch (Exception e) {
//            logger.info("JDBC未注册!");
//        }
//    }

    private static GlobalConfig config = GlobalConfig.getInstance();

    // 数据库驱动名称
    private static String dburl = config.getString("jdbc.url");
    private static String dbusername = config.getString("jdbc.username");
    private static String dbpassword = config.getString("jdbc.password");

//    private PreparedStatement ps;
//    private ResultSet rs;

    /**
     * SQL语句在程序运行前已经进行了预编译，在程序运行时第一次操作数据库之前，SQL语句已经被数据库分析，
     * 编译和优化，对应的执行计划也会缓存下来并允许数据库已参数化的形式进行查询，当运行时动态地把参数传给
     * PreprareStatement时，即使参数里有敏感字符如 or ‘1=1’也数据库会作为一个参数一个字段的属性值来
     * 处理而不会作为一个SQL指令。
     */

    private Connection con;

    /**
     * 构造函数，连接数据库
     *
     * @throws Exception
     */
    public Database() throws SQLException {

        this.con = DriverManager.getConnection(dburl, dbusername, dbpassword);
    }

    /**
     * 构造函数，从连接池获取连接
     *
     * @throws Exception
     */
//    public Database() throws Exception {
//
//        this.con = DatabasePool.getInstance().getTomcatDataSource().getConnection();
//    }


    /**
     * 构造函数，从连接池获取连接
     *
     * @throws Exception
     */
    public Database(DataSource dataSource) throws SQLException {

        this.con = dataSource.getConnection();
    }

    /**
     * 返回连接 INSECURITY
     *
     * @return
     * @throws SQLException
     */
    public Connection getCon() throws SQLException {
        return this.con;
    }

    /**
     * 是否已经关闭
     *
     * @return
     * @throws SQLException
     */
    public boolean isClosed() {
        try {
            return this.con.isClosed();
        } catch (SQLException e) {
            return false;
        }
    }

    /**
     * 执行查询
     *
     * @param sql
     * @param args
     * @return
     * @throws SQLException
     */
    public ResultSet query(String sql, Object... args) throws SQLException {

        if (sql == null) {
//            closeQuietly(con);
            throw new SQLException("Null SQL statement");
        }

        PreparedStatement ps = null;
        ResultSet rs = null;
        CachedRowSet rowSet = null;

        try {
            ps = con.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            rowSet = new CachedRowSetImpl();
            rowSet.populate(rs);
        } catch (SQLException e) {
            throw e;
        } finally {
            //DONT uncomment
//            if (rowSet != null) {
//                rowSet.close();
//            }
            try {
                close(rs);
            } finally {
                if (ps != null) {
                    close(ps);
                }
//                if (con != null) {
//                    closeQuietly(con);
//                }
            }
        }
        return rowSet;
    }

    /**
     * 执行插入 更新 删除
     *
     * @param sql
     * @param args
     * @return
     * @throws SQLException
     */
    public boolean update(String sql, Object... args) throws SQLException {

        if (sql == null) {
//            close(con);
            throw new SQLException("Null SQL statement");
        }

        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            if (ps.executeUpdate() != 1) {
                return false;
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                close(ps);
            }
//            if (con != null) {
//                close(con);
//            }
        }
        return true;
    }

    /**
     * 执行插入,返回自增ID
     *
     * @param sql
     * @param args
     * @return
     * @throws SQLException
     */
    public long insertAutoIncrementLong(String sql, Object... args) throws SQLException {
        if (sql == null) {
//            close(con);
            throw new SQLException("Null SQL statement");
        }

        long id = -1;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            if (ps.executeUpdate() != 1) {
                return id;
            }
            rs = ps.getGeneratedKeys();
            if (rs.next()) {
                id = rs.getLong(1);
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            try {
                close(rs);
            } finally {
                if (ps != null) {
                    close(ps);
                }
//                if (con != null) {
//                    close(con);
//                }
            }
        }
        return id;
    }

    /**
     * 执行插入,返回自增ID
     *
     * @param sql
     * @param args
     * @return
     * @throws SQLException
     */
    public int insertAutoIncrementInt(String sql, Object... args) throws SQLException {
        if (sql == null) {
//            close(con);
            throw new SQLException("Null SQL statement");
        }

        int id = -1;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {

            ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            if (ps.executeUpdate() != 1) {
                return id;
            }
            rs = ps.getGeneratedKeys();
            if (rs.next()) {
                id = rs.getInt(1);
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            try {
                close(rs);
            } finally {
                if (ps != null) {
                    close(ps);
                }
//                if (con != null) {
//                    close(con);
//                }
            }
        }
        return id;
    }

    /**
     * 析构函数，中断数据库的链接
     * 不一定执行,发现未释放conn现象
     *
     * @throws SQLException
     */
    @Override
    protected void finalize() throws Throwable {
        try {
            if (con != null && !con.isClosed()) {
                con.close();
            }
        } finally {
            super.finalize();
        }
    }

    /**
     * 手动中断数据库的链接
     * Database对象完成工作后必须调用
     */
    public void close() throws SQLException {
        if (con != null && !con.isClosed()) {
            con.close();
        }
    }

    /**
     * Close a <code>Connection</code>, avoid closing if null and hide
     * any SQLExceptions that occur.
     */
    public void closeQuietly() {
        try {
            close(con);
        } catch (SQLException e) { // NOPMD
            // quiet
        }
    }

    /**
     * Close a <code>Connection</code>, avoid closing if null.
     *
     * @param conn Connection to close.
     * @throws SQLException if a database access error occurs
     */
    protected void close(Connection conn) throws SQLException {
        if (conn != null) {
            conn.close();
        }
    }

    /**
     * Close a <code>Statement</code>. This implementation avoids closing if
     * null and does <strong>not</strong> suppress any exceptions. Subclasses
     * can override to provide special handling like logging.
     *
     * @param stmt Statement to close
     * @throws SQLException if a database access error occurs
     * @since DbUtils 1.1
     */
    protected void close(Statement stmt) throws SQLException {
        if (stmt != null) {
            stmt.close();
        }
    }

    /**
     * Close a <code>ResultSet</code>. This implementation avoids closing if
     * null and does <strong>not</strong> suppress any exceptions. Subclasses
     * can override to provide special handling like logging.
     *
     * @param rs ResultSet to close
     * @throws SQLException if a database access error occurs
     * @since DbUtils 1.1
     */
    protected void close(ResultSet rs) throws SQLException {
        if (rs != null) {
            rs.close();
        }
    }
}