Connect Java with Oracle 10g Tutorial

For Windows users only

Pre-Requisites:

1. jdk1.6.0_11
2. Oracle 10g
3. ojdbc14.jar

If you want to access oracle10g via JDBC you need to set CLASSPATH environment variable to find the ojdbc14.jar file provided by Oracle. The file is located in:  C:\oracle\product\10.1.0\Db_1\jdbc\lib\ojdbc14.jar           beneath you Oracle home directory, or it’s available for download from http://technet.oracle.com or http://www.findjar.com/
If you are using older version of JDK(1.2 or below). You need to set CLASSPATH for classes12.zip
C:\oracle\product\10.1.0\Db_1\jdbc\lib\classes12.zip
Step:1
Setting CLASSPATH
1. Right click the My Computer icon. In that click properties
2. You will see a window System Properties. In that window click Advanced
3. You will see a button Environment Variables. Click that button.
4. In the popup window you will see User variables for XXXXXX (your name) and System variable. Select the New button which is below the User variables for XXXXX.
5.  You will see the window New User Variable. In Variable name type CLASSPATH and in Variable value type .;C:\oracle\product\10.1.0\Db_1\jdbc\lib\ojdbc14.jar
The first entry must be a period, which denotes the current directory. The second entry must be the directory for the ojdbc14.jar or classess12.zip (jdk 1.2 or below).
If CLASSPATH is not set correctly, you will get a NoClassDefFoundError error when you run a compile class.
NOTE
Be sure to use a version of the JDK that is compatible with the Oracle release you are using.  If you use a new release of the JDK with an older release of Oracle’s drivers, you may encounter “access violation” errors when executing your programs.
Step 2
The PATH environment variable should already be set for JDK Step 3
JDK is already provided by Oracle itself(You can even use your own JDK). If you install Oracle10g It comes automatically. It lies in
C:\oracle\product\10.1.0\Db_1\jdk(I have installed oracle in C: in your case it might be D: or E: ….)
Setting PATH
1. Right click the My Computer icon. In that click properties
2. You will see a window System Properties. In that window click Advanced
3. You will see a button Environment Variables. Click that button.
4. In the popup window you will see User variables for XXXXXX (your name) and System variable. Select the New button which is below the User variables for XXXXX.
5.  You will see the window New User Variable. In Variable name type PATH and in Variable value type
C:\oracle\product\10.1.0\Db_1\jdk\bin;
Click ok. You have finished setting PATH and CLASSPATH.


Testing your Connection
Oracle provides a sample program called JdbcCheckup.java that you can use to verify your JDBC configuration. This file may be in a zip file (demo.zip on the C:\oracle\product\10.1.0\Db_1\jdbc\demo.zip.  After unzipping you will get C:\oracle\product\10.1.0\Db_1\jdbc\demo\samples\generic\. You will need to extract it before running the program. Go to the directory where the sample file is, then compile and execute the JdbcCheckup.iava class:
javac  JdbcCheckup.java
Java JdbcCheckup

NOTE
Java commands are case sensitive. When you execute jdbcCheckup, you are prompted for usename, password, and connect string for a database. That connection Information will be used to attempt a connection; if successful, that attempt will return the following output:

Connecting to the database...Connecting…
 connected.
Hello World.
Your JDBC Installation is correct.
If you don't receive feedback telling you that your Installation is correct, you need to check your configuration. Common problems include incorrectly set environment variables (PATH and CLASSPATH) and mismatched versions of database connection drivers. It you change the environment variable values, you need to shut down and restart the command windows for the changes to take effect.
If you are not able to find demo.zip then try the below programs.

Sample programs

Sample program: 1
ConnectOracle.java

1. /** 
2.  * ConnectOracle.java 
3.  */  
4. package com.fazle.connectoracle;  
5.   
6. import java.sql.Connection;  
7. import java.sql.DriverManager;  
8. import java.sql.ResultSet;  
9. import java.sql.Statement;  
10.   
11. /** 
12.  * @author www.fazlerabbicse.blogspot.com 
13.  *  
14.  */  
15. public class ConnectOracle {  
16.   
17.     public static void main(String[] args) {  
18.   
19.         try {  
20.             DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());  
21.             System.out.println("Connecting to the database...");  
22.             Connection connection = DriverManager.getConnection(  
23.                     "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");  
24.             Statement statement = connection.createStatement();  
25.             ResultSet resultset = statement.executeQuery("select 'Connected' from dual");  
26.             resultset.next();  
27.             String s = resultset.getString(1);  
28.             System.out.println(s);  
29.             statement.close();  
30.             connection.close();  
31.         } catch (Exception e) {  
32.             System.out.println("The exception raised is:" + e);  
33.         }  
34.     }  
35.   
36. }  

connection=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");
In the above line, username is “scott” and password is “tiger”.
Thin is the JDBC driver
1521 is the default port number which the connection is to be established and orcl is the database name.
Sample program: 2

ConnectOracle.java

1. /** 
2.  * ConnectOracle.java 
3.  */  
4. package com.fazle.connectoracle;  
5.   
6. import java.sql.Connection;  
7. import java.sql.DriverManager;  
8. import java.sql.ResultSet;  
9. import java.sql.Statement;  
10.   
11. /** 
12.  * @author www.fazlerabbicse.blogspot.com
13.  *  
14.  */  
15. public class ConnectOracle {  
16.     public static void main(String[] args) {  
17.         try {  
18.             DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());  
19.             Connection connection = DriverManager.getConnection(  
20.                     "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "t");  
21.   
22.             Statement statement = connection.createStatement();  
23.             ResultSet resultSet = statement  
24.                     .executeQuery("SELECT EMPNAME FROM EMPLOYEEDETAILS");  
25.             while (resultSet.next()) {  
26.                 System.out.println("EMPLOYEE NAME:"  
27.                         + resultSet.getString("EMPNAME"));  
28.             }  
29.         } catch (Exception e) {  
30.             e.printStackTrace();  
31.         }  
32.     }  
33. }