CODEWITHSUNDEEP
javadatabasenetbeansbulkinsert
Syed Raza
Syed Raza

Reputation: 331

Java Bulk Insertion Loops Take Time Code Attached?

hi i am new to java and i am inserting in in to database using loop from array it takes time how would i insert data in DB as bulk insertion my code here,

  if(con != null)
            { 

                    rs = dboperation.DBselectstatement(con,"select host_object_id from nagios_hosts where address='"+ip+"'");

                    if (rs != null)
                    { 
                       rs.next();
                       String id = rs.getString(1);
                       for(int i= 0;i<serviceArray.length;i++)
                       {         
                         status.append(serviceArray[i]+"\n");
                         dboperation.DbupdateStatement(DbAcess.getNagios_connection(),"insert into nagios_servicelist(service_name,host_object_id) values('"+serviceArray[i]+"','"+id+"')");
                       }
                    }

            }

do not go in detail about this code i tell you that i am getting id from the first query in "rs" resultset and "servicearray" have services that i want to insert in Db but it takes time in loop how will i do this array bulk insertion in Database?

hopes to listen from you soon

Thanks in Advance

Upvotes: 0

Views: 644

Answers (2)

user1440703
user1440703

Reputation:

The following code avoids out of memory error as well as SQL injection

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: 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();

Upvotes: 1

Pushkar
Pushkar

Reputation: 7580

You shuld use JDBC bulk insert for your purpose -

//Create a new statement
Statement st = con.createStatement();

//Add SQL statements to be executed
st.addBatch("insert into nagios_servicelist(service_name,host_object_id)    values('"+serviceArray[0]+"','"+id+"')");
st.addBatch("insert into nagios_servicelist(service_name,host_object_id)    values('"+serviceArray[1]+"','"+id+"')");
st.addBatch("insert into nagios_servicelist(service_name,host_object_id)    values('"+serviceArray[2]+"','"+id+"')");

// Execute the statements in batch
 st.executeBatch();

You can insert your own logic here. But this is the overview of how this is to be done.

Upvotes: 2

Related Questions