Monday, January 2, 2012

Java Database connectivity (Type 4) to Oracle db

Make sure you have the JDBC driver downloaded and added to External Jar paths in eclipse. my driver file was ojdbc14.jar.

My Oracle version was :
SQL> select BANNER from SYS.V_$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0    Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production



Code Steps :
In general, to process any SQL statement with JDBC, you follow these steps:
  1. Establishing a connection.
  2. Create a statement.
  3. Execute the query.
  4. Process the ResultSet object.
  5. Close the connection.
Note: please make sure the user you are connecting with has create session priviledge.

Give this on your oracle DB.
SQL> grant create session to sriram;
Grant succeeded.

===== Class file I used to connect ======
 import java.sql.*;
class JDBC1example {
  public static void main (String[] args) throws Exception
  {
   Class.forName ("oracle.jdbc.OracleDriver");

   Connection conn = DriverManager.getConnection
     ("jdbc:oracle:thin:@//localhost:1521/XE", "sriram", "sriram321");
                        // @//machineName:port/SID,   userid,  password
   try {
     Statement stmt = conn.createStatement();
     try {
       //ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
         ResultSet rset = stmt.executeQuery("select * from all_users");
       try {
         while (rset.next())
           System.out.println (rset.getString(1));   // Print col 1
       }
       finally {
          try { rset.close(); } catch (Exception ignore) {}
       }
     }
     finally {
       try { stmt.close(); } catch (Exception ignore) {}
     }
   }
   finally {
     try { conn.close(); } catch (Exception ignore) {}
   }
  }
}

================Result-=====================

SRIRAMS
SRIRAM
FLOWS_020100
FLOWS_FILES
HR
MDSYS
ANONYMOUS
XDB
CTXSYS
DBSNMP
TSMSYS
DIP
OUTLN
SYSTEM
SYS
ORADBA1
TEST