Java Database Connectivity with Oracle

Java Database Connectivity with Oracle

In this very tutorial, we will be learning the Java Database Connectivity with Oracle or we can say the process to connect java application with the oracle database. In order to connect the Java application with the oracle database, there is a need to follow the below mentioned 5 steps that are designed to get the maximum efficiency. In this tutorial, Oracle 10g has been used as the database. Therefore, there is a need to know the following mentioned information for the oracle database:

• Driver class: The driver class that is needed for the oracle database is oracle.jdbc.driver.OracleDriver.
• Connection URL: The connection URL that is generally used for the oracle10G database is jdbc:oracle:thin:@localhost:1521:xe where jdbc is known as the API, oracle is the main database, thin is basically the driver, localhost is generally the server name that is responsible for running the oracle, The user can use IP address, 1521 is the port number and XE is basically the Oracle service name. The programmers can get all these mentioned information from the tnsnames.ora file.
• Username: The default username that is generally used for the oracle database is ‘system’.
• Password: This is basically the password that is given by the user at the time of installing the oracle database.

Create a Table

In order to establish the connection there is need to create a table in the oracle database. Here is the SQL query that is generally used to create a table that is depicted below:

create table student(id number(10),name varchar2(40),age number(3));  

Example to Connect Java Application with Oracle database

Here is an example of creating a table in the oracle database and in this example, an Oracle database is being connected to the user and getting data from emp table. Here, the system and oracle are the username and password for the connected Oracle database. Examine the example carefully in order to grasp the process:

import java.sql.*;

class OracleConnection
{
  
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","system", "oracle");
      
 
//step3 create the statement object  
	Statement stmt = con.createStatement ();
      
 
//step4 execute query  
	ResultSet rs = stmt.executeQuery ("select * from student");
      
while (rs.next ())
	
System.out.println (rs.getInt (1) + "  " + rs.getString (2) + "  " +rs.getInt (3));
      
 
//step5 close the connection object  
	con.close ();
    
 
}
    catch (Exception e)
    {
      System.out.println (e);
    }
  
 
}

}

There is another requirement that is on the list that is basically to connect to the java application with the Oracle database ojdbc14.jar file is required to be loaded.

Two ways to load the jar file:

There are generally two methods to load the jar file that are depicted below:

• paste the ojdbc14.jar file in jre/lib/ext folder
• set classpath

1. paste the ojdbc14.jar file in JRE/lib/ext folder:

The process goes like, the user have to search the ojdbc14.jar file first and after finding that go to JRE/lib/ext folder and then paste the jar file there.

2. set classpath:

Now there is a catch, there are generally two ways to set the classpath that are depicted below:

• Temporary
• Permanent

1. Setting the temporary classpath

The programmer have to first search the ojdbc14.jar file in order to set the temporary classpath and then open the command prompt and write the following command:

C:>set classpath=c:\folder\ojdbc14.jar;.;  

2. Setting the permanent classpath

In order to set the permanent classpath the programmer have to go to the environment variable and then click on the new tab. In the variable name, the user have to write classpath and in the variable value the user have to paste the path to ojdbc14.jar just by appending ojdbc14.jar;.; as

C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib\ojdbc14.jar;.;