public class MySQL {

    private MySQLCreditials credetails;
    private Connection connection;

    public MySQL(MySQLCreditials creditails) {
        this.credetails = creditails;
    }

    public void connect() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        this.connection = DriverManager.getConnection("jdbc:mysql://" + credetails.getHost() + ":" + credetails.getPort() + "/" + credetails.getDatabase() + "?autoReconnect=" + credetails.isAutoReconnect().toString() + "", credetails.getUser(), credetails.getPassword());
    }

    public void close() throws SQLException {
        this.connection.close();
    }

    public void update(String qry) {
        try {
            Statement st = (Statement) connection.createStatement();
            st.executeUpdate(qry);
            st.close();
        } catch (SQLException e) {
            System.err.println(e);
        }
    }

    public ResultSet query(String qry) {
        ResultSet rs = null;
        try {
            Statement st = (Statement) connection.createStatement();
            rs = st.executeQuery(qry);
        } catch (SQLException e) {
            System.err.println(e);
        }
        return rs;
    }

    public String getString(String table, String row, String optionkey, String optionvalue) {
        ResultSet rs = query("SELECT " + row + " FROM " + table + " WHERE " + optionkey + " = '" + optionvalue + "' LIMIT 1");
        try {
            if (rs.next() && rs.getString(row) != null) {
                return rs.getString(row);
            }
        } catch (SQLException e) {}
        return null;
    }

    public String getString(String table, String row) {
        ResultSet rs = query("SELECT " + row + " FROM " + table + " LIMIT 1");
        try {
            if (rs.next() && rs.getString(row) != null) {
                return rs.getString(row);
            }
        } catch (SQLException e) {}
        return null;
    }

    public void setString(String table, String row, String value, String optionkey, String optionvalue) {
        update("UPDATE " + table + " SET " + row + " = '" + value + "' WHERE " + optionkey + " = '" + optionvalue + "'");
    }

    public void setString(String table, String row, String value) {
        update("UPDATE " + table + " SET " + row + " = '" + value + "' WHERE 1");
    }

    public void addInteger(String table, String row, String optionkey, String optionvalue) {
        update("UPDATE " + table + " SET " + row + " = " + row + " + 1 WHERE " + optionkey + " = '" + optionvalue + "'");
    }

    public ArrayList<String> getList(String table, String row) {
        ArrayList<String> list = new ArrayList<String>();
        ResultSet rs = query("SELECT " + row + " FROM " + table + " WHERE 1");
        try {
            while(rs.next()) {
                list.add(rs.getString(row));
            }
        } catch (SQLException e) {}
        return list;
    }

    public ArrayList<String> getListSelectAll(String table) {
        ArrayList<String> list = new ArrayList<String>();
        ResultSet rs = query("SELECT " + "*" + " FROM " + table + " WHERE 1");
        try {
            while(rs.next()) {
                list.add(rs.getString(1));
            }
        } catch (SQLException e) {}
        return list;
    }

    public ArrayList<String> getList(String table, String row, String optionkey, String optionvalue) {
        ArrayList<String> list = new ArrayList<String>();
        ResultSet rs = query("SELECT " + row + " FROM " + table + " WHERE " + optionkey + " = '" + optionvalue + "'");
        try {
            while(rs.next()) {
                list.add(rs.getString(row));
            }
        } catch (SQLException e) {}
        return list;
    }

    public ArrayList<String> getListSelectAll(String table, String optionkey, String optionvalue) {
        ArrayList<String> list = new ArrayList<String>();
        ResultSet rs = query("SELECT " + "*" + " FROM " + table + " WHERE " + optionkey + " = '" + optionvalue + "'");
        try {
            while(rs.next()) {
                list.add(rs.getString(1));
            }
        } catch (SQLException e) {}
        return list;
    }

    public ArrayList<String> getList(String table, String row, String optionkey, String optionvalue, Integer limit) {
        ArrayList<String> list = new ArrayList<String>();
        ResultSet rs = query("SELECT " + row + " FROM " + table + " WHERE " + optionkey + " = '" + optionvalue + "' LIMIT " + limit);
        try {
            while(rs.next()) {
                list.add(rs.getString(row));
            }
        } catch (SQLException e) {}
        return list;
    }

    public ArrayList<String> getList(String table, String row, String optionkey, String optionvalue, String order, OrderMode mode) {
        ArrayList<String> list = new ArrayList<String>();
        ResultSet rs = query("SELECT " + row + " FROM " + table + " WHERE " + optionkey + " = '" + optionvalue + "' ORDER BY " + order + " " + mode.name());
        try {
            while(rs.next()) {
                list.add(rs.getString(row));
            }
        } catch (SQLException e) {}
        return list;
    }

    public ArrayList<String> getList(String table, String row, String optionkey, String optionvalue, Integer limit, String order, OrderMode mode) {
        ArrayList<String> list = new ArrayList<String>();
        ResultSet rs = query("SELECT " + row + " FROM " + table + " WHERE " + optionkey + " = '" + optionvalue + "' ORDER BY " + order + " " + mode.name() + " LIMIT " + limit);
        try {
            while(rs.next()) {
                list.add(rs.getString(row));
            }
        } catch (SQLException e) {}
        return list;
    }

    public HashMap<String, String> getRow(String table, String optionkey, String optionvalue) {
        HashMap<String, String> map = new HashMap<String, String>();
        ResultSet rs = query("SELECT * FROM " + table + " WHERE " + optionkey + " = '" + optionvalue + "'");
        try {
            ResultSetMetaData meta = rs.getMetaData();
            rs.next();
            for (int i = 1; i <= meta.getColumnCount(); i++) {
                String key = meta.getColumnName(i);
                String value = rs.getString(key);
                map.put(key, value);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }

    public HashMap<String, String> getRow(String table) {
        HashMap<String, String> map = new HashMap<String, String>();
        ResultSet rs = query("SELECT * FROM " + table + " WHERE 1");
        try {
            ResultSetMetaData meta = rs.getMetaData();
            rs.next();
            for (int i = 1; i <= meta.getColumnCount(); i++) {
                String key = meta.getColumnName(i);
                String value = rs.getString(key);
                map.put(key, value);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }

    public void insert(String table, String keys, String values) {
        update("INSERT INTO " + table + " (" + keys + ") VALUES (" + values + ")");
    }

    public void delete(String table, String optionkey, String optionvalue) {
        update("DELETE FROM " + table + " WHERE " + optionkey + " = '" + optionvalue + "'");
    }

    public void createTable(String name, String struckture) {
        update("CREATE TABLE " + name + " (" + struckture + ")");
    }

    public void createTableIfNotExists(String name, String struckture) {
        update("CREATE TABLE IF NOT EXISTS " + name + " (" + struckture + ")");
    }

    public void dropTable(String table) {
        update("DROP TABLE " + table);
    }

}