Jdbc - How To Piece Of Job Out Row As Well As Column Count From Resultset Inwards Java

One of the mutual work inward JDBC is that there is no way to acquire the full let on of records returned past times an SQL query. When yous execute a Statement, PreparedStatement, or CallableStatement using execute()or executeQuery() they render ResultSet together with it doesn't stimulate got whatever method to render the full let on of records it is holding. The exclusively way to notice the full let on of records is keeping the count piece yous are iterating over ResultSet piece fetching the result. This way, yous tin impress the full let on of rows returned the SQL query but exclusively subsequently yous stimulate 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 let on of rows.

You may shout out back JDBC is a rich API together with ResultSet has got thus many methods thus 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 perchance fifty-fifty fetch) all the rows from the database server.  By iterating to the halt of the ResultSet yous may add together significantly to the fourth dimension taken to execute inward certainly cases.

Btw, if yous stimulate got to in that place are a duad of ways to do it e.g. past times using ResultSet.last() together with ResultSet.getRow() method, that's non the best way to do it but it plant if yous absolutely ask it.

Though, getting the column count from a ResultSet is tardily inward Java. The JDBC API provides a ResultSetMetaData class which contains methods to render the let on of columns returned past times a query together with concord past times ResultSet. Btw, if yous are non familiar amongst JDBC API, yous should starting fourth dimension become through Complete Java Masterclass course from Udemy, it covers JDBC equally well.

Also, in that place is about other improve way though, executing a dissever query to acquire the count, it does hateful executing about 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 demonstrate that equally good inward this article.

How to acquire full let on of rows from ResultSet inward Java

Here is the uncomplicated code snippet to notice the full let on of rows from a ResultSet object inward Java. In this program, nosotros create a Scrollable ResultSet thus that nosotros tin iterate over it together with count the let on of rows inward the lawsuit set. If yous are non familiar amongst Scrollable ResultSet, see JDBC programming guides for consummate Beginner to larn to a greater extent than near it.

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

But the functioning is risky, if the ResultSet is non updateable thus yous volition have next mistake piece using this code:


com.microsoft.sqlserver.jdbc.SQLServerException: The requested functioning is non supported on frontwards exclusively lawsuit 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 shout out back this agency that unless yous execute the query amongst ResultSet.TYPE_SCROLL_INSENSITIVE rSet.beforeFirst() volition throw SQLException.

The argue it is this way is that in that place is a toll amongst a scrollable cursor. According to the documentation, it may throw SQLFeatureNotSupportedException fifty-fifty if yous create a scrollable cursor.

You likewise ask to telephone telephone ResultSet#beforeFirst() to set the cursor dorsum to earlier the starting fourth dimension row earlier yous render the ResultSet object. This way the user volition live able to exercise next() the green way.

How to acquire full let on of columns from ResultSet inward Java

Here is a Java programme acquire the full let on of columns from ResultSet inward Java. There is aught special, but telephone telephone the getColumnCount() method of ResultSet interface together with yous are done. This method volition render the let on of columns inward the lawsuit set.

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

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

That's all near how to acquire a full let on of records together with the full let on of columns from ResultSet inward Java. As I said, in that place is no tardily way to acquire the full let on 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 yous tin exercise the last() together with getRow() method to starting fourth dimension motion to the terminal seat together with thus render the electrical flow row, btw, yous must render dorsum to the previous seat past times calling beforeFirst(), otherwise, yous volition immature adult woman all records from ResultSet.

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

Other JDBC tutorials yous 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 yous similar this tutorial thus delight portion amongst your friends together with colleagues. If yous stimulate got whatever questions or feedback thus delight drib a comment.