1

I have to **inserts 100s of rows** into the database such as all the column values are exactly same except one. For example sake consider a table

------------------
|UserId|Timestamp|
------------------

Now **only timestamp is changing for every insert**.

Is it advisable to use prepared statement in the following way ?

PreparedStatement pstmt = con.prepareStatement("INSERT INTO Pings (UserId,Timestamp) VALUES (?,?)"; 

pstmt.setInt(1,001); //setting user is 
while(true){
   pstmt.setTimestamp(2,getTimestamp());
   pstmt.executeUpdate(); 
}

Compared to 

while(true){
   pstmt.setInt(1,001);
   pstmt.setTimestamp(2,getTimestamp());
   pstmt.executeUpdate(); 
}

Will first approach work given that I am setting 1st column value only once ?

Sridhar DD
  • 1,972
  • 1
  • 10
  • 17
Eastern Monk
  • 6,395
  • 8
  • 46
  • 61

1 Answers1

5

I suggest you use batching with PreparedStatement.addBatch() and Statement.executeBatch(). That might look something like,

int count = 0;
while (true) {
    pstmt.setInt(1, 001);
    pstmt.setTimestamp(2, getTimestamp());
    pstmt.addBatch();
    if (++count % 50 == 0) { // <-- batch size of 50.
        pstmt.executeBatch();
    }
}
pstmt.executeBatch();
Elliott Frisch
  • 198,278
  • 20
  • 158
  • 249
  • Thanks but also tell me if pstmt.setInt(1,001); can be outside the loop. Does it really need to be set every time ? – Eastern Monk Jan 20 '15 at 08:00
  • @AksharPrabhuDesai Best practice would be to set it every time, performance wise I doubt it will make any measurable difference. Also, why did you put leading `0`(s) in `setInt(int, int)`? That's `setInt(1,1);` – Elliott Frisch Jan 20 '15 at 14:14