Java Database Connectivity with Oracle

Connecting to Oracle Datatabase using a Java Application

About JDBC

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) – recommended to use

There are 5 steps to connect any java application with the database using JDBC. These steps are as follows:

Register the Driver class

Create connection

Create Statement/Prepare Statement

Execute queries

Close connection

1.Diver class: The driver class for the oracle database is "oracle.jdbc.driver.OracleDriver."

   Syntax : Class.forName("Diver class")

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 information from the tnsnames.ora file.

  

   jdbc - api

   oracle - databaes

   thin   - driver type

   1521 - exculusive port number of oracle listener

   Syntax : Connection con=DriverManager.getConnection("Connection URL");

3.Username: The default username for the oracle database is system.

4.Password: It is the password given by the user at the time of installing the oracle database.

5. Get downloaded file "ojdbc14.jar" and copy the file in the folder  jre/lib/ext

    Ex: C:\Program Files (x86)\Java\jre1.8.0_60\lib\ext

Program to Connect to Oracle and retrieve the data from a table.

import java.sql.*; 

class OracleCon

{ 

  public static void main(String args[])

 { 

    try

   { 

     //step1 load the driver class 

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

     //step2 create  the connection object 

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

         //step3 create the statement object 

     //Statement stmt=con.createStatement();

     PreparedStatement ps=con.prepareStatement("select * from emp"); 

     ResultSet rs=ps.executeQuery();

      //step4 execute query 

      //ResultSet rs=stmt.executeQuery("select * from employees"); 

      while(rs.next()) 

          System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3)); 

 

      //step5 close the connection object 

        con.close(); 

   }

   catch(Exception e)

   {

      System.out.println(e);

   } 

} 

}

Output:

E:\>javac OracleCon.java

E:\>java OracleCon

111  NAMMU  ANALYST

222  MOHAN  CLERK

333  GURU  MANAGER

444  KRISHNA  ENGINEER

Java ResultSetMetaData Interface

If you have to get metadata of a table like total number of column, column name, column type etc. , ResultSetMetaData interface is used

Commonly used methods of ResultSetMetaData interface

Commonly used methods of ResultSetMetaData interface

Method                                                                          Description

public int getColumnCount()throws SQLException               it returns the total number of

                                                                                          columns in the ResultSet object.

public String getColumnName(int index)     it returns the column name of the specified

throws SQLException                                  column index.

public String getColumnTypeName(int index)   it returns the column type name

throws SQLException                                        for the specified index.

public String getTableName(int index)           it returns the table name for the

throws SQLException                                    specified column index.

Write a program to Display Resultset Metadata

import java.sql.*; 

class rsmd{ 

public static void main(String args[]){ 

try{ 

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

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

 

PreparedStatement ps=con.prepareStatement("select * from emp"); 

ResultSet rs=ps.executeQuery(); 

ResultSetMetaData rsmd=rs.getMetaData(); 

 

System.out.println("Total 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 e){ System.out.println(e);} 

} 

} 

Output:

E:\>javac rsmd.java

E:\>java rsmd

Total columns: 5

Column Name of 1st column: EMPNO

Column Type Name of 1st column: NUMBER