What is RowSet Interface in JDBC

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 given below:

JdbcRowSet
CachedRowSet
WebRowSet
JoinRowSet
FilteredRowSet

 See how to create and execute RowSet 

 JdbcRowSet rowSet = RowSetProvider.newFactory().createJdbcRowSet();
 rowSet.setUrl("jdbc:oracle:thin:@localhost:1521:xe");
 rowSet.setUsername("system");
 rowSet.setPassword("oracle");
 rowSet.setCommand("select * from student");
 rowSet.execute();

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

 Advantage of RowSet:

>1. It is easy and flexible to use.
>2. It is Scrollable and Updatable by default.

Example of JdbcRowSet

import java.sql.*;
import javax.sql.rowset.JdbcRowSet;
import javax.sql.rowset.RowSetProvider;
 public class RowSetExample {
 public static void main(String[]args) throws Exception {
 Class.forName("oracle.jdbc.driver.OracleDriver");
 //Creating and Executing RowSet
 JdbcRowSet rowSet = RowSetProvider.newFactory().createJdbcRowSet();
 rowSet.setUrl("jdbc:oracle:thin:@localhost:1521:xe");
rowSet.setUsername("system");
 rowSet.setPassword("oracle");
 rowSet.setCommand("select * from student");
 rowSet.execute();
 while (rowSet.next()) {

 System.out.println("Id: " + rowSet.getString(1));
 System.out.println("Name: " + rowSet.getString(2));
 System.out.println("Salary: " + rowSet.getString(3));
  System.out.println("Address: " + rowSet.getString(3));
   }
    }
 }

What is DatabaseMetaData in JDBC

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.

 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,SYSTEM TABLE, SYNONYM etc.

How to get the object of DatabaseMetaData ?

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

Syntax:

public DatabaseMetaData getMetaData()throws SQLException

Simple Example of DatabaseMetaData interface :

 import java.sql.*;

 class DatabaseMd {
 public static void main(String []args){
 try{
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Connection con=DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
 DatabaseMetaData dbmd=con.getMetaData();
 System.out.println("Driver Name: "+dbmd.getDriverName());
 System.out.println("Driver Version: "+dbmd.getDriverVersion());
 System.out.println("UserName: "+dbmd.getUserName());
 System.out.println("Database Product Name: "+dbmd.getDatabaseProductName());
 System.out.println("Database Product Version: "+dbmd.getDatabaseProductVersion());
 con.close();
 }catch(Exception e){
 System.out.println(e);
  }
 }

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

What is CallableStatement Interface in JDBC

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.

How to get the instance of CallableStatement ?

The prepareCall() method of Connection interface returns the instance of CallableStatement.

Syntax:

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

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

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

It calls the procedure INSERT_VALUE that receives 3 arguments.

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.

create or replace procedure "INSERT_VALUE"
 (id IN NUMBER, name IN VARCHAR2,college IN VARCHAR2)
 is
 begin
 insert into student values(id,name,college);
 end;
 /
 The table structure is given below:

create table student( id number(10), name varchar2(300), college varchar2(300) );

In this example, we are going to call the stored procedure INSERT_VALUE that receives id, name and college as the parameter and inserts it  into the table student.

Note that you need to create the student table as well to run this application.

 import java.sql.*;

 public class CallProcedure {
 public static void main(String[] args) throws Exception{
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
 CallableStatement stmt=con.prepareCall( " {call INSERT_VALUE(?,?,?)} " );
 stmt.setInt(1, 111);
 stmt.setString(2, "Vishwa");
 stmt.setString(3, "KEC");
 stmt.execute();
 System.out.println("successfully called");
   }
 }

 Output: successfully called

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

 Example to call the function using JDBC:

In this example, we are calling the sum3 function that receives three 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. You have to create a function in the database first.

create or replace function sum3
(x1 in number,x2 in number.x3 in number)
return number
is
temp number(10); // it is a temp variable to store final sum result
begin
temp :=x1+x2+x3;
return temp;
end;
 /

Simple program to call the function.

 import java.sql.*;

 public class FuncSum {
 public static void main(String []args) throws Exception{
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
 CallableStatement stmt=con.prepareCall("{?= call sum3(?,?,?)}");
 stmt.registerOutParameter(1,Types.INTEGER);
 stmt.setInt(2,11);
 stmt.setInt(3,22);
 stmt.setInt(4,20);
 stmt.execute();
 stmt.execute();
 System.out.println("sum of the three numbers="+stmt.getInt(1));
  }
 }

 Output: sum of the three numbers =53

What is Transaction Management in JDBC

Transaction Management in JDBC:

Transaction represents a single unit of work.
Transaction follows the ACID property.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 transactions.
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 the transaction which are given below.

void setAutoCommit(boolean status): It is true bydefault means each transaction is committed bydefault.
void commit(): commits the transaction.
void rollback(): cancels the transaction

Example of transaction management in jdbc using Statement interface.

 import java.sql.*;

 class FetchRecords{
 public static void main(String []ars)throws Exception{
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Connection  con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
 con.setAutoCommit(false);
 Statement stmt=con.createStatement();
 stmt.executeUpdate("insert into student values(111,'Vishwa','KEC')");
 stmt.executeUpdate("insert into student values(222,'Alex','HKU')");
 stmt.executeUpdate("insert into student values(333,'Karan','PTU')");
 System.out.prinlnt("data inserted successfully");
 con.commit();
 con.close();
 }
 }

 Output: data inserted successfully

Note: By default setAutoCommit() is true.

 Example of transaction management in jdbc using PreparedStatement interface:

 import java.sql.*;
 import java.io.*;

 class TransactionMan {
 public static void main(String []args){
 try{
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Connection  con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
 con.setAutoCommit(false);
 PreparedStatement ps=con.prepareStatement("insert into student values(?, ?, ?, ?)");
 BufferedReader br=new BufferedReader(new InputStreamReader(System.in));

 while(true){
 System.out.println("enter id");
 String s1=br.readLine();
 int id=Integer.parseInt(s1);
 System.out.println("enter name");
 String name=br.readLine();
 System.out.println("enter salary");
 String s3=br.readLine();
 int salary=Integer.parseInt(s3);
 System.out.println("enter address");
 String s4=br.readLine();

 ps.setInt(1,id);
 ps.setString(2,name);
 ps.setInt(3,salary);
 ps.setInt(4,address);
 ps.executeUpdate();
 System.out.println("enter commit/rollback");
 String answer=br.readLine();
 if(answer.equals("commit")){
 con.commit();
 }
 if(answer.equals("rollback")){
 con.rollback();
 }
 System.out.println("Want to add more records press y/n");
 String ans=br.readLine();
 if(ans.equals("n")){
 break;
   }
 }  // end of if block
 con.commit();
 System.out.println("records successfully added");
 con.close();    //before closing connection commit() is called
 }catch(Exception ex) {
 System.out.println(ex); }
 }
}

It will ask to add more records until you press n.If you press n,transaction will be commited.

Output: records successfully added

What is Batch Processing in JDBC

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.

Methods of Statement interface:

The required methods for batch processing are given below:

void addBatch(String query): It adds query into batch.
int[] executeBatch(): It executes the batch of queries.

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

Example of batch processing using PreparedStatement:

 import java.sql.*;
 class GetRecords{
 public static void main(String []args)throws Exception{

 Class.forName("oracle.jdbc.driver.OracleDriver");
 Connection  con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
 con.setAutoCommit(false);
 Statement stmt=con.createStatement();
 stmt.addBatch("insert into student values(123,'Vishwa','KEC')");
 stmt.addBatch("insert into student values(124,'Alex','HKU')");
 stmt.addBatch("insert into student values(125,'Karan','PTU')");
 stmt.executeBatch();  //executing the batch
 con.commit();
 System.out.println("batch execute successfully");
 con.close();
  }
 }

 Outut: batch execute successfully

 Example of batch processing using PreparedStatement:

 import java.sql.*;
 import java.io.*;

 class TransactionMan {
 public static void main(String []args) {
 try{
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Connection  con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
 con.setAutoCommit(false);
 PreparedStatement ps=con.prepareStatement("insert into student values(?,?,?,?)");
 BufferedReader br=new BufferedReader(new InputStreamReader(System.in));

 while(true){
 System.out.println("enter id");
 String s1=br.readLine();
 int id=Integer.parseInt(s1);

 System.out.println("enter name");
 String name=br.readLine();

 System.out.println("enter salary");
 String s3=br.readLine();
 int salary=Integer.parseInt(s3);

 System.out.println("enter address");
 String s4=br.readLine();

 ps.setInt(1,id);
 ps.setString(2,name);
 ps.setInt(3,salary);
 ps.setInt(4,address);

 ps.addBatch();
 System.out.println("Want to add more records press y/n");
 String ans=br.readLine();
 if(ans.equals("n")){
 break;
  }
 }  // end of if block
 con.commit();
 System.out.println("records successfully added");
 con.close();  //before closing connection commit() is called
 }catch(Exception ex) {
 System.out.println(ex); }
 }
}

Output: records successfully added

What is ResultSet Interface in JDBC

ResultSetMetaData:

The metadata means data about data i.e. we can get further information from the data. If you have to get information of a table like total number of column, column name, column type etc. ResultSetMetaData interface is useful because it provides methods to get these information from the ResultSet object.

Methods of ResultSetMetaData interface

public int getColumnCount()throws SQLException: it returns the total number of columns in the ResultSet object.
public String getColumnName(int index)throws SQLException: it returns the column name of the specified column index.
public String getColumnTypeName(int index)throws SQLException: it returns the column type name for the specified index.
public String getTableName(int index)throws SQLException: it returns the table name for the specified column index.

How to get the object of ResultSetMetaData:

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

Syntax

public ResultSetMetaData getMetaData()throws SQLException

Simple Example of ResultSetMetaData interface :

 import java.sql.*;

 class ResultSetMd  {
 public static void main(String []args)  {
 try{
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
 PreparedStatement ps=con.prepareStatement("select * from employee");
 ResultSet rs=ps.executeQuery();
 ResultSetMetaData rsmd=rs.getMetaData();
 System.out.println("Total Number of columns: "+rsmd.getColumnCount());
 System.out.println("Column Name of 1st column: "+rsmd.getColumnName(1));
 System.out.println("Column Type Name of 1st column: "+rsmd.getColumnTypeName(1));
 con.close();
 }catch(Exception ex){
 System.out.println(ex);
 }
  }
 }

Output: Total Number of  columns: 3
Column Name of 1st column: ID
Column Type Name of 1st column: NUMBER

To see    DatabaseMetaData Interface  click on link

How to Retrieve image in Oracle Database in Java

Using 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.

Syntax of getBlob() method of PreparedStatement

public Blob getBlob() throws SQLException

Syntax of getBytes() method of Blob interface

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

We are assuming that img is stored in the image table.

create table image( name varchar2(200), img blob);

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.

save as   RetrieveImage .java

import java.sql.*;
import java.io.*;
public class RetrieveImage {
public static void main(String[]args) {
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
PreparedStatement ps=con.prepareStatement("select * from image");
ResultSet rs=ps.executeQuery();
if(rs.next()){   //now on 1st row
Blob b=rs.getBlob(2);  //2 means 2nd column data
byte barr[]=b.getBytes(1,(int)b.length());  //1 means first image
FileOutputStream fout=new FileOutputStream("d:\\rerose.jpg");
fout.write(barr);
fout.close();
}  
System.out.println("successfully image retrieved");
con.close();
}catch (Exception e) {
e.printStackTrace(); }
 }
}

