package util;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.swing.JOptionPane;

public class JdbcUtilV2 {
    /**
     * 定義需要的變量
     */
    private static Connection connection = null;
    /**
     * 在大多情況下，我們使用的是PrepardStatement 來代替Statement
     * 這樣可以防止sql注入
     */
    private static PreparedStatement preparedStatement = null;
    private static ResultSet resultSet = null;

    /**
     * 連接數(shù)據(jù)庫參數(shù)
     */
    private static String username = "";
    private static String password = "";
    private static String driver = "";
    private static String url = "";

    /**
     * 加載驅動，只需一次
     */
    public JdbcUtilV2(String dr, String ur,String user, String passwd) {
        try {
        	username = user;
        	password = passwd;
            driver = dr;
            url = ur;
            Class.forName(driver);
        } catch (Exception e) {
            System.err.println("連接失敗，請檢查連接參數(shù)");
            e.printStackTrace();
        }
    }

    /**
     * 獲取數(shù)據(jù)庫連接
     *
     * @return 返回Connection
     */
    private static Connection getConnection() {
        try {
            connection = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
        	JOptionPane.showConfirmDialog(null , "獲取連接失敗,請檢查網(wǎng)絡是否連通", "系統(tǒng)消息", JOptionPane.CLOSED_OPTION);
            e.printStackTrace();
        }
        return connection;
    }
    
    /**
     *  查詢表
     *  @return 返回查詢結果的List
     */

    public ArrayList<HashMap<String, Object>> query(String sql, Object ...params){
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            setPreparedStatement(params);
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData(); // key name
            int columnCount = metaData.getColumnCount(); // column count
            
            ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
            while(resultSet.next()) {
            	HashMap<String, Object> rowData = new HashMap<String, Object>();
            	for(int i = 1; i <= columnCount; ++i) {
            		rowData.put(metaData.getColumnName(i), resultSet.getObject(i));
            	}
            	list.add(rowData);
            }
            return list;
        }catch (Exception e){
            System.err.println("查詢失??！");
            e.printStackTrace();
        } finally {
            close();
        }
        return null;
    }
    
    public boolean exists(String sql, Object ...params) {
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            setPreparedStatement(params);
            resultSet = preparedStatement.executeQuery();
            return resultSet.next();
        }catch (Exception e){
            System.err.println("查詢失敗！");
            e.printStackTrace();
        } finally {
            close();
        }
        return false;
    }
    /**
     * 可處理insert/delete/update語句
     * @param sql sql語句
     * @param params 占位符參數(shù)數(shù)組
     * @return 返回bool值，表示是否成功
     */
    public boolean execute(String sql, Object ...params){
        try {
            //獲取連接
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //對占位符進行賦值
            setPreparedStatement(params);
            //提交sql
            preparedStatement.executeUpdate();
            return true;
        } catch (Exception e){
            System.err.println("表更新失敗!");
            e.printStackTrace();
        } finally {
            close();
        }
        return false;
    }
    
    public void executeVoidProc(String sql, Object ...params){
        try {
            //獲取連接
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            //對sql中的占位符進行賦值
            setPreparedStatement(params);
            
            preparedStatement.execute();
        }catch (Exception e){
            System.err.println("查詢失??！");
            e.printStackTrace();
        } finally {
            close();
        }
    }
    /**
     * 對sql語句中的占位符進行賦值
     * @param params 參數(shù)值
     * @throws SQLException sql異常
     */
    private void setPreparedStatement(Object ... params) throws SQLException {
        if(params != null && params.length > 0){
            for(int i = 0; i < params.length; i++){
                if("null".equals(params[i])){
                    preparedStatement.setNull(i + 1, Types.NULL);
                }else{
                    preparedStatement.setObject(i + 1, params[i]);
                }
            }
        }
    }
    
 

    /**
     * 關閉資源的函數(shù)
     */
    private void close() {
        if(resultSet != null) {
            try {
                resultSet.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            resultSet = null;
        }
        if(preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            preparedStatement = null;
        }
        if(connection != null) {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            connection = null;
        }
    }

}