JDBC Driver

JDBC Driver is a software component that enables java application to interact with the database.

There are 4 types of JDBC drivers:

1. JDBC-ODBC bridge driver

2. Native-API driver (partially java driver)
3. Network Protocol driver (fully java driver)
4. Thin driver (fully java driver)

1) JDBC-ODBC bridge driver

 The JDBC-ODBC bridge driver uses ODBC driver to connect to the database.
 The JDBC-ODBC bridge driver converts JDBC method calls into the ODBC function calls.
In Java 8, the JDBC-ODBC Bridge has been removed.

Oracle does not support the JDBC-ODBC Bridge from Java 8. Oracle recommends that you
use JDBC drivers provided by the vendor of your database instead of the JDBC-ODBC
Bridge.

Advantages:

o easy to use.
o can be easily connected to any database.

Disadvantages:

o Performance degraded because JDBC method call is converted into the ODBC
function calls.
o The ODBC driver needs to be installed on the client machine.

2) Native-API driver

The Native API driver uses the client-side libraries of the database. The driver converts JDBC method ca
the database API. It is not written entirely in java.
Advantage:

o performance upgraded than JDBC-ODBC bridge driver.

Disadvantage:

o The Native driver needs to be installed on the each client machine.

o The Vendor client library needs to be installed on client machine.

3) Network Protocol driver

The Network Protocol driver uses middleware (application server) that converts JDBC calls
directly or indirectly into the vendor-specific database protocol. It is fully written in java.
Advantage:

o No client side library is required because of application server that can perform many
tasks like auditing, load balancing, logging etc.

Disadvantages:

o Network support is required on client machine.

o Requires database-specific coding to be done in the middle tier.
o Maintenance of Network Protocol driver becomes costly because it requires database-
specific coding to be done in the middle tier.

4) Thin driver

The thin driver converts JDBC calls directly into the vendor-specific database protocol. That is why it is k
It is fully written in Java language.
Advantage:

o Better performance than all other drivers.

o No software is required at client side or server side.

Disadvantage:

o Drivers depend on the Database.

Java Database Connectivity with 5 Steps
1. 5 Steps to connect to the database in java
1. Register the driver class
2. Create the connection object
3. Create the Statement object
4. Execute the query
5. Close the connection object

There are 5 steps to connect any java application with the database using JDBC. These steps are as follo

o Register the Driver class

o Create connection
o Create statement
o Execute queries
o Close connection

1) Register the driver class

 The forName() method of Class class is used to register the driver class. This method is used to dynam
class.

Syntax of forName() method

1. public static void forName(String className)throws ClassNotFoundException  

Note: Since JDBC 4.0, explicitly registering the driver is optional. We just need to put vender's Jar in
the classpath, and then JDBC driver manager can detect and load the driver automatically.

Example to register the OracleDriver class

Here, Java program is loading oracle driver to esteblish database connection.

1. Class.forName("oracle.jdbc.driver.OracleDriver");  

2) Create the connection object

The getConnection() method of DriverManager class is used to establish connection with the database

Syntax of getConnection() method

1. 1) public static Connection getConnection(String url)throws SQLException  
2. 2) public static Connection getConnection(String url,String name,String password)  
3. throws SQLException  

Example to establish connection with the Oracle database

1. Connection con=DriverManager.getConnection(  
2. "jdbc:oracle:thin:@localhost:1521:xe","system","password");  

3) Create the Statement object

The createStatement() method of Connection interface is used to create statement. The object of statem
execute queries with the database.
 Syntax of createStatement() method

1. public Statement createStatement()throws SQLException  

Example to create the statement object

1. Statement stmt=con.createStatement();  

4) Execute the query

The executeQuery() method of Statement interface is used to execute queries to the database. This met
object of ResultSet that can be used to get all the records of a table.

Syntax of executeQuery() method

1. public ResultSet executeQuery(String sql)throws SQLException  

Example to execute query

1. ResultSet rs=stmt.executeQuery("select * from emp");  
2.   
3. while(rs.next()){  
4. System.out.println(rs.getInt(1)+" "+rs.getString(2));  
5. }  

5) Close the connection object

By closing connection object statement and ResultSet will be closed automatically. The close() method o
interface is used to close the connection.

