| Downloads |
|---|
| insper.zip |
At the Microsoft Professional Developer's Conference in March 1996, Microsoft hailed a new era of software development that will concentrate on intranets, the Web, ActiveX, HTML, applets, browsers, and Java. Microsoft emphasized the integration of tool sets for the various versions of Windows and Internet/intranet application development. It also revealed a unified shell and browser that may become a universal client (Internet Explorer--IE--4.0).
If you don't think Microsoft's new direction is relevant to your situation, you need to know that surveys show most organizations will build intranets. As a result, developers face connecting databases to supply content to thousands of Web browser users, even where those databases are not accessible from the Internet. To meet this need, more and more developers are relying on the Java programming language to access SQL databases (for information about the benefits of Java, see the sidebar, "Programming with Java," page 111).
In this article, I describe several aspects of programming SQL databases with Java, including the Open Database Connectivity (ODBC) and Java Database Connectivity (JDBC) APIs. A simple Java program illustrates how to insert data into an ODBC data source. You'll learn how to add interoperability to your Java database programs with adaptive programming, and how to serve many concurrent database users by writing multitier applications in Java.
The Basics of Java
Java is well known for creating Web applets that you download with HTML
pages, but you can also use it to program applications, including SQL
applications. Java programs are of two types. Java applets are
components embedded in Web pages that a browser executes. Java applications
are main programs that the Java interpreter, the Java virtual machine, executes.
Java supports a distributed, client/server computing model, and today's
Web-orientation exemplifies this model. Applets run as clients and conform to
strict client security rules. Applications usually execute at a server, but they
can run anywhere a Java virtual machine exists. In a typical scenario, a Windows
NT server can support PC, Macintosh, OS/2, and other clients. To embed an applet
in an HTML document, you specify an
As an Object-Oriented Programming (OOP) language, Java lets you write programs that use inheritance, encapsulation, and data hiding. Java includes I/O streams, exception handling, and sockets for client/server communication.
The Java architecture includes Unicode strings and characters and a security model that makes developing secure clients easier. Java includes packages that are analogous to application frameworks, which you use with C++ compilers. Java's built-in support for threads also simplifies developing multithreaded clients that can exploit ODBC asynchronous processing mode and high-performance JDBC drivers. Java 1.1, released in December 1996, includes Java Beans (interfaces for interoperable components), Remote Method Invocation (RMI), and other interfaces.
ODBC and JDBC
Multidatabase APIs have gained favor with developers working with SQL
databases. Microsoft's ODBC and JavaSoft's JDBC let you use one API to write
programs that operate on various SQL databases. Java developers can also use
Microsoft data access APIs where the target execution environment is 32-bit
Windows. Presently, ODBC or JDBC is the best choice for developing multiplatform
programs. Let's examine ODBC and JDBC to see what that choice involves.
ODBC is the most widely used call-level interface for accessing SQL databases. JDBC is a new API that provides an object layer that works with ODBC drivers to abstract SQL programming for Java developers.
The ODBC and JDBC architectures include loadable database drivers and a driver manager. Database drivers are conceptually similar to printer drivers because database drivers let you expand a program's functionality without rewriting its source code. The database drivers are libraries that the driver manager invokes when your program connects to an ODBC or JDBC data source. Your program can use drivers that work with a specific database management system (DBMS), such as Oracle, or drivers that connect to ODBC and JDBC servers. Although these servers can connect to multiple data sources, clients use only one driver to connect to the server. This server-based architecture produces a thinner client by replacing multiple drivers, client libraries, and protocol stacks with one driver and network transport. When your code uses ODBC or JDBC, a driver manager validates the arguments in your call and loads the appropriate driver for the data source to which you are connecting. The UNIX and Macintosh versions use shared libraries for the driver manager and drivers, whereas NT, other Windows versions, and OS/2 use DLLs.
Dozens of ODBC drivers already exist, so INTERSOLV developed bridge software for JavaSoft to let JDBC applications use ODBC drivers. Figure 1 illustrates the JDBC architecture that includes drivers, a driver manager, and a bridge for accessing ODBC data sources.
All ODBC and JDBC data access uses SQL queries to define and manipulate database tables. ODBC and JDBC drivers understand the same SQL (ANSI SQL with X/Open escape clauses) and models for query preparation and execution.
Java's raison d'être is portability, so the choice of using JDBC or ODBC involves trade-offs between portability and features. JDBC doesn't support ODBC features such as binding, bookmarks, and scrollable cursors. However, JDBC simplifies writing portable programs and portable, downloadable drivers.
Java programmers deciding between ODBC and JDBC can hedge their bets by using a Java toolkit that works with both. XDB Systems's JetConnect toolkit let's you write Java applets and applications that work with ODBC and JDBC drivers. Java programs can use JetConnect to access local drivers and databases or operate in a client/server configuration to access databases through Web servers. Figure 2 illustrates the JetConnect client/server architecture, where a Java applet connects to ODBC databases through a Web server running a daemon or NT service. JetConnect's classes encapsulate ODBC handles and all ODBC 2.x functions. JetConnect also includes an environment object (DbEnv), connection objects (DbDbc), statement objects (DbStmt), exceptions (DbException), and classes that encapsulate SQL data types. Let's look at a simple Java program that uses JetConnect to insert data into an ODBC data source.
Inserting Data with Java
I've written a simple Java program, insper.java, to demonstrate how to use a
Java application to create tables and insert contact information. You can use it
as a model for low-volume tables of information such as area codes, states, and
postal abbreviations. insper inserts data into tables in an ODBC data source
using SQL and JetConnect (you can download the complete source code to
insper.java from the Windows NT Magazine Web site at
http://www.winntmag.com). Listing 1 shows the first part of the program. If
you've used Visual Basic (VB), C++, or another language to program with the ODBC
API, you'll recognize the program flow. At the top of the program, insper
imports the JetConnect classes. insper prompts the user for the ODBC
data source name, user ID, and password for this application. Because insper is
an application, you run it with JavaSoft's Java Developer's Kit (JDK) Java
interpreter by typing
java insper
at the command line instead of invoking the application from a browser.
insper instantiates environment and connection objects and invokes methods that call the ODBC functions to allocate an environment and connection handle. After insper has a valid connection, it repeats the process to allocate a statement handle. If the program has a valid statement handle, it executes a SQL CREATE TABLE statement to create the Per table. When the program finishes creating tables, it executes an INSERT statement to insert a row of data in the Per table.
Because the INSERT query follows the prepare-and-execute model, it uses parameters when compiling the query, as you see in Listing 2. The question marks in this listing are markers that indicate the program will substitute a value when the query executes. After executing the SQLPrepare logic, the program instantiates new objects that correspond to the columns to be inserted. It binds them as parameters to signify that the program will supply the data when the query executes. The program then sets the data values to be used and executes (SQLExecute) the query.
Although Java provides cross-platform portability, you'll find that Java alone doesn't guarantee that ODBC or JDBC applications will be interoperable (the ability to operate against heterogeneous databases without any need for recoding) across data sources. Fortunately, adaptive programming lets you increase the interoperability of ODBC and JDBC programs, including those you write in Java.
Adding Interoperability with Adaptive Programming
SQL products don't implement a uniform feature set, SQL syntax, or data
types. ODBC and JDBC can report at runtime which features and types a particular
DBMS or driver supports. Because Java lets you write portable clients, it can be
a good language for writing client/server database applications. Java lets you
develop a portable front end or client side of a client/server database
application. However, if you want a client that is not affected by changes at
the back end, or server side, you need to avoid hard-coded logic about features
and data types. You can add this interoperability using adaptive programming. In
simple terms, adaptive programs use execution time techniques to bind to data
and select which features to use. Products such as Access use adaptive
techniques, although Microsoft visual developer tools often require a programmer
to use ODBC API calls when writing interoperable SQL programs. JDBC implements a
different introspection model than ODBC, but the concept of execution time
adaptation is applicable to either API.
Whether you need these techniques depends on program requirements and whether you need to make an application highly interoperable. If you need interoperability, you use ODBC functions and JDBC metadata classes to determine at runtime what features, SQL dialects, and data types the DBMS or database driver support. Adaptive programming techniques exploit these capabilities to craft code that can operate against various SQL back ends.
Features, SQL dialects, and data types often change as DBMS vendors roll out new versions. Adaptive techniques let your program work with a different DBMS without having to rewrite or recompile it. They also add flexibility to programs that work with only one type of SQL DBMS. Adaptive programs interrogate the driver about supported features. The JDBC DatabaseMetaData class and the ODBC SQLGetInfo function return feature information such as whether a DBMS supports outer joins, procedures, or specific SQL scalar functions.
The insper sample Java program uses simple data types. To use it as a model for adaptive programs, you need to modify the code so that it doesn't presume specific type names to use with the CREATE TABLE statement. ODBC includes the SQLGetTypeInfo function, which will report the available types for a data source and the native name for a type. The native name is the string you need to use in SQL statements. JDBC includes metadata classes that provide runtime reporting of types and other column information.
Multitier Applications
The Java database picture isn't complete without looking at distributed
objects and three-tier applications. To better serve several concurrent users,
developers are partitioning the logic of SQL client/server applications by
moving business rules and non-GUI logic to application servers. VB and Visual
C++ (VC++) use automation interfaces and the Distributed Component Object Model
(DCOM) to support this model for distributed applications (for information about
DCOM, see the sidebar, "DCOM in NT 5.0: Strategic Enhancements," by
Keith Pleas, page 76). Java's approach to distributed objects is twofold, and
each alternative has its supporters. Sun Microsystems's APIs let Java programs
invoke methods on remote servers and work with distributed objects that conform
to the Object Management Group's Common Object Request Broker Architecture
(CORBA).
Microsoft's Visual J++ (VJ++) and Java Software Developer's Kit (SDK) use DCOM and ActiveX, although they will support Java 1.1 APIs that work with CORBA objects. Sun Microsystems has a solution, Java Beans, that exposes a uniform component model that encapsulates heterogeneous components such as ActiveX and OpenDoc. Beans will run inside Java programs and ActiveX and OLE containers. Java licensees, including Microsoft, support JDBC and the other Java 1.1 APIs, but Microsoft sees DCOM, Object Linking and Embedding (OLE) DB, Active Data Object (ADO), and the new Microsoft Transaction Server as preferred solutions for multitier development.
Performance is an issue when you're executing Java bytecode, and compiler vendors have improved performance. Performance improvements, availability for most OSs, the ability to work with multidatabase APIs, and a three-tier architecture make Java an important database programming tool.
The ODBC and JDBC SQL APIs provide Java data access for multiple operating systems. If you are writing solely for NT and Windows 95, you can also use Data Access Objects (DAOs), Remote Data Objects (RDOs), and ADOs. With OLE DB, ADO provides connectivity to SQL and non-SQL data. Those APIs offer the advantage of being available for VJ++, VB, and VC++. However, unlike JDBC and ODBC, these APIs are not supported for the writing programs on Macintosh, Solaris, OS/2, and other environments.
The complete source and executable code is available for downloading from Windows NT Magazine's Web site at http://www.winntmag.com.
| INTERSOLV |
| 800-547-4000 Web: http://www.intersolv.com |
| JavaSoft |
| 800-626-5282 Web: http://java.sun.com |
| Microsoft |
| 206-882-8080 Web: http://www.microsoft.com |
| Sun Microsystems |
| 800-786-7638 Web: http://www.sun.com |
| Symantec |
| 408-253-9600
Web: http://www.symantec.com |
| XDB Systems |
| 410-312-9300
Web: http://www.xdb.com |
