The last() method of the ResultSet interface moves the cursor to the last row of the ResultSet and, the getRow() method returns the index/position of the current row.
Therefore, to get the number of rows move the cursor to the last row using the last() method and get the position of that (last) row using the getRow() method.
Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below −
CREATE TABLE MyPlayers( ID INT, First_Name VARCHAR(255), Last_Name VARCHAR(255), Date_Of_Birth date, Place_Of_Birth VARCHAR(255), Country VARCHAR(255), PRIMARY KEY (ID) );
Now, we will insert 7 records in MyPlayers table using INSERT statements −
insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India'); insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica'); insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka'); insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India'); insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India'); insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India'); insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');
Following JDBC program establishes a connection with the database and retrieves the contents of the MyPlayers table into a ResultSet object and retrieves the number of rows in the table and displays it.
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class RS_Row_Count { public static void main(String args[]) throws Exception { //Registering the Driver DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //Getting the connection String mysqlUrl = "jdbc:mysql://localhost/mydatabase"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password"); System.out.println("Connection established......"); //Creating a Statement object Statement stmt = con.createStatement(); //Retrieving the data ResultSet rs = stmt.executeQuery("select * from MyPlayers"); //Moving the cursor to the last row rs.last(); System.out.println("Table contains "+rs.getRow()+" rows"); } }
Connection established...... Table contains 7 rows
You can get the column count in a table using the getColumnCount() method of the ResultSetMetaData interface. On invoking, this method returns an integer representing the number of columns in the table in the current ResultSet object.
//Retrieving the ResultSetMetaData object ResultSetMetaData rsmd = rs.getMetaData(); //getting the column type int column_count = rsmd.getColumnCount();
Following JDBC program establishes connection with the database, retrieves the ResultSetMetaData object of the MyPlayers table, and prints the number of columns in it.
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import java.sql.ResultSetMetaData; public class RS_ColumnCount { public static void main(String args[]) throws Exception { //Registering the Driver DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //Getting the connection String mysqlUrl = "jdbc:mysql://localhost/mydatabase"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password"); System.out.println("Connection established......"); //Creating a Statement object Statement stmt = con.createStatement(); //Retrieving the data ResultSet rs = stmt.executeQuery("select * from MyPlayers"); ResultSetMetaData rsMetaData = rs.getMetaData(); //Moving the cursor to the last row System.out.println("Table contains "+rsMetaData.getColumnCount()+" columns"); } }
Connection established...... Table contains 6 columns