Jdbc - How To Become Row Together With Column Count From Resultset Inwards Java

One of the mutual work inward JDBC is that there is no way to acquire the full release of records returned yesteryear an SQL query. When you lot execute a Statement, PreparedStatement, or CallableStatement using execute()or executeQuery() they render ResultSet together with it doesn't get got whatsoever method to render the full release of records it is holding. The solely way to discovery the full release of records is keeping the count piece you lot are iterating over ResultSet piece fetching the result. This way, you lot tin impress the full release of rows returned the SQL query but solely afterwards you lot get got processed all records together with non before, which may non live the correct way together with incur meaning performance toll if the query returns a large release of rows.

You may recollect JDBC is a rich API together with ResultSet has got thence many methods together with thence why non but a getCount() method? Well, For many databases e.g. Oracle, MySQL together with SQL Server, ResultSet is a streaming API, this agency that it does non charge (or perhaps fifty-fifty fetch) all the rows from the database server.  By iterating to the terminate of the ResultSet you lot may add together significantly to the fourth dimension taken to execute inward certainly cases.

Btw, if you lot get got to at that topographic point are a brace of ways to do it e.g. yesteryear using ResultSet.last() together with ResultSet.getRow() method, that's non the best way to do it but it industrial plant if you lot absolutely postulate it.

Though, getting the column count from a ResultSet is slow inward Java. The JDBC API provides a ResultSetMetaData class which contains methods to render the release of columns returned yesteryear a query together with concur yesteryear ResultSet. Btw, if you lot are non familiar amongst JDBC API, you lot should commencement become through Complete Java Masterclass course from Udemy, it covers JDBC equally well.

Also, at that topographic point is around other improve way though, executing a split query to acquire the count, it does hateful executing around other query together with it may non live accurate if your database is updating inward existent fourth dimension but for most purposes, it works. I'll demo that equally good inward this article.

How to acquire full release of rows from ResultSet inward Java

Here is the elementary code snippet to discovery the full release of rows from a ResultSet object inward Java. In this program, nosotros create a Scrollable ResultSet thence that nosotros tin iterate over it together with count the release of rows inward the effect set. If you lot are non familiar amongst Scrollable ResultSet, see JDBC programming guides for consummate Beginner to larn to a greater extent than most it.

import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet;  /*  * Java Program to discovery the row together with 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 telephone band = con.prepareCall(SQL,                     ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);     call.setInt(1, userId);      ResultSet effect = call.executeQuery();     boolean b = result.last();     int count = result.getRow();     result.beforeFirst();      while(result.next()){      // procedure data      }    } }

But the performance is risky, if the ResultSet is non updateable together with thence you lot volition have next mistake piece using this code:


com.microsoft.sqlserver.jdbc.SQLServerException: The requested performance is non supported on frontwards solely effect 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, Influenza A virus subtype H5N1 default ResultSet object is non updatable together with has a cursor that moves frontwards only. I recollect this agency that unless you lot execute the query amongst ResultSet.TYPE_SCROLL_INSENSITIVE rSet.beforeFirst() volition throw SQLException.

The argue it is this way is that at that topographic point is a toll amongst a scrollable cursor. According to the documentation, it may throw SQLFeatureNotSupportedException fifty-fifty if you lot create a scrollable cursor.

You likewise postulate to telephone band ResultSet#beforeFirst() to pose the cursor dorsum to earlier the commencement row earlier you lot render the ResultSet object. This way the user volition live able to utilization next() the commons way.

How to acquire full release of columns from ResultSet inward Java

Here is a Java programme acquire the full release of columns from ResultSet inward Java. There is zero special, but telephone band the getColumnCount() method of ResultSet interface together with you lot are done. This method volition render the release of columns inward the effect set.

Btw, it's a mutual JDBC best practice to acquire the information using column refer together with non column index equally it volition proceed your code prophylactic fifty-fifty if the social club of column is changed inward the SQL query. See Complete Java Masterclass larn to a greater extent than most such best practices.

 import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet;  /*  * Java Program to discovery 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 telephone band = con.prepareCall(SQL);     call.setInt(1, userId);      ResultSet effect = call.executeQuery();     int count = result.getMetaData().getColumnCount();    } }

That's all most how to acquire a full release of records together with the full release of columns from ResultSet inward Java. As I said, at that topographic point is no slow way to acquire the full release of records without iterating over ResultSet. There is likewise a toll associated amongst iterating over ResultSet, particularly if the query returns a large dataset.

Though you lot tin utilization the last() together with getRow() method to commencement movement to the final seat together with and thence render the electrical current row, btw, you lot must render dorsum to the previous seat yesteryear calling beforeFirst(), otherwise, you lot volition immature adult woman all records from ResultSet.

On reverse to this, getting the full release of count is slow from ResultSet, but utilization the ResultSetMetaData object from ResultSet together with telephone band the getColumnCount() method. It returns an integer, which is equal to a full release of columns.

Other JDBC tutorials you lot may similar to explore
Java, JSP together with JDBC programming guides for consummate BEGINNER
JSP, Servlets, together with JDBC for Beginners: Build a Database App
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 lot similar this tutorial together with thence delight percentage amongst your friends together with colleagues. If you lot get got whatsoever questions or feedback together with thence delight drib a comment. 

Share this post