Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

ResulSet issue

 
Gorakshanath Pandey
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i have a sql query be executed up to thousand times, the only change
is the where clause value change, so each time i need to create a
statement to execute query, before create statement, do i need to close
last time opened resultset and statement ? Please advice.
Second Question is how to return a resultSet from a function ? Does the ResultSet Object gets destroyed once we exit the function or we need to
explicitly close it. The same is question for Statement Object.

temprs=SelQuery(SelStmt,con);
ResultSet dtrs;
String dtsql;
while(temprs.next())
{
String selopen="select count(*) from mast "
+" where reg_cd =" + "'" + temprs.getString("reg_cd ")+ "'"
+" and f_cmr = 'T' and closed_pms_dt between " + "'" + temprs.getDate("st_date") + "'" + " and " +"'" + temprs.getDate("ed_date") + "'";
Statement lstmt = con.createStatement();// Do i need to close this.
ResultSet ltemprs= lstmt.executeQuery(selopen); //Do i neet to close this
}

[ March 12, 2004: Message edited by: Bear Bibeault ]
 
Blake Minghelli
Ranch Hand
Posts: 331
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
do i need to close
last time opened resultset and statement
It is good form to always explicitly close you resources as soon as you are done with them.
Does the ResultSet Object gets destroyed once we exit the function or we need to
explicitly close it.
If the Statement is not closed, then the ResultSet will remain open as well. But if you close a Statement, any ResultSetS you may have created using that Statement will be automatically closed so you won't be able to use them.
Since you are executing the same query (except WHERE clause values), you should definitely consider using a PreparedStatement instead of a Statement. Most JDBC drivers support PreparedStatementS. You'll see better performance when using a PreparedStatement, especially if you are executing the same query 1000 times. PreparedStatementS also take care of that annoying quote-escaping problem.
You might want to try something like this:
 
Gorakshanath Pandey
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Blake...wonderful reply....Only one more question Is there
a way to return a ResultSet() Object from a function like

Public static void(?) Fn()
{
Statement stmt;
ResultSet rs
String Sql
...
...
rs=stmt.executeQuer(sql)

return rs; ///How to close this....
}
 
Dana Hanna
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You would have to convert the ResultSet into domain objects, be sure to close it in the calling method, or use a javax.sql.RowSet
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic