2

I have an SQL table "Stats" with 3 columns, id (int), name (varchar), and display (varchar). In my program, I want to cache the information pulled from that table when it's needed, so I'm using guava's LoadingCache.

    private static final LoadingCache<Integer, Stat> STATS = CacheBuilder.newBuilder()
        .build(new CacheLoader<Integer, Stat>() {
            @Override
            public Stat load(Integer key) throws Exception {
                try (PreparedStatement stmt = SQLProvider.get().prepareStatement(
                        "SELECT name, display FROM Stats WHERE id=?"
                )) {
                    stmt.setInt(1, key);

                    try (ResultSet rs = stmt.executeQuery()) {
                        if (!rs.next())
                            throw new IllegalArgumentException("No stat found in the Stats table with id " + key);

                        String name = rs.getString(1);
                        String display = rs.getString(2);

                        return new Stat(key, name, display);
                    }
                }
            }
        });

However, in most places in my program, I will only have the name for the corresponding stat, and I will need to get the id and display from that. I have this solution, but I'm hoping there is a better way to cache the information so I won't have to make two queries to the table for one stat.

public static Stat getByName(String name) {
    try (PreparedStatement stmt = SQLProvider.get().prepareStatement(
            "SELECT id FROM Stats WHERE name=?"
    )) {
        stmt.setString(1, name);

        try (ResultSet rs = stmt.executeQuery()) {
            if (!rs.next())
                return null;

            return get(rs.getInt(1));
        }
    } catch (SQLException e) {
        e.printStackTrace();
        return null;
    }
}

Is there a way I could get the value in a Cache under a different key? Should I be using two Caches, one for by id and one for by name? If so, how could I keep those two synchronized incase the table changes externally?

If anyone could let me know a better way to be going about this, that would be great, thanks!

Shadow
  • 33,525
  • 10
  • 51
  • 64
JacobRuby
  • 21
  • 3

0 Answers0