How to get number of rows returned by Resultset while using HSQLDB?

Question

I am getting exception feature not supported while getting the total no of rows using ResultSet.last() function. I tried using the hsqldb specific connection and ResultSet class but no success.

Can some one guide any way to get the no of rows from result set except looping through all the rows.

the code snippet used for getting no of rows is given below:

rs.last();
int total = rs.getRow();
System.out.println("total no of rows in stu are "+total);
rs.beforeFirst()

Why not just do `select count(*) from your_condition` and refer to http://stackoverflow.com/questions/19469812/getting-the-count-of-results-returned-by-a-mysql-query-with-jdbc-in-the-most-per/19470146#19470146 — Prateek, Oct 23 '13 at 06:08
you will have to execute a separate query that returns only the count and use the value , else you will have to run a loop to get the count which isnt appropriat — Rat-a-tat-a-tat Ratatouille, Oct 23 '13 at 06:17
The exception thrown is : java.sql.SQLFeatureNotSupportedException: feature not supported — Learner, Oct 23 '13 at 06:37
@Jagdeep Please mention on which line this exception is coming,i think it is on rs.last() — Prateek, Oct 24 '13 at 05:25

Prateek · Accepted Answer · 2013-10-23T08:02:34.607

4

After having look at HSQLDB api ,it can be done same as for ORACLE which is shown below

String URL = "jdbc:oracle:thin:@ip:port:sid";
String USER = "test";
String PASS = "test";
String query = "Select * from mytable";
try {
    Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
    Connection con = DriverManager.getConnection(URL, USER, PASS);
    Statement stmt = con.createStatement(
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = stmt.executeQuery(query);
    if (rs.next()) {
        rs.last();
        System.out.println("total rows is : " + rs.getRow());
    } else {
        System.out.println("No Data");
    }
} catch (Exception e) {
    e.printStackTrace();
}

edited Oct 23 '13 at 08:02

answered Oct 23 '13 at 06:34

Prateek

12,014
12
60
81

4

The key in the answer is specifying ResultSet.TYPE-SCROLL_INSENSITIVE. The default does not allow use of rs.last(). – fredt Oct 23 '13 at 22:10
@fredt yes that's right.he has to mention these flags for manipulating the resultset in both direction – Prateek Oct 24 '13 at 05:21
@fredt: Thanks for your help, setting the flag helped to get the count ...:) – Learner Oct 24 '13 at 06:10

How to get number of rows returned by Resultset while using HSQLDB?

1 Answers1