Multiple use of ResultSet in JDBC

Charith Kulawardana

Hi experts,

I'm using Java 1.7

I'm using  following code in my project

Connection pgConn = DBConnection.getPGSQLConnection();

statement = pgConn.createStatement();

resultSet = statement.executeQuery(selectQuery);  

doc = xmlCnv.converToXML(resultSet);

What I need to do is ...

resultSet = statement.executeQuery(selectQuery);  

while(resultSet .next()){

count += 1;

}

if(count>0 ){

doc = xmlCnv.converToXML(resultSet);

}

But in here after the resultSet iterate through the while loop, it becomes null.

What I want to do is ' doc = xmlCnv.converToXML(resultSet);' part execute , if there's a records in the resultSet.

Pls guide me how to overcome this issue

Thanks

Charith

Cvele_new_account

Question has nothing to do with ADF.

Anyway:

change SELECT query in following way:

<span class="pln">SELECT bla, bla, bla, </span>

<span class="pln">       <strong>count</strong></span><strong><span class="pun">(*)</span><span class="pln"> </span><span class="kwd">over</span><span class="pln"> </span><span class="pun">(</span><span class="kwd">partition</span><span class="pln"> </span><span class="kwd">by</span><span class="pln"> </span><span class="lit">1</span><span class="pun">)</span></strong><span class="pln"><strong> total_rows</strong></span>

Cvele_new_account

OR execute another one, in order to get rownum:

SELECT COUNT(*)

FROM (original_select_statement)

Timo Hahn

Instead of iterating over the result set ask the results if it has records

resultSet = statement.executeQuery(selectQuery);  

if(resultSet .hasNext()){
doc = xmlCnv.converToXML(resultSet);
}

Timo

Charith Kulawardana

Timo,

ResultSet does not has a hasNext() function.

Thanks

Charith Kulawardana

Cvele,

No, I want to get the count without iterating the result set

Thanks

Timo Hahn

You can execute the select count(*) from (your_query) first to find yout how many results the query will return first.

Or you execute the query like you did, but do a rs.first() to find out if hte result set has any row. Depending on this you pass the rowset to your method.

Timo