public class MySQL {

    private static DataSource datasource;
    private static String connection;
        public MySQL (){
        connection = "jdbc:mysql://" + Props.getDbHost() + ":" + Props.getDbPort() + "/" + Props.getDbName();
        //connection = "jdbc:mysql://localhost:3306/kwik_virtuals";

        Logging.info("jdbc:mysql://" + Props.getDbHost() + ":" + Props.getDbPort() + "/" + Props.getDbName())
        init(connection,Configs.getDbUsername(),Configs.getDbPassword(),Configs.getMaxConnections());
    }

    public synchronized static ArrayList<HashMap<String, String>> query(Connection connection, String query) {

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<HashMap<String, String>> results = new ArrayList<>();
        //Logging.info("Running query :: " + query);

        try {
            conn = connection;
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);

            if (rs.next()) {
                //Logging.info("Result set not null.");
                ResultSetMetaData metaData = rs.getMetaData();
                String[] columns = new String[metaData.getColumnCount()];

                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    columns[i - 1] = metaData.getColumnLabel(i);
                }

                rs.beforeFirst();

                while (rs.next()) {
                    HashMap<String, String> record = new HashMap<String, String>();

                    for (String col : columns) {
                        record.put(col, rs.getString(col));
                    }

                    results.add(record);
                }
            }

            rs.close();
            stmt.close();
            conn.close();

        } catch (SQLException e) {
            Logging.error(MySQL.class.getName() + " " + e.getMessage());
            return null;
        } catch (Exception ex) {
            Logging.error(MySQL.class.getName() + " " + ex.getMessage(), ex);
            return null;
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logging.fatal(MySQL.class.getName() + " " + ex.getMessage());
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    Logging.fatal(MySQL.class.getName() + " " + ex.getMessage());
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logging.fatal(MySQL.class.getName() + " " + ex.getMessage());
                }
            }
        }

        //Logging.info("Found query results returning :: " + results.size());
        return results;
    }

    public synchronized static boolean recordExists(Connection connection, String query) {

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Boolean isPresent = false;
        //Logging.info("Running query ::" + query);

        try {
            conn = connection;
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);

            if (rs.next()) {
                //Logging.info("ResultSet not null");
                isPresent = true;
            }

            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException ex) {
            Logging.error(MySQL.class.getName() + " " + ex.getMessage(), ex);
            return isPresent;
        } catch (Exception ex) {
            Logging.error(MySQL.class.getName() + " " + ex.getMessage(), ex);
            return isPresent;
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logging.info(MySQL.class.getName() + " " + ex.getMessage());
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    Logging.info(MySQL.class.getName() + " " + ex.getMessage());
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logging.info(MySQL.class.getName() + " " + ex.getMessage());
                }
            }
        }

        //Logging.info("Found query results returning :: " + isPresent);
        return isPresent;
    }

    public synchronized static String update(Connection connection, String query) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String autoIncKeyFromDb = null;
        //Logging.info("Update query running :: " + query);

        try {
            conn = connection;
            stmt = conn.createStatement();
            stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);

            rs = stmt.getGeneratedKeys();

            if (rs.next()) {
                autoIncKeyFromDb = rs.getString(1);
            }

            //Logging.info("Auto increment key from db :: " + autoIncKeyFromDb + " from query :: " + query);
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException ex) {
            Logging.error(MySQL.class.getName() + " " + query + "  " + ex.getMessage(), ex);
        } catch (Exception e) {
            Logging.error(MySQL.class.getName() + " " + query + " " + e.getMessage(), e);
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    Logging.fatal(MySQL.class.getName() + " " + e.getMessage());
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    Logging.fatal(MySQL.class.getName() + " " + e.getMessage());
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logging.fatal(MySQL.class.getName() + " " + e.getMessage());
                }
            }
        }

        return autoIncKeyFromDb;
    }

    public static void init(String url,String username,String password,int maxConnections){
        PoolProperties p = new PoolProperties();
        p.setUrl(url);
        p.setDefaultAutoCommit(true);
        p.setDriverClassName("com.mysql.jdbc.Driver");
        p.setUsername(username);
        p.setPassword(password);
        p.setJmxEnabled(true);
        p.setTestWhileIdle(false);
        p.setTestOnBorrow(true);
        p.setValidationQuery("SELECT 1 FROM DUAL");
        p.setTestOnReturn(false);
        p.setValidationInterval(60000);
        p.setTimeBetweenEvictionRunsMillis(30000);
        p.setMaxActive(maxConnections);
        p.setInitialSize(10);
        p.setMaxWait(60000);
        p.setMinEvictableIdleTimeMillis(5000);
        p.setMinIdle(maxConnections);
        p.setMaxIdle(maxConnections);
        p.setLogAbandoned(false);
        p.setRemoveAbandoned(false);

        p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
                  "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;"+
                  "org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer");
        try {
            datasource = new DataSource(p);
            datasource.setPoolProperties(p);
            datasource.createPool();
        } catch (SQLException ex) {
            System.err.println(MySQL.class.getName()+" "+ex);
        }

    }
    public static Connection getConnection() throws SQLException {
        Connection conn = null;
        try {
            conn = datasource.getConnection();
            System.err.println("connection availability "+conn);
            Logging.info("connection availability "+conn);
        }catch(SQLException e) {
            Logging.error("connection availability failed "+e.getMessage(),e);
            System.err.println("connection availability failed "+e.getMessage());
 init(connection,Props.getDbUserName(),Props.getDbPassword(),Props.getMaxConnections());
            conn = datasource.getConnection();
        }
        return conn;
    }
}