JDBC : Java Glossary

JDBC (Java Data Base Connectivity)
Oracle’s official position is that it is not an acronym, although that is the
generally accepted assumption. I don’t believe them since the name is so close
to ODBC (Open Data Base Connectivity). It describes a list
of methods a Java programmer can use to access an SQL (Standard Query Language)
relational database. JDBC is similar to Microsoft’s
ODBC
interface to SQL databases.

There are three versions of JDBC
1, 2 and 3. Most SQLs (Standard Query Languages)
use level 2 and few now support level 3. Level three allows things such as
discovering what keys were automatically generated by INSERT.

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

You can learn more about ODBC by reading the Microsoft
ODBC documentation. 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/JDBC 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 (Local Area Network)
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

Oracle JavaSoft’s API (Application Programming Interface)
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 (Common Gateway Interface).
It is similar to Microsoft’s ODBC,
but
platform independent. David Linker maintains a FAQ about
SQL
and JDBC
including small free implementations.

Greyed out stores probably do not have the item in stock. Try looking for it with a bookfinder.

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.

JDBC Data Types

JDBC types

JDBC data types

JDBC Type

Equivalent Java Type

ARRAY

java.sql.Array

BIGINT

long

BINARY

byte[]

BIT

Boolean

BLOB

java.sql.Blob

BOOLEAN

Boolean

CHAR

String

CLOB

java.sql.Clob

DATALINK

java.net.URL

DATE

java.sql.Date

DECIMAL

java.math.BigDecimal

DISTINCT

mapping of underlying type

DOUBLE

double

FLOAT

double

INTEGER

int

JAVA_OBJECT

underlying Java class

LONGNVARCHAR

String

LONGVARBINARY

byte[]

LONGVARCHAR

String

NCHAR

String

NCLOB

java.sql.NClob

NUMERIC

java.math.BigDecimal

NVARCHAR

String

REAL

float

REF

java.sql.Ref

ROWID

java.sql.RowId

SMALLINT

short

SQLXML

java.sql.SQLXML

STRUCT

java.sql.Struct

TIME

java.sql.Time

TIMESTAMP

java.sql.Timestamp

TINYINT

byte

VARBINARY

byte[]

VARCHAR

String

Literals

JDBC
masks many of the differences between SQL
engines by providing a platform independent way of defining various literals in your
SQL queries.

JDBC literals

JDBC Literals

Type

Syntax

Notes

date

{d 'yyyy-mm-dd'}

e.g. {d 'yyyy-mm-dd'}

time

{t 'hh:mm:ss'}

e.g. {t '23:59:59'}. The seconds can have
up to 6 decimal places.

timestamp

{ts 'yyyy-mm-dd hh:mm:ss'}

Can also have fractional seconds. {ts
'2007-12-31-59.123456'}

String

Jim’s dog
or'Jim said I faked every
orgasm.'

String literals are delimited by quotation marks or apostrophes. If a string
is delimited by quotation marks, it may contain apostrophes; if a string is
delimited by apostrophes, it may contain quotation marks. SQL92 only
specifies using single quotes for delimiting string, with two single quotes
to indicate embedded quotes. Double-quotes are reserved for quoting names.
This is not true for all SQLDBMS (DataBase Management System) vendors, though. If you have an apostrophe in
a String delimited by an apostrophes, you need to double it. It gets rather
mind boggling because you have the outer Java String which uses Java-style escapes and inside the string
are SQL literals part of an SQL
expression which use SQL
conventions.

int

10 +10 -10

Optional sign

long

10 +10 10

SQL does not make a distinction between short, int and long in literals.

decimal

10.4 +10.4 -10.4

Optional sign

double

10.4E6 +10.4E6 -10.4E6

Optional sign

binary

B'010'

defines an int using binary. SQL
92 syntax.

hex

X'7FAB'

defines an int using hex. SQL
92 syntax.

Boolean

TRUE FALSE UNKNOWN NULL

UNKNOWN is not universally supported.

Functions

JDBC
masks many of the differences between SQL
engines by providing a platform independent way of defining various functions in your
SQL queries.

JDBC functions

JDBC Standard functions

Function

Notes

Numeric Functions

{fn ABS(number)}

absolute value

{fn ACOS(float)}

arccos giving radians

{fn ASIN(float)}

arcsin giving radians

{fn ATAN(float)}

arctan giving radians

{fn ATAN2(float1, float2)}

accurate arctan of float1/float2 giving radians

