Introduction

This article explains how to connect to the Oracle10g database in Java.

Connect To Oracle10g database In Java

There are 5 steps to create a connection with an Oracle10g database in Java.
  1. First register the driver class
  2. Create the connection
  3. Create a statement
  4. Execute queries
  5. Close the connection
1. First, register the driver class
The class.forName() method of the class registers the driver class.
Syntax
  1. public static void forName(String classname) throws ClassNotFoundException
Example
  1. Class.forName("oracle.jdbc.driver.OracleDriver");
2. Create the connection object
The getConnection() method of the DriverManager class establishes the connection with the Oracle database.
Syntax
  1. public static Connection getConnection(String url) throws Exception
  2. public static Connection getConnetion(String name, String url, String password)
Example
  1. Connection connection=DriverManager.getConnection("jdbc.oracle.thin:@localhost:xe","system","password");
Instead of localhost we provide our system name there.
3. Create the Statement object
The createStatement() method of the Connection interface is used to create the statement. This object is responsible for executing queries with the Oracle database.
Syntax
  1. public Statement createStatement() throws Exception
Example
  1. Statement statement=connection.cretaeStatement();
4. Execute the Queries
This method of the Statement interface executes the queries to the Oracle database. This method returns the object of ResultSet that can be used to get all the records of a table.
Syntax
  1. public ResultSet executeQuery(String s) throws Exception
Example
  1. ResultSet resultset=statement.executeQuery("Select 8 from emp");
  3. while(resultset.next());
  4. {
  5. System.out.println(resultset.getInt(1)+ "" + resultset.getString(2));
  6. }
5. Close the connection object
By closing the connection object the statement and ResultSet will be closed automatically. The close() method of the Connection interface closes the connection.
Syntax
  1. public void close() throws Exception
Example
  1. connection.close();
Now, take an example that shows a connection with Oracle10g database
For making a Java application with the Oracle database, we need to use the above 5 steps to perform database connectivity. In this example, we are using Oracle10g as the database. So we need to know the following information for the Oracle database.
Driver class
For Oracle; the driver class is in oracle.jdbc.driver.OracleDriver.
Connection URL
For Oracle10g the URL is "jdbc:oracle:thin:@localhost:1521:xe" (instead of localhost we provide our system name also) 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. We may get all that information from the tnsnames.ora file.
username
The default username for the Oracle database is SYSTEM.
Password
This is user define given at the time of installing the Oracle database.
Now, start creating a connection with the Oracle10g database.
Create a table
Now open the Run SQL command line and write the following:
  1. create table student(s_id number(5), s_name varchar2(35),s_age number(3));
Our table is created, now insert some data into it.
  1. insert into student values(10,'Sandeep',22);
  2. insert into student values(11,'Rahul',20);
Now we have inserted two rows into the student table that we have to display using Java.
To connect a Java application with the Oracle database we need the ojdbc14.jar file.
There are two ways to load this jar file
  1. paste the jar ojdbc14.jar in C:\Program Files\Java\jre7\lib\ext folder.
  2. set classpath for this jar file.
1. Paste the file
First search the ojdbc14.jar file (Like C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib contains this file) then goto JRE/lib/ext folder and paste the ojdbc14.jar file in it.
2. Set the classpath
Go to environment variable then click on the new tab. In the variable name write the classpath and in the variable value paste the path to the ojdbc14.jar (like C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib\ojdbc14.jar;.;)
See the following image:
Fig-1.jpg
Display the student record
The following example Java file displays the student record.
Example
OracleConnectionEx.java
This example fetches all the records of the student table.

  1. import java.sql.*;
  2. class OracleConnectionEx
  3. {
  4. public static void main(String args[]) throws Exception
  5. {
  6. try
  7. {
  8. Class.forName("oracle.jdbc.driver.OracleDriver");
  9. Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@mcndesktop07:1521:xe", "system", "sandeep");
  10. Statement statement = connection.createStatement();
  11. ResultSet resultset = statement.executeQuery("select * from student");
  12. while (resultset.next())
  13. {
  14. System.out.println(resultset.getInt("S_ID") + " " + resultset.getString("S_NAME") + " " + resultset.getInt("S_AGE"));
  15. }
  16. connection.close();
  17. } catch (Exception ex)
  18. {
  19. System.out.println(ex);
  20. }
  21. }
  22. }
Output
Fig-2.jpg