Java Glossary : JDBC

Java definitions: 0-9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

You are here : home : Java Glossary : J words : JDBC.

JDBC stands for Java Database Connectivity. Sun's official position is that it does not, although that is the generally accepted assumption. I don't believe them since the name is so close to ODBC (Open 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 Data baseConnectivity) interface to SQL databases.

The JDBC classes are documented as part of the standard JDK documentation in the

java.sql package : available:

on the web at java.Sun.com
on J: in the JDK 1.4.2_04: J:\j2sdk1.4.2_04\docs\api\java\sql\package-frame.html
on J: in the JDK 1.5 beta: J:\Program Files\Java\j2sdk1.5.0\docs\api\java\sql\package-frame.html

You will need to augment them with documentation from your SQL vendor.

You can learn more about ODBC by reading the documentation that comes with the Microsoft ODBC SDK. This will give you improved 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.

The Basics

Sun JavaSoft's API standard for attaching to an SQL-style database. There are version 1, 2 and 3 drivers. JDBC allows a client Java application to connect directly to the SQL engine on a server without needing to go through packets sent via CGI. It is similar to Microsoft's ODBC, but platform independent. David Linker maintains a FAQ about SQL and JDBC including small free implementations.

Java Database Programming with JDBC

1-57610-056-1

Pratik Patel and Karl Moss

If you are looking for an example for building a JDBC driver from the ground up, this the book you want.

JDBC is a somewhat wimpy standard. JDBC has many queries that allow you to ask what is supported and how, yet it makes almost no demands on what has to be supported or how. For example, you can't count on there being any way to read a row of a result set more than once or to scroll backwards through the result set. You can give feedback to Sun about JDBC at jdbc-javadev-feedback@eng.sun.com.

Connecting

view

Using PreparedStatement

// simple Database access for squirrels

// using PreparedStatement to insert a record
PreparedStatement stmt = conn.prepareStatement( "INSERT INTO nutcaches VALUES (?,?)" );
stmt.setString( 1, "butternuts" /* type */);
stmt.setInt( 2, 40 /* count of nuts */ );
stmt.executeUpdate();

// using PreparedStatement to do a query
PreparedStatement fetch = conn.prepareStatement( "SELECT * FROM nutcaches WHERE nutcount > ?" );
fetch.setInt( 1, 12 /* nuts wanted */ );
ResultSet r = fetch.executeQuery();
while ( r.next() )
   {
   String nutType = r.getString( 1 );
   int nutCount = r.getInt( 2 );
   } // end while

Blobs

In theory you can store binary data in SQL databases using BLOBs. However, I found the implementation so flawed I gave up and decided it made more sense to store the binary data as Base64u-armoured text instead.

The fundamental problem is that SQL works by exchanging ASCII sentence with the server. Binary data must be embedded in these sentences surrounded in quotes, with accidental quotes doubled. SQL is not designed to deal with streams. It was an afterthought.

Problems include:

No way to instantiate a Blob object to feed binary data into the database.
getBlob was not implemented in the MySQL 1.2 driver I was using. You don't find out till run time.
setBinaryStream insists on knowing ahead of time how long the stream will be.
getBinaryStream won't tell you ahead of time how many bytes are coming.

Auto Increment

The way you generate unique keys with MySQl is with

confirmnumber  INT  NOT NULL  PRIMARY KEY  AUTO_INCREMENT,

When you insert records into the database, you leave that field NULL. You can retrieve the value of the last key so generated with:

SELECT LAST_INSERT_ID();

Gotchas

You can use ? to replace data in a PreparedStatement, but not keywords or operators.
If you don't run ResultSet.next() off the end, you must call ResultSet.close() before you do any other database access.

The Vendor Lists

I have collected three vendor lists:

They are now split off into three separate documents.

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 drivers 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 Driver Vendors

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. SqlSummit.com also maintains one. It would be an understatement to say these tables are not always in perfect sync. Sun's will usually be more current. Mine has more detail.

