JDBC - Java Access to SQL Databases

Last updated 1998 June 25 by Roedy Green ©1996-1998 Canadian Mind Products.

JDBC stands for Java Database Connectivity. It describes a list of methods a Java programmer can use to access an SQL relational database. JDBC is similar to Microsoft's ODBC (Open Database Connectivity) interface to SQL databases.

You can learn more about ODBC by reading the documentation that comes with the Microsoft ODBC SDK. This will give you improve ODBC configuration utilities for the Control panel.

JDBC is just a wrapper to let you feed SQL requests to the server. To be kind, the interface is less than elegant. It comes from gluing ODBC onto SQL, then hurriedly grafting Java/JBDC onto ODBC. JDBC does not in the least look like database interface designed for Java.

JDBC can be used to access a database on the same machine, or a server on a LAN or on a server across the Internet.

JDBC has nothing to say about the format of the packets sent across the net. It is also silent on how the work is divided between client and server. This means that clients must use a JDBC proprietary library matching the server's protocols. This library may be written in pure Java or partly in native code.

Since JDBC is so similar to ODBC, it is expedient to use a bridge to convert JDBC calls into ODBC calls, and exploit existing ODBC drivers, standard protocols and database interfaces. However, this extra layer extracts a performance penalty.

Because the format of the packets are not specified, JDBC drivers are free to do clever things like compress, buffer and encrypt.

Accessing Multiple Databases

If you wanted to have a client application access two different SQL servers, your client would have to load two versions of the JDBC libary interface routines. JDBC handler classes automatically register themselves, so there is no logical problem with multiple JDBC drivers, just a performance hit. Middleware products like dbANYWHERE come to the rescue. The client loads a single version of the JDBC library that either speaks several proprietary protocols, or that talks to a server which in turn talks to the databases in their native proprietary protocols.

Middleware products can also help with buffering output from the server.

Since there is no such thing as a standard JDBC library, every website will require you to download a set of JDBC drivers just to browse its files, even with middleware.

If the client JDBC drivers are written in pure Java (such as those for dbAnywhere), they can be downloaded on the fly by any passing Java-enabled web browser, without violating applet security. If they are native classes, they have to be manually downloaded and installed. Ideally you want JDBC them "thin" -- lightweight so they don't take much time to load.

JDBC Interface Types

There are four types of JDBC drivers. JavaSoft's essay describes them in detail. I will summarize here.

Type 1 - ODBC Bridge
Use in a pinch to hook into a database you already own that does not yet directly support JDBC. ODBC is Microsoft's interface to SQL used in Windows. Nearly every SQL database supports it. Java supports it by loading JDBC drivers into the clients that emulate the ODBC protocol. Because of the extra layers of overhead and likely need to install client native classes, I consider this only an interim solution. The advantage is it lets you access almost any database.
Type 2 - Native API, Native Code
Use for ultimate speed. The JDBC driver is written partly in Java and partly in native code. It speaks the native protocol of the SQL database. This is efficient, but suffers from the problem of having to pre-install native code in the clients. You can't serve the public on the web this way.
Type 3 - Net Protocol, Pure Java
Use for the ultimate flexibility, especially when serving the public. The JDBC driver is written 100% in Java. This means it can be safely loaded on the fly into any Java-powered web browser. The driver speaks DBMS-vendor neutral protocol. Software on the server translates requests into native SQL protocols. This technique allows you to access many different SQL vendor databases without needing to load additional JDBC drivers into the clients. The net protocol can be cleverly designed to make the client JDBC drivers very small and fast to load.
Type 4 - Native Protocol, Pure Java
This is the fastest way to serve the public on the web from a single server. The JDBC driver is written 100% in Java. This means it can be safely loaded on the fly into any Java-powered web browser. The driver speaks DBMS-vendor specific protocol directly to the SQL server. This directness is efficient. However, if you needed to attach to various vendors SQL databases, you would need to load several JDBC drivers into the clients.

JDBC interface packages

