JDBC Tutorial

1. JDBC
2. Why use JDBC?
3. What is API?

This JDBC tutorial covers all the topics of JDBC with the simple
examples. JDBC is a Java API that is used to connect and execute query
to the database. JDBC API uses jdbc drivers to connects to the database.

Why use JDBC?

Before JDBC, ODBC API was used to connect and execute query to the
database. But ODBC API uses ODBC driver that is written in C
language which is plateform dependent and unsecured. That is why Sun
Microsystem has defined its own API (JDBC API) that uses JDBC driver
written in Java language.

Do You Know ?

 How to connect Java application with Oracle and Mysql database

using JDBC?
 What is the difference between Statement and PreparedStatement
interface?
  How to print total numbers of tables and views of a database using
JDBC ?
 How to store and retrieve images from Oracle database using
JDBC?
 How to store and retrieve files from Oracle database using JDBC?

What is API?

API (Application programming interface) is a document that contains

description of all the features of a product or software. It represents
classes and interfaces that software programs can follow to communicate
with each other. An API can be created for applications, libraries,
operating systems, etc

Upcoming topics in JDBC Tutorial

2) JDBC Drivers

In this JDBC tutorial, we will learn 4 types of JDBC drivers, their

advantages and disadvantages.

3) 5 Steps to connect to the database

In this JDBC tutorial, we will see the 5 steps to connect to the database
in java using JDBC.

4) Connectivity with Oracle using JDBC

In this JDBC tutorial, we will connect a simple java program with the
oracle database.

5) Connectivity with MySQL using JDBC

In this JDBC tutorial, we will connect a simple java program with the
mysql database.

6) Connectivity with Access without DSN

Let's connect java application with access database with and without
DSN.

7) DriverManager class

In this JDBC tutorial, we will learn what does the DriverManager class
and what are its methods.

8) Connection interface

In this JDBC tutorial, we will learn what is Connection interface and

what are its methods.

9) Statement interface

In this JDBC tutorial, we will learn what is Statement interface and what
are its methods.

10) ResultSet interface

In this JDBC tutorial, we will learn what is ResultSet interface and what
are its methods. Moreover, we will learn how we can make the ResultSet
scrollable.

11) PreparedStatement Interface

In this JDBC tutorial, we will learn what is benefit of PreparedStatement
over Statement interface. We will see examples to insert, update or
delete records using the PreparedStatement interface.

12) ResultSetMetaData interface

In this JDBC tutorial, we will learn how we can get the metadata of a
table.

13) DatabaseMetaData interface

In this JDBC tutorial, we will learn how we can get the metadata of a
database.

14) Storing image in Oracle

Let's learn how to store image in the oracle database using JDBC.

15) Retrieving image from Oracle

Let's see the simple example to retrieve image from the oracle database
using JDBC.

16) Storing file in Oracle

Let's see the simple example to store file in the oracle database using
JDBC.

17) Retrieving file from Oracle

Let's see the simple example to retrieve file from the oracle database
using JDBC.
JDBC Driver

1. JDBC Drivers
1. JDBC-ODBC bridge driver
2. Native-API driver
3. Network Protocol driver
4. Thin 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. This is now discouraged because of thin
driver.
Advantages:

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

Disadvantages:

 Performance degraded because JDBC method call is converted into

the ODBC function calls.
 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 calls into native calls of the database API.
It is not written entirely in java.
Advantage:

 performance upgraded than JDBC-ODBC bridge driver.

Disadvantage:

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

 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:

 No client side library is required because of application server that

can perform many tasks like auditing, load balancing, logging etc.

Disadvantages:

 Network support is required on client machine.

 Requires database-specific coding to be done in the middle tier.
 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 known as thin driver. It is fully
written in Java language.
Advantage:

 Better performance than all other drivers.

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

Disadvantage:

 Drivers depends on the Database.

5 Steps to connect to the database

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 in
java using JDBC. They are as follows:

 Register the driver class

 Creating connection
 Creating statement
 Executing queries
 Closing connection

1) Register the driver class

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

Syntax of forName() method

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

dException

Example to register the OracleDriver class

Class.forName("com.mysql.cj.jdbc.Driver");//for mysql database

Class.forName("org.postgresql.Driver");//for postgres database

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 SQLEx

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

Example to establish connection with the database

Connection
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/testexa
mple","root","admin");//mysql database

