Solved Get every row from SQL database using manual query

Discussion in 'Spigot Plugin Development' started by MiniGutt, Mar 11, 2020.

  1. Hey, I am trying to get every banned user and add them to a list using a manual sql query, but cant get it to work. I only need the UID(user id) of every player that I have stored in the database. I need it to return a list of all the UIDs.

    This is the two different things I've tried, but none of them worked.
    Code (Text):
        public List<Ban> getBanned() {
            try {
                List<Ban> banned = new ArrayList<>();
                PreparedStatement ps = this.connection.prepareStatement("SELECT * FROM mc_bans");
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    Ban ban = new Ban();
                    ban.setUid(rs.getLong("uid"));
                    banned.add(ban);
                   
                    return banned;
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            return null;
        }
    Code (Text):
        @SuppressWarnings("unchecked")
        public List<Ban> getBanned() {
            try {
                PreparedStatement ps = this.connection.prepareStatement("SELECT * FROM mc_bans");
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                    Ban ban = new Ban();
                    ban.setUid(rs.getLong("uid"));
                   
                    return (List<Ban>) ban;
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            return null;
        }
    Thanks for any help in advance :)
     
  2. You're returning the list after adding at most 1 element to it. Try to move the return statement out of the while loop and it should be able to add all the selected rows to the list. Also, don't forget to close the ResultSet and the PreparedStatement. It looks like you're using a single connection over and over, so not closing these would result in a memory leak until the connection is closed (presumably when your plugin shuts down).
    An easy way to do this would be to use try-with-resources, which closes them when the function finishes. Then you won't have to use a finally{} block either (for both methods, they'll be closed even if an error occurs).

    EDIT: Also, you can just use SELECT uid FROM mc_bans to avoid selecting more data than necessary.
     
    #2 pim16aap2, Mar 11, 2020
    Last edited: Mar 11, 2020
  3. SELECT uid FROM mc_bans

    Why do you get the uuid's by "getLong" ?
    Write the uuid by string and get it by getString