How to Use Different Row Methods to Get Number of Rows in a Table in JDBC?
Last Updated :
13 Jun, 2022
Java supports many databases and for each database, we need to have their respective jar files to be placed in the build path to proceed for JDBC connectivity. For different databases, different jar files are imported to make a connection given below or their built path is supposed to be added for specific databases.
- Types of Database
- SQL
- MySQL: mysql-connector-java-8.0.22
- PostgreSQL
- Oracle: ojdbc14.jar
- Microsoft SQL server
- NoSQL
- MongoDB: mongo-java-driver-3.12.7
- BigTable
- Redis
- Progress
- Cassandra
- CouchDB
- RavenDB
Illustration: SQL and Oracle databases are mostly used for illustration. Here SQL database is taken into consideration. Here Table_Name is Table name. Here it will take all columns and count the rows.
Input: Existing data in the table is shown in the below image
- SQL server used: sqljdbc4.jar
- SQL table used
CREATE TABLE `studentsdetails` (
`id` int(6) unsigned NOT NULL,
`Name` varchar(50) NOT NULL,
`caste` varchar(10) NOT NULL,
`NeetMarks` int(11) NOT NULL,
`gender` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Approaches:
A less efficient way of creating a query
select count(*) from Table_Name;
A more efficient way of creating a query
select count(1) from Table_Name;
This query will take the first column and count the rows. As mostly, the Primary key is the first column, it is ideal enough as the Primary key is always unique and not null.
Example 1 |
Example 2 |
It will give only one row as output containing a number of rows. Hence, ‘resultset’ will be kept as next itself. |
select * will bring the whole resultset and the cursor is forced to move last and finally ‘resultset.getRow()’ method will give the number of rows. |
It is more efficient |
It is comparatively less efficient |
Example 1: To get the number of rows in a table in JDBC by selecting count(1) from ‘studentsdetails’ will provide the result as 5.
Java
import java.sql.*;
public class GFG {
public static void main(String[] args)
{
Connection con = null ;
ResultSet res = null ;
try {
Class.forName( "com.mysql.cj.jdbc.Driver" );
con = DriverManager.getConnection(
"root" , "" );
try {
Statement st = con.createStatement();
res = st.executeQuery(
"SELECT COUNT(1) as NumberOfRows FROM "
+ "studentsdetails" );
res.next();
System.out.println(
"MySQL Table - studentsdetails contains "
+ res.getInt( "NumberOfRows" ) + " rows" );
}
catch (SQLException s) {
System.out.println(
"SQL statement is not executed!" );
}
}
catch (Exception e) {
e.printStackTrace();
}
finally {
res = null ;
con = null ;
}
}
}
|
Output:
Example 2: To get the number of rows in a table in JDBC
Java
import java.sql.*;
public class GFG {
public static void main(String[] args)
{
Connection con = null ;
ResultSet res = null ;
try {
Class.forName( "com.mysql.cj.jdbc.Driver" );
con = DriverManager.getConnection(
"root" , "" );
System.out.println(
"Connection is established" );
try {
Statement st = con.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
res = st.executeQuery( "SELECT * FROM "
+ "studentsdetails" );
res.last();
System.out.println(
"MySQL Table - studentsdetails contains "
+ res.getRow() + " rows" );
}
catch (SQLException s) {
System.out.println(
"SQL statement is not executed!"
+ s.getMessage());
}
}
catch (Exception e) {
e.printStackTrace();
}
finally {
res = null ;
con = null ;
}
}
}
|
Output:
Share your thoughts in the comments
Please Login to comment...