Post 4: JDBC: Example to connect to the Oracle database

Example to connect to the Oracle database

For connecting java application with the oracle database, you need to follow 5 steps to perform database connectivity. In this example we are using Oracle10g as the database. So we need to know following information for the oracle database: Driver class: The driver class for the oracle database is oracle.jdbc.driver.OracleDriver. Connection URL: The connection URL for the oracle10G database is jdbc:oracle:thin:@localhost:1521:xe 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. You may get all these informations from the tnsnames.ora file. Username: The default username for the oracle database is system. Password: Password is given by the user at the time of installing the oracle database.

Let's first create a table in oracle database.

1. create table emp(id number(10),name varchar2(40),age number(3));  

---

Example to Connect Java Application with Oracle database

In this example, system is the username and oracle is the password of the Oracle database.

1. import java.sql.*;  
2. class OracleCon{  
3. public static void main(String args[]){  
4. try{  
5. //step1 load the driver class  
6. Class.forName("oracle.jdbc.driver.OracleDriver");  
7.   
8. //step2 create  the connection object  
9. Connection con=DriverManager.getConnection(  
10. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
11.   
12. //step3 create the statement object  
13. Statement stmt=con.createStatement();  
14.   
15. //step4 execute query  
16. ResultSet rs=stmt.executeQuery("select * from emp");  
17. while(rs.next())  
18. System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3));  
19.   
20. //step5 close the connection object  
21. con.close();  
22.   
23. }catch(Exception e){ System.out.println(e);}  
24.   
25. }  
26. }  

The above example will fetch all the records of emp table.

---

To connect java application with the Oracle database ojdbc14.jar file is required to be loaded.

download the jar file ojdbc14.jar

Two ways to load the jar file:

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

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

Firstly, search the ojdbc14.jar file then go to JRE/lib/ext folder and paste the jar file here.

2) set classpath:

There are two ways to set the classpath: temporary permanent

How to set the temporary classpath:

Firstly, search the ojdbc14.jar file then open command prompt and write:

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

How to set the permanent classpath:

Go to environment variable then click on new tab. In variable name write classpath and in variable value paste the path to ojdbc14.jar by appending ojdbc14.jar;.; as C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib\ojdbc14.jar;.;