1

I have a scenario where I'm supposed to do 20 insertions into a Database. I came across this question whose answer pointed out that it is better to do multiple insertions in a single query than single insertion in multiple queries. To accommodate that, I did the following:

HashMap<String, Integer> values = new HashMap<>();
values.put("String1", 1);
/**
* add other values
*/
values.put("String20", 20);

update(values);

And my update method:

private void update(HashMap<String, Integer> map) throws SQLException {

    StringBuilder queryBuilder = new StringBuilder(
            "INSERT OR REPLACE INTO Table1 (STRINGNAME, VALUE) VALUES ");
    for (int i = 0; i < map.size() - 1; i++) {
        queryBuilder.append("(?,?), ");
    }
    queryBuilder.append("(?,?);");
    try (Connection connection = getDataSource().getConnection();
            PreparedStatement pst = connection.prepareStatement(queryBuilder.toString());) {
        int i = 1;
        for (Map.Entry<String, Integer> entry : map.entrySet()) {
            pst.setString(i++, entry.getKey());
            pst.setInt(i++, entry.getValue());
        }
        pst.executeUpdate();
    }
}

I have seen this question and it's answers. But none of the answers have multiple insertions in a single query but rather have batch updates or updates in loop.

This is the only way of doing multiple insertions in a single query that I could come up with. Is this way of inserting multiple tuples into a Database (SQLite) using a single query, a good approach or is there any better way of doing this?

Update:

Based on a few tests, this way of updating in a single query is really fast when compared to doing it in a batch (refer the update in this question). The only question is, does this way of updating in a single query have any flaws or is there a better way of achieving this?

mtm
  • 504
  • 1
  • 7
  • 25

1 Answers1

1

Either you run the executeUpdate inside the loop or, better, add them in a batch and run the whole batch: pst.addBatch() in the loop and pst.executeBatch() after the loop

grekier
  • 2,322
  • 1
  • 16
  • 23
  • Correct me if I'm wrong, but isn't `addBatch` and `executeBatch` used for executing same query with multiple parameters in a batch i.e. single insertions in multi queries rather than multiple insertions in a single query? – mtm Aug 03 '21 at 12:47
  • I think you are correct. Multiple query within one transaction I believe. – grekier Aug 03 '21 at 13:03
  • Why would you say that the solution in the question is not recommended? I tested it and it is really fast when compared to doing it in batches or executing inside the loop. – mtm Aug 03 '21 at 17:26