Connecting to Oracle Database using Thin Driver

To connect a Java application with Oracle database using Thin Driver. You need to follow the following steps

  1. Load Driver Class: The Driver Class for oracle database is oracle.jdbc.driver.OracleDriver and Class.forName("oracle.jdbc.driver.OracleDriver") method is used to load the driver class for Oracle database.
  2. Create Connection: For creating a connection you will need a Connection URL. The Connection URL for Oracle is

    connecting to oracle

    You will also require Username and Password of your Oracle Database Server for creating connection.
  3. Loading jar file: To connect your java application with Oracle, you will also need to load ojdbc14.jar file. This file can be loaded into 2 ways.
    1. Copy the jar file into C:\Program Files\Java\jre7\lib\ext folder.

      or,

    2. Set it into classpath. For more detail see how to set classpath

    Download ojdbc14.jar file

NOTE: Here we are discussing about Oracle 10g as database. For other version of Oracle you will be require to do some small changes in the Connection URL.


Example

Create a table in Oracle Database

create table Student(sid number(10),sname varchar2(20));

Insert some record into the table

insert into Student values(101,'adam');
insert into Student values(102,'abhi');

Accessing record from Student table in Java application

import java.sql.*;
class Test
{
public static void main(String []args)
{
try{
//Loading driver
Class.forName("oracle.jdbc.driver.OracleDriver");

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

Statement s=con.createStatement();	//creating statement

ResultSet rs=s.executeQuery("select * from Student");	//executing statement

while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}

con.close();	//closing connection
}catch(Exception e){
e.printStacktrace();
}
}
}

Output

101 adam
102 abhi

Inserting record into a table using java application

import java.sql.*;
class Test
{
public static void main(String []args)
{
try{
//Loading driver...
Class.forName("oracle.jdbc.driver.OracleDriver");

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

PreparedStatement pst=con.prepareStatement("insert into Student values(?,?)");
         
         pst.setInt(1,104);
         pst.setString(2,"Alex");
	 pst.executeUpdate();

con.close();	//closing connection
}catch(Exception e){
e.printStacktrace();
}
}
}

Quantitative Aptitude
Reasoning
Programming
Interview