JDBC

from http://www.orafaq.com/wiki/JDBC

JDBC drivers

Oracle provides three categories of JDBC drivers:

• JDBC Thin Driver (no local SQL*Net installation required/ handy for applets)
• JDBC OCI for writing stand-alone Java applications
• JDBC KPRB driver (default connection) for Java Stored Procedures and Database JSPs.

All three drivers support the same syntax and APIs. Oracle needs three drivers to support different deployment options. Looking at the source code, they will only differ in the way you connect to the database. Remember, you must use a JDBC version that matches the version of your Java Development Kit.

Thin driver

Oracle's JDBC Thin driver uses Java sockets to connect directly to Oracle. It provides its own TCP/IP version of Oracle's SQL*Net protocol. Because it is 100% Java, this driver is platform independent and can also run from a Web Browser (applets).

There are 2 URL syntax, old syntax which will only work with SID and the new one with Oracle service name.

Old syntax

 jdbc:oracle:thin:@[HOST][:PORT]:SID

New syntax

 jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE

On new syntax SERVICE may be a oracle service name or a SID.

There are also some drivers that support a URL syntax which allow to put Oracle user id and password in URL.

 jdbc:oracle:thin:[USER/PASSWORD]@[HOST][:PORT]:SID
 jdbc:oracle:thin:[USER/PASSWORD]@//[HOST][:PORT]/SERVICE

Sample connect string (with service name orcl):

 String url = "jdbc:oracle:thin:@//myhost:1521/orcl";

or (with instance name orcl):

 String url = "jdbc:oracle:thin:@myhost:1521:orcl";

You can find the SID/SERVICE name in your tnsnames.ora file:

XE =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = XE)
   )
 )
ORCL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
   (CONNECT_DATA =
     (SID = ORCL)
   )
 )

You can also give a tnsnames.ora entry-like in the string (here for SSL/TCPS):

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=<host>)(PORT=<port>))(CONNECT_DATA=(SERVICE_NAME=<service>)))

Working example program (Conn.java):

import java.sql.*;
class Conn {
  public static void main (String[] args) throws Exception
  {
   Class.forName ("oracle.jdbc.OracleDriver");

   Connection conn = DriverManager.getConnection
     ("jdbc:oracle:thin:@//localhost:1521/orcl", "scott", "tiger");
                        // @//machineName:port/SID,   userid,  password
   try {
     Statement stmt = conn.createStatement();
     try {
       ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
       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) {}
   }
  }
}

OCI driver

Oracle's JDBC OCI drivers uses Oracle OCI (Oracle Call Interface) to interact with an Oracle database. You must use a JDBC OCI driver appropriate to your Oracle client installation. The OCI driver works through SQL*Net.

The JDBC OCI drivers allow you to call the OCI directly from Java, thereby providing a high degree of compatibility with a specific version of Oracle. Because they use native methods, they are platform specific.

String url = "jdbc:oracle:oci:@myhost:1521:orcl";

Here is an example connect class:

import java.sql.*;
class dbAccess {
  public static void main (String args []) throws Exception
  {
        Class.forName ("oracle.jdbc.OracleDriver");
        Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:@hostname_orcl", "scott", "tiger");
                                                      // or oci7 @TNSNames_Entry,    userid,  password
        try { 
                 Statement stmt = conn.createStatement();
        try {
                ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
        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) {}
        }
  }

 

KPRB driver

Oracle's JDBC KPRB driver is mainly used for writing Java stored procedures, triggers and database JSPs. It uses the default/ current database session and thus requires no additional database username, password or URL.

One can obtain a handle to the default or current connection (KPRB driver) by calling the OracleDriver.defaultConnection() method. Please note that you do not need to specify a database URL, username or password as you are already connected to a database session. Remember not to close the default Connection. Closing the default connection might throw an exception in future releases of Oracle.

import java.sql.*;
class dbAccess {
  public static void main (String args []) throws SQLException
  {
        Connection conn = (new oracle.jdbc.OracleDriver()).defaultConnection();
        try {
        Statement stmt = conn.createStatement();
        try {
        ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
        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) {}
        }
// Remember not to close the default Connection.
// Closing the default connection might throw an exception in future releases of Oracle.
//        } finally {
//            try { conn.close(); } catch (Exception ignore) {}
//        }
  }
}

Difference between programs and applets

Applets

Applets do not live in a page as is commonly perceived. Applets are actually Java classes identified via HyperText Markup Language (HTML) tags within Web documents, it is these HTML tags that are embedded within Web documents. Java Applets are loaded from Web Servers somewhere on the Internet or within your corporate Intranet or Extranet.

Sample Java Applet:

import java.applet.Applet;
import java.awt.Graphics;

public class HelloWorld extends Applet {
        public void paint(Graphics g) {
                g.drawString("Hello world!", 50, 25);
        }
}

Applications

Java applications fit the traditional application model in the sense that they are executed from a command line and need to be installed on, or migrated to, each application host machine and then executed within that machine's JVM using the following command line construct:

java myappclass

Sample Java Application:

/**
 * The HelloWorldApp class implements an application that
 * simply displays "Hello World!" to the standard output.
 */
class HelloWorldApp {
        public static void main(String[] args) {
                System.out.println("Hello World!"); //Display the string.
        }
}