How many connections

[saikrishna cinux]

HI TO ALL,
here i want to execute 2 query's using same connection and same statement and resulset.
is there anything wrong in this issue?
should i need to do create seperate connections and stmt and result for both query's


thanx in advace
cinux
[ April 01, 2006: Message edited by: Ernest Friedman-Hill ]

[Martin Simons]

Using the same connection is of course fine, but you create the statement
with the query, and the result set is created by executing the statement.
So for the last two it cannot be the same. If you mean using the same
variables, then yeah, no problem, but the items will not be "concatenated"
or any such stuff. The variables will have one value (or set of values)
for one query, and be completely different (new) items with the second
query.

[Roger Chung-Wee]

Just remember that creating two Statements, even if using the same reference variable, means two Statement objects - both of which should be closed in order to prevent cursor leaks.

[saikrishna cinux]

yep i am using same variables for 2 different queries like ia m using rs for Resultset and con fro Connection stmt for Statement;

i am copying my program here
exactly like this

con = getConnection ();
stmt = con.createStatement ();
query = "select COUNT(*) from testuser where totalscore<="+totalUserScore;
System.out.println(query);

for(rs = stmt.executeQuery ( query ); rs.next();lessTotalScores++){
lessTotalScores=rs.getInt(1);
}

System.out.println("lessTotalScores "+lessTotalScores);

query = "SELECT COUNT(*) FROM TESTUSER WHERE TESTNAME like 'GRE' AND TESTTYPE like 'GRAND TEST'";
System.out.println(query);

rs = stmt.executeQuery ( query );

for(rs = stmt.executeQuery ( query ); rs.next();totalUsers++){

totalUsers=rs.getInt(1);

}


System.out.println("totalUsers "+totalUsers);

plz tell me ...

[Martin Simons]

before the second query string I would place a rs.close( ) and
stmt.close( ) as well as another stmt = con.createStatement( )
just to be sure. As far as reusing the variables no problem.
Don't worry about the "extra" lines of code or the extra actions
involved. An additional time needed to accomplish these are
truely nominal.

[Roger Chung-Wee]

The code looks as if it should work.

As you are creating two Resultset objects, then ensure that you close both.

for(rs = stmt.executeQuery ( query ); rs.next();lessTotalScores++){
lessTotalScores=rs.getInt(1);
}
rs.close();

System.out.println("lessTotalScores "+lessTotalScores);

query = "SELECT COUNT(*) FROM TESTUSER WHERE TESTNAME like 'GRE' AND TESTTYPE like 'GRAND TEST'";
System.out.println(query);

rs = stmt.executeQuery ( query );

for(rs = stmt.executeQuery ( query ); rs.next();totalUsers++){

totalUsers=rs.getInt(1);

}
con.close();

[Jeff Albertson]

On closing resources: it's a good idea to put close operations in a finally block to make sure they are called:

static void closeConnection(Connection con) {
   if (con != null) {
        try {
            con.close();
        } catch (SQLException e) {
            //perhaps log error
        }
   }
}
 
void f() throws SQLException {
   Connection con = getConnection();
   try {
       //use connection
   } finally {
       closeConnection(con);
   }
}

Follow the same pattern with Statement and ResultSet. Many frameworks introduce abstractions to simplify this. For example, in Spring one can write:

Collection requests = jdbc.query("select name, dob from ...",
    new RowMapper() {
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new Something(rs.getString("name"), rs.getDate("dob"));
        }
    });

[saikrishna cinux]

Originally posted by Jeff Albertson:

Follow the same pattern with Statement and ResultSet. Many frameworks introduce abstractions to simplify this. For example, in Spring one can write:

Collection requests = jdbc.query("select name, dob from ...",
    new RowMapper() {
        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new Something(rs.getString("name"), rs.getDate("dob"));
        }
    });

[/QB]

sir here i didn't get what u mean in the last lines of code..
what is this ocde for?
it's little bit complicated code i couldn't get it
please can u explain me once again clearly...
thanking u