JDBC - Java Access to SQL Databases

Last updated by Roedy Green ©1996-1999 Canadian Mind Products.

Stuck in a frame? Click here to break out.

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 library 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://java.sun.com/products/jdbc/jdbc.drivers.html. I strongly suggest you also check with it as well. These tables are not always in perfect sync.

VENDORS: Please help fill in the blanks in this table or report any errors to roedy@mindprod.com.

JDBC Vendor JDBC Product Type SQL DBMSs supported
Agave Software Design JDBC Net Server 3 Oracle, Sybase, Informix, others via ODBC
Altera Software SQL Server 4 Altera SQL Server
Asgard Software Open/A for Java 3 Unisys A series DMSII database
Caribou Lake Software JSQL-Ingres 3 Ingres
Centura Software SQLBase 4 Centura SQLBase
Cloudscape JBMS 4 JBMS
Connect Software Fast Forward MS SQL Server, Sybase
broken_link
DataRamp
Client For Java ODBC
Ensodex Hotsockets 3 Several through ODBC.
Free TDS JDBC Driver 4 MSSQL, Sybase, includes C DB-lib source.
GIE Dyade RmiJdbc RMI Bridge for remote access to JDBC drivers
GNU 4 MySQL
GWE Technologies 4 MySQL
Hit Software HiT JDBC/DB2 4 DB2, DB2/400
HOB Electronic 4 DB2 (many platforms), VSAM under CICS (MVS; OS/390, VSE), IMS-DB under CICS (MVS, OS/390, VSE), IMS-DB under IMS/DC (MVS), DL/1
Hughes Technologies mSQL-JDBC driver mSQL
I-Kinetics OPENjdbc 3 Oracle, Informix, Sybase, and others via ODBC
i-net software 4 MS SQL server
IBM DB2 Client support for Java 2, 3 IBM DB2 Version 2
IBM 4 DB2 for OS/400
IDS Software JDBC Drivers 3 Informix, MS Access, MS SQL Server, Oracle, Sybase, ODBC, SQL Anywhere
Imaginary mSQL-JDBC 4 mSQL
Information Builders 3 ECB
Informix JDBC Driver 4 Informix
Inprise (née Visigenic) Visichannel For Java 3 ODBC
Inprise (née Borland) InterClient InterBase 4.0
InstantDB InstantDB 1.9 4 InstantDB, currently no Internet JDBC support.
InterBase 3 InterBase
InterSoft Essentia-JDBC 3 Essentia
JavaSoft JDBC-ODBC bridge 1 Several dozen through ODBC drivers.
KonaSoft. JDBC 2.0 driver 3, 4 Type 3: Sybase, Oracle, Informix, SQLAnywhere, Type 4: Sybase, Oracle, ODBC
Liberty Integration Software JDBC Driver 3 Most PICK flavors including VMARK, Unidata, General Automation, PICK systems
Lotus Development JDBC Driver for Domino 2 Domino
Microfocus (née Intersolv) DataDirect JDBC/ODBC ODBC
Microfocus (née Intersolv) DataDirect JDBC Oracle and Sybase
Microfocus (née Intersolv) DataDirect J/SQL DB2, Informix, Ingres, Microsoft SQL Server, Oracle, Sybase 10/11
Microfocus (née Intersolv) DataDirect/SequeLink 3 DB2, Ingres, Informix, Oracle, Microsoft SQL Server, Sybase 10/11
Microfocus (née Intersolv) SequelLink IBM DB2, Microsoft SQL Server, OpenIngres, Oracle, Sybase 10/11
Microfocus (née XDB) JetConnect 1, 3 ODBC
NetAway XMLWays JDBC Driver 3, 4 Oracle, Informix, Sybase, MS SQL Server, DB2, others via ODBC
Nogginware RemoteDB JDBC Driver 3 many databases through ODBC*
OpenLink JDBC drivers 3 CA-Ingres, DB2, Informix, Ingres, Oracle, MS SQL Server, PostgreSQL, Progress, Progress95, ODBC, Oracle, PostgresSQL, Sybase, Unify
Oracle JDBC drivers 2, 4 free for Oracle, Personal Oracle
Recital JDBC Developer 3 DB2/6000, Informix, Ingres, Oracle, others via ODBC
Recital JDBC Developer 4 Recital, Xbase, CISAM, RMS
Recital JDBC Developer (n´e Kaleidoscope) CISAM, DB2 6000, Informix, Ingres, ODBC, Oracle, RDB, Recital
RmiJDBC RmiJDBC Microsoft Access
SAS SHARE*NET 3, 4 ADABAS, SAS, and via SAS/ACCESS, ADABAS, Informix, Ingres, Oracle.
SCO SQL-Retriever 3 Informix, Ingres, Interbase, Oracle, Sybase
Simba Technologies Simba Express 3 Oracle, Sybase, MS SQL
Software AG ADABAS D 4 ADABAS D
Solid Information Technology JDBC Driver 4 Solid Server
StarQuest Software StarSQL 1 DB2/MVS, DB2/400, SQL/DS, DB2/CS for AIX, OS/2, HP-UX, Solaris and Windows NT, DB2 Universal Database
broken_link
StormCloud
Web DBC 3.0 Enterprise ODBC
Sun NetDynamics/j.rad j.rad
Sybase jConnect
(aka jdbcCONNECT)
3, 4 Sybase Replication Server, SQL Anywhere, Sybase IQ, Sybase SQL Server, and more than 25 enterprise and legacy database servers via Sybase OmniCONNECT
Symantec dbANYWHERE 3 MS Access, MS SQL Server, ODBC, Oracle, SQL Anywhere, Sybase
T.c.X. DataKonsult AB mySQL mySQL
ThinWeb SoftWare JDBCRemote Any database supporting JDBC or ODBC.
Thought CocoBase JDBC
tjFM twz1jbdcForMysql 4 MySQL
Trifox VORTEXjdbc via VortexServer to Adabas, Informix, Microsoft SequelSerner, Ingres, Orcle, Sybase, Trifox, ODBC, Oracle, Teradata
Trifox VORTEXjava 3 via VortexServer to Adabas, Informix, Microsoft SequelSerner, Ingres, Oracle, Sybase, Trifox, ODBC, Oracle, Teradata, legacy systems via GENESIS
Watershed Relational Object Framework ? ?
WebLogic jdbcKona 4 MS SQL Server and Informix
WebLogic jdbcKona 2 Oracle, Sybase, MS SQL Server
WebLogic jdbcKonaT3 ODBC
WebLogic jdbcKona 3 Several dozen through ODBC drivers.
Yard Software Yard-JDBC 4 YARD-SQL Database
Zhao Yonghong DBF 4 beta, JDBC 2.0, supports Unicode. It's free for education, but there will be a reasonable fee for commerical use. xbase, Foxpro and VFP free format tables. Does not require a separate xbase engine. Features supported now include query, delete, update, insert, complicated expressions and two fuctions(max and min).

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. Java Development Magazine also maintains a list of SQL vendors.

