Working with JdbcRowSet Object

The JdbcRowSet class provides a basic implementation of the javax.sql.RowSet interface, which turns a ResultSet object into a JavaBean and abstracts the details of working with ResultSet objects. You can use this object to simplify connection steps or to provide scrollable and updateable cursors for drivers that do not support these features. However, JdbcRowSet objects can only operate in connected mode, thus requiring the presence of a JDBC driver.

Developing with RowSet objects requires a different approach than developing with the standard JDBC components. Actually, you may find it easier to develop with RowSet objects.

Setting RowSet Connection Property :
Unlike a ResultSet, a RowSet automatically connects to the data source when it needs to retrieve or update data. You do not need to explicitly call a connection method, as the object handles this task for you. The RowSet object’s ability to connect automatically is one of its benefits.

Following snippet illustrates how to configure the connection properties:

//Create JdbcRowSetObject
JdbcRowSet jrs = new JdbcRowSet();
//Load driver and set connection parameters
Class.forName("oracle.jdbc.driver.OracleDriver");
jrs.setUrl("jdbc:oracle:thin:@localhost:1521:khan_db");
jrs.setUsername("scott");
jrs.setPassword("tiger");

Executing SQL commands using JdbcRowSet:
You can execute both DDL and DML SQL statements using a RowSet object. However, because the RowSet object acts like a JavaBean, it need to be execute the statements differently from the way you execute them in standard JDBC programming.

One difference is that you do not need to instantiate Statement, PreparedStatement, or CallableStatement objects to execute SQL statements. The RowSet object manages the details of submitting the SQL command and handling the results. It determines whether you are submitting a parameterized query or calling a stored procedure, and acts accordingly.

//Instantiate a JdbcRowSet object
JdbcRowSet jrs = new JdbcRowSet();
//Load driver and set connection parameters
Class.forName("oracle.jdbc.driver.OracleDriver");
jrs.setUrl("jdbc:oracle:thin:@localhost:1521:khan_db");
jrs.setUsername("scott");
jrs.setPassword("tiger");
//Set and execute the command
String sql = "SELECT SSN, Name, Salary, Hiredate FROM Employees";
jrs.setCommand(sql);
jrs.execute();

Fetching data from a RowSet:
Once you have populated a rowset, you need to extract the data from the rowset before it. To do so you rely on an inherited RowSet.getXXX() methods; where the XXX refers to the Java data type of the variable into which you want to place the value.

Following is the code.

import java.sql.SQLException;
import oracle.jdbc.rowset.OracleJDBCRowSet;

public class JdbcRowSetDemo {
	public static void main(String[] args){
	try {

	//Instantiate a OracleJDBCRowSet object
	// OracleJDBCRowSet is a Oracle implementation
		OracleJDBCRowSet jrs = new OracleJDBCRowSet();
		//Load driver and set connection parameters
		Class.forName("oracle.jdbc.driver.OracleDriver");
		jrs.setUrl("jdbc:oracle:thin:@localhost:1521:khan_db");
		jrs.setUsername("scott");
		jrs.setPassword("tiger");
		//Set and execute the command
		String sql;
		sql = "SELECT SSN, Name, Salary, Hiredate FROM Employees";
		jrs.setCommand(sql);
		jrs.execute();
		//Display values
		while(jrs.next()){
			System.out.print("SSN: " + jrs.getInt("ssn"));
			System.out.print(", Name: " + jrs.getString("name"));
			System.out.print(", Salary: $" + jrs.getDouble("salary"));
			System.out.print(", HireDate: " + jrs.getDate("hiredate"));
			System.out.println();
		}
		//Close the resource
		jrs.close();
		}catch (SQLException se){
			se.printStackTrace();
		}catch (Exception ex) {
			ex.printStackTrace();
		}
	//Say goodbye
	System.out.println("Goodbye!");
	}//end main
}// end JdbcRowSetDemo