Java JDBC Batch Insert data operation

In this note, we'll see how we can use the JDBC API like statement and PreparedStatement to bulk insert data into any database. In addition, we will try to explore scenarios such as working properly when memory is low, and how to optimize bulk operations.

First, BULK insert data into the database using the Java JDBC Basic API.

Simple batch-Easy batching
I call it the simple batch process. The requirement is simple to perform a BULK insert list instead of submitting the database each time for each INSERT statement, we will use the JDBC batch operation and optimize performance.

Think about the following code:

Bad Code String [] queries = {    "INSERT into employee (name, city, phone) values (' A ', ' X ', ' 123 ')",    "insert INTO employee ( Name, city, phone) values (' B ', ' Y ', ' 234 ') ",    " INSERT into employee (name, city, phone) values (' C ', ' Z ', ' 345 ') ",};c onnection connection = new getconnection (); Statement statemenet = Connection.createstatement (); for (String query:queries) {    statemenet.execute (query);} Statemenet.close (); Connection.close ();

This is a bad code. It executes each query individually and submits the database once for each INSERT statement. Consider if you want to insert 1000 records? This is not a good idea.

The following is the basic code for bulk insert execution. Take a look at:

Good Code

Connection Connection = new getconnection (); Statement statemenet = Connection.createstatement (); for (String query:queries) {    statemenet.addbatch (query);} Statemenet.executebatch (); Statemenet.close (); Connection.close ();

Notice how we use the Addbatch () method instead of executing the query directly. Then, to join all the queries, we use the Statement.executebatch () method to execute them at once. Nothing fancy, just a simple bulk insert.

Notice that we have constructed the query from a string array. Now, you might want to make it dynamic. For example:

Import Java.sql.connection;import java.sql.statement;//... Connection Connection = new getconnection (); Statement statemenet = Connection.createstatement (); for (Employee employee:employees) {    String query = "INSERT INTO E Mployee (name, city) VALUES (' "            + employee.getname () +" ', ' "+ employee.getcity +" ') ";    Statemenet.addbatch (query);} Statemenet.executebatch (); Statemenet.close (); Connection.close ();

Notice how we dynamically create the query from the data in the Employee object and add it in the batch, inserting one go. Perfect! Isn't it?

Wait a minute...... What do you have to think about SQL injection? This dynamically created query SQL injection is easy. And each insert query is compiled every time.

Why not use PreparedStatement instead of simple declarations. Yes, it's a solution. The following is a SQL injection security batch.

SQL injection safe Batch-sql injected with secure batch
Think about the following code:

Import Java.sql.connection;import java.sql.PreparedStatement; //... String sql = "INSERT into employee (name, city, phone) values (?,?,?)"; Connection Connection = new getconnection (); PreparedStatement PS = connection.preparestatement (SQL); for (Employee employee:employees) {    ps.setstring (1, Employee.getname ());    Ps.setstring (2, employee.getcity ());    Ps.setstring (3, Employee.getphone ());    Ps.addbatch ();} Ps.executebatch ();p s.close (); Connection.close ();

Look at the code above. Pretty. We use the java.sql.PreparedStatement and add Insert queries in the batch process. This is the solution you have to implement BULK insert logic, not the statement one above.

There remains a problem with this solution. Consider a scenario in which you want to insert tens of thousands of records into a database by using batch processing. Well, the OutOfMemoryError may be produced:

Java.lang.OutOfMemoryError:Java Heap Space
Com.mysql.jdbc.serverpreparedstatement$batchedbindvalues.<init> (serverpreparedstatement.java:72)
Com.mysql.jdbc.ServerPreparedStatement.addBatch (serverpreparedstatement.java:330)
Org.apache.commons.dbcp.DelegatingPreparedStatement.addBatch (delegatingpreparedstatement.java:171)

This is because you are trying to add all the statements in a batch and insert them one at a time. The best way is to execute batches of times. Take a look at the following solutions

Smart Insert:batch within batch-smart insert: Batch Batches
This is a simple solution. Consider a batch size of 1000, and each of the 1000 query statements is a batch of insert commits.

String sql = "INSERT into employee (name, city, phone) values (?,?,?)"; Connection Connection = new getconnection (); PreparedStatement PS = connection.preparestatement (SQL), final int batchsize = 1000;int count = 0;for (Employee employee:e mployees) {    ps.setstring (1, Employee.getname ());    Ps.setstring (2, employee.getcity ());    Ps.setstring (3, Employee.getphone ());    Ps.addbatch ();    if (++count% batchsize = = 0) {        ps.executebatch ();    }} Ps.executebatch (); Insert remaining recordsps.close (); Connection.close ();

This is the ideal solution, which avoids SQL injection and out-of-memory issues. To see how we increment the counter count, once batchsize reaches 1000, we call ExecuteBatch () to commit.

Source: http://itindex.blog.51cto.com/3619105/801447

Java JDBC Batch Insert data operation