Introduction to JDBC

Java Database Connectivity(JDBC) is the industry standard for database-independent connectivity between Java applets/applications and a broad range of SQL databases. All the benefits of "Write Once, Run Anywhere" equally apply to JDBC. The JDBC API def ines Java classes that represent database connections, SQL statements, result sets, database metadata, etc.
It allows a Java programmer to do three things:

Establish a connection to a database
Issue SQL statements
Process the results

The JDBC API is implemented via a driver manager that can support multiple drivers connecting to different databases. JDBC drivers can either be entirely written in Java so that they can be downloaded as part of an applet, or they can be implemented us ing native methods to bridge to existing database access libraries. (more on this later)

History of the development of JDBC

The JDBC Project was started in January, 1996 and the specification was frozen in June, 1996 to seek the input of industry database vendors to insure that JDBC would be widely accepted upon its release. JDBC draws heavily from the ANSI SQL-92 stand ard. This does not imply that a JDBC driver must implement every SQL-92 function, it may implement a subset of the whole. Now comes the confusing point; while JDBC draws heavily from SQL-92 it is based on the X/Open SQL Call Level Interface (CLI). If it’s easier, you can think of the CLI as a SQL wrapper. One should note here that Microsoft’s ODBC is also based on the X/Open SQL CLI.

Java Developer’s Kit (JDK)

This is about as simple as it gets: JDK1.1 implements JDBC1.0, and JDK1.2 will implement JDBC2.0. Remember, the JDBC API is for both using and creating low-level JDBC drivers. From the programmer’s point of view, JDBC is simply another package call ed java.sql.*. One exciting fact is that drivers necessary to connect to respective database can be loaded dynamically along with an applet.

The JDBC Core consists of seven (7) interfaces and two (2) classes. 90% of what you will need to do with a database will be found in these interfaces and classes. Every JDBC driver must implement the Driver interface. This is the class the Dr iverManager uses to locate a driver for the database Uniform Resource Locator (URL) you specify. (more on URLs later) The DriveManager class is part of JDBC runtime and it loads Driver objects and creates database Connection objects as y ou request. Very few clients need to use the DriverPropertyInfo class in order to discover and supply properties for connections.

Statement objects are used as containers to execute SQL statements on a connection. Programmers can allocate multiple Statement objects and reuse each one repeatedly to process many SQL statements. PreparedStatement and Callable Statement objects are important extensions to the Statement interface. A PreparedStatement object is used to precompile and then execute a statement multiple times. A CallableStatement object is used to represent and execute a sto red procedure. Both object types allow for passing parameters in an SQL statement.

A ResultSet object holds the rows returned by a database when you execute a query Statement. The object maintains a cursor that points to the current row of data. Numerous methods exist for manipulating data in the ResultSet object . A programmer can invoke the getMetaData method to obtain a ResultSetMetaData object which can obtain the types and properties of a result set.

JDBC2.0 offers the following improvements over JDBC1.0:

Scroll forward and backward in a result set or move to a specific row
Make updates to database tables using methods in the Java programming language instead of using SQL commands
Send multiple SQL statements to the database as a unit, or batch
Use the new SQL3 datatypes as column values

JDBC Architecture

Applications and Applets may access databases via JDBC using pure Java drivers as follows:

Direct-to Database Pure Java Driver: This type of driver converts JDBC calls into the network protocol used directly by DBMSs, allowing a direct call from the client machine to the DBMS server and providing a practical solution for intranet access.

Pure Java Driver for Database Middleware: This type of driver translates JDBC calls into the middleware vendor’s protocol, which is then translated to a DBMS protocol by a middleware server. The middleware provides connectivity to many different datab ases.

You may also use ODBC drivers and existing database client libraries as part of a JDBC connectivity solution:

JDBC-ODBC Bridge plus ODBC Driver: The Sun bridge product provides JDBC access via ODBC drivers. Both ODBC binary code and sometimes the database client code must be loaded on each machine that uses this driver.

Native-API Partly Java Driver: This style of driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix and the like. Requires that some binary code be loaded on each client machine.