JDBC interface packages are usually free or of nominal cost. The big bucks are for the underlying SQL database. The exception is Symantec's dbAnywhere Server which gives you simultaneous hooks to many different underlying SQL databases.

The following table is based on the one Javasoft maintains at http://splash.javasoft.com/jdbc/jdbc.drivers.html. I strongly suggest you also check with it as well. These tables are not always in perfect sync.

JDBC Vendor JDBC Product Type SQL DBMSs supported
Agave Software Design JDBC Net Server 3 Informix, Oracle, Sybase, ODBC
Asgard Software Open/A for Java 3 Unisys A series DMSII database
Borland InterClient 4 InterBase 4.0
Caribou Lake Software JSQL-Ingres 3 Ingres
Connect Software Fast Forward 4 MS SQL Server, Sybase
DataRamp Client For Java 3 ODBC
IBM DB2 Client support for Java 2/3 IBM DB2 Version 2
IDS Software JDBC Drivers 3 Informix, MS Access, MS SQL Server, Oracle, Sybase, ODBC, SQL Anywhere
I-Kinetics OPENjdbc 3 Informix, ODBC, Oracle, Sybase
InstantDB InstantDB 4 InstantDB
Hughes Technologies mSQL-JDBC driver 4 mSQL
InterSoft Essentia-JDBC 3 Essentia
Intersolv DataDirect JDBC/ODBC 1 ODBC
DataDirect JDBC 2 Oracle and Sybase
DataDirect J/SQL 3 DB2, Informix, Ingres, Microsoft SQL Server, Oracle, Sybase 10/11
SequelLink 3 IBM DB2, Microsoft SQL Server, OpenIngres, Oracle, Sybase 10/11
j.rad j.rad 4 j.rad
JavaSoft JDBC-ODBC bridge 1 ODBC.
OpenLink JDBC drivers 3 DB2, Informix, Ingres, Oracle, MS SQL Server, Progress, Progress95, ODBC, PostgresSQL, Sybase, Unify
Oracle JDBC drivers ? free for Oracle, Personal Oracle
Recital Kaleidoscope 4 CISAM, DB2 6000, Informix, Ingres, ODBC, Oracle, RDB, Recital
RmiJDBC RmiJDBC ? Microsoft Access
SAS SHARE*NET 4 SAS, and via SAS/ACCESS, ADABAS, Informix, Ingres, Oracle.
SCO SQL-Retriever 3 Informix, Ingres, Interbase, Oracle, Sybase
StormCloud Web DBC 3.0 Enterprise 3 ODBC
Sybase jConnect
(aka jdbcCONNECT)
3/4 Sybase Replication Server, SQL Anywhere, Sybase IQ, Sybase SQL Server, and more than 25 enterprise and legacy datase servers via Sybase OmniCONNECT
Symantec dbANYWHERE 3 MS Access, MS SQL Server, ODBC, Oracle, SQL Anywhere, Sybase
T.c.X. DataKonsult AB MySQL 4 MySQL
Thought CocoBase ? JDBC
Trifox VORTEXjava ? via VortexAccelerator
Trifox VORTEXjdbc ? via VortexAccelerator
Visigenic Visichannel For Java 3 ODBC
WebLogic jdbcKona 2 MS SQL Server, Oracle, Sybase
jdbcKonaT3 3 ODBC
Yard Software Yard-JDBC 4 YARD-SQL Database
XDB JetConnect 3 ODBC

Notes

CocoBase sits of top of JDBC to make it more programmer-friendly. It lets you read and write Java Objects directly.

Underlying SQL servers

In most cases the server price shown is for NT. The seat cost is usually for Windows-95 clients. Seats typically come in bundles of 5, 10 or 20. The per seat prices are calculated on the size of the smallest bundle. Seat prices usually decline with volume. Prices are approximate street prices in US dollars. Please help fill in the blanks in this table or report any errors to roedy@mindprod.com .

