JDBC

JDBC is an API included in the J2SE and J2EE platforms that provides access to a variety of data sources, typically relational database management systems such as Oracle, within Java programs. Sun Microsystems introduced JDBC in January 1997 as a standardized way to query and update data in the data sources. A common belief is that JDBC stands for Java Database Connectivity although Sun s JDBC specification does not list a definition of the abbreviation.

The JDBC API consists of two packages:

• java.sql Classes and interfaces to connect to data sources; process SQL statements that retrieve data into result sets; insert, update, and delete data; and execute stored procedures

• javax.sql Classes and interfaces for advanced server-side processing features, such as connection pooling and distributed transactions

The Oracle JDBC drivers also provide packages containing Oracle-specific extensions. The Oracle JDBC extension packages are

• oracle.sql

• oracle.jdbc

• oracle.jdbc.pool

To access the JDBC API in your program without having to specify the package name , use the import keyword to define the packages, classes, or interfaces you wish to reference before declaring the class. For example:

 import java.sql.*; public class MyClass { } 

JDBC Driver Types

Each underlying data source requires a Java Database Connectivity (JDBC) driver. The data source vendors as well as third parties develop JDBC drivers to allow Java programs to access data in the data source. Sun maintains a searchable database of available drivers, and a quick query shows 36 drivers are available to access Oracle databases. The search results show the driver type, and the version of the JDBC specification the driver supports. J2SE 1.4.1 and J2EE 1.3 both include JDBC 3.0.

There are four types of JDBC drivers. When selecting a driver, make sure the selected driver is of the driver type that best meets your individual needs.

• Map to another data access API The JDBC driver maps calls to another data access API, such as Open Database Connectivity (ODBC). One such Type 1 driver is the JDBC-ODBC Bridge that is included in the Java SDK. On Windows platforms, the Bridge can provide JDBC access to data sources using an existing ODBC connection. Due to its limited feature set, only use the JDBC-ODBC Bridge for experimenting with JDBC, or if no other JDBC driver is available.

• Native-API partly Java The JDBC driver converts the SQL statements to the equivalent calls for the native API on the client, such as the Oracle Call Interface (OCI).

• Net-protocol fully Java Net server middleware allows the same Java clients to access data in different data sources. The JDBC driver converts the SQL statements into a DBMS- independent protocol, which the middleware server converts to the specific DBMS protocol.

• Native-protocol fully java Allows for direct calls from a client to a server without requiring the data source native libraries, such as SQL*Net, to be installed on the client.

Oracle JDBC Drivers

Oracle Database 10 g provides four JDBC drivers, three of which were available in prior Oracle releases. According to the Oracle documentation, Oracle has restructured all JDBC drivers from previous releases to improve performance. The four drivers are

• OCI driver

• Thin driver

• Server-Side Thin driver

• Server-Side Internal driver

Oracle JDBC drivers are available to download from the Oracle Technology Network at http://otn.oracle.com. If you have installed the Oracle Database Client software, the drivers are in <ORACLE_HOME>/jdbc/lib directory. The common Oracle JDBC drivers are

• ojdbc14.jar Java SDK 1.4

• classes12.jar Java SDK 1.2 and 1.3

• classes111.jar JDK 1.1 (Not supported with Oracle 10 g )

In prior Oracle releases, the JDBC drivers were also available in ZIP archives. Starting with Oracle 10 g , all JDBC drivers are only available in Java Archive (JAR) format.

OCI Driver

The Oracle OCI driver, also known as the fat (or thick) driver, is a Type 2 driver. Client/server Java programs using the OCI driver must have Net8 and the appropriate shared libraries installed; therefore, the OCI driver is best suited for server-side applications such as servlets. For client applications using the OCI driver, Oracle recommends installing the Oracle Database client software instead of the shared libraries separately. The following is an example of using the OCI driver:

 Connection conn = DriverManager.getConnection   ("jdbc:oracle:oci:@myhost:1521:inst1", "scott", "tiger); 

Thin Driver

The Oracle Thin driver is a 100-percent Java Type 4 driver. The Thin driver is ideal for writing 100-percent pure Java applications, such as web-based and client-side applications where the client does not have Net8 installed. Instead, the Thin driver connects to the server using a protocol named TTC (Two-Task Common ”a presentation layer type that is used in a typical Oracle Net connection to provide character set and data type conversion between different character sets or formats on the client and server) that Oracle developed to access an Oracle database. Data access using the Thin driver is slower than that using the OCI driver, but the applications are smaller and do not require the client-side Net8 installation.

If you are writing an application that must be portable, and does not require the OCI-specific features such as support for networks other than TCP/IP, use the Thin driver. In addition, applets can only use the Thin driver. The following is an example of using the Thin driver:

 Connection conn = DriverManager.getConnection   ("jdbc:oracle:thin:@//myhost:1521/orcl", "scott", "tiger"); 

Server-Side Thin Driver

The Server-Side Thin driver is new with Oracle Database 10 g . It has the same functionality as the client Thin driver, but runs inside an Oracle database instead of a client machine. The benefit of this new driver is that it can access other Oracle databases, or different sessions within the same database.

Note  

The Server-Side Thin driver is only available through Java Stored Procedures (JSProcs).

Using the Server-Side Thin driver opens a socket connection, and therefore requires granting access to the java.net.SocketPermission object to the users whose accounts open connections using the driver. You should create a role to group the user accounts that can create the socket connections. For example:

 CREATE ROLE JDBCTHIN; CALL dbms_java.grant_permission(