Open Database Connectivity (ODBC) & JDBC/ODBC Bridge

JDBC and ODBC share a common parent in that both are based on the X/Open CLI for SQL. Although there are JDBC drivers cropping up for many databases, you can write database-aware Java programs using existing ODBC drivers. Javasoft and Intersolv hav e written a special JDBC driver—the JDBC/ODBC Bridge so that developers may use existing ODBC drivers in Java programs. The JDBC/ODBC Bridge requires pre-installation on the client or wherever the Java program is actually running because the Bridge must m ake native method calls to do the translation from ODBC to JDBC. This is also true for JDBC drivers that use native methods. Only 100% pure Java drivers can be downloaded across a network with a Java applet.

The JDBC/ODBC Bridge is actually a sophisticated JDBC Driver that does low-level translation to and from ODBC. Its like the Tower of Babel, but you can easily switch to a JDBC driver for a certain database when it becomes available. Few, if any changes to the underlying Java program code will be necessary.

How JDBC uses URL

The format for specifying a data source is an extended Uniform Resource Locator (URL), broadly defined as follows:

For example:

jdbc:odbc://shrike.depaul.edu:8080/my_database

The subprotocol is used by the JDBC drivers to identify themselves, and the DriverManager uses the subprotocol to match the proper driver to a specific subprotocol. A connection is attmepted when the DriverManager launches the DriverMa nager.getConnection method. Additional programming details are left to the reader as they are beyond the scope of this introduction.

Questions, Comments, Security Issues with JDBC

1) The JDBC 2.0 API is the latest update of JDBC. JDBC 2.0 contains lots of new features, including scrollable result sets. There are two parts to JDBC 2.0: the JDBC 2.0 Core API and the JDBC 2.0 Standard Extension API. The JDBC 2.0 Core API is include d in the JDK 1.2 release.

2) The JDBC-ODBC bridge that is included in the JDK 1.2 beta4 release doesn't support the JDBC 2.0 API. Sun and Intersolv are working to produce a new version of the bridge that does support the new features in the JDBC 2.0 API, such as scrollable resu lt sets.

3) Use of the JDBC-ODBC bridge from an untrusted applet running in a browser, such as Netscape Navigator, isn't allowed. The JDBC-ODBC bridge doesn't allow untrusted code to call it for security reasons. This is good because it means that an untrusted applet that is downloaded by the browser can't circumvent Java security by calling ODBC. Remember that ODBC is native code, so once ODBC is called, Java can't guarantee that a security violation won't occur. On the other hand, Pure Java JDBC drivers work well with applets. They are fully downloadable and do not require any client-side configuration.

Finally, we would like to note that it is possible to use the JDBC-ODBC bridge with applets that will be run in appletviewer since appletviewer assumes that applets are trusted. It is also possible to use the JDBC-ODBC bridge with applets that are run in the HotJava browser (available from Java Software), since HotJava provides an option to turn off applet security. In general, it is dangerous to turn applet security off, but it may be appropriate in certain controlled situations, such as for applets t hat will only be used in a secure intranet environment. Remember to exercise caution if you chose this option, and use an all-Java JDBC driver whenever possible to avoid security problems.

4) Most desktop databases currently require a JDBC solution that uses ODBC underneath. This is because the vendor's of these database products haven't implemented all-Java JDBC drivers. The best approach is to use a commercial JDBC driver that supports ODBC and the database you want to use. .

5) The JDBC-ODBC bridge from JavaSoft does not provide network access to desktop databases by itself. The JDBC-ODBC bridge loads ODBC as a local DLL, and typical ODBC drivers for desktop databases like Access aren't networked. The JDBC-ODBC bridge can be used together with the RMI-JDBC bridge , however, to access a desktop database like Access over the net. This RMI-JDBC-ODBC solution is free.

6) JDK 1.1 and the JDK 1.2 contain both JDBC and the JDBC-ODBC bridge. The JDK 1.2 contains the JDBC 2.0 Core API which is the latest version of JDBC. The versions of JDBC and the JDBC-ODBC bridge provided for separate download on the JDBC download pag e are only for use with the JDK 1.0.2.