Syntax of close() method

1. public void close()throws SQLException  

Example to close connection

1. con.close();  
 Java Database Connectivity with Oracle
To connect java application with the oracle database, we need to follow 5 following steps. In this exampl
Oracle 10g as the database. So we need to know following information for the oracle database:
1. Driver class: The driver class for the oracle database is oracle.jdbc.driver.OracleDriver.
2. Connection URL: The connection URL for the oracle10G database is jdbc:oracle:thin:@localho
jdbc is the API, oracle is the database, thin is the driver, localhost is the server name on which or
may also use IP address, 1521 is the port number and XE is the Oracle service name. You may g
information from the tnsnames.ora file.
3. Username: The default username for the oracle database is system.
4. Password: It is the password given by the user at the time of installing the oracle database.

Create a Table

Before establishing connection, let's first create a table in oracle database.

Following is the SQL query to create a table.
1. create table emp(id number(10),name varchar2(40),age number(3));  

Example to Connect Java Application with Oracle database

In this example, we are connecting to an Oracle database and getting data from emp table.
Here, system and oracle are the username and password of the Oracle database.

1. import java.sql.*;  
2. class OracleCon{  
3. public static void main(String args[]){  
4. try{  
5. //step1 load the driver class  
6. Class.forName("oracle.jdbc.driver.OracleDriver");  
7.   
8. //step2 create  the connection object  
9. Connection con=DriverManager.getConnection(  
10. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
11.   
12. //step3 create the statement object  
13. Statement stmt=con.createStatement();  
14.   
15. //step4 execute query  
16. ResultSet rs=stmt.executeQuery("select * from emp");  
17. while(rs.next())  
18. System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3));  
19.   
20. //step5 close the connection object  
21. con.close();  
22.   
23. }catch(Exception e){ System.out.println(e);}  
24.   
25. }  
26. }  
 Java Database Connectivity with MySQL
To connect Java application with the MySQL database, we need to follow 5 following steps.

In this example we are using MySql as the database. So we need to know following
informations for the mysql database:

1. Driver class: The driver class for the mysql database is com.mysql.jdbc.Driver.

2. Connection URL: The connection URL for the mysql database
is jdbc:mysql://localhost:3306/sonoo where jdbc is the API, mysql is the
database, localhost is the server name on which mysql is running, we may also use
IP address, 3306 is the port number and sonoo is the database name. We may use
any database, in such case, we need to replace the sonoo with our database name.
3. Username: The default username for the mysql database is root.
4. Password: It is the password given by the user at the time of installing the mysql
database. In this example, we are going to use root as the password.

Let's first create a table in the mysql database, but before creating table, we need to create
database first.

1. create database sonoo;  
2. use sonoo;  
3. create table emp(id int(10),name varchar(40),age int(3));  

Example to Connect Java Application with mysql database

In this example, sonoo is the database name, root is the username and password both.

1. import java.sql.*;  
2. class MysqlCon{  
3. public static void main(String args[]){  
4. try{  
5. Class.forName("com.mysql.jdbc.Driver");  
6. Connection con=DriverManager.getConnection(  
7. "jdbc:mysql://localhost:3306/sonoo","root","root");  
8. //here sonoo is database name, root is username and password  
9. Statement stmt=con.createStatement();  
10. ResultSet rs=stmt.executeQuery("select * from emp");  
11. while(rs.next())  
12. System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3));  
13. con.close();  
14. }catch(Exception e){ System.out.println(e);}  
15. }  
16. }  

DriverManager class
The DriverManager class acts as an interface between user and drivers. It keeps track of the
drivers that are available and handles establishing a connection between a database and the
appropriate driver. The DriverManager class maintains a list of Driver classes that have
registered themselves by calling the method DriverManager.registerDriver().

Useful methods of DriverManager class

Method Description

1) public static void registerDriver(Driver driver): is used to register the given driver wi

2) public static void deregisterDriver(Driver driver): is used to deregister the given driver
from the list) with DriverManager.

3) public static Connection getConnection(String url): is used to establish the connection wi

4) public static Connection getConnection(String url,String is used to establish the connection wi

userName,String password): username and password.

