How would I get multiple results from a MySQL table? [closed]

Say I have 2 columns, the first one is ID and the second one is colour.

How would I search for a specific colour and get all of the id’s that are in the same row as the colour?

How would I turn the results into an array?

I know how to do a simple string.

try {
    PreparedStatement ps = plugin.SQL.getConnection().prepareStatement("SELECT ID FROM playerinfo WHERE COLOUR = ?");
    ps.setString(1, colour);
    ResultSet rs = ps.executeQuery();
    int id = 0;
    if (rs.next()) {
        id = rs.getInt("ID");
        return id;
    }
} catch (SQLException e) {
    e.printStackTrace();
}

This is what it should look like

id colour
 1 **red**
 2 blue
 3 green
 4 **red**
 5 **red**
 6 blue


 **red** = highlighted (but we don't know why)

Answer

try (java.sql.Connection conn = plugin.SQL.getConnection();
     PreparedStatement ps = conn.prepareStatement("SELECT ID FROM playerinfo WHERE COLOUR = ?");) {
    ps.setString(1, colour);
    java.util.ArrayList<Integer> ids = new ArrayList<>();
    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
        ids.add(rs.getInt(1));
    }
}
catch (SQLException e) {
    e.printStackTrace();
}

• Use try-with-resources to make sure that you close the PreparedStatement and the Connection.
• Each call to method next() (in ResultSet) will return the next row in the result set. Extract the ID from the row and add it to the ArrayList.
• If you want an array, rather than ArrayList, you can convert ArrayList to array with one of the toArray methods in class ArrayList.

Maybe this will also help:
https://docs.oracle.com/javase/tutorial/jdbc/index.html