JDBC

Description

JDBC is a programming-level interface for communicating with databases in a uniform manner similar to Microsoft's Open Database Connectivity (ODBC), which is the standard for WINTEL environments. The JDBC standard itself is based on the X/Open SQL Call Level Interface, the same basis as that of ODBC. This is one of the reasons why the initial development of JDBC has progressed so fast. [1] [14]

JDBC enables Java developers to connect to any SQL compliant database, send SQL statements, and process return messages and data. As a point of interest, JDBC is a trademarked name and is not an acronym; nevertheless, JDBC is often thought of as standing for "Java Database Connectivity". JDBC consists of a set of classes and interfaces written in the Java programming language. This makes it possible to write database applications using a pure Java API. This allows much closer interaction than you would get by embedding C language function calls in Java programs using the Java Native Interface (JNI). [3] [7]

The following code fragment gives a basic example of these three steps: [3]

    Connection con = DriverManager.getConnection (

    "jdbc:odbc:wombat", "login", "password");

    Statement stmt = con.createStatement();

    ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");

    while (rs.next()) {

       int x = getInt("a");

       String s = getString("b");

       float f = getFloat("c");

    }

Sun Microsystems has described JDBC as a "low-level" interface, meaning it is used to invoke (or "call") SQL queries directly. It works very well in this capacity and is easier to use than other database connectivity APIs. JDBC, however, was designed also to be a base upon which to build higher-level interfaces and tools. A higher-level interface would be application "user-friendly," therefore it would be a more convenient API that is translated behind the scenes into the specific interface such as JDBC. [3]

Sun Microsystems also mentions two kinds of higher-level APIs that are under development on top of JDBC:

1. Embedded SQL for Java. At least one vendor plans to build embedded SQL for Java. JDBC requires that the SQL statements be passed as Strings to Java methods. An embedded SQL preprocessor allows a programmer to mix SQL statements directly with Java: for example, a Java variable can be used in a SQL statement to receive or provide SQL values. The embedded SQL preprocessor then translates this Java/SQL mix into Java with JDBC calls. [3]
2. Direct mapping of relational database tables to Java classes. JavaSoft and others have announced plans to implement direct mapping. In this "object/relational" mapping, each row of the table becomes an instance of that class, and each column value corresponds to an attribute of that instance. Java programmers can operate directly on Java objects; the required SQL calls to fetch and store data are automatically generated "beneath the covers." More sophisticated mappings are also provided, for example, where rows of multiple tables are combined in a Java class. [3]

There are also other types of implementations for JDBC:

1. Serialized object storage. Serialization is new in JDK 1.1 and provides a mechanism for applications to send objects through a stream. This stream can be a file stream, network stream, or a database stream. This process will allow an application to save an object entirely not just the data within it. This can be performed with standard SQL databases that have a "blob" or "binary" data field. [8]
2. Middleware services which provide a higher level interface to business objects. A middleware service would be responsible for the actual data access via JDBC and provide a simpler application level interface. Many of the complexities of database access and error handling can then be hidden from the application itself. [3] [8]

JDBC is available as part of the JDK 1.1 release but there is also an add-on kit available for JDK 1.0.2. The initial JDBC specification was released on March 8.1996 as a draft for open analysis [1]

Features

JDBC is a set of object classes for processing transactions with databases and are written completely in Java. JDBC enables the programmer to maintain the security, robustness, and portability that make Java so exciting. However, to promote its use and maintain some level of backward compatibility, JDBC can be implemented on top of ODBC and other common SQL APIs from vendors. To compensate for the features of specific databases, vendors are allowed to extend the basic statement classes for their own designs. However, the basic statement classes must be supported at minimum with all databases for JDBC compliance. [1]

To access databases, JDBC borrowed the format of the Uniform Resource Locator (URL) syntax. [1]

A complete URL consists of several elements as outlined in the example below:

http://host.domain.com:80/directory/directory/filename

The word "http" indicates the URL schema; this example indicates that it is of the HyperText Transport Protocol. The next group "host.domain.com" is the unique Internet hostname as assigned to each machine. The number "80" indicates the Internet host port number at which the service is located; port number 80 is the default port for http services. The set of directories then outline where in the hierarchy on that service the "filename" is located and, of course, the filename indicates the actual data item to retrieve.

JDBC's URL structure is very similar:

jdbc:odbc://host.domain.com:400/databasefile

Note that in addition to the main schema, JDBC, this structure also includes a subprotocol for ODBC. The subprotocol indicates how the JDBC Manager will access the database; in this case, the database file is accessed through the JDBC-ODBC bridge. The rest is the same as for regular URLs, indicating the hostname, port, and database location and name.

JDBC consists of three main layers: [1] [9]

