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.
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.
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.
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 |
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.