Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Can we execute two sql statements with one Connection object?

 
Mallika Kumar
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 124
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic