Solved MySQL Grabbing Data

Discussion in 'Spigot Plugin Development' started by Willim, Jan 12, 2020.

  1. I have a table that stores previous player bans. If the player gets banned twice then the table will have two instances of the player, if I am trying to grab the both bans and store them separately how could I go about doing that? I tried doing a test where I grab the UUID stored to see if it would retrieve two (due to the uuid being stored twice) but it only retrieves it one time.
     
  2. I think it depends on how your data is set up. If you simply add a player every time they get banned, you probably won't have anything but one instance that keeps getting overwritten.
    You might have to manage your table differently. Instead of simply bans -> playerID, you can do something linke bans -> player id -> ban info.
    In ban info you could include things like what the ban was for, times the ban was issued, who issued it etc.
    Then just check how how many ban infos are there for the player, or add up all of the times issued.
     
  3. I personally use a Gson array to store all of my player's ban works for what I need it for. Storing it in a bans column and access it using the players UUID.
     
  4. I have it set so the table can have more than one instance but when you go to retrieve any data it will always choose the first one. Also, thank you for the reply I appreciate it.

    [​IMG]
     

    Attached Files:

  5. what about instead of storing twice you have a number taht counts 2 for that id, weird but i think it's this what you look for don't you?
     
  6. Your UUID column shouldn’t be UNIQUE or a PRIMARY KEY if you want multiple entries to not overwrite one another; if it is already it sounds like your selection query isn’t written correctly

    This nullifies many of the benefits that MySQL offers. It makes it harder to interact via mysql-client, port to other DBMSs, and forces you to do parsing on the server side rather than offloading filtering or other computations on the database. It is worth looking into database normalization for more on this.

    This will make it more complicated to store anything else (eg ban reason, ban date, etc). Best not to go with this route, it’s a terrible way to design a database schema.
     
  7. It's not UNIQUE or a PRIMARY KEY:

    Code (Text):

    PreparedStatement tableBanRecord = connection.prepareStatement("CREATE TABLE IF NOT EXISTS ban_record (UUID VARCHAR(36),REASON VARCHAR(36),TIME VARCHAR(36),BANNEDBY VARCHAR(36))");
    tableBanRecord.executeUpdate();
     
     
  8. Have you tried running the query manually in a client?
     
  9. What do you mean? I tried getting the information using the method below, but that will always return the top result found in the table.

    What the MySQL Table looks like:

    Code (Text):


    UUID REASON TIME BANNEDBY
    uuid reason1 1578793150325 lol
    uuid reason2 1578793150329 lol
     
    Method:
    Code (Text):

    public String get(String uuid) {
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("SELECT REASON FROM `ban_record` WHERE UUID=?;");
            preparedStatement.setString(1, uuid);

            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) return resultSet.getString("REASON");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
     
    Result: reason1
     
  10. Doesn’t look like there’s any issues with MySQL to me, only how you are interpreting the ResultSet. Remember that a ResultSet may contain more than 1 row, so you’ll need to use ResultSet#next() in a loop to advance the row pointer and get the next reason.

    I suspect you’re probably calling this method 2 times but that’s now how it works. The ResultSet contains both of the rows you have in your table, you should return some sort of Collection<String> if you want to get multiple ban reasons.
     
    • Like Like x 1
  11. Code (Text):

    public ArrayList<String> get(String uuid) {
        try {
            ArrayList<String> history = new ArrayList<String>();
            PreparedStatement preparedStatement = connection.prepareStatement("SELECT REASON FROM `ban_record` WHERE UUID=?;");
            preparedStatement.setString(1, uuid);

            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) { // You loop until there is no more
                      history.add(resultSet.getString("REASON")); // Add each reason into an array
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return history; // return the array list
    }
     
     
  12. Thank you for the help, I have figured it out!
     
  13. Thanks for the help, I figured it out before your post but I appreciate it and hopefully it may be of a use to other people who may need it.