package com.oolong.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

public class DBHelper {

    private String driverName = "com.mysql.jdbc.Driver";

    private String url = "jdbc:mysql://localhost:3306/hrms?useSSL=true";

    private String userName = "root";

    private String password = "1234";

    private Connection conn;

    private static DBHelper dbHelper;

    public static Connection getConnection(){

        if (dbHelper == null) {

            DBHelper.dbHelper = new DBHelper();

        }

        return dbHelper.conn;

    }

    private DBHelper() {

        try {

            Class.forName(driverName);

            this.conn = DriverManager.getConnection(url, userName, password);

        } catch (ClassNotFoundException e) {

            e.printStackTrace();

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

    public static int executeUpdate(String sql, String[] param) {

        Connection conn = null;

        PreparedStatement pst = null;

        int res = 0;

        try{

            conn = DBHelper.getConnection();

            pst = conn.prepareStatement(sql);

            for (int i = 0; i < param.length; i++) {

                pst.setString(i+1, param[i]);

            }

            res = pst.executeUpdate();

            pst.close();

            conn.close();

        } catch (SQLException ex) {

            ex.printStackTrace();

        } catch (Exception e) {

            e.printStackTrace();

        }

        return res;

    }

    public static List<Map<String, Object>> executeQuery(String sql) {

        return executeQuery(sql, new String[0]);

    }

    public static List<Map<String, Object>> executeQuery(String sql, String[] param) {

        Connection conn = null;

        PreparedStatement pst = null;

        ResultSet rs = null;

        List<Map<String, Object>> resut = new ArrayList<>();

        Map<String, Object> map = null;

        try{

            conn = DBHelper.getConnection();

            pst = conn.prepareStatement(sql);

            for (int i = 0; i < param.length; i++) {

                pst.setString(i+1, param[i]);

            }

            rs = pst.executeQuery();

            ResultSetMetaData rsm = rs.getMetaData();

            String[] columns = new String[rsm.getColumnCount()];

            for (int i = 0; i < rsm.getColumnCount(); i++) {

                columns[i] = rsm.getColumnName(i + 1);

            }

            rs.beforeFirst();

            while(rs.next()) {

                map = new HashMap<>();

                for(String col : columns) {

                    map.put(col, rs.getObject(col));

                }

                resut.add(map);

            }

            rs.close();

            pst.close();

            conn.close();

        } catch (SQLException ex) {

            ex.printStackTrace();

        } catch (Exception e) {

            e.printStackTrace();

        }

        return resut;

    }

}