VENDORS: 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
Agave Software Design JDBC Netserver 1.2 Solaris, NT, HPUX $2995 unlimited free
Allaire Cold Fusion 4.0 ? $495 unlimited free
Centura
(née Gupta)
SQLBase 6 ? ? ? ?
Cloudscape Developer ORDBMS 1.5 pure Java, embedded. $895 1 n/a
Embeddable Light Server ORDBMS 1.5 pure Java, embedded. $4895 unlimited free
Lite Server ORDBMS 1.5 pure Java, client server. $6395 unlimited free
Compaq (née DEC Digital Equipment) RDB DBI ? Alpha ? ? ?
Computer Associates Ingres 6.4 ? ? ? ?
Connect Software FastForward ? ? $2995 unlimited free
Empress Personal Empress ? AIX, HP, Irix, Linux PC Solaris, SCO Open Server, Sun Solaris $149 1 n/a
Empress RDBMS 8.1.0 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 ? ? ?
I-Kinetics OPENjdbc ? pure Java $1995 10 $22
IBM DB2 2 AIX, NT, OS/2, Win95 $1400 ? $350
IDS Software IDS Server 3.0.2 NT $1495 unlimited free
IDS Server Lite 2.0 NT $995 unlimited free
Inprise (née Borland) Data Gateway ? ? $1495 unlimited free
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
InstantDB InstantDB 1.9 pure Java free for non-commercial use unlimited free
Hughes Technologies Mini SQL 2.0.4.1 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 ? ? ? ? ?
broken_link
JB Development
(unfortunately out of business)
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
broken_link
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 7.0 NT $500 ? $140
NCR Teradata
per node
2/2 NCR Unix WorldMark secret unlimited free
NCSA Decibel 0.5a4 pure Java free ? ?
Oracle Personal Oracle Lite 3.5 Mac, NT, OS/2, Win95 $222 1 n/a
Personal Oracle 8.0 NT, OS/2, Win95, (Mac support dropped after 7.1) $316 1 n/a
Oracle 8 8.0 HP MPE/XL, ICL, MVS, NLM, NT, OS/2, Siemens, 40+ Unix, VM, Win95, (Mac support dropped after 7.1) $1180 5 ?
Oracle 8 Enterprise Edition 8.0 HP MPE/XL, ICL, MacIntosh, MVS, NLM, NT, OS/2, Siemens, 40+ Unix, VM, Win95 secret, if you have to ask you can't afford it. ? ?
Pervasive
(née Btrieve)
Scaleable SQL
concurrent users
7.0 clients DOS, Win3.1, Win95
servers NLM, NT
$595 5 $80
Pick D3 Database Server ? Not SQL. AIX, DGUX, HPUX, Linux, NT, SCO, Win95 ? ? ?
ProstgreSQL PostgresSQL 6.4 Linux, various Unixes free unlimited free
Progress Progress 8.3 DOS, NLM, NT, OpenVMS, every major Unix, Win95 ? ? ?
Progress 95 ? ? ? ? ?
Red Brick Systems Red Brick Warehouse 5.1 ? $50,000+ ? ?
Quadbase 5.0 NLM, NT, Win95 $995 5 $100
SAS Access ? ? ? ? ?
Sequiter (sic) CodeBase/CodeServer xBase 6 DOS, MacIntosh, OS/2, Win95, Unix $495 unlimited free
Solid Information Technology Solid Server Single User 2.3 Linux free 1 n/a
Solid Server Single User 2.3 Windows $99 1 n/a
Solid Server Workgroup 2.3 Linux, HPUX, NT, Sun Unix, Win3.1, Win95, ... $199 1 $199
Software AG Adabas ? ? ? ? ?
SPCS Scandinavian PC Systems Primabase ? ? ? ? ?
Sun NetDynamics/j.rad 1.0 anything that runs Java $1495 unlimited free
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é Database Edition
includes: dbAnywhere Workgroup and Sybase SQL Anywhere
2.5a 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 Cocobase Workgroup 2.0 anything that supports Java $895 unlimited free
TriFox GENESISsql ? ISAM, dBase, flat files ? ? ?
TurboPower FlashFiler 1.5 includes Delphi/C++ source $229 unlimited free
broken_link
Unidata
Unidata RDBMS 4.0 NT, Win95, others ? ? ?
Unify Unify Dataserver 6 ? ? ? ? ?
Unisys DMSII ? Unisys A series mainframes secret ? secret
Ardent (née Vmark) Datastage (née UniVerse) 9.3.1 NT, over 70 Unix versions $375+ 1 $375
W3Apps Jeevan 2.0 anything that runs Java $69 1 $69
Watcom SQLAnywhere
Now handled by Sybase
Yard Software Yard-SQL 4.05.00 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!

