JDBC – RowSet


JDBC – RowSet


”;


Previous

Next

RowSet is an interface, which is like ResultSet, which resides in javax.sql.rowset under java.sql. It inherits from ResultSet, and has more capabilities than a ResultSet. RowSets can be scrollable and updatable. Some DBMS do not support scrollability and updatability of ResultSets. In that case, RowSets have to be used. Also, RowSets have Java Beans functionality. They have properties. Properties can be set or retrieved using setter and getter methods. RowSet uses event model, where events are propagated to when certain events occur.

The events are of following three types:

  • Update, insert, or deletion of a row

  • Cursor movement

  • Change in RowSet contents

Types of RowSet Interfaces

There are 5 major types sub-interfaces of RowSet interface.

  • JdbcRowSet

  • CachedRowSet

  • WebRowSet

  • JoinRowSet

  • FilteredRowSet

Example: Performing Basic CRUD operations using RowSet

In this example, we”ve three static strings containing a dababase connection url, username, password. Now using RowSetProvider.newFactory().createJdbcRowSet() method, we”ve prepared a JdbcRowSet object as rowSet. In rowSet, we have set url, username, password and a SQL command to get details of employees from employees table. Using rowSet.execute(), the query is fired and records are stored in the rowSet instance.

Using rowSet.next() method, we”re checking if more records are present then using getInt() and getString() methods, we”re getting the required values and printing them in a while loop to print all the employees.

Using rowSet.absolute(3) method, we”ve moved to third record, and using rowSet.updateInt(), age is marked to be updated as 30. Next using rowSet.updateRow(), the complete record is updated in database. Now using rowSet.last() method, we moved to last record and using rowSet.deleteRow() method, the record is deleted. Now to verify the changes, we”ve set command to select details of employees and execute() it. Finally we”ve printed all the records of the employees again.

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


import javax.sql.rowset.*;
import java.sql.*;

// This class demonstrates use of basic functionality of rowsets
public class RowSetExample {

   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "root";
   static final String PASS = "guest123";

   public static void main(String args[])throws SQLException {
      JdbcRowSet rowSet = RowSetProvider.newFactory().createJdbcRowSet();  
      rowSet.setUrl(DB_URL);  
      rowSet.setUsername(USER);  
      rowSet.setPassword(PASS);  
      rowSet.setCommand("select id, age, first, last from employees");  
      rowSet.execute();  

      while (rowSet.next()) {  
         System.out.print("Id: " + rowSet.getInt(“id”));
         System.out.print(" Age: " + rowSet.getInt("age"));
         System.out.print(" First: " + rowSet.getString("first"));
         System.out.println(" Last: " + rowSet.getString("last"));
      }  

      System.out.println("---------------------------------------");
      // Position the cursor to the 3rd row
      rowSet.absolute(3);
      rowSet.updateInt("age",20 );
      rowSet.updateRow();

      //position the cursor to the last row
      rowSet.last();
      rowSet.deleteRow();

      // After updating the 3rd row and deleting the last row, doing a select to view updated records.
      rowSet.setCommand("select id, age, first, last from employees");  
      rowSet.execute();

      while (rowSet.next()) {  
         System.out.print("Id: " + rowSet.getInt(“id”));
         System.out.print(" Age: " + rowSet.getInt("age"));
         System.out.print(" First: " + rowSet.getString("first"));
         System.out.println(" Last: " + rowSet.getString("last"));
      }  
   }
}

Output

Now let us compile the above example as follows −


C:>javac RowSetExample.java
C:>

When you run RowSetExample, it produces the following result −


C:>java RowSetExample
Id: 1 Age: 18 First: Zara Last: Ali
Id: 2 Age: 25 First: Mahnaz Last: Fatma
Id: 3 Age: 30 First: Zaid Last: Khan
Id: 4 Age: 28 First: Sumit Last: Mittal
Id: 7 Age: 20 First: Rita Last: Tez
Id: 8 Age: 20 First: Sita Last: Singh
Id: 21 Age: 35 First: Jeevan Last: Rao
Id: 22 Age: 40 First: Aditya Last: Chaube
Id: 25 Age: 35 First: Jeevan Last: Rao
Id: 26 Age: 35 First: Aditya Last: Chaube
Id: 34 Age: 45 First: Ahmed Last: Ali
Id: 35 Age: 50 First: Raksha Last: Agarwal
Id: 36 Age: 50 First: Sankalp Last: Hawladar
Id: 37 Age: 50 First: Anand Last: Roy
---------------------------------------
Id: 1 Age: 18 First: Zara Last: Ali
Id: 2 Age: 25 First: Mahnaz Last: Fatma
Id: 3 Age: 20 First: Zaid Last: Khan
Id: 4 Age: 28 First: Sumit Last: Mittal
Id: 7 Age: 20 First: Rita Last: Tez
Id: 8 Age: 20 First: Sita Last: Singh
Id: 21 Age: 35 First: Jeevan Last: Rao
Id: 22 Age: 40 First: Aditya Last: Chaube
Id: 25 Age: 35 First: Jeevan Last: Rao
Id: 26 Age: 35 First: Aditya Last: Chaube
Id: 34 Age: 45 First: Ahmed Last: Ali
Id: 35 Age: 50 First: Raksha Last: Agarwal
Id: 36 Age: 50 First: Sankalp Last: Hawladar

C:>

Event Handling in RowSet Operations

There is an interface RowSetListener which needs to be added to JdbcRowSet via addRowSetListener. There are three methods in RowSetListener that needs to be implemented.


public void cursorMoved(RowSetEvent event)

This method is trigerred on RowSet cursor movement.


public void rowChanged(RowSetEvent event)

This is trigerred when a row in a RowSet has changed.


public void rowSetChanged(RowSetEvent event);

This is trigerred when the entire contents of RowSet changed.

Example: Handling of events in RowSet Operations with Listeners

In this example, we”ve three static strings containing a dababase connection url, username, password. Now using RowSetProvider.newFactory().createJdbcRowSet() method, we”ve prepared a JdbcRowSet object as rowSet. In rowSet, we have set url, username, password and a SQL command to get details of employees from employees table. Using rowSet.execute(), the query is fired and records are stored in the rowSet instance.

We”ve defined a RowSetListener as CustomListener which listens to various RowSetEvent. Using rowSet.addRowSetListener(), this listener is registered with the rowSet.

Using rowSet.next() method, we”re checking if more records are present then using getInt() and getString() methods, we”re getting the required values and printing them in a while loop to print all the employees.

Using rowSet.absolute(3) method, we”ve moved to third record, and using rowSet.updateInt(), age is marked to be updated as 30. Next using rowSet.updateRow(), the complete record is updated in database. Now when program runs, CustomListener object listens to various events and prints the result accordingly on the console.

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


import javax.sql.rowset.*;
import javax.sql.*;
import java.sql.*;

// This class demonsrates use of RowSetEvent and event-handling in RowSet
public class RowSetExample {

   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "root";
   static final String PASS = "guest123";

   public static void main(String args[]) throws SQLException {
      JdbcRowSet rowSet = RowSetProvider.newFactory().createJdbcRowSet();  
      rowSet.setUrl(DB_URL);  
      rowSet.setUsername(USER);  
      rowSet.setPassword(PASS);
      rowSet.setCommand("select id, age, first, last from employees");  
      rowSet.execute();
      rowSet.addRowSetListener(new CustomListener());  

      while (rowSet.next()) {  
         // Generating cursor Moved event        
         System.out.print("Id: " + rowSet.getInt(1));
         System.out.print(" Age: " + rowSet.getInt("age"));
         System.out.print(" First: " + rowSet.getString("first"));       
      } 
      System.out.println(" Last: " + rowSet.getString("last"));
      System.out.println("-------------------------------------------------");
      rowSet.absolute(3);
      rowSet.updateInt("age",30 );
      //Generating row changed event
      rowSet.updateRow();
   }  
}  

class CustomListener implements RowSetListener {  
   public void cursorMoved(RowSetEvent evt) {  
      System.out.println("Cursor Moved...");  
   }  
   public void rowChanged(RowSetEvent evt) {  
      System.out.println("Row Changed...");  
   }  
   public void rowSetChanged(RowSetEvent evt){
      System.out.println("RowSet changed..");
   }
}

Output

Now let us compile the above example as follows −


C:>javac RowSetExample.java
C:>

When you run RowSetExample, it produces the following result −


Cursor Moved...
Id: 1 Age: 18 First: Zara Last: Ali
Cursor Moved...
Id: 2 Age: 25 First: Mahnaz Last: Fatma
Cursor Moved...
Id: 3 Age: 20 First: Zaid Last: Khan
Cursor Moved...
Id: 4 Age: 28 First: Sumit Last: Mittal
Cursor Moved...
Id: 7 Age: 20 First: Rita Last: Tez
Cursor Moved...
Id: 8 Age: 20 First: Sita Last: Singh
Cursor Moved...
Id: 21 Age: 35 First: Jeevan Last: Rao
Cursor Moved...
Id: 22 Age: 40 First: Aditya Last: Chaube
Cursor Moved...
Id: 25 Age: 35 First: Jeevan Last: Rao
Cursor Moved...
Id: 26 Age: 35 First: Aditya Last: Chaube
Cursor Moved...
Id: 34 Age: 45 First: Ahmed Last: Ali
Cursor Moved...
Id: 35 Age: 50 First: Raksha Last: Agarwal
Cursor Moved...
Id: 36 Age: 50 First: Sankalp Last: Hawladar
Cursor Moved...
-------------------------------------------------
Cursor Moved...
Row Changed...

CachedRowSet

A CachedRowSet holds data in rows which is cached in memory. The example below shows how setPageSize on CachedRowSet is used. Page size is the size of displayed rows per page, for example, on a GUI.

CachedRowSet is a disconnected rowset. It means it gets a connection to a database while its updating or retrieving from database. In other times, the database connection is lost.

Example: Using CacheRowSet Operation

In this example, using RowSetProvider.newFactory().createCachedRowSet() method, we”ve prepared a CachedRowSet object as rowSet. In rowSet, we have set url, username, password and a SQL command to get details of employees from employees table. Using setPageSize(), we”ve set the page size for the CachedRowSet object rowSet as 2 rocords. Using rowSet.execute(), the query is fired and records are stored in the rowSet instance.

Using rowSet.nextPage() method, we”re checking if more records are present in next page and printing the page number. Now using rowSet.next(), we”re checking available record in the page and using getInt() and getString() methods, we”re getting the required values and printing them in a while loop to print all the employees pagewise.

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


import java.sql.Date;
import java.sql.DriverManager;
import java.sql.Time;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;

// This class demonstrates use of CachedRowSet
public class RowSetExample {
   public static void main(String args[]) throws Exception {
      RowSetFactory factory = RowSetProvider.newFactory();
      CachedRowSet rowSet = factory.createCachedRowSet();
      //Setting the URL
      String mysqlUrl = "jdbc:mysql://localhost/TUTORIALSPOINT";
      rowSet.setUrl(mysqlUrl);
      //Setting the user name
      rowSet.setUsername("guest");
      //Setting the password
      rowSet.setPassword("guest123");
      //Setting the query/command
      rowSet.setCommand("select * from employees");
      rowSet.setPageSize(2);
      rowSet.execute();   
      System.out.println("Contents of the row set");
      int i = 1;
      while(rowSet.nextPage()) {
         System.out.println(" Page No: " + i);
         System.out.println("-------------------------------------");
         while(rowSet.next()){
            System.out.print("ID : "+rowSet.getInt("id")+", ");
            System.out.print("Age : "+rowSet.getInt("age")+", ");
            System.out.print("First : "+rowSet.getString("first")+", ");
            System.out.println("Last : "+rowSet.getString("last")+", ");
         }
         System.out.println("------------------------------------- End of page");
         i++;
      }
   }
}

Output

Now let us compile the above example as follows −


C:>javac RowSetExample.java
C:>

When you run RowSetExample, it produces the following result −


Contents of the row set
 Page No: 1
-------------------------------------
ID : 3, Age : 20, First : Zaid, Last : Khan, 
ID : 4, Age : 28, First : Sumit, Last : Mittal, 
------------------------------------- End of page
 Page No: 2
-------------------------------------
ID : 7, Age : 20, First : Rita, Last : Tez, 
ID : 8, Age : 20, First : Sita, Last : Singh, 
------------------------------------- End of page
 Page No: 3
-------------------------------------
ID : 21, Age : 35, First : Jeevan, Last : Rao, 
ID : 22, Age : 40, First : Aditya, Last : Chaube, 
------------------------------------- End of page
 Page No: 4
-------------------------------------
ID : 25, Age : 35, First : Jeevan, Last : Rao, 
ID : 26, Age : 35, First : Aditya, Last : Chaube, 
------------------------------------- End of page
 Page No: 5
-------------------------------------
ID : 34, Age : 45, First : Ahmed, Last : Ali, 
ID : 35, Age : 50, First : Raksha, Last : Agarwal, 
------------------------------------- End of page



Print Page




Previous



Next


Advertisements

”;

Leave a Reply

Your email address will not be published. Required fields are marked *