Oracle Database JDBC Connection with Example

In this post, we will discuss how to establish the JDBC connection in Java with Oracle database. After establishing the connection we will develop a sample JDBC application to check whether the connection is done properly or not, and also develop a Sample JDBC program to select the records.

Before establishing the connection make sure you have the following software setup ready,

1) Oracle database version 11.2 or later
2) JDK1.8 or later version
3) Text editor to write the code

Oracle database versions with the main JDBC jar file and required JDK version.

Oracle Version	Main Jar file with Required JDK version
11.2 or 11gR2	ojdbc5.jar with JDK5 or later ojdbc6.jar with JDK 6, JDK 7, and JDK 8 or later
12.1 or 12cR1	ojdbc6.jar with JDK 6 or later ojdbc7.jar with JDK 7, and JDK 8 or later
12.2 or 12cR2	ojdbc8.jar with JDK 8 or later
18.3	ojdbc8.jar with JDK 8, JDK 9, JDK10, JDK11 or later
19.3	ojdbc8.jar with JDK8, JDK9, JDK10, JDK11 or later ojdbc10.jar with JDK10, JDK11 or later

Jar file with supported JDBC driver API version. Learn more:- different JDBC driver API version and features

Jar file	Supported JDBC API Version
ojdbc5.jar	JDBC 3.0
ojdbc6.jar	JDBC 4.0
ojdbc7.jar	JDBC 4.1
ojdbc8.jar	JDBC 4.2
ojdbc10.jar	JDBC 4.3

Steps to Establish Oracle JDBC Connection

Now let us begin the establishing oracle JDBC connection.

Step1) Collect JDBC jar file of Oracle database.

In Oracle JDBC driver is in-built software, that is it came along with Oracle software installation. We need not to download or collect it seprately. We can collect jar file of JDBC driver from the Oracle database installation folder. The jar file of JDBC driver of oracle database located at <Oracle_Home>\jdbc\lib.

If you want to download it separately then us the following link. Download link:- download the JDBC driver jar file for the Oracle database.

For this tutorial, I am using Oracle 11g and the jar file is located at:- D:\app\User\product\11.2.0\dbhome_1\jdbc\lib. It has both ojdbc5.jar and ojdbc6.jar. I will use ojdbc6.jar because it supports JDBC 4.x version.

Step2) Add jar file to the CLASSPATH.

Note:- We have to add the location of the jar file, not the folder where jar file is available.

Don’t try to write the location name manually because we can do a spelling mistake, so it is recommended to copy the jar file location. In the Windows operating system, Go to the folder where the jar file is locating, select jar file, right-click and select properties, in the security section you will find “Object name”, and from here copy the location.

In my case,

• The folder where the jar file is located:- D:\app\User\product\11.2.0\dbhome_1\jdbc\lib
• The location which will be added to Classpath:- D:\app\User\product\11.2.0\dbhome_1\jdbc\lib\ojdbc6.jar

How to add the jar file to the classpath? For this, we have to go to “Advance system settings”. There are different ways to go to “Advance system settings”.

• This PC -> Properties -> Advance system settings ( OR )
• Control panel -> System and Security -> System -> Advance system settings

Now in Advance system settings, go to Environment Variables -> System Variables.

If the CLASSPATH variable already exists in the System variable then “Edit” it otherwise create “New”.

In case of “New”

Variable Name: CLASSPATH
Value: <location-of-oracle-jdbc-jar-file>;.

In case of “Edit”

Variable Name: CLASSPATH
Value: <location-of-oracle-jdbc-jar-file>;<existing-values>;.

It is recommended to place dot (.) after the values, and semicolon (;) is a separator that separates two variable values. If you find difficulties to set classpath then Learn:- Different Ways to Set Java Classpath Environment Variables in Windows

Step3) Develop the application.

We have established the connection successfully. Now, we can develop a sample JDBC application to check the connection is established properly or not. But before developing the application, let us discuss some important things required to develop the JDBC application.

Oracle JDBC Driver Details

We need some basic details of Oracle JDBC driver to develop the program.

JDBC Driver Class Name :: oracle.jdbc.OracleDriver
URL :: jdbc:oracle:thin:@<ip-address/host-name>:<port-no-of-oracle-db>:<sid>
Username :: <username_of_the_oracle_database>
Password :: <password_of_the_oracle_database>

• On a local machine, generally, the Oracle database is located at the port no = 1521, the default port number of Oracle database is 1521
• If you are developing JDBC application for the first time then use, host-name = localhost

How to find the sid /global name?
Open SQL Plus tool, Login to the user account and type below query,

SQL> select sys_context('userenv','instance_name') from dual;

SYS_CONTEXT('USERENV','INSTANCE_NAME')
-----------------------------------------
knowprogram

If there are more then one word for sid/global name then use the first word as sid/global name. Hence, in My case the URL will be => jdbc:oracle:thin:@localhost:1521:knowprogram

Also Learn,

• MySQL database connection in Java
• Database connection in Java using Eclipse IDE

Oracle JDBC Connection Code in Java Example

Now, let us develop a simple Java program to check connection is established properly or not?

import java.sql.*;

public class ConnectionTest {

