Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

multiple prepared statemnets using executeBatch

 
ramu av
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1364
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Check out the PreparedStatement page in the JDBC Tutorial
 
ramu av
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
ramu av
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Agree with Paul,

you should use stored procedure
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic