Java and DataBases

    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");
      }

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?

    JDBC versus ODBC and other APIs

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

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.

    Two-tier and Three-tier Models

• the top layer is the JDBC API. The API communicates with the JDBC manager driver API, sending it the various SQL statements.

• the manager should communicate with the various third part drivers that actually connect to the database and return the information from the query or perform the action specified by the query

The JDBC API supports both two-tier and three-tier models for database access.

In the two-tier model, a Java applet or application talks directly to the database. This requires a JDBC driver that can communicate with the particular database management system being accessed. A user's SQL statements are delivered to the database, and the results of those statements are sent back to the user. The database may be located on another machine to which the user is connected via a network. This is referred to as a client/server configuration, with the user's machine as the client, and the machine housing the database as the server. The network can be an intranet, which, for example, connects employees within a corporation, or it can be the Internet.

Three tier model means Java application calls on a middleware layer that in turn accesses the data (e.g. RMI)

Specifically, in the three-tier model, commands are sent to a "middle tier" of services, which then send SQL statements to the database. The database processes the SQL statements and sends the results back to the middle tier, which then sends them to the user. MIS directors find the three-tier model very attractive because the middle tier makes it possible to maintain control over access and the kinds of updates that can be made to corporate data. Another advantage is that when there is a middle tier, the user can employ an easy-to-use higher-level API which is translated by the middle tier into the appropriate low-level calls. Finally, in many cases the three-tier architecture can provide performance advantages.

Until now the middle tier has typically been written in languages such as C or C++, which offer fast performance. However, with the introduction of optimizing compilers that translate Java bytecode into efficient machine-specific code, it is becoming practical to implement the middle tier in Java. This is a big plus, making it possible to take advantage of Java's robustness, multithreading, and security features. JDBC is important to allow database access from a Java middle tier.

ODBC

ODBC, a C-based interface to SQL-based database engines, provides a consistent interface for communicating with a database and for accessing database metadata (information about the database system vendor, how the data is stored, and so on). Individual vendors provide specific drivers or "bridges" to their particular database management system. Consequently, thanks to ODBC and SQL, you can connect to a database and manipulate it in a standard way. It is no surprise that, although ODBC began as a PC standard, it has become nearly an industry standard.

Though SQL is well suited for manipulating databases, it is unsuitable as a general application language and programmers use it primarily as a means of communicating with databases--another language is needed to feed SQL statements to a database and process results for visual display or report generation. Unfortunately, you cannot easily write a program that will run on multiple platforms even though the database connectivity standardization issue has been largely resolved. Enter Java.

Since ODBC drivers exist on most databases, JavaSoft decided to write a JDBC to ODBC bridge (JdbcOdbc.ddl). To make a connection between such a database and Java, one needs to install the database's ODBC driver and the JDBC to ODBC bridge.