1. JDBC API: support application to JDBC manager communications
2. JDBC driver manager: support JDBC manager to driver implementation
3. The driver: which handles the driver to the DBMS

Figure 1. The JDBC API Layers

JDBC Drivers

There are 4 basic types of JDBC drivers: [2] [3]

1. JDBC-ODBC bridge: provides JDBC access using ODBC as the communications pipe.
2. Native API, partly Java Driver: converts JDBC calls directly into calls on the client to the native database API. This requires binary code on the client machine.
3. JDBC Net Driver: translates JDBC into DBMS independent protocol. This independent protocol is translated by a server into native DBMS calls. Generally deployed using a middleware server.
4. Native Protocol - Pure Java: converts JDBC into DBMS native network calls.

JDBC Classes

In terms of the Java classes, the core JDBC API consists of: [1] [10]

java.sql.DriverManager - matches the driver manager to the type specified in the URL;

java.sql.Connection - connection-specific data structures;

java.sql.Statement - container class for embedded SQL statements;

Two-sub-types are:

• java.sql.PreparedStatement, for executing pre-compiled SQL statements
• java.sql.CallableStatement, for executing a call to database stored procedures

java.sql.ResultSet - access control to results of a statement. [1]

A simple SELECT statement can be realized with JDBC as follows. [2]

try {

    Connection con = DriverManager.getConnection (

        "jdbc:mydriver:mydbserver", "userid", "password");

    Statement s = con.createStatement();

    ResultSet r = stmt.executeQuery("SELECT name, age, sex FROM Persons");

    while (r.next()) {    //for all rows of the query result do

        String name = getString("name");

        Int age = getInteger("age");

        boolean sex = getBoolean("sex");

        System.out.println("Name: "+name+", Age: "+age+", Sex: "+sex");

    }

    con.close();   //close database connection

} catch (SQLException e) {

    System.out.println("An error occurred!");

}

A ResultSet contains the complete result (all rows) of a query. Therefore, a cursor is available which points to the current row of data. Initially the cursor is positioned before the first row. The method next() moves the cursor to the next row. There is no corresponding previous() method, therefore the result set can only be read once. Several get...() methods are available to read the data of the current row, or getObject() can be used if the datatype is unknown during runtime. [2] [10]

JDBC Data Types

The JDBC datatypes cover the basic and most used datatypes of common RDBMS. The next table shows their properties and the corresponding Java datatypes. Besides converting JDBC to vendor specific calls, the DBMS vendor specific data types must be converted to Java types. The following table shows the data types supported in JDBC. [2] [14]

These data types are defined in the JDBC class java.sql.Types and can be used to check a specific items type with the following code snippet:

int currentType;

...

if (currentType == Types.VARCHAR)

    System.out.println("current type is VARCHAR!");

In summary, JDBC follows an established specification and tried implementation for database-neutral communications from applications. The bridging solution to current ODBC systems allow users to maintain compatibility with older systems that may not have JDBC counterparts. Leading vendors are working actively on getting JDBC components for their databases out to market in a relatively short period of time. [1] [3]

JDBC vs. ODBC

At this point, Microsoft's ODBC (Open DataBase Connectivity) API is probably the most widely used programming interface for accessing relational databases. It offers the ability to connect to almost all databases on almost all platforms. So why not just use ODBC from Java? [3]

The answer is you can use ODBC from Java, but this is best done with the help of JDBC in the form of the JDBC-ODBC Bridge. The question now becomes, "Why do you need JDBC?" There are several answers to this question: [3]

1. ODBC is not appropriate for direct use from Java because it uses a C interface. Calls from Java to native C code have a number of drawbacks in the security, implementation, robustness, and automatic portability of applications.
2. A literal translation of the ODBC C API into a Java API would not be desirable. For example, Java has no pointers, and ODBC makes copious use of them, including the notoriously error-prone generic pointer "void *". You can think of JDBC as ODBC translated into an object-oriented interface that is natural for Java programmers.
3. ODBC is hard to learn. It mixes simple and advanced features together, and it has complex options even for simple queries. JDBC, on the other hand, was designed to keep simple things simple while allowing more advanced capabilities where required.
4. A Java API like JDBC is needed in order to enable a "pure Java" solution. When ODBC is used, the ODBC driver manager and drivers must be manually installed on every client machine. When the JDBC driver is written completely in Java, however, JDBC code is automatically installable, portable, and secure on all Java platforms from network computers to mainframes.

In summary, the JDBC API is a natural Java interface to the basic SQL abstractions and concepts. It builds on ODBC rather than starting from scratch, so programmers familiar with ODBC will find it very easy to learn JDBC. The big difference is that JDBC builds on and reinforces the style and virtues of Java, and, of course, it is easy to use. [3]