Inprise'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 Inprise'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.

JustLogic is most unusual. It not only charges nothing per seat, but charges no royalties either. You can distribute it freely 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 SQLAnywhere. 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 SQLAnywhere has three pricing schemes. You have to pick one scheme and stick with it.

Symantec's Visual Café Database Edition comes with dbAnywhere and Sybase SQLAnywhere 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 SQLAnywhere. The license agreement contradicts itself.

It is also possible that the SQLAnywhere/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 SQLAnywhere 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 Friendly ISPs

Normally you need your own server attached 24-hours to the Internet if you want to do any JDBC or servlet programming. However, there are a few ISPs that will let you run SQL engines on their server. Hopefully SQL/JDBC/Servlet will become a commodity service.

ISP Services: SQL/JDBC/Servlet
Adrenaline Group group of over 15 ISPs supplying various services
www.servlets.com A list of about 20 ISPs supplying various servlet hosting services for $10 to $110 per month.
Centralogic Sybase, Poet, JavaServer
Icom cgi plus java
The Sphere weblet savvy
N-ary British servlet consultants

JDBC Books

Java Database Programming With JDBC (Visual Developer) (ISBN: 1-57610-159-2) by Pratik Patel and Karl Moss. $32 US.

Java Database Programming With JDBC (Webmaster Series) (ISBN: 1-57610-056-1) by Pratik Patel and Karl Moss. $40 US.

JDBC Database Access With Java: A Tutorial and Annotated Reference (Java Series) (ISBN: 0-201-30995-5) by Graham Hamilton, Rick Cattell, Maydene Fisher. Good reference. $38 US.

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.


CMP_home You are visitor number
.
HTML Checked! award
CMP_home Canadian Mind Products You can get an updated copy of this page from http://mindprod.com/jdbc.html The Mining Company's
Focus on Java
Best of the Net Award