File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Can we execute two sql statements with one Connection object? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Can we execute two sql statements with one Connection object?" Watch "Can we execute two sql statements with one Connection object?" New topic
Author

Can we execute two sql statements with one Connection object?

Mallika Kumar
Ranch Hand

Joined: Feb 15, 2001
Posts: 61
Hi,
I have a query application, where users enter query parameters and view results in a multi-page format.I'm trying to reduce the number of database trips made during displaying of results. The way my application currently works is that initially, a query gets a count(*) of rows that matched what the users are trying to look for. If count(*) is greater than one, then I execute another query which then gets the data from the database. I want to know if there is a way where I can execute both these queries using one connection object, ie, having one database hit.
Any response will be appreciated.
Thanks.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

It depends on your JDBC driver. If you are using the jdbc dbc driver provided by sun then check out http://java.sun.com/products/jdbc/faq.html#21 . If you are using Oracle, then you can have as many as you like. Somewhere along the line, if you use too many Statements per connection, Oracle will deal with it "under the covers" by creating a new connection(can't find where I read it). For any other, you would have to check your driver documentation for details.
Jamie
Shiv Sidhaarth
Ranch Hand

Joined: Aug 06, 2001
Posts: 119
Hi,
If the database is Oracle, you can write Procedures/Functions which contain PL/Sql code in it. In that code you can have any number of queris as well as the programming logic in it. So, database hit will be once.
Sankar
Bhupinder Dhillon
Ranch Hand

Joined: Oct 12, 2000
Posts: 124
Or you could just send the last query and create a scrollable result set. You can call the ResultSet.last() method and then ResultSet.row() to get the number of row and hence the total count of your result set.
kshitij raval
Greenhorn

Joined: Oct 20, 2001
Posts: 15
But then just make sure to close each Statement after use. Otherwise, I have encountered this exception:
Too many cursors open
and then no further data will reached my JSP page. After closing each Statement after use things were fine.
 
Consider Paul's rocket mass heater.
 
subject: Can we execute two sql statements with one Connection object?
 
Similar Threads
Values not retrieved from the DB
JDBC Connections Freezing with Multiple Users Accessing DB
Passing Database query results through a method to a JTable
Web application goes extremely slow with more than 5 users logged in
Paging-Query based