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
  1. Create table employee (empid varchar(10),empname varchar(10),sal int)  
  2.   
  3. insert into employee values('e001','Raj',10000)  
  4. insert into employee values('e002','Harry',20000)  
  5. insert into employee values('e003','Sunil',30000)  
  6. insert into employee values('e004','Pollock',40000)  
  7. insert into employee values('e005','Jonty',50000)  
  8. insert into employee values('e006','Kallis',60000)  
  9. insert into employee values('e007','Richard',70000)  
type4connection.java file
  1. /*This makes connection to Oracle by using thin driver */  
  2. import java.sql.*;  
  3.   
  4. public class type4connection  
  5. {  
  6.      public static void main(String args[]) throws Exception  
  7.     {  
  8.          Class.forName("oracle.jdbc.driver.OracleDriver");  
  9.          Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system","pintu");  
  10.          Statement stmt=con.createStatement();  
  11.          ResultSet rs=stmt.executeQuery("select * from employee");  
  12.          while(rs.next())  
  13.          {  
  14.             String empid=rs.getString("empid");  
  15.             String empname=rs.getString("empname");  
  16.             int sal=rs.getInt("sal");  
  17.             System.out.println(empid+" "+empname + " "+sal);  
  18.          }  
  19.          con.close();  
  20.     }  
  21. }  
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
 
display data in jdbc 
 
Execution of the program
 
java -classpath ojdbc14.jar;. type4connection
 
display data from database in jdbc