Using Java HashMap as cache for database

Question

Came across this bit of sample code provided by a lecturer, and noticed something a bit odd: The data in the database must be emptied before executing a new insert query (I presume to avoid redundant data in the database).

Is there any way to avoid this roundabout way of inserting data to the database via hashmaps?

public void saveCustomers() throws ClassNotFoundException, SQLException {
    Statement statement = connection.createStatement();
    String query = "delete from customer";
    statement.executeUpdate(query);

    Iterator customers = HRMS.Customers.entrySet().iterator();

    while (customers.hasNext()) {
        Customer customer;
        Map.Entry customerMap = (Map.Entry) customers.next();
        customer = (Customer) customerMap.getValue();
        query = "insert into customer (memberId, name, nicn, gender, contact) "
                + "values ('" + customer.getMemberId() + "', '" + customer.getName() + "',"
                + " '" + customer.getId() + "', '" + customer.getGender().toString() + "', '" + customer.getContact() + "');";
        statement.executeUpdate(query);
    }
}

Edit: Will clearing the HashMap and inserting the data into the database simultaneously (presumably with different threads) be an improvement?

That's a horrible example of using JDBC. Ressource leaks, SQL injection, ... — Matthias, Jan 07 '16 at 18:35
@Matthias So many things wrong with this. OP might want to ask for his money back if he paid for this lecture. — rmlan, Jan 07 '16 at 18:36
That code smells awful! However, without a lot more context and knowledge of what is happening it's not really possible to decide if you should cache updates. This is a basic Computer Science problem: caching speeds things up at the expense of consistency, and if something fails while the cache doesn't match the underlying store, you have a problem. Someone once said _"The two hardest things in software development are cache consistency, naming things, and off-by-one errors"_ — Jim Garrison, Jan 07 '16 at 18:37
Using a HashMap for caching database queries is a half-hearted solution. A database running on the same computer will cache itself far better. It is O/R, Object-Relational mapping were a kind of caching comes to bear, but that is another level. — Joop Eggen, Jan 07 '16 at 19:09

score 1 · Answer 1 · answered Jan 07 '16 at 18:50

You can have another thread running forever that periodically checks the cache, and depending on an interval parameter that you can configure (once every 10 seconds for example) synchronize the database with the cache. But pay attention to concurrency! Is this what you're looking for?

score 1 · Answer 2 · edited May 23 '17 at 11:53

Easy to say for me, but I have to say it: "leave the lessons asap".

Nothing bad about caching some data for yourself, but in overall meaning, you won't do it better then SQL databases.

In some cases, you need to truncate tables before filling it with data, but you will know those cases for sure when you need them.

In this case, when you have a set of Customers, and you need to insert them all to database. You can query the database to get Customers, which needs to be updated, update those and insert others.

For instance, in mysql database you can do:

INSERT .. ON DUPLICATE KEY UPDATE ..

So if you have some unique index in database you can "insert or update" them in one statement, even for inserting multiple rows at once.

And inserting one row after one driving me mad in your code, when all (I hope so) sql databases allow you to insert them at once (by transactions or as "multiple rows insert").

Edit: before using HashMaps in multiple threads read something about thread safe operations, like here.

Using Java HashMap as cache for database

2 Answers2