Connection of Java program with Oracle11g database

Connection of Java program with Oracle11g database

For connecting a Java program to Oracle11g, we need the following software :
1) Jdk (any version)
2) Oracle11g


Few steps in Oracle11g:  After installing Oracle11g, perform the following steps to create a new "user" and set "password" for it as it is required in Java program.

i) sql >conn / as sysdba;                                                              ----> Connection to Sysdba
ii) sql >create user <username>;                                                ----> Creating new user
iii) sql >grant dba <username> identified as <password>;        ----> Granting dba to the user
iv) sql >conn <username>/<password>;                                     ----> Connecting to the user

Now create table using sql commands and insert values in it and commit.

Set CLASSPATH of Oracle11g Driver class :
Set the classpath  C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc6.jar in the environment variable named CLASSPATH if location of ojdbc jar file is the same as mentioned in above line otherwise change the classpath accordingly.

However the Java Program shown below creates the table table2 from the program itself and inserts values in it. If we don't want to create the table in the program and like to use the table table2 already created in database, then skip the 9th line of the program containing the create table statement.        
In the following program,
         table name = table2
         username = frndz
         password = frndz


Java Program :


import java.sql.*;
public class DBCoracle2
{
     public static void main(String args[]) throws Exception
{
    Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","frndz","frndz");
Statement st= con.createStatement();
int i1= st.executeUpdate("create table table2(name varchar(12), roll_no number(10))");//creating table table2
int i2=st.executeUpdate("insert into table2 values('mayank',1009007)");                       //insertion of values
int i3=st.executeUpdate("insert into table2 values('vikash',1009014)");                         //insertion of values
ResultSet rs = st.executeQuery("select * from table2");
while(rs.next())
System.out.println(rs.getString(1)+"    "+rs.getString(2));                                          //Print values on console
rs.close();
st.close();
con.close();
}
}

Note : When we are working with Oracle database, we need not bother about the jar file containing Driver class separately as it is given along with the Oracle11g software. During installation jar file containing Driver class(ojdbc6.jar in case of Oracle11g)  is supplied automatically. So after installing Oracle11g, we just need to set the classpath and we are ready to connect the database using java program. In case of other databases like MySql, DB2, PostgreSql etc we need to download the jar file containing Driver class(es) separately and then set their classpath as they are not supplied along with their database softwares.