Portable Database Programming with Java

At the Microsoft Professional Developer's Conference in March 1996,Microsoft hailed a new era of software development that will concentrate onintranets, the Web, ActiveX, HTML, applets, browsers, and Java. Microsoftemphasized the integration of tool sets for the various versions of Windows andInternet/intranet application development. It also revealed a unified shell andbrowser 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 aresult, developers face connecting databases to supply content to thousands ofWeb browser users, even where those databases are not accessible from theInternet. To meet this need, more and more developers are relying on the Javaprogramming language to access SQL databases (for information about the benefitsof Java, see the sidebar, "Programming with Java," page 111).

In this article, I describe several aspects of programming SQL databaseswith Java, including the Open Database Connectivity (ODBC) and Java DatabaseConnectivity (JDBC) APIs. A simple Java program illustrates how to insert datainto an ODBC data source. You'll learn how to add interoperability to your Javadatabase programs with adaptive programming, and how to serve many concurrentdatabase users by writing multitier applications in Java.

The Basics of Java
Java is well known for creating Web applets that you download with HTMLpages, but you can also use it to program applications, including SQLapplications. Java programs are of two types. Java applets arecomponents embedded in Web pages that a browser executes. Java applicationsare main programs that the Java interpreter, the Java virtual machine, executes.Java supports a distributed, client/server computing model, and today'sWeb-orientation exemplifies this model. Applets run as clients and conform tostrict client security rules. Applications usually execute at a server, but theycan run anywhere a Java virtual machine exists. In a typical scenario, a WindowsNT server can support PC, Macintosh, OS/2, and other clients. To embed an appletin an HTML document, you specify an tag, but other scenarios willsoon appear. Microsoft applications will soon use applets in a way that issimilar to how they use Visual Basic custom controls (VBXs), Object Linking andEmbedding custom controls (OCXs), and ActiveX controls. As an Object-Oriented Programming (OOP) language, Java lets you writeprograms that use inheritance, encapsulation, and data hiding. Java includes I/Ostreams, exception handling, and sockets for client/server communication. The Java architecture includes Unicode strings and characters and asecurity model that makes developing secure clients easier. Java includespackages that are analogous to application frameworks, which you use with C++compilers. Java's built-in support for threads also simplifies developingmultithreaded clients that can exploit ODBC asynchronous processing mode andhigh-performance JDBC drivers. Java 1.1, released in December 1996, includesJava Beans (interfaces for interoperable components), Remote Method Invocation(RMI), and other interfaces. ODBC and JDBC
Multidatabase APIs have gained favor with developers working with SQLdatabases. Microsoft's ODBC and JavaSoft's JDBC let you use one API to writeprograms that operate on various SQL databases. Java developers can also useMicrosoft data access APIs where the target execution environment is 32-bitWindows. Presently, ODBC or JDBC is the best choice for developing multiplatformprograms. Let's examine ODBC and JDBC to see what that choice involves. ODBC is the most widely used call-level interface for accessing SQLdatabases. JDBC is a new API that provides an object layer that works with ODBCdrivers to abstract SQL programming for Java developers. The ODBC and JDBC architectures include loadable database drivers and adriver manager. Database drivers are conceptually similar to printer driversbecause database drivers let you expand a program's functionality withoutrewriting its source code. The database drivers are libraries that the drivermanager invokes when your program connects to an ODBC or JDBC data source. Yourprogram 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 onlyone driver to connect to the server. This server-based architecture produces athinner client by replacing multiple drivers, client libraries, and protocolstacks 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 appropriatedriver for the data source to which you are connecting. The UNIX and Macintoshversions 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 bridgesoftware for JavaSoft to let JDBC applications use ODBC drivers. Figure 1illustrates the JDBC architecture that includes drivers, a driver manager, and abridge for accessing ODBC data sources. All ODBC and JDBC data access uses SQL queries to define and manipulatedatabase tables. ODBC and JDBC drivers understand the same SQL (ANSI SQL withX/Open escape clauses) and models for query preparation and execution. Java's raison d'être is portability, so the choice of usingJDBC or ODBC involves trade-offs between portability and features. JDBC doesn'tsupport ODBC features such as binding, bookmarks, and scrollable cursors.However, JDBC simplifies writing portable programs and portable, downloadabledrivers. Java programmers deciding between ODBC and JDBC can hedge their bets byusing a Java toolkit that works with both. XDB Systems's JetConnect toolkitlet's you write Java applets and applications that work with ODBC and JDBCdrivers. Java programs can use JetConnect to access local drivers and databasesor operate in a client/server configuration to access databases throughWeb servers. Figure 2 illustrates the JetConnect client/server architecture,where a Java applet connects to ODBC databases through a Web server running adaemon or NT service. JetConnect's classes encapsulate ODBC handles and all ODBC2.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 asimple Java program that uses JetConnect to insert data into an ODBC datasource. Inserting Data with Java
I've written a simple Java program, insper.java, to demonstrate how to use aJava application to create tables and insert contact information. You can use itas a model for low-volume tables of information such as area codes, states, andpostal abbreviations. insper inserts data into tables in an ODBC data sourceusing SQL and JetConnect (you can download the complete source code toinsper.java from the Windows NT Magazine Web site athttp://www.winntmag.com). Listing 1 shows the first part of the program. Ifyou've used Visual Basic (VB), C++, or another language to program with the ODBCAPI, you'll recognize the program flow. At the top of the program, insperimports the JetConnect classes. insper prompts the user for the ODBCdata source name, user ID, and password for this application. Because insper isan application, you run it with JavaSoft's Java Developer's Kit (JDK) Javainterpreter by typing java insper at the command line instead of invoking the application from a browser. insper instantiates environment and connection objects and invokes methodsthat call the ODBC functions to allocate an environment and connection handle.After insper has a valid connection, it repeats the process to allocate astatement handle. If the program has a valid statement handle, it executes a SQLCREATE TABLE statement to create the Per table. When the program finishescreating tables, it executes an INSERT statement to insert a row of data in thePer table. Because the INSERT query follows the prepare-and-execute model, it usesparameters when compiling the query, as you see in Listing 2. The question marksin this listing are markers that indicate the program will substitute a valuewhen the query executes. After executing the SQLPrepare logic, theprogram instantiates new objects that correspond to the columns to be inserted.It binds them as parameters to signify that the program will supply the datawhen the query executes. The program then sets the data values to be used andexecutes (SQLExecute) the query. Although Java provides cross-platform portability, you'll find that Javaalone doesn't guarantee that ODBC or JDBC applications will be interoperable(the ability to operate against heterogeneous databases without any need forrecoding) across data sources. Fortunately, adaptive programming lets youincrease the interoperability of ODBC and JDBC programs, including those youwrite in Java. Adding Interoperability with Adaptive Programming
SQL products don't implement a uniform feature set, SQL syntax, or datatypes. ODBC and JDBC can report at runtime which features and types a particularDBMS or driver supports. Because Java lets you write portable clients, it can bea good language for writing client/server database applications. Java lets youdevelop a portable front end or client side of a client/server databaseapplication. However, if you want a client that is not affected by changes atthe back end, or server side, you need to avoid hard-coded logic about featuresand data types. You can add this interoperability using adaptive programming. Insimple terms, adaptive programs use execution time techniques to bind to dataand select which features to use. Products such as Access use adaptivetechniques, although Microsoft visual developer tools often require a programmerto use ODBC API calls when writing interoperable SQL programs. JDBC implements adifferent introspection model than ODBC, but the concept of execution timeadaptation is applicable to either API. Whether you need these techniques depends on program requirements andwhether you need to make an application highly interoperable. If you needinteroperability, you use ODBC functions and JDBC metadata classes to determineat runtime what features, SQL dialects, and data types the DBMS or databasedriver support. Adaptive programming techniques exploit these capabilities tocraft code that can operate against various SQL back ends. Features, SQL dialects, and data types often change as DBMS vendors rollout new versions. Adaptive techniques let your program work with a differentDBMS without having to rewrite or recompile it. They also add flexibility toprograms that work with only one type of SQL DBMS. Adaptive programs interrogatethe driver about supported features. The JDBC DatabaseMetaData class and theODBC SQLGetInfo function return feature information such as whether a DBMSsupports outer joins, procedures, or specific SQL scalar functions. The insper sample Java program uses simple data types. To use it as a modelfor adaptive programs, you need to modify the code so that it doesn't presumespecific type names to use with the CREATE TABLE statement. ODBC includes theSQLGetTypeInfo function, which will report the available types for a data sourceand the native name for a type. The native name is the string you need to use inSQL statements. JDBC includes metadata classes that provide runtime reporting oftypes and other column information. Multitier Applications
The Java database picture isn't complete without looking at distributedobjects and three-tier applications. To better serve several concurrent users,developers are partitioning the logic of SQL client/server applications bymoving business rules and non-GUI logic to application servers. VB and VisualC++ (VC++) use automation interfaces and the Distributed Component Object Model(DCOM) to support this model for distributed applications (for information aboutDCOM, see the sidebar, "DCOM in NT 5.0: Strategic Enhancements," byKeith Pleas, page 76). Java's approach to distributed objects is twofold, andeach alternative has its supporters. Sun Microsystems's APIs let Java programsinvoke methods on remote servers and work with distributed objects that conformto the Object Management Group's Common Object Request Broker Architecture(CORBA). Microsoft's Visual J++ (VJ++) and Java Software Developer's Kit (SDK) useDCOM and ActiveX, although they will support Java 1.1 APIs that work with CORBAobjects. Sun Microsystems has a solution, Java Beans, that exposes a uniformcomponent model that encapsulates heterogeneous components such as ActiveX andOpenDoc. 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 DataObject (ADO), and the new Microsoft Transaction Server as preferred solutionsfor multitier development. Performance is an issue when you're executing Java bytecode, and compilervendors have improved performance. Performance improvements, availability formost OSs, the ability to work with multidatabase APIs, and a three-tierarchitecture make Java an important database programming tool. The ODBC and JDBC SQL APIs provide Java data access for multiple operatingsystems. If you are writing solely for NT and Windows 95, you can also use DataAccess Objects (DAOs), Remote Data Objects (RDOs), and ADOs. With OLE DB, ADOprovides connectivity to SQL and non-SQL data. Those APIs offer the advantage ofbeing available for VJ++, VB, and VC++. However, unlike JDBC and ODBC, theseAPIs are not supported for the writing programs on Macintosh, Solaris, OS/2, andother environments.The complete source and executable code is available for downloading fromWindows NT Magazine's Web site at http://www.winntmag.com. INTERSOLV 800-547-4000

Web: http://www.intersolv.com JavaSoft800-626-5282

Web: http://java.sun.comMicrosoft206-882-8080

Web: http://www.microsoft.comSun Microsystems800-786-7638
Web: http://www.sun.com Symantec408-253-9600
Web: http://www.symantec.comXDB Systems410-312-9300
Web: http://www.xdb.com