How to obtain the number of rows and columns of the resultset

When we execute a database query and return a resultset, we need to know the size of this resultset in many cases, that is, its number of rows and number of columns. We know that the number of columns can be easily obtained through resultset. getmetadata (). getcolumncount (). However, Java APIs do not provide an interface to directly access the number of rows in resultset.

At this time, there are three solutions:

1. Use the select count statement instead, and then obtain the result directly from the resultset:

        try {
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("select count(*) as rowCount from tableName");
            resultSet.next();
            int rowCount = resultSet.getInt("rowCount");
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }

However, we do not only need to know the number of rows in the results for executing database queries, but also need to use the query results later. If this method is used, you need to execute the SELECT statement again to obtain the desired result set. In this way, an additional database query is performed, greatly reducing the execution speed.

2. traverse the resultset and use a variable to record the number of rows. The Code is as follows:

        int count = 0;
        try {
            while(resultSet.next()){
                count = count + 1;
            }
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }

The obtained Count value is the number of rows in the result set. However, this method is the same as the problem with the first method, and the result set cannot be used any more. This is because the pointer has been moved out of the result set and no longer points to any records.

3. Knowing the cause of the problem in the second method, we will know how to better solve the problem. The problem with the second method is that the pointer in the returned result set cannot be moved freely. Fortunately, Java provides us with a choice that allows us to create a result set where the pointer can be freely moved, only one thing to do is to add two parameters when creating a statement:

        try {
            //Statement statement = connection.createStatement();
            Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            ResultSet resultSet = statement.executeQuery("select * from " + tableName);
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }

In this way, the pointer can be freely moved in the result set. Then, you can use the following method to obtain the number of rows in the result set:

        int rowCount = 0;
        try {
            resultSet.last();
            rowCount = resultSet.getRow();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }

Where resultset. Last () is to move the pointer to the last record of the result set. Then, use resultset. getrow () to obtain the row number of the current pointer (starting from 1)

If you want to use the result set next, do not forget to move the pointer to the first line:

resultSet.first();

Since the result set is scrollable, you can use the absolute () method to access records of the specified row number:

Boolean java. SQL. resultset. Absolute (INT row) throws sqlexception

The row parameter can be positive or negative. For more information, see the absolute () method of resultset.

How to obtain the number of rows and columns of the resultset