Connection
con=DriverManager.getConnection("jdbc:postgresql://localhost:5432/
Emp_dtl","postgres","postgres");//postgres database

3) Create the Statement object

The createStatement() method of Connection interface is used to create

statement. The object of statement is responsible to 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 method returns the 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 of Connection interface is used to
close the connection.

Syntax of close() method

1. public void close()throws SQLException

Example to close connection

1. con.close();

Example to connect to the Oracle database

For connecting java application with the oracle database, you need to
follow 5 steps to perform database connectivity. In this example we are
using Oracle10g as the database. So we need to know following
informations 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:@localhost:1521:xe where jdbc is the API,
oracle is the database, thin is the driver, localhost is the server
name on which oracle is running, we may also use IP address,
1521 is the port number and XE is the Oracle service name. You
may get all these informations from the tnsnames.ora file.
3. Username: The default username for the oracle database is
system.
4. Password: Password is given by the user at the time of installing
the oracle database.

Let's first create a table in oracle database.

1. create table emp(id number(10),name varchar2(40),age number(3

));
Example to Connect Java Application with Oracle database

In this example, system is the username and oracle is the 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.ge
tString(3));
19.
20. //step5 close the connection object
21. con.close();
22.
23. }catch(Exception e){ System.out.println(e);}
24.
25. }
26. }

download this example

The above example will fetch all the records of emp table.

To connect java application with the Oracle database ojdbc14.jar file is

required to be loaded.

download the jar file ojdbc14.jar

Two ways to load the jar file:

1. paste the ojdbc14.jar file in jre/lib/ext folder

2. set classpath

1) paste the ojdbc14.jar file in JRE/lib/ext folder:

Firstly, search the ojdbc14.jar file then go to JRE/lib/ext folder and paste
the jar file here.

2) set classpath:

There are two ways to set the classpath:

 temporary
 permanent

How to set the temporary classpath:

Firstly, search the ojdbc14.jar file then open command prompt and
write:

1. C:>set classpath=c:\folder\ojdbc14.jar;.;

How to set the permanent classpath:

Go to environment variable then click on new tab. In variable name
write classpath and in variable value paste the path to ojdbc14.jar by
appending ojdbc14.jar;.; as C:\oraclexe\app\oracle\product\10.2.0\
server\jdbc\lib\ojdbc14.jar;.;

To see the slides of seting parmanent path click here

Example to connect to the mysql database

For connecting java application with the mysql database, you need to
follow 5 steps to perform database connectivity.

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, you need to replace the sonoo with your database name.
3. Username: The default username for the mysql database is root.
4. Password: Password is 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.

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

download this example

The above example will fetch all the records of emp table.

To connect java application with the mysql database mysqlconnector.jar

file is required to be loaded.

download the jar file mysql-connector.jar

Two ways to load the jar file:

1. paste the mysqlconnector.jar file in jre/lib/ext folder

2. set classpath

1) paste the mysqlconnector.jar file in JRE/lib/ext folder:

Download the mysqlconnector.jar file. Go to jre/lib/ext folder and paste

the jar file here.

2) set classpath:

There are two ways to set the classpath:

 temporary
 permament

How to set the temporary classpath

open comman prompt and write:

 1. C:>set classpath=c:\folder\mysql-connector-java-5.0.8-bin.jar;.;

How to set the permanent classpath

Go to environment variable then click on new tab. In variable name

write classpath and in variable value paste the path to the
mysqlconnector.jar file by appending mysqlconnector.jar;.; as C:\folder\
mysql-connector-java-5.0.8-bin.jar;.;

Connectivity with Access without DSN

There are two ways to connect java application with the access database.

1. Without DSN (Data Source Name)

2. With DSN

Java is mostly used with Oracle, mysql, or DB2 database. So you can
learn this topic only for knowledge.

Example to Connect Java Application with access without DSN

In this example, we are going to connect the java program with the
access database. In such case, we have created the login table in the
access database. There is only one column in the table named name.
Let's get all the name of the login table.

1. import java.sql.*;
2. class Test{
3. public static void main(String ar[]){
 4. try{
5. String database="student.mdb";//Here database exists in the cu
rrent directory
6.
7. String url="jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};
8. DBQ=" + database + ";DriverID=22;READONLY=true";
9.
10. Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
11. Connection c=DriverManager.getConnection(url);
12. Statement st=c.createStatement();
13. ResultSet rs=st.executeQuery("select * from login");
14.
15. while(rs.next()){
16. System.out.println(rs.getString(1));
17. }
18.
19. }catch(Exception ee){System.out.println(ee);}
20.
21. }}

