Java Sql Resultset Count Example

One of the common problems in JDBC is that there is no way to get the total number of records returned by an SQL query. When you execute a Statement, PreparedStatement, or CallableStatement using execute()or executeQuery() they return ResultSet and it doesn't have any method to return the total number of records it is holding. The only way to find the total number of records is to keep the count while you are iterating over ResultSet while fetching the result. This way, you can print the total number of rows returned the SQL query but only after you have processed all records and not before, which may not be the right way and incur significant performance cost if the query returns a large number of rows.

You may think JDBC is a rich API and ResultSet has got so many methods then why not just a getCount() method? Well, For many databases like Oracle, MySQL, and SQL Server, ResultSet is a streaming API, which means that it does not load (or maybe even fetch) all the rows from the database server.  By iterating to the end of the ResultSet you may add significantly to the time taken to execute in certain cases.

Btw, if you have to there are a couple of ways to do it e.g. by using ResultSet.last() and ResultSet.getRow() method, that's not the best way to do it but it works if you absolutely need it.

Though, getting the column count from a ResultSet is easy in Java. The JDBC API provides aResultSetMetaData class which contains methods to return the number of columns returned by a query and hold by ResultSet. Btw, if you are not familiar with JDBC API, you should first go through these free Java Programming courses from Udemy, it covers JDBC as well.

Also, there is another better way though, executing a separate query to get the count, it does mean executing another query and it may not be accurate if your database is updating in real-time but for most purposes, it works. I'll show that as well in this article.

How to get the total number of rows from ResultSet in Java

Here is the simple code snippet to find the total number of rows from a ResultSet object in Java. In this program, we create a Scrollable ResultSet so that we can iterate over it and count the number of rows in the result set. If you are not familiar with Scrollable ResultSet, see these free JDBC programming courses for complete Beginner to learn more about it.

            import            java.sql.CallableStatement;            import            java.sql.Connection;            import            java.sql.ResultSet;            /*  * Java Program to find the row and column count from ResultSet  */            public            class            Hello            {            public            static            void            main(String[]            args)            throws            Exception            {            Connection            con            =            getDBConnection();            String            SQL            =            "{call Users..usp_getUserDetails(?)}";            int            userId            =            23;            CallableStatement            call            =            con.prepareCall(SQL,                          ResultSet            .TYPE_SCROLL_SENSITIVE,            ResultSet            .CONCUR_READ_ONLY);     call.setInt(1, userId);            ResultSet            result            =            call.executeQuery();            boolean            b            =            result.last();            int            count            =            result.getRow();     result.beforeFirst();            while(result.next()){            // process data            }    } }

But the operation is risky, if the ResultSet is not updateable then you will receive the following error while using this code:

com.microsoft.sqlserver.jdbc.SQLServerException: The requested operation is not supported on forward only result sets.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.throwNotScrollable(SQLServerResultSet.java:402)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.verifyResultSetIsScrollable(SQLServerResultSet.java:425)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.last(SQLServerResultSet.java:1460)
at Testing.main(Testing.java:37)

Precisely, A default ResultSet object is not updatable and has a cursor that moves forward only. I think this means that unless you execute the query with ResultSet.TYPE_SCROLL_INSENSITIVE rSet.beforeFirst() will throw SQLException.

The reason it is this way is that there is a cost with a scrollable cursor. According to the documentation, it may throw SQLFeatureNotSupportedException even if you create a scrollable cursor.

You also need to call ResultSet#beforeFirst() to put the cursor back to before the first row before you return the ResultSet object. This way the user will be able to use next() the usual way. You can also see Complete Java Masterclass learn more about such best practices.

How to get the total number of columns from ResultSet in Java

Here is a Java program that gets the total number of columns from ResultSet in Java. There is nothing special, just call the getColumnCount() method of the ResultSet interface and you are done. This method will return the number of columns in the result set.

Btw, it's a common JDBC best practice to get the data using column name and not column index as it will keep your code safe even if the order of column is changed in the SQL query.

              import            java.sql.CallableStatement;            import            java.sql.Connection;            import            java.sql.ResultSet;            /*  * Java Program to find the column count from ResultSet  */            public            class            Hello            {            public            static            void            main(String[]            args)            throws            Exception            {            Connection            con            =            getDBConnection();            String            SQL            =            "{call Users..usp_getUserDetails(?)}";            int            userId            =            23;            CallableStatement            call            =            con.prepareCall(SQL);     call.setInt(1, userId);            ResultSet            result            =            call.executeQuery();            int            count            =            result.getMetaData().getColumnCount();    } }

That's all about how to get a total number of records and the total number of columns from ResultSet in Java. As I said, there is no easy way to get the total number of records without iterating over ResultSet. There is also a cost associated with iterating over ResultSet, especially if the query returns a large dataset.

Though you can use the last() and getRow() method to first move to the last position and then return the current row, btw, you must return back to the previous position by calling beforeFirst(), otherwise, you will miss all records from ResultSet.

On contrary to this, getting the total number of counts is easy from ResultSet, just use the ResultSetMetaData object from ResultSet and call the getColumnCount() method. It returns an integer, which is equal to a total number of columns.

Other JDBC tutorials you may like to explore
How to connect Eclipse IDE to Oracle DataBase?
How to connect MySQL database from Java Program?
How to connect Microsoft SQL Server from Java Program?
How to connect Oracle Database from Java Program?
5 JDBC Tips to improve performance of Java application

Thanks for reading this article, if you like this tutorial then please share it with your friends and colleagues. If you have any questions or feedback then please drop a comment.

griffinwrowspent.blogspot.com

Source: https://www.java67.com/2018/01/jdbc-how-to-get-row-and-column-count-from-resultset-java.html

Share this post