VENDORS: Please help fill in the blanks in this table or report any errors via email.

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
Atinav	aveConnect JDBC Drivers 1.x,	3 and 4	DBF, MS Access, MS SQL Server
Atinav	aveConnect JDBC Drivers 2.x, 2,x extended API	3 and 4	MS SQL Server
Avenir	JDBC 2.0	3,4	MS SQL Server, MS Access
Caribou Lake Software	JSQL-Ingres	3	Ingres
Cloudscape	JBMS	4	JBMS
Connect Software	Fast Forward		MS SQL Server, Sybase
CROSS ACCESS Corporation	CrossAccess SERIESfour	3	ADABAS, Datacom/DB, DB2, DL/I, IDMS, IMS/DB, Sequential files, VSAM files
DataDirect Technologies (née Intersolv, Microfocus, Merant)	DataDirect SequeLink for JDBC	3	DB2, Oracle, Microsoft SQL Server, Sybase, Informix plus others via ODBC Socket
DataDirect Technologies	DataDirect Connect for JDBC	4	DB2, Oracle, Microsoft SQL Server, Sybase, Informix
DataRamp	Client For Java		ODBC
Daffodil Software	Daffodil DB	4	JDBC-3 for Daffodil DB.
Ensodex	Hotsockets	3	Several through ODBC.
Free TDS	JDBC Driver	4	MSSQL, Sybase, includes C DB-lib source.
GNU		4	MySQL
Gupta Technologies	SQLBase	4	Gupta SQLBase (née Centura)
GWE Technologies	GWEMySQL	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 2.0 Drivers	3	Informix, MS Access, MS SQL Server, Oracle, Sybase, ODBC, SQL Anywhere. You can't buy the drivers separately from IDS Server.
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
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
Linus AS	DUALITY JDBC Proxy	3	It functions as a proxy for any other JDBC driver. It uses a reversed connection scheme, so that connections may be established through firewalls without requiring any openings for inbound connections. All features of the JDBC API are implemented, including streamed data types.
Lotus Development	JDBC Driver for Domino	2	Domino
Lutris	InstantDB 3.25	4	InstantDB, JDBC level 2 support.
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
Novell	Oracle JDBC Remote Driver	4	Oracle 7.2.3 or higher, Requires a Java 1.1 JVM.
Objectweb née Objectware, née GIE Dyade	rmijdbc	3	GPL
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
Quadcap	JDBC 2.1	4	Quadcap
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
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
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.com	ThinAccess (née 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
Viaserv	Viaserv Direct JDBC Driver	4	DB2, SQL/DS, IMS, DL/I, IDMS, VSAM, sequential, ADABAS, Datacom
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. Also have JDBC 2.0 driver.	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.

List of similar O-R tools

MS Access is a popular toy database. Even so, you won't find free type 3 or 4 drivers for it. Your options are the rather expensive IDS Server or rmiJDBC. The JDBC-ODBC bridge is your last resort.

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?

Read and write objects with binary fields in them. Field names match SQL column names. This is what Java programmers want, but in talking with SQL vendors, it is not likely to happen. We will get the ability to read and write Java objects, but these map to a single cell in SQL. That means you can't do SQL searching on the fields of the object, or retrieve objects with only some of the fields.
CREATE statements that use Java terminology.
CREATE statements generated from ordinary Java declarations for the objects representing rows. (field names match SQL column names).
Something more elegant to pass input to and receive output from canned SQL procedures.
Handle two kinds of date -- one with day resolution and one with timestamp resolution. Do whatever it takes to hook it into the non-standard date formats provided by SQL vendors, where Java always sees them as 64-bit INTEGER UTC millisecond timestamps with epoch 1970 Jan 1 midnight.
Add more built-in types e.g. ZIP, Country, Phone, that have automatic validation implied as well as conversion to/from binary/display formats.
Smart objects that let you forget they are backed by an SQL database. You treat them programmatically like arrays or simple objects.

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.

Learning more

Sun's JDBC api docs : available:

on the web at java.Sun.com
on J: in the JDK 1.4.2_04: J:\j2sdk1.4.2_04\docs\api\java\sql\package-summary.html
on J: in the JDK 1.5 beta: J:\Program Files\Java\j2sdk1.5.0\docs\api\java\sql\package-summary.html

Java Database Programming with JDBC: Discover the Essentials for Developing Databases for Internet and Intranet Applications

1-57610-159-2

Pratik Patel and Karl Moss

Java Database Programming With JDBC

1-57610-056-1

Pratik Patel and Karl Moss

JDBC Database Access with Java: A Tutorial and Annotated Reference

0-201-30995-5

Graham Hamilton, Rick Cattell, Maydene Fisher

Hibernate ¤ JDBC 1.2 tutorial ¤ JDBC 1.2-3.0 specifications and API docs ¤ JDBC 2.0 Tutorial ¤ JDO ¤ JDX ¤ Jeevan ¤ JSQL ¤ metadata ¤ O'Reilly On Java JDBC essays ¤ Object-Relational Bridge ¤ ODBC ¤ ORDBMS ¤ POD ¤ SQL books ¤ SQL

home	Canadian Mind Products
	mindprod.com IP:[24.87.56.253]
	Your IP:[80.134.30.163]
	You are visitor number 122613.
	Please send errors, omissions and suggestions
	to improve this page to Roedy Green.
	You can get a fresh copy of this page from:	or possibly from your local J: drive mirror:
	http://mindprod.com/jgloss/jdbc.html	J:\mindprod\jgloss\jdbc.html