Output: successfully image retrieved

How to Store image in Oracle Database in Java

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.

Syntax of setBinaryStream() method is given below:

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

For storing image into the oracle database, BLOB (Binary Large Object) datatype is used while creating the table.

For example:

create table image ( name varchar2(300),  img blob)

Below  is the  code to store the image in the database.Here we are using d:\\rose.jpg for the location of image. You can change it according to the image location.

save as  InsertImage.java

 import java.sql.*;
 import java.io.*;
 public class InsertImage {
 public static void main(String []args) {
 try{
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
 PreparedStatement ps=con.prepareStatement("insert into image values(?,?)");
 ps.setString(12,"flower");
 FileInputStream fin=new FileInputStream("d:\\rose.jpg");
 ps.setBinaryStream(2, fin, fin.available());
 int i=ps.executeUpdate();
 System.out.println(i+" record affected");
 con.close();
 }catch (Exception e) {
e.printStackTrace(); }
   }
 }

 Output: 1 record affected


If you see the table, 1 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 post.

Next Post:  How to Retrieving image from Oracle Database
  

What is PreparedStatement interface in JDBC

PreparedStatement interface:

The PreparedStatement interface is a subinterface of Statement. It is used to exeucte parameterized query.

How  PreparedStatement is faster and better than Statement interface ?

