aspose file tools*
The moose likes JDBC and the fly likes Is it possible to use batch for SQL select statment Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Is it possible to use batch for SQL select statment" Watch "Is it possible to use batch for SQL select statment" New topic
Author

Is it possible to use batch for SQL select statment

Sharma Ashutosh
Bartender

Joined: Apr 06, 2001
Posts: 346
Is it possible to use batch for SQL select statment?
I m using prepared statement-which is setting one argument and then immedietely it fires a SQL select query to fetch the data. This way it's not very effient as i keep on getting this argument in bulk and they are grouped in certain manner.
What i want to know whether i can addBatch() and then executeBatch() or some other varient of batch updates in SQL select kind of queries?
for(int i=0; i < someLenth; i++)
{
pstmt.setLong(1,arr[i])
pstmt.addBatch();
}
pstmt.executeBatch();
where pstmt is just a prepared statement.
I need a resultSet object just the way i can get it from pstmt.executeQuery()


Ashutosh Sharma
SCJP 1.2, SCEA 5, Brainbench certified J2EE Developer, Documentum Certified Professional
Blog : http://scea5-passingpart2and3.blogspot.com/
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

I dont think you can do in this way.

Just a suggestion
you make a single query using IN clause and then you will get one resultset and process your data in code.
HashMap/Treemap will help you to implement this logic

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Sharma Ashutosh
Bartender

Joined: Apr 06, 2001
Posts: 346
Thanks Shailesh-but IN expects all the values to be resolved at compile time-i just cant iterate thru this array and then use IN.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

However I dont recommend to use Statement over PreparedStatement but for this case you can use Statement to avoid multiple hit to server.

I will look for, that how it can be done with preparedstatement.

Shailesh
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30957
    
158

Monu,
You can still use a prepared statement:
select fields from table where id in (?, ?, ?, ?)

If you search this forum for batching, I made some posts on preparing a few different size in clauses. This gives a performance benefit as you reuse the same set of preparaed statements each time.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
 
subject: Is it possible to use batch for SQL select statment