download this example

Example to Connect Java Application with access with DSN

Connectivity with type1 driver is not considered good. To connect java

application with type1 driver, create DSN first, here we are assuming
your dsn name is mydsn.

1. import java.sql.*;
2. class Test{
3. public static void main(String ar[]){
4. try{
5. String url="jdbc:odbc:mydsn";
6. Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 7. Connection c=DriverManager.getConnection(url);
8. Statement st=c.createStatement();
9. ResultSet rs=st.executeQuery("select * from login");
10.
11. while(rs.next()){
12. System.out.println(rs.getString(1));
13. }
14.
15. }catch(Exception ee){System.out.println(ee);}
16.
17. }}

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().

Commonly used methods of DriverManager class:

1) public static void is used to register the given

registerDriver(Driver driver): driver with DriverManager.
is used to deregister the given
2) public static void
driver (drop the driver from the
deregisterDriver(Driver driver):
list) with DriverManager.
3) public static Connection is used to establish the
getConnection(String url): connection with the specified url.
4) public static Connection is used to establish the
getConnection(String url,String connection with the specified url,
userName,String password): username and password.
ResultSetMetaData Interface

The metadata means data about data i.e. we can get further information
from the data.

If you have to get metadata of a table like total number of column,

column name, column type etc. , ResultSetMetaData interface is useful
because it provides methods to get metadata from the ResultSet object.

Commonly used methods of ResultSetMetaData interface

Method Description

it returns the total number of

public int getColumnCount()throws
columns in the ResultSet
SQLException
object.

public String getColumnName(int it returns the column name of

index)throws SQLException the specified column index.

public String getColumnTypeName(int it returns the column type

index)throws SQLException name for the specified index.

public String getTableName(int it returns the table name for

index)throws SQLException the specified column index.

How to get the object of ResultSetMetaData:

The getMetaData() method of ResultSet interface returns the object of

ResultSetMetaData. Syntax:
 1. public ResultSetMetaData getMetaData()throws SQLException

Example of ResultSetMetaData interface :

1. import java.sql.*;
2. class Rsmd{
3. public static void main(String args[]){
4. try{
5. Class.forName("oracle.jdbc.driver.OracleDriver");
6.
7. Connection con=DriverManager.getConnection(
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
9.
10. PreparedStatement ps=con.prepareStatement("select * fro
m emp");
11. ResultSet rs=ps.executeQuery();
12.
13. ResultSetMetaData rsmd=rs.getMetaData();
14.
15. System.out.println("Total columns: "+rsmd.getColumnCount
());
16. System.out.println("Column Name of 1st column: "+rsmd.ge
tColumnName(1));
17. System.out.println("Column Type Name of 1st column: "+rs
md.getColumnTypeName(1));
18.
19. con.close();
20.
21. }catch(Exception e){ System.out.println(e);}
22.
23. }
24. }
Output:Total columns: 2
Column Name of 1st column: ID
Column Type Name of 1st column: NUMBER

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 object of ResultSet.
2) public int executeUpdate(String sql): is used to execute specified
query, it may be create, drop, insert, update, delete 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.

import java.sql.*;

class FetchRecord{

public static void main(String args[])throws Exception{

Class.forName("com.mysql.jdbc.Driver");
 Connection con=DriverManager.getConnection("jdbc:mysql://
localhost:3306/testexample”,”root”,”admin”);

Statement stmt=con.createStatement();

stmt.executeUpdate("insert into student_info values(33,'Irfan',’tybsc’)

");

int result=stmt.executeUpdate("update student_info set name='Vimal'

where rollno=33");

int result=stmt.executeUpdate("delete from student_info where rollno

=33");

System.out.println(result+" records affected");

con.close();

}}

ResultSet interface

The object of ResultSet maintains a cursor pointing to a particular row

of data. 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_I

NSENSITIVE,
2. ResultSet.CONCUR_UPDATABLE);

Commonly used methods of ResultSet interface

is used to move the cursor to the one row

1) public boolean next():
next from the current position.

2) public boolean is used to move the cursor to the one row

previous(): previous from the current position.

is used to move the cursor to the first row

3) public boolean first():
in result set object.

