Total Results for getRow Method

Question

Read the following code:

public class selectTable { public static ResultSet rSet; public static int total=0; public static ResultSet onLoad_Opetations(Connection Conn, int rownum,String sql) { int rowNum=rownum; int totalrec=0; try { Conn=ConnectionODBC.getConnection(); Statement stmt = Conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); String sqlStmt = sql; rSet = stmt.executeQuery(sqlStmt); total = rSet.getRow(); } catch(Exception e) { System.out.println(e.getMessage()); } System.out.println("Total Number of Records="+totalrec); return rSet; } } 

The following dos't code shows the actual total:

 total = rSet.getRow(); 

my jTable display 4 entries in jTable, but total = 0; when I evaluate through debug it shows:

 total=(int)0; 

not total = (int) 4 And if I use

 rSet=last(); above from the code total = rSet.getRow(); 

then the total shows the exact value = 4, but rSet returns nothing. then jTable is empty. Update me!

Answer 1

BalusC's answer is correct! but I have to mention according to the user instance variable, for example:

 rSet.last(); total = rSet.getRow(); 

and then which you are missing

 rSet.beforeFirst(); 

the remaining code will be the same, you will get the result of your desire.

Answer 2

You need to call ResultSet#beforeFirst() to return the cursor to the first line before you return the ResultSet object. Thus, the user will be able to use next() usual way.

 resultSet.last(); rows = resultSet.getRow(); resultSet.beforeFirst(); return resultSet; 

However, you have more problems with the above code. This is a database resource leak, and it is also not the right OOP approach. Find the DAO pattern. Ultimately you would like to get

 public List<Operations> list() throws SQLException { // Declare Connection, Statement, ResultSet, List<Operation>. try { // Use Connection, Statement, ResultSet. while (resultSet.next()) { // Add new Operation to list. } } finally { // Close ResultSet, Statement, Connection. } return list; } 

Thus, the caller should simply use List#size() to find out the number of entries.

Answer 3

As others answered, there is no way to get the row count without iterating to the end. You could do this, but you might not want to, pay attention to the following points:

• For many RDBMS systems, ResultSet is a streaming API, which means that it does not load (or even retrieve) all rows from the database server. See this question about SO. Iterating to the end of the ResultSet can significantly increase the time it takes to execute in certain cases.

• By default, the ResultSet object is not updated and has a cursor that moves only forward. I think this means that if you execute the query with ResultSet.TYPE_SCROLL_INSENSITIVE rSet.beforeFirst() will rSet.beforeFirst() SQLException . The reason for this is because there is value with the scrollable cursor. According to the documentation, it can throw an SQLFeatureNotSupportedException , even if you create a scrollable cursor.

• Filling and returning a List<Operations> means that you also need extra memory. For very large results, this will not work at all.

So the big question is, which RDBMS ?. In general, I would suggest not recording the number of records.

Answer 4

The getRow () method retrieves the current line number, not the number of lines. Therefore, before starting an iteration on a ResultSet , getRow() returns 0.

There is no free method to get the actual number of rows returned after executing your query: you have to iterate over it.

However, if you really need to get the total number of lines before processing them, you can:

• ResultSet.last ()
• ResultSet.getRow () to get the total number of rows
• ResultSet.beforeFirst ()
• Handle ResultSet usually

Answer 5

It would be best to use a SELECT COUNT SQL statement.

Just when you need to count the number of rows returned, run another query that returns the exact number of results for this query.

  try { Conn=ConnectionODBC.getConnection(); Statement stmt = Conn.createStatement(); String sqlStmt = sql; String sqlrow = SELECT COUNT(*) from (sql) rowquery; String total = stmt.executeQuery(sqlrow); int rowcount = total.getInt(1); } 

Answer 6

The getRow() method will always give 0 after the request:

ResultSet.getRow ()

Retrieves the line number of current .

Secondly, you totalrec , but you never assign anything to it.

Answer 7

You cannot get the number of rows returned in a ResultSet without repeating through it. And why would you return a ResultSet without repeating through it? At first, it would not make sense to execute the request.

A better solution would be to separate persistence from presentation. Create a separate data access object that handles all database queries. Let it get the values ​​that will be displayed in JTable , load them into the data structure and then return them to the user interface for display. The user interface will have all the information he needs.

Answer 8

I solved this problem. The only thing I do:

 private int num_rows; 

And then in your method using the result set, enter this code

 while (this.rs.next()) { this.num_rows++; } 

What all

Answer 9

The best way to get the number of rows from a result set is to use the function request counter to access the database, and then the rs.getInt (1) method to get the number of rows. from my code look:

  String query = "SELECT COUNT() FROM table"; ResultSet rs = new DatabaseConnection().selectData(query); rs.getInt(1); 

this will return an int value of the number of rows retrieved from the database. Here is DatabaseConnection (). SelectData () is my code to access the database. I was also stuck here, but then decided ...