Connection interface
A Connection is the session between java application and database. The Connection
interface is a factory of Statement, PreparedStatement, and DatabaseMetaData i.e. object
of Connection can be used to get the object of Statement and DatabaseMetaData. The
Connection interface provide many methods for transaction management like commit(),
rollback() etc.
By default, connection commits the changes after executing queries.

Commonly used methods of Connection interface:

1) public Statement createStatement(): creates a statement object that can be used to execute SQL
queries.

2) public Statement createStatement(int resultSetType,int resultSetConcurrency): Creates a

Statement object that will generate ResultSet objects with the given type and concurrency.

3) public void setAutoCommit(boolean status): is used to set the commit status.By default it is true

4) public void commit(): saves the changes made since the previous commit/rollback permanent.

5) public void rollback(): Drops all changes made since the previous commit/rollback.

6) public void close(): closes the connection and Releases a JDBC resources immediately.

Statement interface
The Statement interface provides methods to execute queries with the database. The
statement interface is a factory of ResultSet i.e. it provides factory method to get the object
of ResultSet.

Commonly used methods of Statement interface:

The important methods of Statement interface are as follows:

1) public ResultSet executeQuery(String sql): is used to execute SELECT query. It returns the obje
 2) public int executeUpdate(String sql): is used to execute specified query, it may be create, drop,
etc.
3) public boolean execute(String sql): is used to execute queries that may return multiple results.
4) public int[] executeBatch(): is used to execute batch of commands.

Example of Statement interface

Let’s see the simple example of Statement interface to insert, update and delete the record.

1. import java.sql.*;  
2. class FetchRecord{  
3. public static void main(String args[])throws Exception{  
4. Class.forName("oracle.jdbc.driver.OracleDriver");  
5. Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe
","system","oracle");  
6. Statement stmt=con.createStatement();  
7.   
8. int k=stmt.executeUpdate("insert into emp765 values(33,'Irfan',50000)");  
9. If(k>0){Sop(“executed successfully”);}
10. else
11. Sop(“not executed”);
12. int result=stmt.executeUpdate
("update emp765 set name='Vimal',salary=10000 where id=33");  
13.
14. int result=stmt.executeUpdate("delete from emp765 where id=33");  
15. System.out.println(result+" records affected");  
16. con.close();  
17. }}  

ResultSet interface
The object of ResultSet maintains a cursor pointing to a row of a table. Initially, cursor
points to before the first row.
 By default, ResultSet object can be moved forward only and it is not updatable.

But we can make this object to move forward and backward direction by passing either
TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE in createStatement(int,int)
method as well as we can make this object as updatable by:

1. Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,  
2.                      ResultSet.CONCUR_UPDATABLE);  

Commonly used methods of ResultSet interface

1) public boolean next(): is used to move the cursor to the one row next from the

2) public boolean previous(): is used to move the cursor to the one row previous from
position.

3) public boolean first(): is used to move the cursor to the first row in result set

4) public boolean last(): is used to move the cursor to the last row in result set o

5) public boolean absolute(int row): is used to move the cursor to the specified row number
object.

6) public boolean relative(int row): is used to move the cursor to the relative row number i
object, it may be positive or negative.

7) public int getInt(int is used to return the data of specified column index of t
columnIndex): int.

8) public int getInt(String is used to return the data of specified column name of t
columnName): int.

9) public String getString(int is used to return the data of specified column index of t
columnIndex): String.
 10) public String getString(String is used to return the data of specified column name of t
columnName): String.

Example of Scrollable ResultSet

Let’s see the simple example of ResultSet interface to retrieve the data of 3rd row.

1. import java.sql.*;  
2. class FetchRecord{  
3. public static void main(String args[])throws Exception{  
4.   
5. Class.forName("com.mysql.jdbc.Driver");  
6. Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/sonoo",
"root","root");  
7. Statement stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSe
t.CONCUR_UPDATABLE);  
8. ResultSet rs=stmt.executeQuery("select * from emp765");  
9.   
10. //getting the record of 3rd row  
11. rs.absolute(3);  
12. System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));  
13.   
14. con.close();  
15. }}  
 PreparedStatement interface
The PreparedStatement interface is a subinterface of Statement. It is used to execute
parameterized query.

Let's see the example of parameterized query:

