Spring JDBCTemplate vs Plain JDBC for inserting large numbers of records

We have to insert 2 millions of records across multiple tables and right now we are writing into a CSV file and using db2 import to load into database.

We wanted to change this logic to some kind of JDBC. While looking into multiple options,开发者_Go百科 I am confused with Spring JDBC template and plain JDBC.

Let us say I wanted to insert 1 million records into 10 tables, each of which will have 100 thousand, and all these are simple JDBC statements (not prepared statements because I don't know which table I am dealing with at runtime).

Whatever system we choose will need to handle inserting up to 15 million records for a peak request.

Which framework will be better?

---

If you want to move a lot of data, then using JDBC (or any library building on top of JDBC) may be a bad choice, compared to using bulk copy tools (like db2import). JDBC is going to be orders of magnitude slower, because

• JDBC is a very chatty protocol, and

• usually bulk copy tools relax constraints during the copy process.

The difference in time can be extreme: what takes the bulk copy tool 10 minutes can take hours using JDBC. You'll want to create a prototype and do some timings and be certain about what kind of performance you'll get before you commit to something like this.

---

If you're already using Spring, then you may as well use JdbcTemplate. It makes things a bit easier, and in some simple cases means you need not use the JDBC API directly yourself. Essentially, JdbcTemplate is a very thin wrapper around JDBC that removes some of your annoying boiler-plate code.

---

As skaffman said, if you are already using Spring then your choice is probably JdbcTemplate. Specifically you may want to look at the batchUpdate() method. Here is a pretty good example of how it works. I've used it to insert a couple hundred thousand rows quickly with great success.

---

Consider JdbcSession from jcabi-jdbc. It's as simple as JDBC should be, for example (inserting a million records):

JdbcSession session = new JdbcSession(source);
for (int i = 0; i < 1000000; ++i) {
  session.sql("INSERT INTO foo (number) VALUES (?)")
    .set(i)
    .insert(new VoidHandler());
}

That's it.