The performance of the application will be faster if you use PreparedStatement interface because query is compiled only once. The prepareStatement() method of Connection interface is used to return the object of PreparedStatement.

Syntax:
public PreparedStatement prepareStatement(String query) throws SQLException{  }

Example of PreparedStatement interface that inserts the record:

First create a table in database.

create table employee(id number(8),name varchar2(60),address varchar2(80) );

save as  Insert.java

import java.sql.*;
 class Insert{
 public static void main(String args[]){
 try{
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
 PreparedStatement stmt=con.prepareStatement("insert into employee values(?,?,?)");
 stmt.setInt(1,111);  //here 1 specifies the first parameter in the query.
 stmt.setInt(2,"Vishwa");
 stmt.setInt(3,"New Delhi");
 int i=stmt.executeUpdate();
 System.out.println(i+" record inserted");
 con.close();
 }catch(Exception e){ System.out.println(e);}
  }
 }

 Ouput: 1 record inserted

 Example of PreparedStatement interface that updates the record:

 save as Update.java

 import java.sql.*;
 class Update{
 public static void main(String args[]){
 try{
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Connection con=DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
 PreparedStatement stmt=con.prepareStatement(" update employee set name=? where id=? ");
 stmt.setString(1,"Alex");  //1 specifies the first parameter in the query i.e. name
 stmt.setInt(2,111);
 int i=stmt.executeUpdate();
 System.out.println(i+" record updated");
 con.close();
 }catch(Exception e){ System.out.println(e);}
  }
 }

 Ouput: 1 record updated

 Example of PreparedStatement interface that retrieve the records of a table:

 save as Retrieve.java

 import java.sql.*;
 class Retrieve{
 public static void main(String args[]){
 try{
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
 PreparedStatement stmt=con.prepareStatement("select * from employee");
 ResultSet rs=stmt.executeQuery();
 while(rs.next()){
 System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+r.getString(3);
 }
 con.close();
 } catch(Exception e){ System.out.println(e);}
   }
 }

 Ouput: 111 Alex New Delhi

 Example of PreparedStatement interface that deletes the record:

 save as Delete.java

 import java.sql.*;
 class Delete{
 public static void main(String args[]){
 try{
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
 PreparedStatement stmt=con.prepareStatement(" delete from employee where id=? ");
 stmt.setInt(1,111);
 int i=stmt.executeUpdate();
 System.out.println(i+" record deleted");
 con.close();
 }catch(Exception e){ System.out.println(e);}
  }
 }

 Ouput: 1 record deleted

Next>>  CallableStatement Interface  

What is Statement interface and ResultSet interface in JDBC

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 methods to get the object of ResultSet.

 Methods of Statement interface:

>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(multiple) at once commands.

ResultSet interface:

The object of ResultSet maintains a cursor pointing to a particular row of data. Initially, cursor points to before the first row.

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

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

 Methods of ResultSet interface:

>1. public boolean next(): is used to move the cursor to the one row next from the current position.
>2. public boolean previous(): is used to move the cursor to the one row previous from the current position.
>3. public boolean first(): is used to move the cursor to the first row in result set object.
>4. public boolean last(): is used to move the cursor to the last row in result set object.
>5. public boolean absolute(int row): is used to move the cursor to the specified row number in the ResultSet object.
>6. public boolean relative(int row): is used to move the cursor to the relative row number in the ResultSet object, it may be positive or negative.
>7. public int getInt(int columnIndex): is used to return the data of specified column index of the current row as int.
>8. public int getInt(String columnName): is used to return the data of specified column name of the current row as int.
>9. public String getString(int columnIndex): is used to return the data of specified column index of the current row as String.
>10 public String getString(String columnName): is used to return the data of specified column name of the current row as String.