SQL Vendor SQL DBMS Server Product Version Server
Platforms
SQL
Server
Software
Price
Seats
Included
with
Server
Additional
Seat
Software
Price
Borland dBase 5.5 DOS, NT, Win3.1, Solaris, Win95, Unix $350 1 $250
Paradox 7 DOS, NT, Win3.1, Win95 $1500 1 $250
Delphi Client Server 2.0 NT, Win95 $2000 unlimited? free
Local Interbase 4 NT Win95 $250 1 n/a
Interbase 4 NLM, NT, SCO, Win95 $850 5 $40
Centura
(née Gupta)
SQLBase 6 ? ? ? ?
Computer Associates Ingres 6.4 ? ? ? ?
Digital Equipment RDB DBI ? Alpha ? ? ?
Empress Personal Empress ? AIX, HP, Irix, Linux PC Solaris, SCO Open Server, Sun Solaris $149 1 n/a
Empress RDBMS 6.8 AIX, DEC OSF, DEC Unix, HP, Irix, Linux, Lynx, Max, NT, QNX, SCO Open Desktop, SCO OpenServer, Solaris, Sun OS, Sys V, Unicos, Unixware, Win3.1, Win95 ? ? ?
IBM DB2 2 AIX, NT, OS/2, Win95 $1400 ? $350
IDS Software IDS Server 3.0 NT $796 unlimited free
IDS Server Lite 2.0 NT $595 unlimited free
InstantDB InstantDB 1.5 pure Java free for non-commercial use unlimited free
Hughes Technologies Mini SQL 2.0 Unix free unlimited free
Informix Informix-SE
general purpose limited support
7.2 NT, Unix, Win-95 ? ? ?
Informix-OnLine Workstation ? NT ? ? ?
Informix-OnLine/Secure Dynamic Server
muli-level secure
? ? ? ? ?
Informix-Illustra Server
ODBMS
? ? ? ? ?
Informix-OnLine Dynamic Server
general purpose high availability, high performance RDBMS
? ? ? ? ?
Informix-Universal Server
ORDBMS
? ? ? ? ?
Informix-Extended Parallel Server
data warehousing MPP
? ? ? ? ?
Intersoft Essentia ? ? ? ? ?
JB Development Harmonia Lite 1.0 anything that supports Java $100 1 n/a
Harmonia Pro 1.0 anything that supports Java $250 1 n/a
Brian Jepson tinySQL ? ? negotiable 1 n/a
j.rad j.rad 1.0 anything that runs Java $1495 unlimited free
Just Logic Just Logic/SQL ? BSDI Unix, Linux, NT, SCO OpenServer, SCO Unixware, Win3.1, Win95 $300 unlimited free
Microsoft Access 2.0 NT, Win95 n/a n/a $300
MS SQL Server 6.5 NT $500 ? $140
NCR Teradata
per node
2/2 NCR Unix WorldMark secret unlimited free
NCSA Decibel ? ? free ? ?
Oracle Personal Oracle Lite 2.3 Mac, NT, OS/2, Win95 $200 1 n/a
Personal Oracle 7.2 Mac, NT, OS/2, Win95 $400 1 n/a
Oracle Universal Workgroup Server
per concurrent user
7.3 AIX, HP, NLM, NT, OS/2, SCO OpenServer, SCO Unixware, Solaris, Sparc $1475 5 ?
Oracle 7 7.3 HP MPE/XL, ICL, MacIntosh, MVS, NLM, NT, OS/2, Siemens, 40+ Unix, VM, Win95 $1475 5 ?
Oracle 8 ? HP MPE/XL, ICL, MacIntosh, MVS, NLM, NT, OS/2, Siemens, 40+ Unix, VM, Win95 $1475 5 ?
Oracle 7 Parallel Server ? ? secret ? secret
Pervasive
(née Btrieve)
Scaleable SQL
concurrent users
6.15 clients DOS, Win3.1, Win95
servers NLM, NT
$595 5 $80
Pick D3 ? AIX, DGUX, HPUX, Linux, NT, SCO, Win95 ? ? ?
Prime Time Freeware PostgresSQL 4.2 Linux, various Unixes free unlimited free
Progress Progress 8.1 DOS, NLM, NT, OpenVMS, every major Unix, Win95 ? ? ?
Progress 95 ? ? ? ? ?
Red Brick Systems Red Brick Warehouse 5.0 ? $50,000+ ? ?
Quadbase 4 NLM, NT, Win95 $995 5 $100
SAS Access ? ? ? ? ?
Sequiter (sic) CodeBase/CodeServer xBase 6 DOS, MacIntosh, OS/2, Win95, Unix $400 unlimited free
Solid Information Technology Solid Server Single User 2.2 Linux free 1 n/a
Solid Server Single User 2.2 Windows $99 1 n/a
Solid Server Workgroup 2.2 Linux, HPUX, NT, Sun Unix, Win3.1, Win95, ... $199 1 $199
Software AG Adabas ? ? ? ? ?
SPCS Primabase ? ? ? ? ?
Sybase SQL Anywhere
per standalone user
5.5.01 DOS, NLM, NT, OS/2, Win3.1, Win95 $300 1 $180
SQL Anywhere
per seat
5.5.01 DOS, NLM, NT, OS/2, Win3.1, Win95 $300 1 $120
SQL Anywhere
per concurrent user
5.5.01 DOS, NLM, NT, OS/2, Win3.1, Win95 $600 4 $150
SQL Anywhere Professional
(includes Web server)
5.5.01 DOS, NLM, NT, OS/2, Win3.1, Win95 $400 1 $200
jConnect 1.0 NT and low end Unix $500 unlimited free
jConnect Enterprise 1.0 high end Unix, various mainframes $2000 unlimited free
SQL Server 11 Alpha, HP, NT, PowerPC, Sun secret ? secret
SQL Server Pro 11 NT $695 ? $39?
Symantec Visual Café Pro
includes: dbAnywhere Workgroup and Sybase SQL Anywhere
1.0 NT, Win95 $500 unlimited free
dbAnywhere Server 1.0 NT, Win95 $1500 unlimited free
Tandem NonStop SQL ? Tandem NonStop ? ? ?
T.c.X. DataKonsult AB MySQL 3.2.0.17 BSD, Linux, Solaris Sparc, Solaris i386, SunOS Sparc, source available for any platform with Posix Threads and a C++ compiler. free unlimited free
Thought Inc ? ? anything that supports Java $125 unlimited free
TriFox GENSIS ? ISAM, dBase, flat files ? ? ?
TurboPower FlashFiler ? ? ? ? ?
Unidata Unidata RDBMS 4.0 NT, Win95, others ? ? ?
Unify Unify 2000 ? ? ? ? ?
Unisys DMSII ? Unisys A series mainframes secret ? secret
Vmark UniVerse 9.3.1 NT, over 70 Unix versions $375+ 1 $375
W3Apps Jeevan ? anything that runs Java ? ? ?
Watcom SQL Anywhere
Now handled by Sybase
Yard Software Yard-SQL ? NT, Win95 free to schools 1 n/a?

