• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Taking an argument from a file into SQL prep statement

 
rastas biggs
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have values from a file that I want to update my database with.

Now I'm not to sure how I do this, I want to take these values in the iteration of the file in this case the value MSM.

The I want to do an update in the database like so, there will be some 600 rows at a time.




Now I think there has to be a better way of doing this can someone give me and example of the most effeciant way of codeing this.

Regards

RB
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
it is looking fine to me except there are some sytax errors.

and you have one more option. but it will not result in gain in performance but it would clear your code a bit.

- read the file contents
- populate a collection from those
- then iterate over that collection and get your work done
 
nilesh Katakkar
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes there's a better way -

String sql = "UPDATE ACTIVE SET STATUS = 'ACTIVE' WHERE where MobileNumber = ?";
PreparedStatement ps = conn.prepareStatement(sql);

try {
while ((line=bwin.readLine()) !=null){
StringTokenizer st = new StringTokenizer(line, "|");
String MSN = (st.nextToken());
System.out.println(MSN);
ps.setString(1, MSN)
ps.addBatch();
}
ps.executeBatch();
} catch(...)

1. SQL UPDATE statement use bind variable instead of string.

2. conn.prepareStatement - outside of while loop. By putting it inside of loop you are actually making it parse the sql every time.

3. Use batching API.. add all statements to the batch and executeAll at once reducing round trips.

4. Since # of records are gonna be 500 to 600 I guess you can also try setting the default batch size. Some of the jdbc drivers and in specific ORACLE's jdbc driver, has default batch size of 1. Call setDefaultExecuteBatch on the OracleConnection object to set the batch size.
 
rastas biggs
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Neelesh, you are a deadset legend, thats the way to go. thanks Rastas.
 
nilesh Katakkar
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
oops.. . Okay !
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic