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!