1. String sql="insert into emp values(?,?,?)";  

As you can see, we are passing parameter (?) for the values. Its value will be set by calling
the setter methods of PreparedStatement.

Why use PreparedStatement?

Improves performance: The performance of the application will be faster if you use
PreparedStatement interface because query is compiled only once.

How to get the instance of PreparedStatement?

The prepareStatement() method of Connection interface is used to return the object of

PreparedStatement. Syntax:

1. public PreparedStatement prepareStatement(String query)throws SQLException{}  

Methods of PreparedStatement interface

The important methods of PreparedStatement interface are given below:

Method Description

public void setInt(int sets the integer value to the given parameter index.
paramIndex, int value)

public void sets the String value to the given parameter index.
setString(int
paramIndex, String
value)
 public void setFloat(int sets the float value to the given parameter index.
paramIndex, float
value)

public void sets the double value to the given parameter index.
setDouble(int
paramIndex, double
value)

public int executes the query. It is used for create, drop, insert, update,
executeUpdate() delete etc.

public ResultSet executes the select query. It returns an instance of ResultSet.

executeQuery()

Example of PreparedStatement interface that inserts the record

First of all create table as given below:

1. create table emp(id number(10),name varchar2(50));  

Now insert records in this table by the code given below:

1. import java.sql.*;  
2. class InsertPrepared{  
3. public static void main(String args[]){  
4. try{  
5. Class.forName("com.mysql.jdbc.Driver");  
6.   
7. Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/sonoo",
"root","root");  
8.   
9. PreparedStatement stmt=con.prepareStatement("insert into Emp values(?,?)");  
10. stmt.setInt(1,101);//1 specifies the first parameter in the query  
11. stmt.setString(2,"Ratan");  
12.   
13. int i=stmt.executeUpdate();  
14. System.out.println(i+" records inserted");  
15.   
16. con.close();  
17.   
18. }catch(Exception e){ System.out.println(e);}  
19.   
20. }  
21. }  

Example of PreparedStatement interface that updates the record

1. PreparedStatement stmt=con.prepareStatement("update emp set name=? where id
=?");  
2. stmt.setString(1,"Sonoo");//1 specifies the first parameter in the query i.e. name  
3. stmt.setInt(2,101);  
4.   
5. int i=stmt.executeUpdate();  
6. System.out.println(i+" records updated");  

Example of PreparedStatement interface that deletes the record

1. PreparedStatement stmt=con.prepareStatement("delete from emp where id=?");  
2. stmt.setInt(1,101);  
3.   
4. int i=stmt.executeUpdate();  
5. System.out.println(i+" records deleted");  

Example of PreparedStatement interface that retrieve the records

of a table
1. PreparedStatement stmt=con.prepareStatement("select * from emp");  
2. ResultSet rs=stmt.executeQuery();  
3. while(rs.next()){  
4. System.out.println(rs.getInt(1)+" "+rs.getString(2));  
5. }  
 Example of PreparedStatement to insert records until user press
n
1. import java.sql.*;  
2. import java.io.*;  
3. class RS{  
4. public static void main(String args[])throws Exception{  
5. Class.forName("oracle.jdbc.driver.OracleDriver");  
6. Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe
","system","oracle");  
7.   
8. PreparedStatement ps=con.prepareStatement("insert into emp130 values(?,?,?)");  
9.   
10. BufferedReader br=new BufferedReader(new InputStreamReader(System.in));  
11.   
12. do{  
13. System.out.println("enter id:");  
14. int id=Integer.parseInt(br.readLine());  
15. System.out.println("enter name:");  
16. String name=br.readLine();  
17. System.out.println("enter salary:");  
18. float salary=Float.parseFloat(br.readLine());  
19.   
20. ps.setInt(1,id);  
21. ps.setString(2,name);  
22. ps.setFloat(3,salary);  
23. int i=ps.executeUpdate();  
24. System.out.println(i+" records affected");  
25.   
26. System.out.println("Do you want to continue: y/n");  
27. String s=br.readLine();  
28. if(s.startsWith("n")){  
29. break;  
30. }  
31. }while(true);  
32.   
33. con.close();  
34. }}  
 Example to store image in Oracle database
You can store images in the database in java by the help of PreparedStatement interface.

The setBinaryStream() method of PreparedStatement is used to set Binary information

into the parameterIndex.

Signature of setBinaryStream method

The syntax of setBinaryStream() method is given below:

1. 1) public void setBinaryStream(int paramIndex,InputStream stream)  
2. throws SQLException  
3. 2) public void setBinaryStream(int paramIndex,InputStream stream,long length)  
4. throws SQLException  

