Forum Stats

  • 3,770,702 Users
  • 2,253,155 Discussions
  • 7,875,552 Comments

Discussions

Multiple use of ResultSet in JDBC

Charith Kulawardana
Charith Kulawardana Member Posts: 69
edited Jul 31, 2015 11:08AM in JDeveloper and ADF

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

Tagged:

Answers

  • Cvele_new_account
    Cvele_new_account Member Posts: 4,810 Bronze Crown
    edited Jul 30, 2015 11:09AM

    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
    Cvele_new_account Member Posts: 4,810 Bronze Crown
    edited Jul 30, 2015 11:15AM

    OR execute another one, in order to get rownum:

    SELECT COUNT(*)

    FROM (original_select_statement)

  • Timo Hahn
    Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 37,675 Red Diamond
    edited Jul 30, 2015 12:40PM

    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
    Charith Kulawardana Member Posts: 69
    edited Jul 31, 2015 10:44AM

    Timo,

    ResultSet does not has a hasNext() function.

    Thanks

  • Charith Kulawardana
    Charith Kulawardana Member Posts: 69
    edited Jul 31, 2015 10:46AM

    Cvele,

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

    Thanks

  • Timo Hahn
    Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 37,675 Red Diamond
    edited Jul 31, 2015 11:08AM

    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

This discussion has been closed.