How to Use Different Row Methods to Get Number of Rows in Table using JDBC?

The ability to interact with databases is an important requirement for many software programs in this data-driven world. Java is a versatile and robust programming language that offers Java Database Connectivity (JDBC), an effective mechanism that enables smooth interaction with different database systems.

JDBC is an API that offers a standardised interface for Java applications to communicate with databases. We can perform operations like querying, inserting, updating, and deleting data using it as a bridge between the Java application and the database.

This article helps us to delve into the world of Java's JDBC API, enabling us to take use of its features to perform efficient database operations.

Important Key Components of JDBC are :

• JDBC Driver: A JDBC driver is in the role of connecting to the database and converting JDBC requests into commands that are particular to the database. Make sure you select the correct driver for your database system because different databases require different types of drivers.

• JDBC API: The JDBC API is made up of classes and interfaces that specify the methods and capabilities used to conduct database operations. It comprises classes for connecting to databases, running SQL queries, obtaining and altering data, and managing transactions.

• JDBC URL: The JDBC URL, an unique string identifier, comprises critical information for establishing a database connection. This information contains things like the database type, host, port, database name, and login credentials.

Inorder to get the number of rows in table , first let’s create a table in MYSQL Database or else you can use any database that you are comfortable with.

Let’s Create a Table : -

Open Mysql database and now we will create a new Database and use it.

create database Test;
use Test;

Now let’s create an Employee Table with emp_id , emp_name , emo_email.

Query

create table Employee(emp_id int primary key,emp_name varchar(20), emp_email varchar(20));
desc Employee;

Output

Insert some records into the Employee Table:-

insert into Employee values(1,"Hari","hari123@gmail.com");
insert into Employee values(2,"Syam","syam98@gmail.com");
insert into Employee values(3,"Revanth","Revanth53@gmail.com");

Let’s check whether the data has been inserted into table or not :-

Query

select *from Employee;

Output

Now we are ready with the data. Let's create a Java application and try to connect to this database with the help of Eclipse IDE. After creating the java application we need to use JDBC Driver in order to get the number of rows from the table that we have created in Mysql this process includes mainly 4 steps:-

• Establish a Connection.

• Creation of Statement.

• Execute the sql Statement.

• Close the Connection.

Before establishing a Connection to the database we need to load the JDBC driver or just add the mysql dependency from the maven repository that is required to establish a connection, the driver class name will vary depending upon the database we use.

Class.forName("com.mysql.jdbc.Driver");

or

Dependency

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>8.0.27</version>
</dependency>

Now we will establish a connection to the database using JDBC url , username and the password.

Connection con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/Test","root", "root");

Once the connection is established we can execute our sql statements using Statement or PreparedStatement classes.

Mainly we have two ways to get the number of rows from the table one most common way where majority of people use is :

select count(*) from Employee;

But it is less efficient way, we have one more way to get the number of rows is:

select count(1) from Employee;

This query counts the number of rows using the first column. Since the primary key is often the first column, as the primary key is not null and is always unique.

Example 1

In this example we are using count(1) to get the total number of records that are in the table by using JDBC.

import java.net.ConnectException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class App {

   
   public static void main(String[] args) throws Exception
   {
      String url = "jdbc:mysql://localhost:3306/Test";
      String user ="root";
      String password = "root";
      Connection myConn = null;
      ResultSet result = null;
      try {
         //We will provide the url,username and the password to establish the connection
         myConn = DriverManager.getConnection(url,user,password);
         
         //creating a statement
         Statement myStmt = myConn.createStatement();
         
         //Total Rows is nothing but Alias name
         //and we will specify the query which we need to execute inside executeQuery()
         result= myStmt.executeQuery("select count(1) as TotalRows from Employee");
         
         //result.next() will executes the query
         result.next();
         //displaying the result i.e  we will display number of rows in the table in console
         System.out.println("Employee Table contains "+ result.getInt("TotalRows") + " rows");

      }
      catch(Exception e){
         // this catch block is used to handle the exceptions
         e.printStackTrace();
      }
      
      finally {
         //close the connection
         myConn=null;
         result=null;
      }

   }
}

Output

Employee Table contains 3 rows

Example 2

We have one more way to get the number of rows using resultSet.getRow() method.

import java.net.ConnectException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class App {

   
   public static void main(String[] args) throws Exception
   {
      String url = "jdbc:mysql://localhost:3306/Test";
      String user ="root";
      String password = "root";
      Connection myConn = null;
      ResultSet result = null;
      try {
         //We will provide the url,username and the password to establish the connection
         myConn = DriverManager.getConnection(url,user,password);
         
         //creating a statement
         //TYPE_SCROLL_SENSITIVE helps to move the cursor in forward or backward direction
         //CONCUR_READ_ONLY means we cannot the update the resultSet only read is possible
         Statement myStmt = myConn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
         //Total Rows is nothing but Alias name
         //and we will specify the query which we need to execute inside executeQuery()
         result= myStmt.executeQuery("select * from Employee");
         
         //result.next() will executes the query
         result.last();
         //displaying the result i.e  we will display number of rows in the table in console
         System.out.println("Employee Table contains " +result.getRow()+ " rows");

      }
      catch(Exception e){
         // this catch block is used to handle the exceptions
         e.printStackTrace();
      }
      
      finally {
         //close the connection
         myConn=null;
         result=null;
      }

   }
}

Output

Employee Table contains 3 rows

Conclusion

In this article we looked at the approaches and implementation on how to get the total number of rows in the table with the help of JDBC and we have also seen the key components of JDBC that enable smooth interaction with different database systems.