Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Java Java-get-example Java get number of rows in resultset

Related Tutorials


 
 

Share on Google+Share on Google+

Java get number of rows in resultset

Advertisement
In this section, you will learn how to retrieve the number of rows from the database table.

Java get number of rows in resultset

     

In this section, you will learn how to retrieve the number of rows from the database table. As you know that the data is stored in the database table in the form of row and column. Therefore, to access the database, you need to create a database table.

Table structure of 'student'

create table student (
   id int not null auto_increment,
   Name  varchar(20),
   Score varchar(20),
   primary key(id)  );

Table is:

Now, in order to retrieve the number of rows from the database table, you need to create a connection between the database and the java class file. 

Class.forName(driver)- This will loads the driver. The driver is: com.mysql.jdbc.Driver.

getConnection(url, username, password)- This method create a connection by taking parameters of string type  url, username and password to connect to the database. Here the url is 'jdbc:mysql://localhost:3306/chart', username is 'root' and password is 'root'. In the url, the chart is the name of the database.

createStatement()- This method is used for sending sql statements to the specified database.

executeQuery()- This method executes the query.

By using rs.next(), you can extract the data from the resultset from the top and rs.last() will move you to the end of the resultset. The method  rs.getRow() get the row number of the last row and also shows you the number of rows in the table.

Here is the code of GetNumberOfRows.java

import java.sql.*;

public class GetNumberOfRows {
public static Connection getConnection() throws Exception {
    String driver = "com.mysql.jdbc.Driver";
    String url = "jdbc:mysql://localhost:3306/chart";
    String username = "root";
    String password = "root";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
    public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      String query = "select id from student";
      stmt = conn.createStatement();

      rs = stmt.executeQuery(query);
      while (rs.next()) {
        String id = rs.getString(1);
       }
      rs.last();
      int rowCount = rs.getRow();
      System.out.println("Number of Rows=" + rowCount);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        rs.close();
        stmt.close();
        conn.close();
      } catch (SQLException e) {}
     }
  }
}

Output will be displayed as:

Download Source Code

Advertisement

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: November 1, 2008

Related Tutorials

Discuss: Java get number of rows in resultset  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:2
Some Guy
June 18, 2013
not really...

This is not very general and it depends on your id column being perfect. If you were to delete row 3 in the table you suggest the program would still return 4. Thus it does not return the number of rows, by the Id to the last given row.
Jeff
August 14, 2013
More Efficient Result Set Row Count

A way I usually use to get the number of rows in a result is to jump to the last row in the result set and get its index. This just requires a scroll insensitive result set. I am not sure of the complexity of ResultSet.last() over a loop but i am confident its quicker. ie. // Creating prepared statement prepared = conn.prepareStatement("Some SQL Statement WHERE thing =?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); prepared.setInt(1, uniqueId); // Executing Query ResultSet result = prepared.executeQuery(); // Getting row count result.last(); int rowCount = result.getRow(); retult.beforeFirst();
DMCA.com