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.
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
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
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.
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.