0

I have a HashMap of the column names and values that I need to store for a given user ID in a SQL database and as I need to store lots of key value pairs for each player, I decided to use a table full of players, with columns as keys.

This suggestion: Insert a HashMap into any database table looked like a good idea, however, upon more research, I found that keySet() and values() aren't guaranteed to be in the same order: Same iteration order on Map.keySet and Map.values?. This could cause problems, as with this suggestion, it is critical that both keySet() and values() line up perfectly in the same order, or else the wrong data may be inserted into a column.

At the moment, I am using the following code which works well, however if the HashMap is large, it can quickly create a large number of statements that could be cut down into just a single statement.

HashMap<String, Object> data = //SOME DATA
String uuid = //PLAYER UUID
try (Connection c = pool.getConnection()) {
    for (String key : data.keySet()) {
        try (PreparedStatement ps = c.prepareStatement("INSERT INTO `table` (`uuid`,`" + key + "`) VALUES (?,?) ON DUPLICATE KEY UPDATE `" + key + "`=?;")) {
            Object value = data.get(key);
            ps.setString(1, uuid);
            ps.setObject(2, value);
            ps.setObject(3, value);
            try {
                ps.execute();
            } catch (SQLException e) {
                // ERROR
            }
        } catch (SQLException e) {
            // ERROR
        }
    }
} catch (SQLException e) {
    // ERROR
}

I would like to use a PreparedStatement to sanitize the values, however this means I need to iterate through the HashMap once to create the list of columns for the statement, then iterate through it again to set the values. This is where the problem arises that keySet() and values() may not be in the same order, also it does add extra wasted time iterating through the map twice.

Is there a good way of accomplishing this, or should I somehow restructure my database to avoid this issue?

Josh
  • 86
  • 1
  • 2
  • 14
  • As to restructuring the database, would it make more sense to create a new table for each column, and then store key value pairs of UUID and value in the tables instead? I tried to condense it into one table to reduce the redundant storage spent on the UUIDs, but most resources I found online pointed towards this method instead. – Josh Jul 03 '19 at 22:30

1 Answers1

2

You can use LinkedHashMap to accomplish the task.

karthik akinapelli
  • 710
  • 1
  • 7
  • 14
  • I considered this, but it still means I have to iterate through the map twice, plus if I remember correctly, LinkedHashMaps are less efficient in performance and memory usage, but I'm not sure how much of an impact that would have over something else. – Josh Jul 03 '19 at 12:15