7) Most ODBC 2.0 drivers should work with the Bridge. Since there is some variation in functionality between ODBC drivers, the functionality of the bridge may be affected. The bridge works with popular PC databases, such as Microsoft Access and FoxPro.

8) MicroSoft J++ does not support the JDBC-ODBC bridge since it doesn't implement the Java Native Interface (JNI). Any all-Java JDBC driver should work with J++, however.

9) The 'No suitable driver' error usually occurs during a call to DriverManager.getConnection(). The cause can be failing to load the appropriate JDBC driver before calling getConnection(), or specifying an invalid JDBC URL---one that isn't recognized by your JDBC driver. Your best bet is to check your JDBC driver's product documentation or contact your JDBC driver vendor if you suspect that the URL you are specifying is not being recognized by your JDBC driver. In addition, when using the JDBC-ODBC bridge this error can occur if one or more of the shared libraries needed by the bridge can't be loaded. If you think this the cause, check your configuration to be sure that the shared libraries are accessible to the bridge.

10)Sometimes the java.sql.DriverManager class isn’t found when a JDBC applet is run in a browser, like Netscape Navigator 3.0, that supports the JDK 1.0.2. Since the JDK1.0.2 doesn't contain JDBC, the DriverManager class typically isn't found by the Java virtual machine running in the browser. Here's a solution that doesn't require any additional configuration of your web clients. Remember that classes in the java.* packages cannot be dowloaded by most browsers for security reasons. Because of this, many vendors of all-java JDBC drivers s upply versions of the java.sql.* classes that have been renamed to jdbc.sql.*, along with a version of their driver that uses these modified classes. If you import jdbc.sql.* in your applet code (instead of java.sql.*), and add the jdbc.sql.* classes prov ided by your JDBC driver vendor to your applet's codebase, then all of the JDBC classes needed by the applet can be downloaded by the browser at runtime, including the DriverManager class. This solution will allow your applet to work in any client browser that supports the JDK 1.0.2. Your applet will also work in browsers that support the JDK 1.1, although you may want to switch to the JDK 1.1 classes for performance reasons. Also, ke ep in mind that the solution outlined here is just an example, and that other solutions are possible.

11) It is possible that calling the method class.forName() fails to load your JDBC driver. This is a bug in the JDK 1.1.x that can cause Class.forName() to fail. A work around is to explicitly call: DriverManager.registerDriver(new YourDriverClass()). The exact problem in the JDK is a race condition in the class loader that prevents the static section of code in the driver class from executing and registering the driver with the driver manager.

12) For security reasons, browsers will not download java.* packages. In order to use JDBC with browsers that have not been upgraded to JDK1.1, we recommend the java.sql and java.math packages be renamed jdbc.sql and jdbc.math. Most vendors supplying P ure Java JDBC drivers already provide versions of these renamed packages. When JDK 1.1 support has been added to your browser, you should convert your applets back to the java.* package names.

Summary (Why is JDBC the best!)

JDBC Advantages

Businesses can leverage existing enterprise data with JDBC by continuing to use their installed databases and access information easily-even if it's stored on different database management systems.

Businesses benefit from reduced development time. The combination of Java and JDBC makes application development easy and economical. JDBC is simple to learn, easy to deploy and inexpensive to maintain.

With JDBC, there is zero configuration for network computers because configuration is required on the client side, since the connection is completely defined by the JDBC URL. This supports the network computing paradigm and centralizes software mai ntenance.

JDBC Key Features

JDBC meta-data access enables the development of sophisticated applications that need to understand the underlying facilities and capabilities of the specific database connection.

A Pure JDBC driver doesn't require special installation; it's automatically downloaded as part of the applet that makes the JDBC calls.

JDBC exploits the advantages of Internet-standard URLs to identify database connections.

As a core part of the Java Platform, JDBC is available anywhere that the platform is. This means that Java applications can truly write database applications once and access data anywhere.

Credit: Javasoft , Patrick Grage