Self-made manager for MySQL database. Very convenient query calls, set up parameter types manually instead of automatic identification. Used together with external config or hardcoded values for DB connection

gistfile1.txt

public class MySqlManager {

    private final static String user = "`" + App.config.DatabaseName + "`.`user`";
    private final static String account = "`" + App.config.DatabaseName + "`.`account`";

    static {
        try{
            Class.forName("com.mysql.cj.jdbc.Driver").getDeclaredConstructor().newInstance();
        }
        catch(Exception ex){
            Logger.logException(ex);
        }
    }

    static {
        checkSchema();
    }

    public void addAccount(String name, String middlename, String surname, String ssn, String mobile, String email, String password, LocalDate birthday) {
        executeQuery(QueryType.UPDATE, "INSERT INTO "+ user +" (name, middlename, surname, ssn, mobile, birthday) " +
                "VALUES (?, ?, ?, ?, ?, ?);" +
                "INSERT INTO "+account+" (email, password) " +
                "VALUES (?, SHA2(?, 256));",//AES better with secret key,
                new Object[] { name, middlename, surname, ssn, mobile, birthday, email, password },
                new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.DATE, Types.VARCHAR, Types.VARCHAR }
        );
    }

    public void removeAccount(String ssn) {
        executeQuery(QueryType.UPDATE, "DELETE FROM "+ user +" WHERE ssn = ?;",
                new String[] { ssn }, Types.VARCHAR);
    }

    public void updateName(int id, String name) {
        executeQuery(QueryType.UPDATE, "UPDATE "+ user +" SET name = ? WHERE id = ?;",
                new Object[] { name, id },
                new int[] { Types.VARCHAR, Types.INTEGER }
        );
    }

    public void updateBalance(int id, double value) {
        executeQuery(QueryType.UPDATE, "UPDATE " + user + " SET balance = ? WHERE id = ? ORDER BY id DESC LIMIT 1;",
                new Object[] { value, id },
                new int[] { Types.DECIMAL, Types.VARCHAR }
        );
    }

    public void updateMiddleName(int id, String middleName) {
        executeQuery(QueryType.UPDATE, "UPDATE "+ user +" SET middleName = ? WHERE id = ?;",
                new Object[] { middleName, id },
                new int[] { Types.VARCHAR, Types.INTEGER }
        );
    }

    public void updateSurname(int id, String surname) {
        executeQuery(QueryType.UPDATE, "UPDATE "+ user +" SET surname = ? WHERE id = ?;",
                new Object[] { surname, id },
                new int[] { Types.VARCHAR, Types.INTEGER }
        );
    }

    public void updatePhoneNumber(int id, String mobile) {
        executeQuery(QueryType.UPDATE, "UPDATE "+ user +" SET mobile = ? WHERE id = ?;",
                new Object[] { mobile, id },
                new int[] { Types.VARCHAR, Types.INTEGER }
        );
    }
    @SuppressWarnings("Because QueryType is Reader")
    public boolean updatePassword(int id, String oldPassword, String newPassword){
        boolean matches = false;
        Map<String, ArrayList<Object>> memberData = (Map<String, ArrayList<Object>>)executeQuery(QueryType.READER,
                "SELECT 1 FROM "+account+" WHERE member_id = ? AND password = SHA2(?, 256);",
                new Object[] { id, oldPassword },
                new int[] { Types.INTEGER, Types.VARCHAR }
        );
        if(memberData.size() != 0){
            executeQuery(QueryType.UPDATE, "UPDATE "+account+" SET password = SHA2(?, 256) WHERE member_id = ?;",
                    new Object[] { newPassword, id },
                    new int[] { Types.VARCHAR, Types.INTEGER }
            );
            matches = true;
        }

        return matches;
    }

    public boolean existsEmail(String email){
        return (boolean) executeQuery(QueryType.BOOL, "SELECT email FROM "+account+" WHERE email = ?",
            new String[] { email }, new int[] { Types.VARCHAR }
        );
    }

    @SuppressWarnings("Because QueryType is Reader")
    public boolean updateEmail(int id, String oldEmail, String newEmail) {
        boolean matches = false;
        Map<String, ArrayList<Object>> memberData = (Map<String, ArrayList<Object>> )executeQuery(QueryType.READER,
                "SELECT member_id FROM "+account+" WHERE member_id = ? AND email = ?;",
                new Object[] { id, oldEmail },
                new int[] { Types.INTEGER, Types.VARCHAR }
        );
        ArrayList<Object> entries = memberData.get("member_id");
        if(entries.size() != 0){
            executeQuery(QueryType.UPDATE, "UPDATE "+account+" SET email = ? WHERE id = ?;",
                    new Object[] { newEmail, id },
                    new int[] { Types.VARCHAR, Types.INTEGER }
            );
            matches = true;
        }

        return matches;
    }
    public User getUser(int id){
        Map<String, ArrayList<Object>> result = (Map<String, ArrayList<Object>>)executeQuery(QueryType.READER,
                "SELECT * FROM " + user + " WHERE id = ?;",
                new Object[] { id },
                new int[] { Types.INTEGER }
        );
        return new User(
                (int)result.get("id").get(0),
                (String)result.get("name").get(0),
                (String)result.get("middlename").get(0),
                (String)result.get("surname").get(0),
                (String)result.get("ssn").get(0),
                (String)result.get("mobile").get(0),
                ((java.sql.Date)result.get("birthday").get(0)).toLocalDate(),
                (BigDecimal)result.get("balance").get(0)
        );
    }