   public static void main(String[] args ) throws Exception {

      // register Oracle thin driver with DriverManager service
      // It is optional for JDBC4.x version
      Class.forName("oracle.jdbc.OracleDriver");
	  
      // variables
      final String url = 
              "jdbc:oracle:thin:@localhost:1521:knowprogram";
      final String user = "scott";
      final String password = "tiger";

      // establish the connection
      Connection con = 
              DriverManager.getConnection(url, user, password);

      // display status message
      if(con == null) {
         System.out.println("JDBC connection is not established");
         return;
      }
      else
         System.out.println("Congratulations,"+
                   " JDBC connection is established successfully.\n");
	   
      // close JDBC connection
      con.close();

   } //main
} //class

Compile the Java Program,
> javac ConnectionTest.java

Execution,
> java ConnectionTest

Congratulations, JDBC connection is established successfully.

---

Standard Steps to Develop JDBC Application

Every JDBC program have these standard steps. These are common steps for all JDBC programs.

1. Register JDBC driver with DriverManager service

// register Oracle thin driver with DriverManager service
// optional for JDBC4.x version
Class.forName("oracle.jdbc.driver.OracleDriver");

This step is only required for the JDBC3.0 or lesser versions. JDBC4.x version supports the auto-loading of the driver class, so registering JDBC driver with DriverManager class is optional. Here auto-loading means when we call the method of the class in our program then due to logic of static block of Driver class, it loads the driver class at runtime.

2. Establish the connection with database software

// establishing the connection with database software
Connection con = DriverManager.getConnection(url, username, password);

3. Create JDBC Statement object

// create JDBC Statement object
Statement st = con.createStatement();

4. Gather SQL query result back to Java application from database software. (Or) the logic to perform main task.

5. Close JDBC objects.

// close JDBC objects
rs.close();
st.close();
con.close();

Oracle JDBC Connection Example with Simple Program

Previously we have tested that the Oracle JDBC connection is established properly or not? Now, let us develop a JDBC program to select the record. We will develop a JDBC program that will fetch the records of the student table from the Oracle database and display it on the console.

For developing the JDBC application we need a table in the Oracle database. You can use an existing table, But here we are developing JDBC for the first time so we will create a new table in the Oracle database. First, log in with your own username and password.

SQL> conn scott/tiger;
Connected.

Create table,

SQL> 
create table student
(
  sno number(5) primary key,
  sname varchar2(15),
  sadd varchar2(15),
  avg float
);

Table created. Now, insert some record into the table,

SQL> insert into student values(100, 'SOPHIA', 'LONDON', 85);
SQL> insert into student values(101, 'William', 'Boise', 80);
SQL> insert into student values(110, 'Alex', 'Washington', 90);
SQL> insert into student values(200, 'Amelia', 'Manchester', 72);

Now, execute the commit command.

SQL> commit;
Commit complete.

Note:- Don’t forget to execute the commit command, Otherwise inserted data will not be saved into the database and our Java application can’ t retrieve the data which doesn’t exist in the database.

Displaying the table,

SQL> select * from student;

SNO SNAME    SADD        AVG
--- -------- ----------- ---
100 SOPHIA   LONDON      85
101 William  Boise       80
110 Alex     Washington  90
200 Amelia   Manchester  72

Java Program

import java.sql.*;

public class SelectTest {

   public static void main(String[] args ) throws Exception {
	   
      // variables
      final String url = 
                  "jdbc:oracle:thin:@localhost:1521:knowprogram";
      final String user = "scott";
      final String password = "tiger";

      // establish the connection
      Connection con = 
                    DriverManager.getConnection(url, user, password);

      // create JDBC statement object
      Statement st = con.createStatement();

      // prepare SQL query
      String query = 
               "SELECT SNO, SNAME, SADD, AVG FROM STUDENT";

      // send and execute SQL query in Database software
      ResultSet rs = st.executeQuery(query);

      // process the ResultSet object
      boolean flag = false;
      while(rs.next()) {
         flag = true;
         System.out.println( rs.getInt(1) + " " + rs.getString(2) +
                    " " + rs.getString(3) + " " + rs.getFloat(4) );
      }

      if(flag == true) {
         System.out.println("\nRecords retrieved and displayed");
      } else {
         System.out.println("Record not found");
      }

      // close JDBC objects
      rs.close();
      st.close();
      con.close();

   } //main
} //class

Now compile and Execute the program,

> javac SelectTest.java
> java SelectTest

Output of the JDBC program:-

100 SOPHIA LONDON 85.0
101 William Boise 80.0
110 Alex Washington 90.0
200 Amelia Manchester 72.0

Records retrieved and displayed

Note:- Since it was our first JDBC program so while developing this program we didn’t follow Java coding standards. We should follow some Java coding standards and rules while developing JDBC applications. These are discussed in the next JDBC tutorials. Learn more:- Coding Standards and Guidelines for JDBC Application, Best Way to Close JDBC Connection Object

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

• MySQL database connection in Java
• Database connection in Java using Eclipse IDE
• Introduction to Java.sql package
• JDBC DriverManager class
• JDBC get Connection
• JDBC Statement interface
• JDBC statement example
• Different types of statements in JDBC
• JDBC PreparedStatement interface
• Statement vs PreParedStatement

Search