Java application	bridge (dll's)	RDBMS
JDBC	ODBC

The JDBC-ODBC bridge driver is just one of four types of drivers available to support JDBC connectivity. ( also). The JDBC API is included in both J2SE and J2EE releases.

Types of JDBC drivers

1. The JDBC-ODBC bridge provides JDBC access via most ODBC drivers. Note that some ODBC binary code and in many cases database client code must be loaded on each client machine that uses this driver, so this kind of driver is most appropriate on a corporate network, or for application server code written in Java in a 3-tier architecture.
2. A native-API partly-Java driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.
3. A net-protocol all-Java driver translates JDBC calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server. This net server middleware is able to connect its all Java clients to many different databases. The specific protocol used depends on the vendor. In general, this is the most flexible JDBC alternative. It is likely that all vendors of this solution will provide products suitable for Intranet use. In order for these products to also support Internet access they must handle the additional requirements for security, access through firewalls, etc., that the Web imposes. Several vendors are adding JDBC drivers to their existing database middleware products.
4. A native-protocol all-Java driver converts JDBC calls into the network protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver. Several database vendors have these in progress.

Table 1.1 shows the four categories and their properties. The table uses the following definitions for types of network connections:

• Direct -a connection that a JDBC client makes directly to the DBMS server, which may be remote
• Indirect--a connection that a JDBC client makes to a middleware process that acts as a bridge to the DBMS server

  DRIVER CATEGORY	ALL JAVA	NETWORK CONNECTION
  1 - JDBC-ODBC Bridge	No	Direct
  2 - Native API as basis	No	Direct
  3 - JDBC-Net	client Yes server Maybe	Indirect
  4 - Native protocol as basis	Yes	Direct

Most major vendors are currently developing native JDBC drivers to remove the extra layer between JDBC and the DB

    SQL Conformance

One way the JDBC API deals with this problem is to allow any query string to be passed through to an underlying DBMS driver. This means that an application is free to use as much SQL functionality as desired, but it runs the risk of receiving an error on some DBMSs. In fact, an application query need not even be SQL, or it may be a specialized derivative of SQL designed for specific DBMSs (for document or image queries, for example).

A second way JDBC deals with problems of SQL conformance is to provide ODBC-style escape clauses, which are discussed in the Guide section "SQL Escape Syntax in Statement Objects."

The escape syntax provides a standard JDBC syntax for several of the more common areas of SQL divergence. For example, there are escapes for date literals and for stored procedure calls.

For complex applications, JDBC deals with SQL conformance in a third way. It provides descriptive information about the DBMS by means of the DatabaseMetaData interface so that applications can adapt to the requirements and capabilities of each DBMS.

Because the JDBC API will be used as a base API for developing higher-level database access tools and APIs, it also has to address the problem of conformance for anything built on it. The designation "JDBC COMPLIANT^TM" was created to set a standard level of JDBC functionality on which users can rely. In order to use this designation, a driver must support at least ANSI SQL-2 Entry Level. (ANSI SQL-2 refers to the standards adopted by the American National Standards Institute in 1992. Entry Level refers to a specific list of SQL capabilities.) Driver developers can ascertain that their drivers meet these standards by using the test suite available with the JDBC API.

The "JDBC COMPLIANT^TM" designation indicates that a vendor's JDBC implementation has passed the conformance tests provided by JavaSoft. These conformance tests check for the existence of all of the classes and methods defined in the JDBC API, and check as much as possible that the SQL Entry Level functionality is available. Such tests are not exhaustive, of course, and JavaSoft is not currently branding vendor implementations, but this compliance definition provides some degree of confidence in a JDBC implementation. With wider and wider acceptance of the JDBC API by database vendors, connectivity vendors, Internet service vendors, and application writers, JDBC is quickly becoming the standard for Java database access.

    JavaSoft Framework

• the JDBC driver manager,
  
  
• the JDBC driver test suite, and
  
  
• the JDBC-ODBC bridge.
  
  

The JDBC driver test suite provides some confidence that JDBC drivers will run your program. Only drivers that pass the JDBC driver test suite can be designated JDBC COMPLIANT^TM.

The JDBC-ODBC bridge allows ODBC drivers to be used as JDBC drivers. It was implemented as a way to get JDBC off the ground quickly, and long term will provide a way to access some of the less popular DBMSs if JDBC drivers are not implemented for them.

A Complete Example

• Creating a database. You can either create the database outside of Java, via tools supplied by the database vendor, or via SQL statements fed to the database from a Java program.
• Connecting to an ODBC data source. An ODBC data source is a database that is registered with the ODBC driver. In Java you can use either the JDBC to ODBC bridge, or JDBC and a vendor-specific bridge to connect to the datasource.
• Inserting information into a database. Again, you can either enter data outside of Java, using database-specific tools, or with SQL statements sent by a Java program.
• Selectively retrieving information. You use SQL commands from Java to get results and then use Java to display or manipulate that data.

Creating a Database and Connecting:

Getting Started: local copy and Java Developer copy from the Short Course. I will show this example

An exercise to help you set up a system (for the tutorial exercises... but useful to see). There are basically two steps: The first is to create an ODBC-accessible datasource to use, and the second is register it with your system.

Found it: For Windows 2000, the 32bit ODBC Data Source Adminstrator executable that use to be in the Control Panel is now in the subdirectory Administrative tools under the Control Panel and is now called Data Sources (ODBC)
• SUN tutorial with new DB stuff in the JAVA 1.2 release

Next, the following Database URL, Making the connection, Creating Statements, Executing Queries, ResultSet

Example: a small example to look at and work with. It will be used in the next notes

PS. See also the JDO