Is it possible to insert arraylist into database at once or do I have to iterate through every record and insert one by one?
Add a comment
|
3 Answers
You do batch insertions using JDBC:
PreparedStatement ps = c.prepareStatement("INSERT INTO MY_TABLE VALUES (?, ?)");
for(MyElement e : myList) {
ps.setString(1, e.getString());
ps.setInt(2, e.getInt());
ps.addBatch();
}
ps.executeBatch();
5 Comments
Ondrej Tokar
I do the same thing but with execute update instead addBatch, would you mind telling me the difference and then I mark it as answer? Also if you dont mind - What is faster, if you consider doing it with 5 milion records?
Jean Logeart
The difference is with
executeBatch, only one call containing all the inserts is made to the DB while when calling executeUpdate in the loop, as many calls as the size of your list are made to the DB. Since a call to the DB is what takes the longest time here, the first solution using executeBatch is definitely faster.Ondrej Tokar
One last thing, cannot the Batch be overloaded by that many records?
Jean Logeart
It might happen if your list is really large. In that case you might call
ps.executeBatch() in the loop every (say) 10 million records.Ondrej Tokar
If I could, I would give you more likes, thanks :), I have tested it and in 1000 records it is 5 times faster with the batch.
you can insert into database at once with batchquery like
int i =0;
PreparedStatement ps = con.prepareStatement("INSERT INTO DataTable VALUES (?)");
while(i<list.size()){
ps.setInt(1,list.get(i));
ps.addBatch();
i++;
}
ps.executeBatch();
Comments
You can convert ArrayList to Array and then pass array to the PrepareStatement using setArray() method.
