Java Database Connectivity (JDBC)

Java Database Connectivity was proposed (and implemented) by the Sun Corporation to provide its popular Java programming language (some would say "programming platform," but we will not fight holy wars here) with a uniform set of database access methods. Using JDBC you can connect to a data source (which might not necessarily be an RDBMS data source), execute SQL statements (i.e., forward them to the SQL processing software — like RDBMS — for execution), and process the output of these SQL statements. The most current JDBC standard is JDBC v.3.0 standard, which is included with J2SE (Java 2 Standard Edition) and J2EE (Java 2 Enterprise Edition), version 1.4.

Tip 

The difference between J2SE and J2EE is in scope and capabilities. Both are standards for developing applications, applets in Java programming language; but J2EE adds a component-based model (EJB — Enterprise Java Beans) for building enterprise level multi-tier applications.

JDBC provides two sets of interfaces: one for the database application programmers, and one for the database driver programmers, and its core set is included with Java2 Platform.

Note 

Many industry heavyweights support the JDBC standard for their products; among the others the following RDBMS vendors are IBM, Oracle, Gupta Technologies, Borland, Compaq, Pervasive Software, and POET software. Microsoft supports JDBC/ODBC bridging with its recently released SQL Server 2000 driver for JDBC.

Figure 16-5 illustrates generic RDBMS access using JDBS drivers.

Figure 16-5: illustrates generic RDBMS access using JDBS drivers.

The JDBC functionality — basic and extended — is contained in the java.sql package (shipped with Java2 Core) and javax.sql package, listed in Table 16-2.

Table 16-2: JDBC 3.0 Features

Feature	Package	Description
Enhanced resultset manipulations: scrollable set and updateable resultset	java.sql	Facilitates programmatic manipulation of the resultsets fetched with SQL statements or returned by stored procedures.
New Data Types Support	java.sql	Ability to manipulate with structured data types like CLOB and BLOB server-side, without transferring them over to the client application.
Batch Updates	java.sql	Ability to combine SQL statements into a single batch, to reduce network traffic and control overhead.
Transactional Support	java.sql	Declares, rolls back, and commits transactions on the client-side, including SAVEPOINT support.
JNDI (Java Naming and Directory Interface) Support	javax.sql	Facilitates deployment of Java applications by abstracting JDBC driver into independently managed components.
Connection Pooling	javax.sql	Maintains a cache of the database connections for future reuse.
Distributed Transactions Support	javax.sql	Provides support for distributed transactions processing (see Chapter 7).
Java Beans	javax.sql	Encapsulates data and functionality (rowset objects).
Reference to JDBC Rowset	javax.sql	Encapsulates a driver as a Java Bean component.
Statement Pooling	javax.sql	Pools parameterized statements for reuse.

There are four types of the JDBC drivers available today:

• JDBC-ODBC drivers (Type 1). These provide JDBC API access on top of the standard ODBC drivers. Essentially, they add an additional level of communications slowing the whole process down. The advantage of using this approach is that the current infrastructure can be used for the Java-based applications.

• Native API Partly Java technology-enabled drivers (Type 2). These drivers translate JDBC calls into direct calls to the RDBMS native interface (CLI).

• Net-Protocol Fully Java technology-enabled drivers (Type 3). These drivers translate JDBC calls into a DBMS independent network protocol, which is sub-sequently translated into a DBMS-specific protocol by some middleware server.

• Native-Protocol Fully Java technology-enabled drivers (Type 4). These drivers translate JDBC calls into a DATABASE-specific network protocol directly.

  Tip 

  Drivers usually implement a subset of the functions and features available in RDBMS; check for the compliance level, and do not assume that every feature will be supported. Vendor's drivers are usually most complete.

Table 16-3 lists several JDBC drivers of different types for the big three RDBMS vendors: Oracle, IBM DB2 UDB, and Microsoft SQL Server 2000.

Table 16-3: Selected JDBC Drivers Vendors

Vendor	Oracle 9i	IBM DB2 UDB	Microsoft SQL Server 2000	Remarks
Atinav Inc.	n/a	n/a	ü	Types 3, 4
Attunity	ü	ü	n/a	Types 2, 2
Bea Weblogic	ü	n/a	ü	Types 2, 3
Computer Associates	ü	ü	ü	Type 3
CONNX Solutions	ü	ü	ü	Type 3
DataDirect Technologies	ü	ü	ü	Types 3, 4
Recital Corp.	ü	ü	n/a	Type 3
OpenLink Inc.	ü	ü	ü	Types 1, 3
Oracle	ü	n/a	n/a	Types 2, 3, 4
Object Industries	ü	ü	ü	Types 1, 2, 3, 4
Microsoft	n/a	n/a	ü	Type 4
IBM	ü	n/a	n/a	Types 3, 4
IDS Software	ü	ü	ü	Types 3, 4

JDBC Connector and Java Data Objects (JDO)

Sun Corporation has introduced the JDBC Connector to facilitate integration of the databases with Java 2 Enterprise Edition (J2EE) application servers. It forms an intermediate layer between the JDBC driver and an applications server.

Another development from Sun is the Java Data Object. It is a complementary technology that provides object-relational mappings between Java classes and object databases. From the Java programmer's standpoint, it provides a standard, familiar, SQL-free approach to the RDBMS.

JDO integrates well into Enterprise Java Beans technology and is part of the Java Community Process, which comes close to giving it the status of being an open standard.

In addition to the JDBC standard, Oracle provides enhanced support for Java technology through JServer, which is Oracle's own Java Virtual Machine (JVM). It runs within RDBMS address space and provides for Java to be used for stored procedures (instead of PL/SQL). For the outside world, Oracle complies with "regular" JDBC access and adds a couple of its own: JDBC OCI drivers accessing RDBMS directly from Java code through Oracle Call Interface (discussed later in this chapter), and JDBC KPRB drivers for executing Java-stored procedures and database JSP (Java Server Pages).

The following code snippet illustrates the use of the JDBC thin driver to establish a connection to Oracle:

import java.sql class JDBC_access { public static void main (Strings args[ ] ) throws SQL Exception { DriverManager.registerDriver (new oracle.jdbc.driver.Oracledriver()); // //connecting string includes //machine name (@alexhome) //port (1522) //SID (Oracle's ID) ORA_TEST //User ID (acme), Password (acme) // Connection objConn = DriverManager.getConnection ("jdbc:oracle:thin:@alexhome:1522:ORA_TEST", "acme", "acme"); // // Create a statement object // using the connection object // Statement objStatement = objConn.CreateStatement(); // // assemble SQl statement // String s_SQL = "SELECT * FROM customer"; // // retrieve the result set // ResultSet objRecordset = s_SQL.ExecuteQuery(s_SQL); // // scroll the recordset object // while (objRecordset.next()) // // print the fourth column value // onto the standard output // System.out.println(objRecordset.getString(4); // // clean up // close the connection // all other objects will be dereferenced // automatically // objConnection.Close(); } }

The syntax for any other RDBMS accessible through JDBC is essentially the same, though some differences may appear in the connection string and, of course, you need an RDBMS-specific JDBC driver installed on your machine.