{fn CEILING(number) }

next highest integer

{fn COS(float)}

cosine of radians

{fn COT(float) }

cotangent of radians

{fn DEGREES(number)}

convert radians to degrees

{fn EXP(float) }

exponential, e to the float

{fn FLOOR(number) }

next lower integer

{fn LOG(float) }

base e logarithm

{fn LOG10(float)}

base 10 logarithm

{fn MOD(integer1, integer2)}

remainder, like Java %, not a true modulus
function. The result is negative only if integer1 is negative.

{fn PI()}

π

{fn POWER(number, power)}

number to the power

{fn RADIANS(number) }

converts degrees to radians

{fn RAND() }

float random number 0..1

{fn RAND(integer) }

set seed for random number generator

{fn ROUND(number places)}

round to given number of decimal places.

{fn SIGN(number)}

signum: +1= positive 0=zero -1=negative

{fn SIN(float) }

sine of radians

{fn SQRT(float)}

square root

{fn TAN(float)}

tangent of radians

{fn TRUNCATE(number places) }

truncate to number of places

String Functions

{fn ASCII (string)}

convert a single-character string to the corresponding
ASCII (American Standard Code for Information Interchange)
int, which will be 0..255.

{fn CHAR(code)}

char corresponding to ASCII
code. The code must be in the range 0..255..

{fn CHAR_LENGTH(string)

String length in chars. c.f. LENGTH

{fn CONCAT(string1,string2)}

Concatenate two strings

{fn DIFFERENCE(string1, string2)}

returns an integer that is the difference between two SOUNDEX string
expressions. The return value is in the range of 0..4.

In some JDBC drivers, you cannot nest {fn-style functions. You must use ordinary SQL
expressions or variables as arguments.

Connecting

Connection to Caucho

Connecting with DataSources

The preferred way to connect to an SQL
database is to use DataSources. This way your application program needs know only the
name of the database. It need not know the userid, password,
URL (Uniform Resource Locator),
JDBC driver
name etc. You define the details of each connection using XML (extensible Markup Language)
in a configuration file for your servlet womb. Your application code then uses ugly
but simple code

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. You must use the
methods of PreparedStatement to compose them.

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

confirmnumber� INT� NOT NULL��PRIMARY KEY��AUTO_INCREMENT,

When you insert records into the database, you leave that field NULL. You can retrieve

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.

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

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.

Under the Hood

JDBC
specifies how Java talks to the JDBC
driver, but from there the JDBC
and SQL
database designer are free to do pretty well what they want. Usually the
JDBC
driver batches up requests until an executeStatement then
sends them over the wire to the SQL
end. The link would use its own proprietary binary protocol, perhaps based on
DataOutputStream, RMI or TDS. RMI (Remote Method Invocation)
or serialized objects is the easiest, but a slow way to do it. The server may return
one row at a time, entire result sets or ideally batches of results, again in some
binary format. Typically character data goes across at UTF-8 which means efficient
8-bit transmission most of the time with no complications
about national encodings.

The JDBC driver scans your queries for the { character to pre-process the generic JDBC
syntax into the native SQL syntax. Other than that, it just passes your queries
on as text SQL
sentences to the server.

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.

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 (Coordinated Universal Time/Temps Universel Coordonné)
millisecond timestamps with epoch 1970-01-01 0:00 midnight
UTC .

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 (Object Database Management Group)
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 (International Business Machines), Tandem and Oracle are working on a
programmer-friendly SQL interface called JSQL (Java Standard Query Language)
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 FØRTRAN 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.

Using JDBC in Applets

It is generally not a good idea to use
JDBC
directly in an Applet.

You need to presume some brat will decompile your Applet and use that knowledge to create a substitute Applet that causes as much havoc as possible. If your Applet has direct access to JDBC,
the brat’s substitute can snoop or pillage the database to its heart’s
content.

You thus need two layers of security, in the Applet and
in the server. The proper way to handle it is your Applet
communicates via a socket or CGI
GET/POST [UTF-8 or binary] to a Servlet, that does the JDBC
calls. The Servlet can request reams of data via JDBC
and intelligently summarise them for the Applet, thus
cutting down on traffic.

On the other paw, if this is for pure intranet use, having the Applet do its own JDBC
saves the work of writing Servlets, defining packet formats etc. You can write and
maintain your code much more quickly. You gain flexibility and give up only a tiny
bit of speed, since the LAN
is relatively quick.