BULK insert of data

Source: Internet
Author: User

Tags: batch processing of data

Let's look at how to use the JDBC API to perform bulk inserts in Java. Although you may already know, I will try to explain the basics to complex scenarios.

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
[Java] String [] queries = {        INSERT into employee (name, city, phone) values (' A ', ' X ', ' 123 '),        INSERT into employee (NAM E, city, phone) values (' B ', ' Y ', ' 234 '),        insert in employee (name, city, phone) values (' C ', ' Z ', ' 345 '),    };
   connection Connection = new getconnection ();    Statement statemenet = Connection.createstatement ();    for (String query:queries) {        statemenet.execute (query);    }    Statemenet.close ();    Connection.close (); [/java]

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
[Java]    Connection Connection = new getconnection ();    Statement statemenet = Connection.createstatement ();    for (String query:queries) {        statemenet.addbatch (query);    }    Statemenet.executebatch ();    Statemenet.close ();    Connection.close (); [/java]

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:

[Java]   Import java.sql.Connection;   Import java.sql.Statement;   //...   Connection Connection = new getconnection ();   Statement statemenet = Connection.createstatement ();   for (Employee employee:employees) {       String query = "insert to Employee (name, city) VALUES ('               + Employe E.getname () + ', ' + employee.getcity + ');       Statemenet.addbatch (query);   }   Statemenet.executebatch ();   Statemenet.close ();   Connection.close ();  [/java]

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:

[Java] 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 ();  Ps.close ();  Connection.close (); [/java]

?

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 a semi-universal record into a database using batch processing. Well, the OutOfMemoryError may be produced:

[Java] 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) [/java]

?

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.

[java] String sql = &quot;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:employees) {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 records ps.close (); Connection.close (); [/java] 

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.

Hope to be of help to you.

BULK insert of data

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

Tags Index: