How to get the row count from ResultSet in JDBC
Whenever we execute SQL statements using the executeQuery() method, it returns a ResultSet object which holds the tabular data returned by the SELECT queries(in general). The ResultSet object contains a cursor/pointer which points to the current row. Initially this cursor is positioned before first row (default position).
The ResultSet interface provides various methods to find, the no.of columns, name of the column, type of the column etc.. but, it does not provides any method to find the number of rows in a table directly.
Using count(*) function in the SELECT query you can get the number of rows in a table as −
select count(*) from Table_Name;
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 finds the number of rows in the above table and displays the value.
Example
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 count(*) from MyPlayers"); rs.next(); //Moving the cursor to the last row System.out.println("Table contains "+rs.getInt("count(*)")+" rows"); >> Output
Connection established. Table contains 8 rows
Getting the number of rows using methods
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.
Example
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"); >> Output
Connection established. Table contains 8 rows
How to count rows – count (*) and Java
The SQL Count() function returns the number of rows in a table. Using this you can get the number of rows in a table.
select count(*) from TABLE_NAME;
Let us create a table with name cricketers_data in MySQL database using CREATE statement as shown below −
CREATE TABLE cricketers_data( First_Name VARCHAR(255), Last_Name VARCHAR(255), Date_Of_Birth date, Place_Of_Birth VARCHAR(255), Country VARCHAR(255), );
Now, we will insert 5 records in cricketers_data table using INSERT statements −
insert into cricketers_data values('Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India'); insert into cricketers_data values('Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica'); insert into cricketers_data values('Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka'); insert into cricketers_data values('Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India'); insert into cricketers_data values('Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India'); Following JDBC program establishes connection with MySQL and displays the number of rows in the table named cricketers_data.
Example
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Count_Example < 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 the Statement object Statement stmt = con.createStatement(); //Query to get the number of rows in a table String query = "select count(*) from Cricketers_Data"; //Executing the query ResultSet rs = stmt.executeQuery(query); //Retrieving the result rs.next(); int count = rs.getInt(1); System.out.println("Number of records in the cricketers_data table: "+count); >> Output
Connection established. Number of records in the cricketers_data table: 5
Как получить количество строк в таблице в базе данных MySQL?
Как считать строки на Java? Функция SQL Count возвращает количество строк в таблице. Используя это, вы можете получить количество строк в таблице. Count select из TABLE NAME. Давайте создадим таблицу с именами игроков в крикет в базе данных MySQL с использованием оператора CREATE.
Функция SQL Count() возвращает количество строк в таблице.
select count(*) from TABLE_NAME;
Давайте создадим таблицу с именем cricketers_data в базе данных MySQL, используя инструкцию CREATE, как показано ниже –
CREATE TABLE cricketers_data( First_Name VARCHAR(255), Last_Name VARCHAR(255), Date_Of_Birth date, Place_Of_Birth VARCHAR(255), Country VARCHAR(255), );
Теперь мы вставим 5 записей в таблицу cricketers_data, используя операторы INSERT –
insert into cricketers_data values('Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India'); insert into cricketers_data values('Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica'); insert into cricketers_data values('Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka'); insert into cricketers_data values('Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India'); insert into cricketers_data values('Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India'); Далее программа JDBC устанавливает соединение с MySQL и отображает количество строк в таблице с именем cricketers_data.
Пример
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Count_Example < 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 the Statement object Statement stmt = con.createStatement(); //Query to get the number of rows in a table String query = "select count(*) from Cricketers_Data"; //Executing the query ResultSet rs = stmt.executeQuery(query); //Retrieving the result rs.next(); int count = rs.getInt(1); System.out.println("Number of records in the cricketers_data table: "+count); >> Вывод
Connection established.
Number of records in the cricketers_data table: 5
Средняя оценка 5 / 5. Количество голосов: 1
Спасибо, помогите другим — напишите комментарий, добавьте информации к статье.
Видим, что вы не нашли ответ на свой вопрос.
Напишите комментарий, что можно добавить к статье, какой информации не хватает.
java mysql count number of rows
You are returning value from variable count which is of type int therefore the return type of the method should be int as well.
You should also make sure there is a return statement in every execution path through your code including the exception handler in the catch blocks (or you will get a «missing return statement» error message). However, it is best to avoid catch statements which catch all exceptions (like yours). Also, ignoring (i.e. not handling) exceptions in the catch block often leads to hard to diagnose problems and is a bad practice.
There are also other problems with the code: with the exception of count none of your variables have been declared.
Note that you may use the following SQL statement to obtain the number of rows directly:
select count(*) from testdb.emg This avoids sending all of the data from table testdb.emg to your application and is much faster for big tables.
Solution 3
How to get count(*) mysql data table in java. TRY IT:
public int getRowNumber() < int numberRow = 0; Connection mysqlConn = DriverManager.getConnection(HOST, USER_ID, PASSWORD); try< mysqlConn.getConnection(); String query = "select count(*) from dataTable"; PreparedStatement st = mysqlConn.preparedStatement(query); ResultSet rs = st.executeQuery(); while(rs.next())< numberRow = rs.getInt("count(*)"); >>catch (Exception ex) < System.out.println(ex.getMessage()); >return numberRow; >