Display data from database using type4 driver using JDBC

Introduction

 

JDBC (Java Database Connectivity): This is a technology to establish communication between a java program and a DBMS. It uses SQL (structured query language) for storing, updating, or removing data from a DBMS. The java program can be a stand-alone application, web application or enterprise application. The DBMS can be of any type, such as Oracle, SQLserver or MS Access

 

There are four drivers:

 

Type-1 Driver - The JDBC: ODBC bridge driver

 

Type 2 Drivers - The Native-API Driver

 

Type 3 Drivers - The Network-Protocol Driver

 

Type 4 Drivers - The Native-Protocol Driver

 

Here we will discuss the type-4 driver.

 

This driver is also known as the native-protocol driver or the thin driver of Oracle. In this driver, the client and server API'S are written in java and the server API uses the protocol of the DBMS for communication. Hence this driver is a full java driver and DBMS independent. It is also called a pure java driver.

 

Connection to oracle database using a type-4 driver

 

Driver class name:oracle.jdbc.driver.OracleDriver

 

Path of driver: jdbc:oracle:thin:@localhost:1521:SID-of-Oracle

 

Compilation of the program:

 

>javac fileName

 

Execution of the program

 

Get the copy of classes111.zip (oracle 8i/9i) or ojdbc14.jar (Oracle 10g xe) into the working directory (where program exists). The classes111.zip/ojdbc14.jar can be available from Oracle's installation folder (E:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib).

 

Why?

 

The class for Oracle driver is present in that .zip/.jar file.

 

Use the following command to execute the program:

 

For Oracle 8i/9i

 

>java -classpath classes111.zip;. className 

 

For Oracle 10g XE

 

>java -classpath ojdbc14.jar;. className

 

To display data in the console from the oracle database using the type-4 driver in JDBC

 

Table creation with data

Create table employee (empid varchar(10),empname varchar(10),sal int)

insert into employee values('e001','Raj',10000)
insert into employee values('e002','Harry',20000)
insert into employee values('e003','Sunil',30000)
insert into employee values('e004','Pollock',40000)
insert into employee values('e005','Jonty',50000)
insert into employee values('e006','Kallis',60000)
insert into employee values('e007','Richard',70000)

type4connection.java file

/*This makes connection to Oracle by using thin driver */
import java.sql.*;

public class type4connection
{
     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","pintu");
         Statement stmt=con.createStatement();
         ResultSet rs=stmt.executeQuery("select * from employee");
         while(rs.next())
         {
            String empid=rs.getString("empid");
            String empname=rs.getString("empname");
            int sal=rs.getInt("sal");
            System.out.println(empid+" "+empname + " "+sal);
         }
         con.close();
    }
}

Note: - Store the above file (type4connection.java file) in any drive (E:\jdbc). Here we have to store ojdbc14.jar file, which we will get it from Oracle's installation folder (E:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib).

 

Compile the file

 

javac type4connection.java

 

 

 

Execution of the program

 

java -classpath ojdbc14.jar;. type4connection