Monday, March 30, 2009

Java : Java Database Connectivity (JDBC), Explained All four types with simple example

There are four types of JDBC drivers explained with simple examples using oracle database:

Type 1 : JDBC-ODBC bridge plus ODBC driver.

This driver translates JDBC method calls into ODBC function calls.
The Bridge implements Jdbc for any database for which an Odbc driver is available.

/********Code for Type1.java**********/

import java.sql.*;
public class Type1
{
public static void main(String[] args) throws Exception
{
Connection con;
Statement stat;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//Step 1: Loading Drivers

con=DriverManager.getConnection("jdbc:odbc:madan","scott","tiger");//Step 2: Making Connection

stat=con.createStatement();//Step 3: Creating JDBC Statement

String query = "SELECT * FROM Employee";

ResultSet rset=stat.executeQuery(query);//Step 4: Execute the Ststement

while(rset.next())//Step 5: Looping through the ResultSet
{
System.out.println(rset.getInt(1)+" "+rset.getString(2));
}
stat.close();//step 6: Close the Connection and Statement
con.close();
}
}

Type 2 : Native-API, partly Java driver.

Type 2 drivers use the Java Native Interface (JNI) to make calls to a local
database library API. This driver converts the JDBC calls into a database
specific call for databases such as SQL, ORACLE etc.

Note : to set path

set Classpath=%Classpath%;.;D:\oracle\ora92\jdbc\lib\Classes12.jar

/********Code for Type2.java**********/

import java.sql.*;
public class Type2
{
public static void main(String[] args) throws Exception
{
Connection con;
Statement stat;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//Step 1: Loading Drivers

con=DriverManager.getConnection("jdbc:oracle:oci8:@madan","scott","tiger");//Step 2: Making Connection

stat=con.createStatement();//Step 3: Creating JDBC Statement

String query = "SELECT * FROM Employee";

ResultSet rset=stat.executeQuery(query);//Step 4: Execute the Ststement

while(rset.next())//Step 5: Looping through the ResultSet
{
System.out.println(rset.getInt(1)+" "+rset.getString(2));
}
stat.close();//step 6: Close the Connection and Statement
con.close();
}
}

Type 3 : JDBC-Net, pure Java driver.

Type 3 drivers are pure Java drivers that uses a proprietary network protocol to
communicate with JDBC middleware on the server.Its requests are passed through the
network to the middle-tier server. The middle-tier then translates the request to the
database. The middle-tier server can in turn use Type1, Type 2 or Type 4 drivers.

Type 4 : Native-protocol, pure Java driver.


Type 4 drivers communicates directly with the database engine rather than through
middleware or a native library, they are usually the fastest JDBC drivers available.
This driver directly converts the java statements to SQL statements.

Note : to set path

set Classpath=%Classpath%;.;D:\oracle\ora92\jdbc\lib\Classes111.jar

/********Code for Type4.java**********/


import java.sql.*;
public class Type4
{
public static void main(String[] args) throws Exception
{
Connection con;
Statement stat;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//Step 1: Loading Drivers

con=DriverManager.getConnection("jdbc:oracle:thin:@madan:1521:oracle9","scott","tiger");//Step 2: Making Connection

stat=con.createStatement();//Step 3: Creating JDBC Statement

String query = "SELECT * FROM Employee";

ResultSet rset=stat.executeQuery(query);//Step 4: Execute the Ststement

while(rset.next())//Step 5: Looping through the ResultSet
{
System.out.println(rset.getInt(1)+" "+rset.getString(2));
}
stat.close();//step 6: Close the Connection and Statement
con.close();
}
}