Notes

Some vendors flatly refused to give me pricing information (marked secret), especially for high end products. Most simply ignored my requests for information. I gather either the prices are negotiated, or they are afraid of having out of date prices on the web. Perhaps they are just embarrassed by their high prices. This is one heck of a way to treat someone with money burning a hole in his pocket! Frustrating!

Borland's Local Interbase is single user, though it lets you run several programs at once. Delphi Client Server package contains Interbase servers for NT and Windows 95, as well as some native code links to its Delphi product that work faster than the ODBC interface. Presumably Borland's JBuilder product will be compatible with it.

Codebase is not an SQL server, but an xBase server. It offers Java support through a proprietary API, not JDBC. The advantage of that is CodeBase's small footprint -- about 50K.

Harmonia is 100% pure Java and JDBC-compliant. It is single user only. JB Development is not selling single copies of the product, but is working with OEM's, VARS, and large end users.

JustLogic is most unusual. It not only charges nothing per seat, but charges no royalties either. You can distribute it freely with with any code you write.

Microsoft Access is an ultra lightweight slightly incompatible SQL. It does not even use a server. Each client updates the database independently using ordinary file locking.

MySQL from Sweden is most unusual in that they give you source. Under some circumstances you must pay a licensing fee.

Oracle's upcoming J/SQL allows you to write triggers in Java. A trigger is code stored as part of the database that automatically gets executed when a row is updated.) Oracle looks to be on the way to more thoroughly integrating Java and SQL than JDBC offers. Oracle has a family of upwardly compatible SQL databases.