For storing image into the database, BLOB (Binary Large Object) datatype is used in the
table. For example:

1. CREATE TABLE  "IMGTABLE"   
2.    (    "NAME" VARCHAR2(4000),   
3.     "PHOTO" BLOB  
4.    )  
5. /  

Let's write the jdbc code to store the image in the database. Here we are using d:\\d.jpg for
the location of image. You can change it according to the image location.

Java Example to store image in the database

1. import java.sql.*;  
2. import java.io.*;  
3. public class InsertImage {  
4. public static void main(String[] args) {  
5. try{  
6. Class.forName("oracle.jdbc.driver.OracleDriver");  
7. Connection con=DriverManager.getConnection(  
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
9.               
10. PreparedStatement ps=con.prepareStatement("insert into imgtable values(?,?)");  
11. ps.setString(1,"sonoo");  
12.   
13. FileInputStream fin=new FileInputStream("d:\\g.jpg");  
14. ps.setBinaryStream(2,fin,fin.available());  
15. int i=ps.executeUpdate();  
16. System.out.println(i+" records affected");  
17.           
18. con.close();  
19. }catch (Exception e) {e.printStackTrace();}  
20. }  
21. }  

Example to retrieve image from Oracle

database
By the help of PreparedStatement we can retrieve and store the image in the database.

The getBlob() method of PreparedStatement is used to get Binary information, it returns

the instance of Blob. After calling the getBytes() method on the blob object, we can get
the array of binary information that can be written into the image file.

Signature of getBlob() method of PreparedStatement

1. public Blob getBlob()throws SQLException  

Signature of getBytes() method of Blob interface

1. public  byte[] getBytes(long pos, int length)throws SQLException  

We are assuming that image is stored in the imgtable.

1. CREATE TABLE  "IMGTABLE"   
2.    (    "NAME" VARCHAR2(4000),   
3.     "PHOTO" BLOB  
4.    )  
5. /  
 Now let's write the code to retrieve the image from the database and write it into the
directory so that it can be displayed.

In AWT, it can be displayed by the Toolkit class. In servlet, jsp, or html it can be displayed
by the img tag.

1. import java.sql.*;  
2. import java.io.*;  
3. public class RetrieveImage {  
4. public static void main(String[] args) {  
5. try{  
6. Class.forName("oracle.jdbc.driver.OracleDriver");  
7. Connection con=DriverManager.getConnection(  
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
9.       
10. PreparedStatement ps=con.prepareStatement("select * from imgtable");  
11. ResultSet rs=ps.executeQuery();  
12. if(rs.next()){//now on 1st row  
13.               
14. Blob b=rs.getBlob(2);//2 means 2nd column data  
15. byte barr[]=b.getBytes(1,(int)b.length());//1 means first image  
16.               
17. FileOutputStream fout=new FileOutputStream("d:\\sonoo.jpg");  
18. fout.write(barr);  
19.               
20. fout.close();  
21. }//end of if  
22. System.out.println("ok");  
23.               
24. con.close();  
25. }catch (Exception e) {e.printStackTrace();  }  
26. }  
27. }  
Java CallableStatement Interface
CallableStatement interface is used to call the stored procedures and functions.

We can have business logic on the database by the use of stored procedures and functions
that will make the performance better because these are precompiled.

Suppose you need the get the age of the employee based on the date of birth, you may
create a function that receives date as the input and returns age of the employee as the
output.

What is the difference between stored procedures and functions.

The differences between stored procedures and functions are given below:

Stored Procedure Function

is used to perform business logic. is used to perform calculation.

must not have the return type. must have the return type.

may return 0 or more values. may return only one values.

We can call functions from the Procedure cannot be called from function.
procedure.

Procedure supports input and output Function supports only input parameter.
parameters.

Exception handling using try/catch Exception handling using try/catch can't be used in user
block can be used in stored defined functions.
procedures.
 How to get the instance of CallableStatement?
The prepareCall() method of Connection interface returns the instance of CallableStatement.
Syntax is given below:

1. public CallableStatement prepareCall("{ call procedurename(?,?...?)}");  

The example to get the instance of CallableStatement is given below:

1. CallableStatement stmt=con.prepareCall("{call myprocedure(?,?)}");  

It calls the procedure myprocedure that receives 2 arguments.

Full example to call the stored procedure using JDBC

To call the stored procedure, you need to create it in the database. Here, we are assuming
that stored procedure looks like this.

1. create or replace procedure "INSERTR"  
2. (id IN NUMBER,  
3. name IN VARCHAR2)  
4. is  
5. begin  
6. insert into user420 values(id,name);  
7. end;  
8. /     

The table structure is given below:

1. create table user420(id number(10), name varchar2(200));  

In this example, we are going to call the stored procedure INSERTR that receives id and
name as the parameter and inserts it into the table user420. Note that you need to create
the user420 table as well to run this application.

1. import java.sql.*;  
2. public class Proc {  
3. public static void main(String[] args) throws Exception{  
4.   
5. Class.forName("oracle.jdbc.driver.OracleDriver");  
6. Connection con=DriverManager.getConnection(  
7. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
8.   
9. CallableStatement stmt=con.prepareCall("{call insertR(?,?)}");  
10. stmt.setInt(1,1011);  
11. stmt.setString(2,"Amit");  
12. stmt.execute();  
13.   
14. System.out.println("success");  
15. }  
16. }  

Now check the table in the database, value is inserted in the user420 table.

Example to call the function using JDBC

In this example, we are calling the sum4 function that receives two input and returns the
sum of the given number. Here, we have used the registerOutParameter method of
CallableStatement interface, that registers the output parameter with its corresponding
type. It provides information to the CallableStatement about the type of result being
displayed.

The Types class defines many constants such as INTEGER, VARCHAR, FLOAT, DOUBLE,

BLOB, CLOB etc.

Let's create the simple function in the database first.

1. create or replace function sum4  
2. (n1 in number,n2 in number)  
3. return number  
4. is   
5. temp number(8);  
6. begin  
7. temp :=n1+n2;  
8. return temp;  
9. end;  
10. /  

Now, let's write the simple program to call the function.

1. import java.sql.*;  
2.   
3. public class FuncSum {  
4. public static void main(String[] args) throws Exception{  
5.   
6. Class.forName("oracle.jdbc.driver.OracleDriver");  
7. Connection con=DriverManager.getConnection(  
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
9.   
10. CallableStatement stmt=con.prepareCall("{?= call sum4(?,?)}");  
11. stmt.setInt(2,10);  
12. stmt.setInt(3,43);  
13. stmt.registerOutParameter(1,Types.INTEGER);  
14. stmt.execute();  
15.   
16. System.out.println(stmt.getInt(1));  
17.           
18. }  
19. }  
Output: 53
 JDBC RowSet
The instance of RowSet is the java bean component because it has properties and java
bean notification mechanism. It is introduced since JDK 5.

It is the wrapper of ResultSet. It holds tabular data like ResultSet but it is easy and flexible
to use.

The implementation classes of RowSet interface are as follows:

o JdbcRowSet
o CachedRowSet
o WebRowSet
o JoinRowSet
o FilteredRowSet

Let's see how to create and execute RowSet.

1. JdbcRowSet rowSet = RowSetProvider.newFactory().createJdbcRowSet();  
2. rowSet.setUrl("jdbc:oracle:thin:@localhost:1521:xe");  
3. rowSet.setUsername("system");  
4. rowSet.setPassword("oracle");  
5.            
6. rowSet.setCommand("select * from emp400");  
7. rowSet.execute();  

It is the new way to get the instance of JdbcRowSet since JDK 7.

Advantage of RowSet

The advantages of using RowSet are given below:

1. It is easy and flexible to use

2. It is Scrollable and Updatable bydefault

Simple example of JdbcRowSet

Let's see the simple example of JdbcRowSet without event handling code.

1. import java.sql.Connection;  
2. import java.sql.DriverManager;  
3. import java.sql.ResultSet;  
4. import java.sql.Statement;  
5. import javax.sql.RowSetEvent;  
6. import javax.sql.RowSetListener;  
7. import javax.sql.rowset.JdbcRowSet;  
8. import javax.sql.rowset.RowSetProvider;  
9.   
10. public class RowSetExample {  
11.         public static void main(String[] args) throws Exception {  
12.                  Class.forName("oracle.jdbc.driver.OracleDriver");  
13.       
14.     //Creating and Executing RowSet  
15.         JdbcRowSet rowSet = RowSetProvider.newFactory().createJdbcRowSet();  
16.         rowSet.setUrl("jdbc:oracle:thin:@localhost:1521:xe");  
17.         rowSet.setUsername("system");  
18.         rowSet.setPassword("oracle");  
19.                    
20.         rowSet.setCommand("select * from emp400");  
21.         rowSet.execute();  
22.                    
23.     while (rowSet.next()) {  
24.                         // Generating cursor Moved event  
25.                         System.out.println("Id: " + rowSet.getString(1));  
26.                         System.out.println("Name: " + rowSet.getString(2));  
27.                         System.out.println("Salary: " + rowSet.getString(3));  
28.                 }  
29.                  
30.         }  
31. }  

The output is given below:

Id: 55
Name: Om Bhim
Salary: 70000
Id: 190
Name: abhi
Salary: 40000
Id: 191
Name: umesh
Salary: 50000

Full example of Jdbc RowSet with event handling

To perform event handling with JdbcRowSet, you need to add the instance
of RowSetListener in the addRowSetListener method of JdbcRowSet.

The RowSetListener interface provides 3 method that must be implemented. They are as
follows:

1) public void cursorMoved(RowSetEvent event);

2) public void rowChanged(RowSetEvent event);
3) public void rowSetChanged(RowSetEvent event);

Let's write the code to retrieve the data and perform some additional tasks while cursor is
moved, cursor is changed or rowset is changed. The event handling operation can't be
performed using ResultSet so it is preferred now.

1. import java.sql.Connection;  
2. import java.sql.DriverManager;  
3. import java.sql.ResultSet;  
4. import java.sql.Statement;  
5. import javax.sql.RowSetEvent;  
6. import javax.sql.RowSetListener;  
7. import javax.sql.rowset.JdbcRowSet;  
8. import javax.sql.rowset.RowSetProvider;  
9.   
10. public class RowSetExample {  
11.         public static void main(String[] args) throws Exception {  
12.                  Class.forName("oracle.jdbc.driver.OracleDriver");  
13.       
14.     //Creating and Executing RowSet  
15.     JdbcRowSet rowSet = RowSetProvider.newFactory().createJdbcRowSet();  
16.     rowSet.setUrl("jdbc:oracle:thin:@localhost:1521:xe");  
17.     rowSet.setUsername("system");  
18.     rowSet.setPassword("oracle");  
19.                    
20.         rowSet.setCommand("select * from emp400");  
21.         rowSet.execute();  
22.                    
23.     //Adding Listener and moving RowSet  
24.     rowSet.addRowSetListener(new MyListener());  
25.   
26.                  while (rowSet.next()) {  
27.                         // Generating cursor Moved event  
28.                         System.out.println("Id: " + rowSet.getString(1));  
29.                         System.out.println("Name: " + rowSet.getString(2));  
30.                         System.out.println("Salary: " + rowSet.getString(3));  
31.                 }  
32.                  
33.         }  
34. }  
35.   
36. class MyListener implements RowSetListener {  
37.       public void cursorMoved(RowSetEvent event) {  
38.                 System.out.println("Cursor Moved...");  
39.       }  
40.      public void rowChanged(RowSetEvent event) {  
41.                 System.out.println("Cursor Changed...");  
42.      }  
43.      public void rowSetChanged(RowSetEvent event) {  
44.                 System.out.println("RowSet changed...");  
45.      }  
46. }  

The output is as follows:

Cursor Moved...
Id: 55
Name: Om Bhim
Salary: 70000
Cursor Moved...
Id: 190
Name: abhi
Salary: 40000
Cursor Moved...
Id: 191
Name: umesh
Salary: 50000
Cursor Moved...