is used to move the cursor to the last row in

4) public boolean last():
result set object.

5) public boolean is used to move the cursor to the specified

absolute(int row): row number in the ResultSet object.

is used to move the cursor to the relative

6) public boolean
row number in the ResultSet object, it may
relative(int row):
be positive or negative.
7) public int getInt(int is used to return the data of specified
columnIndex): column index of the current row as int.

8) public int getInt(String is used to return the data of specified

columnName): column name of the current row as int.

9) public String
is used to return the data of specified
getString(int
column index of the current row as String.
columnIndex):

10) public String

is used to return the data of specified
getString(String
column name of the current row as String.
columnName):

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("oracle.jdbc.driver.OracleDriver");
6. Connection con=DriverManager.getConnection("jdbc:oracle:thin:
@localhost:1521:xe","system","oracle");
7. Statement stmt=con.createStatement(ResultSet.TYPE_SCROLL_SE
NSITIVE,ResultSet.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

paramIndex, int value) parameter index.

public void setString(int sets the String value to the given

paramIndex, String value) parameter index.

public void setFloat(int sets the float value to the given

paramIndex, float value) parameter index.

public void setDouble(int sets the double value to the given

paramIndex, double value) parameter index.

executes the query. It is used for

public int executeUpdate() create, drop, insert, update, delete
etc.

executes the select query. It returns an

public ResultSet executeQuery()
instance of ResultSet.

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("oracle.jdbc.driver.OracleDriver");
6.
7. Connection con=DriverManager.getConnection("jdbc:oracle:thin:
@localhost:1521:xe","system","oracle");
8.
9. PreparedStatement stmt=con.prepareStatement("insert into Emp
values(?,?)");
10. stmt.setInt(1,101);//1 specifies the first parameter in the qu
ery
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. }

download this example

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 q
uery i.e. name
3. stmt.setInt(2,101);
 4.
5. int i=stmt.executeUpdate();
6. System.out.println(i+" records updated");

download this example

Example of PreparedStatement interface that deletes the record

1. PreparedStatement stmt=con.prepareStatement("delete from em

p where id=?");
2. stmt.setInt(1,101);
3.
4. int i=stmt.executeUpdate();
5. System.out.println(i+" records deleted");

download this example

Example of PreparedStatement interface that retrieve the records of

a table

1. PreparedStatement stmt=con.prepareStatement("select * from e

mp");
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 emp13
0 values(?,?,?)");
9.
10. BufferedReader br=new BufferedReader(new InputStreamR
eader(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. }}

ResultSetMetaData Interface

The metadata means data about data i.e. we can get further information
from the data.

If you have to get metadata of a table like total number of column,

column name, column type etc. , ResultSetMetaData interface is useful
because it provides methods to get metadata from the ResultSet object.

Commonly used methods of ResultSetMetaData interface

Method Description

it returns the total number of

public int getColumnCount()throws
columns in the ResultSet
SQLException
object.

public String getColumnName(int it returns the column name of

index)throws SQLException the specified column index.

public String getColumnTypeName(int it returns the column type

index)throws SQLException name for the specified index.

public String getTableName(int it returns the table name for

index)throws SQLException the specified column index.

How to get the object of ResultSetMetaData:

The getMetaData() method of ResultSet interface returns the object of

ResultSetMetaData. Syntax:

1. public ResultSetMetaData getMetaData()throws SQLException

Example of ResultSetMetaData interface :

1. import java.sql.*;
2. class Rsmd{
3. public static void main(String args[]){
4. try{
5. Class.forName("oracle.jdbc.driver.OracleDriver");
6.
7. Connection con=DriverManager.getConnection(
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
9.
10. PreparedStatement ps=con.prepareStatement("select * fro
m emp");
11. ResultSet rs=ps.executeQuery();
12.
13. ResultSetMetaData rsmd=rs.getMetaData();
14.
15. System.out.println("Total columns: "+rsmd.getColumnCount
());
16. System.out.println("Column Name of 1st column: "+rsmd.ge
tColumnName(1));
17. System.out.println("Column Type Name of 1st column: "+rs
md.getColumnTypeName(1));
18.
19. con.close();
20.
21. }catch(Exception e){ System.out.println(e);}
 22.
23. }
24. }
Output:Total columns: 2
Column Name of 1st column: ID
Column Type Name of 1st column: NUMBER

DatabaseMetaData interface:

DatabaseMetaData interface provides methods to get meta data of a

database such as database product name, database product version,
driver name, name of total number of tables, name of total number of
views etc.

Commonly used methods of DatabaseMetaData interface

 public String getDriverName()throws SQLException: it returns the
name of the JDBC driver.
 public String getDriverVersion()throws SQLException: it returns
the version number of the JDBC driver.
 public String getUserName()throws SQLException: it returns the
username of the database.
 public String getDatabaseProductName()throws SQLException: it
returns the product name of the database.
 public String getDatabaseProductVersion()throws SQLException:
it returns the product version of the database.
 public ResultSet getTables(String catalog, String schemaPattern,
String tableNamePattern, String[] types)throws SQLException: it
returns the description of the tables of the specified catalog. The
table type can be TABLE, VIEW, ALIAS, SYSTEM TABLE, SYNONYM
etc.
How to get the object of DatabaseMetaData:

The getMetaData() method of Connection interface returns the object of

DatabaseMetaData. Syntax:

1. public DatabaseMetaData getMetaData()throws SQLException

Simple Example of DatabaseMetaData interface :

1. import java.sql.*;
2. class Dbmd{
3. public static void main(String args[]){
4. try{
5. Class.forName("oracle.jdbc.driver.OracleDriver");
6.
7. Connection con=DriverManager.getConnection(
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
9.
10. DatabaseMetaData dbmd=con.getMetaData();
11.
12. System.out.println("Driver Name: "+dbmd.getDriverName())
;
13. System.out.println("Driver Version: "+dbmd.getDriverVersio
n());
14. System.out.println("UserName: "+dbmd.getUserName());
15. System.out.println("Database Product Name: "+dbmd.getDa
tabaseProductName());
16. System.out.println("Database Product Version: "+dbmd.getD
atabaseProductVersion());
17.
18. con.close();
19.
20. }catch(Exception e){ System.out.println(e);}
 21.
22. }
23. }
Output:Driver Name: Oracle JDBC Driver
Driver Version: 10.2.0.1.0XE
Database Product Name: Oracle
Database Product Version: Oracle Database
10g Express Edition
Release
10.2.0.1.0 -Production
download this example

Example of DatabaseMetaData interface that prints total number of

tables :

1. import java.sql.*;
2. class Dbmd2{
3. public static void main(String args[]){
4. try{
5. Class.forName("oracle.jdbc.driver.OracleDriver");
6.
7. Connection con=DriverManager.getConnection(
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
9.
10. DatabaseMetaData dbmd=con.getMetaData();
11. String table[]={"TABLE"};
12. ResultSet rs=dbmd.getTables(null,null,null,table);
13.
14. while(rs.next()){
15. System.out.println(rs.getString(3));
16. }
17.
 18. con.close();
19.
20. }catch(Exception e){ System.out.println(e);}
21.
22. }
23. }

download this example

Example of DatabaseMetaData interface that prints total number of

views :

1. import java.sql.*;
2. class Dbmd3{
3. public static void main(String args[]){
4. try{
5. Class.forName("oracle.jdbc.driver.OracleDriver");
6.
7. Connection con=DriverManager.getConnection(
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
9.
10. DatabaseMetaData dbmd=con.getMetaData();
11. String table[]={"VIEW"};
12. ResultSet rs=dbmd.getTables(null,null,null,table);
13.
14. while(rs.next()){
15. System.out.println(rs.getString(3));
16. }
17.
18. con.close();
19.
20. }catch(Exception e){ System.out.println(e);}
21.
 22. }
23. }

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 strea

m)
2. throws SQLException
3. 2) public void setBinaryStream(int paramIndex,InputStream strea
m,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.
 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 img
table 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. }

If you see the table, record is stored in the database but image will not be
shown. To do so, you need to retrieve the image from the database
which we are covering in the next page.

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 i
mgtable");
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 imag
e
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. }

Now if you see the d drive, sonoo.jpg image is created.

Example to store file in Oracle database:

The setCharacterStream() method of PreparedStatement is used to set

character information into the parameterIndex.

Syntax:

1) public void setBinaryStream(int paramIndex,InputStream

stream)throws SQLException
2) public void setBinaryStream(int paramIndex,InputStream stream,long
length)throws SQLException
For storing file into the database, CLOB (Character Large Object)
datatype is used in the table. For example:

1. CREATE TABLE "FILETABLE"

2. ( "ID" NUMBER,
3. "NAME" CLOB
4. )
5. /

1. import java.io.*;
2. import java.sql.*;
3.
4. public class StoreFile {
5. public static void main(String[] args) {
6. try{
7. Class.forName("oracle.jdbc.driver.OracleDriver");
8. Connection con=DriverManager.getConnection(
9. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
10.
11. PreparedStatement ps=con.prepareStatement(
12. "insert into filetable values(?,?)");
13.
14. File f=new File("d:\\myfile.txt");
15. FileReader fr=new FileReader(f);
16.
17. ps.setInt(1,101);
18. ps.setCharacterStream(2,fr,(int)f.length());
19. int i=ps.executeUpdate();
20. System.out.println(i+" records affected");
21.
22. con.close();
23.
24. }catch (Exception e) {e.printStackTrace();}
25. }
26. }
Example to retrieve file from Oracle database:

The getClob() method of PreparedStatement is used to get file

information from the database.

Syntax of getClob method

1. public Clob getClob(int columnIndex){}

Let's see the table structure of this example to retrieve the file.

1. CREATE TABLE "FILETABLE"

2. ( "ID" NUMBER,
3. "NAME" CLOB
4. )
5. /

The example to retrieve the file from the Oracle database is given below.

1. import java.io.*;
2. import java.sql.*;
3.
4. public class RetrieveFile {
5. public static void main(String[] args) {
6. try{
7. Class.forName("oracle.jdbc.driver.OracleDriver");
8. Connection con=DriverManager.getConnection(
9. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
10.
11. PreparedStatement ps=con.prepareStatement("select * from f
iletable");
12. ResultSet rs=ps.executeQuery();
13. rs.next();//now on 1st row
14.
 15. Clob c=rs.getClob(2);
16. Reader r=c.getCharacterStream();
17.
18. FileWriter fw=new FileWriter("d:\\retrivefile.txt");
19.
20. int i;
21. while((i=r.read())!=-1)
22. fw.write((char)i);
23.
24. fw.close();
25. con.close();
26.
27. System.out.println("success");
28. }catch (Exception e) {e.printStackTrace(); }
29. }
30. }

CallableStatement Interface

To call the stored procedures and functions, CallableStatement

interface is used.

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
procedure. function.
Procedure supports input and output Function supports only input
parameters. parameter.
Exception handling using try/catch Exception handling using try/catch
block can be used in stored can't be used in user defined
procedures. functions.

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

Transaction Management in JDBC

Transaction represents a single unit of work.

The ACID properties describes the transaction management well. ACID

stands for Atomicity, Consistency, isolation and durability.

Atomicity means either all successful or none.

Consistency ensures bringing the database from one consistent state to

another consistent state.

Isolation ensures that transaction is isolated from other transaction.

Durability means once a transaction has been committed, it will remain

so, even in the event of errors, power loss etc.
Advantage of Transaction Mangaement

fast performance It makes the performance fast because database is hit

at the time of commit.

In JDBC, Connection interface provides methods to manage

transaction.

Method Description

void setAutoCommit(boolean It is true bydefault means each

status) transaction is committed bydefault.

void commit() commits the transaction.

void rollback() cancels the transaction.

Simple example of transaction management in jdbc using Statement

Let's see the simple example of transaction management using
Statement.

1. import java.sql.*;
2. class FetchRecords{
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. con.setAutoCommit(false);
7.
8. Statement stmt=con.createStatement();
9. stmt.executeUpdate("insert into user420 values(190,'abhi',40000)
");
10. stmt.executeUpdate("insert into user420 values(191,'umesh
',50000)");
11.
12. con.commit();
13. con.close();
14. }}

If you see the table emp400, you will see that 2 records has been added.

Example of transaction management in jdbc using

PreparedStatement

Let's see the simple example of transaction management using

PreparedStatement.

1. import java.sql.*;
2. import java.io.*;
3. class TM{
4. public static void main(String args[]){
5. try{
6.
7. Class.forName("oracle.jdbc.driver.OracleDriver");
8. Connection con=DriverManager.getConnection("jdbc:oracle:thin:
@localhost:1521:xe","system","oracle");
9. con.setAutoCommit(false);
10.
11. PreparedStatement ps=con.prepareStatement("insert into u
ser420 values(?,?,?)");
12.
13. BufferedReader br=new BufferedReader(new InputStreamR
eader(System.in));
14. while(true){
15.
16. System.out.println("enter id");
17. String s1=br.readLine();
18. int id=Integer.parseInt(s1);
19.
20. System.out.println("enter name");
21. String name=br.readLine();
22.
23. System.out.println("enter salary");
24. String s3=br.readLine();
25. int salary=Integer.parseInt(s3);
26.
27. ps.setInt(1,id);
28. ps.setString(2,name);
29. ps.setInt(3,salary);
30. ps.executeUpdate();
31.
32. System.out.println("commit/rollback");
33. String answer=br.readLine();
34. if(answer.equals("commit")){
35. con.commit();
 36. }
37. if(answer.equals("rollback")){
38. con.rollback();
39. }
40.
41.
42. System.out.println("Want to add more records y/n");
43. String ans=br.readLine();
44. if(ans.equals("n")){
45. break;
46. }
47.
48. }
49. con.commit();
50. System.out.println("record successfully saved");
51.
52. con.close();//before closing connection commit() is called
53. }catch(Exception e){System.out.println(e);}
54.
55. }}

It will ask to add more records until you press n. If you press n,
transaction is committed.

Batch Processing in JDBC

Instead of executing a single query, we can execute a batch (group) of

queries. It makes the performance fast.

The java.sql.Statement and java.sql.PreparedStatement interfaces

provide methods for batch processing.
Advantage of Batch Processing

Fast Performance

Methods of Statement interface

The required methods for batch processing are given below:

Method Description

void addBatch(String query) It adds query into batch.

int[] executeBatch() It executes the batch of queries.

Example of batch processing in jdbc

Let's see the simple example of batch processing in jdbc. It follows

following steps:

 Load the driver class

 Create Connection
 Create Statement
 Add query in the batch
 Execute Batch
 Close Connection

1. import java.sql.*;
2. class FetchRecords{
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. con.setAutoCommit(false);
 7.
8. Statement stmt=con.createStatement();
9. stmt.addBatch("insert into user420 values(190,'abhi',40000)");
10. stmt.addBatch("insert into user420 values(191,'umesh',5000
0)");
11.
12. stmt.executeBatch();//executing the batch
13.
14. con.commit();
15. con.close();
16. }}

If you see the table user420, two records has been added.

Example of batch processing using PreparedStatement

1. import java.sql.*;
2. import java.io.*;
3. class BP{
4. public static void main(String args[]){
5. try{
6.
7. Class.forName("oracle.jdbc.driver.OracleDriver");
8. Connection con=DriverManager.getConnection("jdbc:oracle:thin:
@localhost:1521:xe","system","oracle");
9.
10. PreparedStatement ps=con.prepareStatement("insert into u
ser420 values(?,?,?)");
11.
12. BufferedReader br=new BufferedReader(new InputStreamR
eader(System.in));
13. while(true){
14.
15. System.out.println("enter id");
 16. String s1=br.readLine();
17. int id=Integer.parseInt(s1);
18.
19. System.out.println("enter name");
20. String name=br.readLine();
21.
22. System.out.println("enter salary");
23. String s3=br.readLine();
24. int salary=Integer.parseInt(s3);
25.
26. ps.setInt(1,id);
27. ps.setString(2,name);
28. ps.setInt(3,salary);
29.
30. ps.addBatch();
31. System.out.println("Want to add more records y/n");
32. String ans=br.readLine();
33. if(ans.equals("n")){
34. break;
35. }
36.
37. }
38. ps.executeBatch();
39.
40. System.out.println("record successfully saved");
41.
42. con.close();
43. }catch(Exception e){System.out.println(e);}
44.
45. }}

It will add the queries into the batch until user press n. Finally it
executes the batch. Thus all the added queries will be fired.
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:

 JdbcRowSet
 CachedRowSet
 WebRowSet
 JoinRowSet
 FilteredRowSet

Let's see how to create and execute RowSet.

1. JdbcRowSet rowSet = RowSetProvider.newFactory().createJdbcRo

wSet();
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().cre
ateJdbcRowSet();
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().creat
eJdbcRowSet();
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...