Personal Oracle is single user only.

PostgresSQL has a more object-oriented interface than most. SQL columns and rows are mapped onto classes and instances. Jeevan too is object oriented.

Sequiter offers to sell you the source code for $800. That is good insurance should the vendor drop the product.

Solid Software is from Finland. Despite the engine's low cost, it has referential integrity, stored procedures, database events, but no triggers. I'm told its even faster than SQL Anywhere. You can download a free evaluation copy.

Sybase's new jConnect bundle gives you their full SQL Server with unlimited access for a remarkably low price. This gives you Java thin client-only access. Sybase SQL Anywhere has three pricing schemes. You have to pick one scheme and stick with it.

Symantec's Visual Café Pro comes with dbAnywhere and Sybase SQL Anywhere for unlimited seats. It also comes with 5-seat dbAnywhere Workgroup drivers for Oracle, Sybase SQL Server, MS SQL Server and ODBC. If you want more seats, you have to go to dbAnywhere Server, which includes your choice of driver for one of Oracle, Sybase SQL Server, MS SQL Server or ODBC. You still have to buy the SQL server and seats. The Symantec documentation on installing dbAnywhere is full of errors. See the essay I wrote on how to install it.

I am still unclear on whether you need to buy a second copy of Visual Café when you deploy your database using SQL Anywhere. The license agreement contradicts itself.

It is also possible that the SQL Anywhere/dbAnywhere package that comes with Visual Café Pro is intended to be limited to five seats even if that is not enforced by the software.

Thought Inc's SQL comes with source written in Java ??. It is accessible only via JDBC.

  1. Standalone refers to the situation when users are usually disconnected each running with their own copy of the server. Periodically they connect via the Remote feature to resynchronise.
  2. Per Seat refers to the normal situation when many computers are accessing the same database running on a server on a LAN.
  3. Per Concurrent User allows an unlimited number of potential computers to access the database, but only a limited number of them can be actively logged onto the database at any one time.

One notable feature of Sybase SQL Anywhere is you get code all platforms on the same CD, and the ability to migrate your database between platforms.

Yard's Database is free if the total database is under 5 MB. Unfortunately the JDBC interface is incomplete.

JDBC++ Future SQL interfaces

JDBC looks like a rush job to get an SQL interface out the door. With more time, what might replace it?

SQL vendors are hopeless about using standard names or representations for common business objects like dates, times, timestamps, 8, 16, 32 and 64-bit integers, zip codes, postal codes, states, provinces and phone numbers.

Most likely we will have to wait for SQL standards groups to upgrade the embedded interface specifications.

I have heard there is something called ODMG binding which will let you get at a database in a more object oriented way. JDBC is not really a suitable interface for application programmers. It is meant to be hidden inside something more programmer-friendly.

Also IBM, Tandem and Oracle are working on a programmer-friendly SQL interface called JSQL that calls for automatic mapping of SQL types to Java objects, thereby producing bridges between the two languages, in addition to delineating methods for checking at application compile-time to make sure that SQL and Java types match.

Oracle demonstrated JSQL at the Colorado Summit. It is a preprocessor that generates Java code. It even clumsier to use that embedded SQL was in FORTRAN two decades ago. It is somewhat better than JDBC, but still far cry from being able creating components that are dbAware, that do their reads/writes more or less invisibly. We have a long way to go.


HTML Checked!
Canadian Mind Products You can get an updated copy of this page from http://mindprod.com/jdbc.html