aspose file tools*
The moose likes JDBC and the fly likes Taking an argument from a file into SQL prep statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Taking an argument from a file into SQL prep statement" Watch "Taking an argument from a file into SQL prep statement" New topic
Author

Taking an argument from a file into SQL prep statement

rastas biggs
Ranch Hand

Joined: Sep 24, 2004
Posts: 41
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

Joined: Aug 15, 2004
Posts: 2874
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

Joined: Oct 27, 2004
Posts: 35
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.


nilesh<br />neilindallas@hotmail.com
rastas biggs
Ranch Hand

Joined: Sep 24, 2004
Posts: 41
Neelesh, you are a deadset legend, thats the way to go. thanks Rastas.
nilesh Katakkar
Ranch Hand

Joined: Oct 27, 2004
Posts: 35
oops.. . Okay !
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Taking an argument from a file into SQL prep statement