//    @SuppressWarnings("Because QueryType is Reader")
//    public User getUser(int id) {
//        Map<String, ArrayList<Object>> memberData = (Map<String, ArrayList<Object>>)executeQuery(QueryType.READER,
//                "SELECT * FROM "+member+" WHERE id = ?;",
//                new Object[] { id },
//                new int[] { Types.INTEGER }
//        );
//        Member member = new Member((int)memberData.get("id").get(0), (String)memberData.get("name").get(0), (String)memberData.get("middlename").get(0),
//                (String)memberData.get("surname").get(0), (String)memberData.get("ssn").get(0),
//                (String)memberData.get("mobile").get(0), ((java.sql.Date)memberData.get("birthday").get(0)).toLocalDate()
//        );
//        Map<String, ArrayList<Object>> leaderData = (Map<String, ArrayList<Object>>)executeQuery(QueryType.READER,
//                "SELECT * FROM "+leader+" WHERE member_id = ?;",
//                new Object[] { id },
//                new int[] { Types.INTEGER }
//        );
//        ArrayList<Object> entries = leaderData.get("member_id");
//        if(entries.size() != 0){
//            Map<String, ArrayList<Object>> activities = (Map<String, ArrayList<Object>>)executeQuery(QueryType.READER,
//                    "SELECT name FROM "+activity+" JOIN "+ leader_activity +" ON leader_member_id = ? AND activity_id = id",
//                    new Object[] { id },
//                    new int[] { Types.INTEGER }
//            );
//            ArrayList<Object> sports = activities.get("name");
//            String[] arr = new String[sports.size()];
//            for(int i = 0; i < arr.length; i++)
//                arr[i] = (String) sports.get(i);
//
//            member = member.toLeader((String)leaderData.get("key_number").get(0), (String)leaderData.get("board_position").get(0), arr);
//        }
//
//        return member;
//    }

    @SuppressWarnings("Because QueryType is Reader")
    public int checkCredentials(String email, String password) {
        int id = -1;

        Map<String, ArrayList<Object>> memberData = (Map<String, ArrayList<Object>>) executeQuery(QueryType.READER,
                "SELECT member_id FROM "+account+" WHERE email = ? AND password = SHA2(?, 256);",
                new Object[] { email, password },
                new int[] { Types.VARCHAR, Types.VARCHAR }
        );
        ArrayList<Object> entries = memberData.get("member_id");
        if(entries.size() != 0)
            id = (int) entries.get(0);

        return id;
    }

    private String repeat(int len){
        StringBuilder output = new StringBuilder();
        for(int i = 0; i < len; i++)
            output.append(",(?)");

        return output.toString();
    }

    private static Object getObject(ResultSet set, int col, int type) throws SQLException {
        switch (type){
            case -7 :
            case -6 :
            case 5 :
            case 4 :
                return set.getInt(col);
            case -1 :
            case 1 :
            case 12 :
                return set.getString(col);
            case 91 :
                return set.getDate(col);
            case 2004 :
            case -2 :
            case -3 :
            case -4 :
                return set.getBlob(col);
            case 0:
                return null;
            default:
                return set.getObject(col);
        }
    }
    
    public static void checkSchema()
    {
        try {
            executeQuery(QueryType.UDP,
                    "CREATE TABLE IF NOT EXISTS " + user + " ("+
                    "`id` INT NOT NULL AUTO_INCREMENT," +
                    "`name` VARCHAR(45) NOT NULL," +
                    "`middlename` VARCHAR(45) NULL DEFAULT NULL," +
                    "`surname` VARCHAR(45) NULL," +
                    "`ssn` CHAR(13) NOT NULL," +
                    "`mobile` VARCHAR(25) NOT NULL," +
                    "`balance` DECIMAL(15,2) NOT NULL DEFAULT 0," +
                    "`birthday` DATE NOT NULL," +
                    "PRIMARY KEY (`id`));" +

                    "CREATE TABLE IF NOT EXISTS " + account + " (" +
                    "`member_id` INT NOT NULL AUTO_INCREMENT," +
                    "`email` VARCHAR(128) NOT NULL," +
                    "`password` VARCHAR(128) NOT NULL," +
                    "INDEX `fk_account_member_idx` (`member_id` ASC),\n" +
                    "PRIMARY KEY (`member_id`),\n" +
                    "CONSTRAINT `fk_account_member`\n" +
                    "FOREIGN KEY (`member_id`)\n" +
                    "REFERENCES " + user + " (`id`)\n" +
                    "ON DELETE CASCADE\n" +
                    "ON UPDATE CASCADE);\n"
            );
        }
        catch (Exception ex){
            Logger.logException(ex, "Errors in query.");
        }
    }

    public static Object executeQuery(QueryType type, String query)
    {
        return executeQuery(type, query, null, null);
    }

    public static Object executeQuery(QueryType type, String query, Object[] parameters, int commonType){//java.sql.Types
        int[] types = new int[parameters.length];
        Arrays.fill(types, commonType);

        return executeQuery(type, query, parameters, types);
    }
    //STRICT ORDER
    public static Object executeQuery(QueryType type, String query, Object[] parameters, int[] types)//java.sql.Types
    {
        // This method is to reduce the amount of copy paste that there was within this class.
        if(parameters != null && parameters.length != types.length)
            throw new IllegalArgumentException("executeQuery(): parameters length is different to types length.");
        Object result = null;
        try(Connection connection = createConnection();
            PreparedStatement command = connection.prepareStatement(query)
        )
        {
            if(parameters != null)
                for(int i = 0; i < parameters.length; i++)
                    command.setObject(i + 1, parameters[i], types[i]);

            if(type == QueryType.READER){
                ResultSet set = command.executeQuery();
                ResultSetMetaData metaData = set.getMetaData();
                Map<String, ArrayList<Object>> results = new HashMap<>(10);
                int count = metaData.getColumnCount();
                for(int i = 1; i < count + 1; i++)
                    results.put(metaData.getColumnName(i), new ArrayList<>(30));
                while (set.next())
                    for(int i = 1; i < count + 1; i++)
                        results.get(metaData.getColumnName(i)).add(getObject(set, i, metaData.getColumnType(i)));

                result = results;
            }
            else if(type == QueryType.UPDATE){
                result = command.executeUpdate();
                //System.out.println(command.);
            }
            else if(type == QueryType.BOOL)
                result = command.executeQuery().next();//returns true if there is at least 1 selection result
            else
                command.execute();
        }
        catch (SQLException ex)
        {
            Logger.logException(ex);
        }

        return result;
    }

    private static Connection createConnection(){
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(App.config.DatabaseAddress, App.config.DatabaseUsername, App.config.DatabasePassword);
        } catch (SQLException ex) {
            Logger.logException(ex);
        }

        return conn;
    }

    public enum QueryType {
        UPDATE,//INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE
        READER,//SELECT -> ResultSet
        BOOL,//SELECT -> Exists selection or not
        UDP
    }
}

MySQL manager for public usage. No licence.