JDBC - Viewing a Result Set Examples

Following is the example, which makes use of few getInt and getString methods described in the Result Set chapter. This example is very similar to previous example explained in the Navigation Result Set Section.

This sample code has been written based on the environment and the database setup done in the previous chapters.

Viewing Records in a ResultSet One by One Example

In this example, we've four static strings containing a dababase connection url, username, password and a SELECT query. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've created a Statement object using connection.createStatement() method. While creating Statement Object, we've used ResultSet types as TYPE_SCROLL_INSENSITIVE and CONCUR_READ_ONLY, then using statement.executeQuery(), the SELECT Query is executed and result is stored in a resultset.

In first step, we've moved resultset cursor to the last row using ResultSet.last() method and printed the last record's details using getInt() and getString() methods. getInt() method is used to get numeric fields like id, age and getString() is used to get the varchar fields like first name, last name. In second step, we've moved cursor to the first row using ResultSet.first() method and printed the first record. As last step, we moved cursor to next record using ResultSet.next() method and printed the record.

Copy and paste the following example in ResultSetExample.java, compile and run as follows −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ResultSetExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Employees";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement(
         ResultSet.TYPE_SCROLL_INSENSITIVE,
         ResultSet.CONCUR_READ_ONLY);
         ResultSet rs = stmt.executeQuery(QUERY);
      ) {		
         // Move cursor to the last row.
         System.out.println("Moving cursor to the last...");
         rs.last();

         // Extract data from result set
         System.out.println("Displaying record...");
         //Retrieve by column name
         int id  = rs.getInt("id");
         int age = rs.getInt("age");
         String first = rs.getString("first");
         String last = rs.getString("last");

         // Display values
         System.out.print("ID: " + id);
         System.out.print(", Age: " + age);
         System.out.print(", First: " + first);
         System.out.println(", Last: " + last);

         // Move cursor to the first row.
         System.out.println("Moving cursor to the first row...");
         rs.first();

         // Extract data from result set
         System.out.println("Displaying record...");
         // Retrieve by column name
         id  = rs.getInt("id");
         age = rs.getInt("age");
         first = rs.getString("first");
         last = rs.getString("last");

         // Display values
         System.out.print("ID: " + id);
         System.out.print(", Age: " + age);
         System.out.print(", First: " + first);
         System.out.println(", Last: " + last);
         // Move cursor to the first row.

         System.out.println("Moving cursor to the next row...");
         rs.next();

         // Extract data from result set
         System.out.println("Displaying record...");
         id  = rs.getInt("id");
         age = rs.getInt("age");
         first = rs.getString("first");
         last = rs.getString("last");

         // Display values
         System.out.print("ID: " + id);
         System.out.print(", Age: " + age);
         System.out.print(", First: " + first);
         System.out.println(", Last: " + last);		

      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Output

Now let us compile the above example as follows −

C:\>javac ResultSetExample.java
C:\>

When you run ResultSetExample, it produces the following result −

C:\>java ResultSetExample
Moving cursor to the last...
Displaying record...
ID: 103, Age: 30, First: Sumit, Last: Mittal
Moving cursor to the first row...
Displaying record...
ID: 100, Age: 18, First: Zara, Last: Ali
Moving cursor to the next row...
Displaying record...
ID: 101, Age: 25, First: Mehnaz, Last: Fatma
C:\>

Viewing Records in a ResultSet in Loop Example

In first step, we've used ResultSet.next() in a while loop to move resultset cursor to next record and to check if record exists. In while Loop, we've used getInt() and getString() where arguments are column names to get the corresponding values. Once all records are printed, we've moved resultset cursor to the first record using ResultSet.first() method. Here we've used getInt() and getString() where arguments are column indexes starting from 1 to get the corresponding values and printed the record.

Copy and paste the following example in ResultSetExample.java, compile and run as follows −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ResultSetExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT StudentID, FirstName, LastName , Dept FROM Students";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement(
         ResultSet.TYPE_SCROLL_INSENSITIVE,
         ResultSet.CONCUR_READ_ONLY);   
         ResultSet rs = stmt.executeQuery(QUERY);
      ) {		
         // Extract data from result set
         System.out.println("Displaying record by column name ...");
         //Retrieve by column name
         while(rs.next()){
            int id  = rs.getInt("StudentID");
            String first = rs.getString("FirstName");
            String last = rs.getString("LastName");
            String dept = rs.getString("Dept");

            // Display values
            System.out.print("StudentID: " + id);
            System.out.print(", First: " + first);
            System.out.print(", Last: " + last);
            System.out.println(", Dept: " + dept);
         }

         // Move cursor to the first row.
         System.out.println("Moving cursor to the first row...");
         rs.first();

         System.out.println("Displaying record by column index...");
         // Retrieve by column index
         int id  = rs.getInt(1);
         String first = rs.getString(2);
         String last = rs.getString(3);
         String dept = rs.getString(4);

         // Display values
         System.out.print("StudentID: " + id);
         System.out.print(", First: " + first);
         System.out.print(", Last: " + last);
         System.out.println(", Dept: " + dept);

      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Output

Now let us compile the above example as follows −

C:\>javac ResultSetExample.java
C:\>

When you run ResultSetExample, it produces the following result −

C:\>java ResultSetExample
Displaying record by column name ...
StudentID: 1000, First: Bonny, Last: Agarwal, Dept: Mathematics
StudentID: 1001, First: Amit, Last: Pandey, Dept: Physics
StudentID: 1002, First: Shefali, Last: Kumar, Dept: English
StudentID: 1004, First: Mohammed, Last: Ali, Dept: Mathematics
StudentID: 1005, First: Kishore, Last: Kumar, Dept: Biology
StudentID: 1006, First: Ganesh, Last: Khan, Dept: English
Moving cursor to the first row...
Displaying record by column index...
StudentID: 1000, First: Bonny, Last: Agarwal, Dept: Mathematics

C:\>

jdbc-result-sets.htm

Print Page