What's a more efficient way to use multiple values in Insert statement w/ PreparedStatement? – Mysql

Efficiency is a rather nebulous word. It sounds like you mean it in the sense of ‘I find this a rather clumsy way to write, surely there is a way to write an insert statement that takes less effort and is easier to read – that is more efficient from the point of view of writing/reading/maintaining the code. Let’s call that the ‘code efficiency’ factor.

The other obvious meaning of ‘efficient’ is how long it takes from the top of this method to the end of it, let’s call that db efficiency.

Code efficiency

Sure, there are vastly better ways than this. JDBC is intentional ‘low level glue’ – a ton of libraries are built on top of it, which means extending it or changing it is complicated (java is not, as a rule, in the business of making all existing code and libraries broken by making breaking changes). The JDBC API needs to expose every feature a database could plausibly want to expose, even features that are rarely needed.

As a consequence, you should not use raw JDBC when interacting with the DB directly. It’s glue – stuff that DB abstraction layers use.

JDBI and JOOQ

Instead, use libraries like JOOQ(https://jooq.org) or JDBI.

These libraries offer various options. Here are some JDBI samples:

public interface UserDao {
  @SqlUpdate("INSERT INTO "user" (id, "name") VALUES (?, ?)")
  void insert(int id, String name);
}

// .... and to use:

jdbi.withExtension(UserDao.class, dao -> {
  dao.insert(0, "Alice");
});

Note that if you want the safest, easiest to use transaction level (SERIALIZABLE), all your access to the DB needs to be in lambda form, because manually handling the retry is silly, don’t do that. Yet another excellent example why using JDBC directly is a very bad idea.

What’s retry? Long story – see the footnote ¹.

Focus on the insert part – that’s easier here. You just.. call a method, passing the data.

Or, even simpler if you’re doing a one-off insert (as in, only one spot in the code would ever do it, not ‘I only insert one record’):

jdbi.withHandle(handle -> {
  handle.execute("INSERT INTO "user" (id, "name") VALUES (?, ?)", 0, "Alice");
});

Table mapping

A third option is to define classes in java and then ask a DB abstraction engine to ‘convert’ these into CREATE TABLE statements. Then inserting a new row in the table involves making an instance of that class and saving it. Hibernate is the go-to tool for this. Hibernate is best treated as a system that saves and retrieves java objects that has nothing to do with SQL, the fact that it works by saving things to a DB should be treated as an implementation detail. Using hibernate can be very convenient indeed but expect high learning curves if you need to tweak or optimize the queries it ends up running.

No, only use JDBC

This boils down to ‘Badly reinvent JOOQ or JDBI yourself’. Make a method that takes the 5 args as argument and runs an insert, and consider turning that method into a Builder if you want a prettier API that is a lot easier to read later on when you forgot the order in which the arguments go.

DB efficiency

Databases will have plenty of docs on how to do fast inserts. A few highlights:

• If you’re doing a ton of inserts, consider using COPY instead.
• Generally, delete all indices and triggers, insert it all, then add them afterwards. Or, disable indices and triggers, add everything, then re-enable them and check them. How to do that? Depends on your DB. These steps would mean you can’t have a ‘running system’ (if other code is querying while you are doing this, you can’t just turn the triggers off).
• If you have to go with one-by-one inserts, re-use the prepared statement, and batch commits: You don’t want to commit after every insert (so, turn off auto-commit mode!), but you also don’t want to never commit. Maintain a counter and commit every ~1000 inserts or so.

[1] To truly get perfect transactions, you want any given sequence of DB actions to act as if it was the only thing on the planet that happened, and that it all happened in one go. This is extremely complicated and to do it truly right, the DB pretty much needs to lock everything the moment you begin. Which would mean a serious, multi-core system is going to run slow as molasses, so DBs instead double check once you commit if it could have been done like that – basically, it checks if every query you made during the transaction would still return the same results. If yes, the commit happens. If not, the commit fails – with a ‘retry error’. Which you handle by… doing the transaction again. However, computers tend to be annoyingly reliable, so you run the risk of the computer equivalent of you almost smacking into someone on the street, so you lean left, but they lean right, and it’s like a slapstick routine. To avoid that, you wait increasingly random amounts of times. This is all quite complicated (both handling the retry, as you need some DB-engine specific trickery to detect these, as well as doing the randomized incremental backoff) so you definitely don’t want to write it yourself. By passing a lambda (the thing with the array), JDBI/JOOQ can do it for you.