Java: 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

   

   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

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.

Time for an Example!

Let's see programs for performing simple operations like create, insert and select on database tables.

Create a table in Oracle Database

Use the below SQL command to create a table Student into oracle database. This table has two columns: sid and sname.

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

Insert some record into the table

After creating the table now insert two records into table using the below sql command. Here we are inserting adam and abhi named two rows.

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

JDBC Example : Accessing record

In the above example, we inserted two records using SQL command. Now we are fetching those records using JDBC 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();
		}
	}
}
	

101 adam 102 abhi

Example: Inserting record

We can insert data into table using Java application using the JDBC. Here we are using Prepared statement and parameterized query that discussed in previous topic.

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();
		}
	}
}