aspose file tools*
The moose likes JDBC and the fly likes multiple prepared statemnets using executeBatch Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "multiple prepared statemnets using executeBatch" Watch "multiple prepared statemnets using executeBatch" New topic
Author

multiple prepared statemnets using executeBatch

ramu av
Greenhorn

Joined: Feb 25, 2003
Posts: 28
hello,

suppose, i have 1000 transactions say inserts, updates, deletes to be executed. I have to execute these in order. that is i can not break the order. I have to perform these tansactions on the database using executeBatch and using PreparedStatement. I am able to execute these transactions using Statement objects but i need to make use of bind variables. Is there some way i can do this. please help.

current code:


But I need to use PreparedStatement instead of Statement.


Thanks in advance.

[Bear edit: not URGENT]
[ March 09, 2005: Message edited by: Bear Bibeault ]
Carol Enderlin
drifter
Ranch Hand

Joined: Oct 10, 2000
Posts: 1364
Check out the PreparedStatement page in the JDBC Tutorial
ramu av
Greenhorn

Joined: Feb 25, 2003
Posts: 28
i wanted to know a way to execute the follwing three sql statements
in a single batch, using a single prepared statement.

insert into emp (EmpNumber,EmpName) values (123,'Mike');
update emp set empName = 'Mikey' where EmpNumber=123;
delete from emp where EmpNumber = 123;
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Ehm, I have to ask, why?

You could wite SQL which defines an anonomous predure I suppose, but it would be awkward. Also you could write a Stored Procedure which does this, and execute that via a CallableStatement. But again, I'd have to ask why you would want to do this in the first place?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
ramu av
Greenhorn

Joined: Feb 25, 2003
Posts: 28
hi,

i have a very large(>10000) number of transactions to execute. hence i want to use batchExecute(). my large number of transactions are a collection of insert,update and delete transactions. I have to follow the order of transaction and hence i cannot use three seperate batches. I have to use preparedStatement for the reason that i can use bind variables and hence it is very efficient. So I would like to know a work around for the above situation.

thanks
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Agree with Paul,

you should use stored procedure


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Your explanation makes more sense now ramu av. I asked why because your example has you creating, updateing then deleting the same record, which seemed like an odd thing to do.

Statements addBatch method adds another distinct SQL statement to a batch of SQL statements. If you use PreparedStatements, you only have three distinct pieces of SQL which are compiled and reused, so you don't need to create a batch. You shouldn't however be trying to do this in one PreparedStatement. All you need do is loop through your 10000+ collection of objects to create,update and delete and bind their parameters accordingly.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: multiple prepared statemnets using executeBatch
 
Similar Threads
Closing batch update causes NullPointer.
Regarding Bulk insert
Using select values for inserts in a batch
Difference between Statement and PreparedStatement
help me in improving performance of prepared statement