Java RedshiftClient 封装

package com.lattice.qa.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class RedshiftClient {
    private Connection con = null;
    private Statement stmt = null;

    public RedshiftClient(String dbURL, String userName, String password) throws SQLException {
        setupDBConnection(dbURL, userName, password);
    }

    public RedshiftClient() throws SQLException {
        setupDBConnection(Environment.INSTANCE.getValueByEnv("cdl.redShiftDBUrl"),
                Environment.INSTANCE.getValueByEnv("cdl.redShiftDBUser"),
                Environment.INSTANCE.getValueByEnv("cdl.redShiftDBPWD"));
    }

    ///
    // IsClose() is only guaranteed to return true if the connection was closed by a
    /// call to Connection.close(). If the connection was closed due to some errors,
    /// isClose() will not necessarily return true. Therefore, if it returns true,
    /// you can be sure the connection is closed, but if it returns false, you can't
    /// be sure.

    // isValid() does the opposite of isClose(). It attempts to do a positive check
    // that the connection is still open, by running a database query. If it returns
    // true, you know for sure the connection is open. If it returns false, you
    // can't be sure if it's open or not (since the query may be delayed due to some
    // network issues, which prevent it from completing before the given timeout).
    ///
    public boolean connectionIsClosed() {
        try {
            return !con.isValid(10);
        } catch (Exception exp) {
            return true;
        }
    }

    /**
     * The dbConnection will lost connection, when after waiting delete and PA job
     * completed, if the connection is closed, need to reset the connection.
     *
     * @throws SQLException
     */
    public void resetClosedConnection() throws SQLException {
        if (connectionIsClosed()) {
            setupDBConnection(Environment.INSTANCE.getValueByEnv("cdl.redShiftDBUrl"),
                    Environment.INSTANCE.getValueByEnv("cdl.redShiftDBUser"),
                    Environment.INSTANCE.getValueByEnv("cdl.redShiftDBPWD"));
        } else {
            System.out.println("Connecting is alive.");
        }
    }

    /**
     * The dbConnection will lost connection, when after waiting delete and PA job
     * completed, need to reset the connectin.
     *
     * @throws SQLException
     */
    public void resetConnection() throws SQLException {
        setupDBConnection(Environment.INSTANCE.getValueByEnv("cdl.redShiftDBUrl"),
                Environment.INSTANCE.getValueByEnv("cdl.redShiftDBUser"),
                Environment.INSTANCE.getValueByEnv("cdl.redShiftDBPWD"));
    }

    private void setupDBConnection(String dbURL, String userName, String password) throws SQLException {
        try {
            Class.forName("com.amazon.redshift.jdbc42.Driver");
        } catch (ClassNotFoundException ex) {
            System.out.println("Error: unable to load driver class!");
            System.exit(1);
        }
        System.out.println("Connecting to database...");
        Properties props = new Properties();
        props.setProperty("user", userName);
        props.setProperty("password", password);
        con = DriverManager.getConnection(dbURL, props);
        stmt = con.createStatement();
        System.out.println("Have Connected to database!");
    }

    public ResultSet executeQuery(String sql) throws SQLException {
        ResultSet rs = stmt.executeQuery(sql);
        return rs;
    }

    public int executeUpdate(String sql) throws SQLException {
        int count = stmt.executeUpdate(sql);
        return count;
    }

    public boolean execute(String sql) throws SQLException {
        boolean result = stmt.execute(sql);
        return result;
    }

    public void closeStament() throws SQLException {
        if (stmt != null) {
            stmt.close();
        }
    }

    public void closeConnection() throws SQLException {
        if (con != null) {
            con.close();
        }
        LogHelper.log("Redshift Connection Closed.");
    }

    public boolean existColumnName(String tableName, String columnName) throws SQLException {
        ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName);
        ResultSetMetaData rsmd = rs.getMetaData();
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
            if (rsmd.getColumnName(i).equalsIgnoreCase(columnName)) {
                return true;
            }
        }
        return false;
    }
}