What is DriverManager class and Connection interface in JDBC

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

Methods of DriverManager class:

>1. public static void registerDriver(Driver driver): It is used to register the given driver with DriverManager.
>2. public static void deregisterDriver(Driver driver): It is used to deregister the given driver (drop the driver from the list) with DriverManager.
>3. public static Connection getConnection(String url): It is used to establish the connection with the specified url.
>4. public static Connection getConnection(String url,String userName,String password): It is used to establish the connection with the specified url, 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.

Note: By default, connection commits the changes after executing the queries.

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.

JDBC Example to connect with 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.

>1. Create a table with name employee in oracle database and insert some value to show our                    connection.

 create table employee(eid number(10), ename varchar2(40), salary number(20));

 >2. Insert some values.
 insert into employee (eid, ename, salary) values (111, 'Vishwa', 25000);
 insert into employee (eid, ename, salary) values (22, 'Alex', 30000);
 insert into employee (eid, ename, salary) values (333, 'Karan', 40000);

Example to Connect Java Application with Oracle Database.

In this example, system is the username and oracle is the password of the Oracle database.

import java.sql.*;
class OracleConnection
{
public static void main(String [] args) {

try{
 // loading  the driver class
 Class.forName("oracle.jdbc.driver.OracleDriver");

 // creating the connection object
 Connection con=DriverManager.getConnection(
 "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");

 // creating the statement object
 Statement stmt=con.createStatement();

 // executing  query
 ResultSet rs=stmt.executeQuery("select * from employee");
 while( rs.next())
{
 System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
}

 // closing  the connection object
 con.close();
 }
 catch(Exception e)  {   System.out.println(e);  }
  }
 }

 Output: 
 111 Vishwa 25000
 222 Alex 30000
 333 Karan 40000

Steps to connect with database using JDBC in java

There are 5 steps to connect any java application with the database in java using JDBC API. They are given below:

>Register the driver class
>Create  connection
>Create statement
>Execute queries
>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 dynamically load the driver class to connect with database.

Syntax of forName() method:

public static void forName(String className) throws ClassNotFoundException

For Example to register the OracleDriver class:

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

Note: Here oracle.jdbc.driver  is a package location where OracleDriver class is exits.You can see it in the ojdbc14.jar file.

>2.Create the connection object

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

Syntax of getConnection() method

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

For Example to establish connection with the Oracle database:

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

Here you want to know what is url ( "jdbc:oracle:thin:@localhost:1521:xe","username","password")
This is known as connection url in which...

jdbc: It is a jdbc api.
oracle: It is a database name to which you are going to connect.It may be change for different             database.
thin: A type of driver.
localhost: It is server name or system address on which oracle database is running.You can also use   IP address of that system or server.
1521: It  is port number.
xe: An Oracle service name.
username: It is an username of the Oracle Database.By default it is system
password: Password is given by the user at the time of installing the oracle 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:

public Statement createStatement() throws SQLException

For Example to create the statement object:

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:

public ResultSet executeQuery(String sql) throws SQLException

For Example to execute query:

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

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

public void close()throws SQLException

For Example 

con.close();

What is JDBC drivers ?

Type of JDBC Driver:

JDBC Driver is a software component that make java applications 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. Now this is not used because of thin driver not full secure.

Advantages:

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

Disadvantages:

> Performance degraded because JDBC method call is converted into the ODBC funcion calls that          takes time
> The ODBC driver needs to be installed on the client machine also.

>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 better than JDBC-ODBC bridge driver.

Disadvantage:

> Native driver needs to be installed on the each client machine also.
> The Vendor client library needs to be installed on client machine also.

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

This is mostly used 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

Next >>   Steps to connect with Database 

What is JDBC Connection in Java ?

JDBC  means Java DataBase Connectivity. 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.

What before JDBC used ?

Before JDBC, ODBC API(Open DataBase Connectivity) 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.

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

To know more about types of  JDBC drivers  click on link.