Get the number of rows returned by a ResultSet in Java

Question

I used a ResultSet that returns a specific number of rows. My code looks something like this:

 ResultSet res = getData(); if(!res.next()) { System.out.println("No Data Found"); } while(res.next()) { // code to display the data in the table. } 

Is there a way to check the number of rows returned by a ResultSet ? Or do I need to write my own?

Answer 1

You can use the do ... while instead of the while , so rs.next() is called after the loop is rs.next() , for example:

 if (!rs.next()) { //if rs.next() returns false //then there are no rows. System.out.println("No records found"); } else { do { // Get data from the current row and use it } while (rs.next()); } 

Or read the lines yourself when you receive them:

 int count = 0; while (rs.next()) { ++count; // Get data from the current row and use it } if (count == 0) { System.out.println("No records found"); } 

Answer 2

First, you must create a Statement that can move the cursor on the command:

 Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 

Then retrieve the ResultSet as shown below:

 ResultSet rs = stmt.executeQuery(...); 

Move the cursor to the last line and get it:

 if (rs.last()) { int rows = rs.getRow(); // Move to beginning rs.beforeFirst(); ... } 

Then the row variable will contain the number of rows returned by sql

Answer 3

A simple getRowCount method might look like this:

 private int getRowCount(ResultSet resultSet) { if (resultSet == null) { return 0; } try { resultSet.last(); return resultSet.getRow(); } catch (SQLException exp) { exp.printStackTrace(); } finally { try { resultSet.beforeFirst(); } catch (SQLException exp) { exp.printStackTrace(); } } return 0; } 

Just in order to know that this method will require a scrollable sensitive result set, when creating a connection, you must specify the scroll option. The default is FORWARD, and using this method will throw an exception.

Answer 4

Another way to differentiate between 0 lines or multiple lines from a ResultSet:

 ResultSet res = getData(); if(!res.isBeforeFirst()){ //res.isBeforeFirst() is true if the cursor //is before the first row. If res contains //no rows, rs.isBeforeFirst() is false. System.out.println("0 rows"); } else{ while(res.next()){ // code to display the rows in the table. } } 

If you need to know the number of rows specified in a ResultSet, here is a way to get it:

 public int getRows(ResultSet res){ int totalRows = 0; try { res.last(); totalRows = res.getRow(); res.beforeFirst(); } catch(Exception ex) { return 0; } return totalRows ; } 

Answer 5

res.next() method will take a pointer to the next line. and in your code you use it twice, first for the if condition (the cursor moves to the first line), then for the condition (the cursor moves to the second line).

Therefore, when you access your results, it starts on the second line. Thus, fewer rows are shown in the results.

you can try the following:

 if(!res.next()){ System.out.println("No Data Found"); } else{ do{ //your code } while(res.next()); } 

Answer 6

You can calculate using sql and get the answer from the result set as follows:

 Statment stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet ct = stmt.executeQuery("SELECT COUNT(*) FROM [table_name]"); if(ct.next()){ td.setTotalNumRows(ct.getInt(1)); } 

Here I count everything, but you can easily change the SQL to count based on criteria.

Answer 7

  rs.last(); int rows = rs.getRow(); rs.beforeFirst(); 

Answer 8

You can load the ResultSet into a TableModel and then create a JTable that uses this TableModel and then use the table.getRowCount () method. If you intend to display the result of a query, you should still do this.

 ResultSet resultSet; resultSet = doQuery(something, somethingelse); KiransTableModel myTableModel = new KiransTableModel(resultSet); JTable table = new JTable(KiransTableModel); int rowCount; rowCount = table.getRowCount; 

Answer 9

 Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); ResultSet rs=st.executeQuery("select * from emp where deptno=31"); rs.last(); System.out.println("NoOfRows: "+rs.getRow()); 

the first line of code says that we can move anywhere in the result set (either to the first line, or to the last line or before the first line without having to move along the line, starting from the first line, which takes time). The second line of code retrieves the records matching the request, here I accept (25 entries), the third line of code moves the cursor to the last line, and the last line of code gets the number of the current line, which in my case is 25. if there are no entries, rs.last returns 0, and getrow moves the cursor to the first line, therefore, a returned negative value indicates the absence of entries in db

Answer 10

this is my decision

  ResultSet rs=Statement.executeQuery("query"); int rowCount=0; if (!rs.isBeforeFirst()) { System.out.println("No DATA" ); } else { while (rs.next()) { rowCount++; System.out.println("data1,data2,data3...etc.."); } System.out.println(rowCount); rowCount=0; rs.close(); Statement.close(); } 

Answer 11

If your query is something like this SELECT Count(*) FROM tranbook , then do it rs.next(); System.out.println(rs.getInt("Count(*)")); rs.next(); System.out.println(rs.getInt("Count(*)"));

Answer 12

You can use res.previous() as follows:

 ResulerSet res = getDate(); if(!res.next()) { System.out.println("No Data Found."); } else { res.previous(); while(res.next()) { //code to display the data in the table. } }