JDBC : Java Glossary

*0-9ABCDEFGHIJKLMNOPQRSTUVWXYZ (all)

JDBC Gazelle JDBC
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.

JDBC Vendors Blobs
SQL Vendors Auto Increment
ISP Vendors Gotchas
Canadian IAP Vendors Multiple Databases
JDBC Documentation JDBC Interface Types
The Basics Under The Hood
Data Types JDBC++
Literals Using JDBC in Applets
Functions Books
Connecting Learning more
PreparedStatement Links

JDBC Documentation

The JDBC classes are documented as part of the standard JDK (Java Development Kit) documentation in the
Oracle’s Javadoc on java.sql package : available:
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.
book cover recommend book⇒JDBC API Tutorial and Reference, third editionto book home
by Maydene Fisher, Jon Ellis, Jonathan Bruce 978-0-321-17384-3 paperback
publisher Prentice Hall
published 2003-06-21
This is Oracle’s official book on JDBC.
Australian flag abe books anz abe books.ca Canadian flag
German flag abe books.de amazon.ca Canadian flag
German flag amazon.de Chapters Indigo Canadian flag
Spanish flag amazon.es Chapters Indigo eBooks Canadian flag
Spanish flag iberlibro.com abe books.com American flag
French flag abe books.fr amazon.com American flag
French flag amazon.fr Barnes & Noble American flag
Italian flag abe books.it Nook at Barnes & Noble American flag
Italian flag amazon.it Kobo American flag
India flag junglee.com Google play American flag
UK flag abe books.co.uk O’Reilly Safari American flag
UK flag amazon.co.uk Powells American flag
UN flag other stores
Greyed out stores probably do not have the item in stock. Try looking for it with a bookfinder.
book cover recommend book⇒Database Programming with JDBC and Javato book home
by George Reese 978-1-56592-616-5 paperback
birth 1969-02-17 age:49
publisher O’Reilly recommended
published 2000-01-15
Covers JDBC 2 and RMI.
Australian flag abe books anz abe books.ca Canadian flag
German flag abe books.de amazon.ca Canadian flag
German flag amazon.de Chapters Indigo Canadian flag
Spanish flag amazon.es Chapters Indigo eBooks Canadian flag
Spanish flag iberlibro.com abe books.com American flag
French flag abe books.fr amazon.com American flag
French flag amazon.fr Barnes & Noble American flag
Italian flag abe books.it Nook at Barnes & Noble American flag
Italian flag amazon.it Kobo American flag
India flag junglee.com Google play American flag
UK flag abe books.co.uk O’Reilly Safari American flag
UK flag amazon.co.uk Powells American flag
UN flag other stores
Greyed out stores probably do not have the item in stock. Try looking for it with a bookfinder.
book cover recommend book⇒Visual Developer Java Database Programming with JDBC, second edition: The Essentials for Developing Databases for Internet and Intranet Applicationsto book home
by Pratik Patel and Karl Moss 978-1-57610-159-9 paperback
publisher Coriolis
published 1997-07-22
Australian flag abe books anz abe books.ca Canadian flag
German flag abe books.de amazon.ca Canadian flag
German flag amazon.de Chapters Indigo Canadian flag
Spanish flag amazon.es Chapters Indigo eBooks Canadian flag
Spanish flag iberlibro.com abe books.com American flag
French flag abe books.fr amazon.com American flag
French flag amazon.fr Barnes & Noble American flag
Italian flag abe books.it Nook at Barnes & Noble American flag
Italian flag amazon.it Kobo American flag
India flag junglee.com Google play American flag
UK flag abe books.co.uk O’Reilly Safari American flag
UK flag amazon.co.uk Powells American flag
UN flag other stores
Greyed out stores probably do not have the item in stock. Try looking for it with a bookfinder.
book cover recommend book⇒Java Database Programming with JDBC: Discover the Essentials for Developing Databases for Internet and Intranet Applicationsto book home
by Pratik Patel and Karl Moss 978-1-57610-056-1 paperback
publisher Coriolis
published 1996-09-13
If you are looking for an example for building a JDBC driver from the ground up, this the book you want.
Australian flag abe books anz abe books.ca Canadian flag
German flag abe books.de amazon.ca Canadian flag
German flag amazon.de Chapters Indigo Canadian flag
Spanish flag amazon.es Chapters Indigo eBooks Canadian flag
Spanish flag iberlibro.com abe books.com American flag
French flag abe books.fr amazon.com American flag
French flag amazon.fr Barnes & Noble American flag
Italian flag abe books.it Nook at Barnes & Noble American flag
Italian flag amazon.it Kobo American flag
India flag junglee.com Google play American flag
UK flag abe books.co.uk O’Reilly Safari American flag
UK flag amazon.co.uk Powells American flag
UN flag other stores
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 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
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 SQL DBMS (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 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.
{fn INSERT(string1, start, length, string2)} Insert string2 into a slot in string 1, leaving string1 and string2 themselves intact.
{fn LCASE(string) Lower case
{fn LEFT(string, count)} The leftmost count chars of a string
{fn LENGTH(string) String length in chars, excluding trailing blanks. cf CHAR_LENGTH
{fn LOCATE(string1, string2, start)} 1-based index of string2 in string1. The third argument is optional.
{fn LTRIM(string) Trim spaces from the left end of the string, leaving the original intact.
{fn REPEAT(string, count)} concatenate the string count times to itself so there are count copies in total.
{fn REPLACE(string1, string2, string3)} replaces all occurrences of string2 in string1 with string3. The original strings are left intact.
{fn RIGHT(string, count)} Rightmost count chars of a string.
{fn RTRIM(string)} trim spaces from the right end leaving the original string intact.
{fn SOUNDEX(string)} Generate a soundex from the string.
{fn SPACE(count)} Generate a string count spaces long.
{fn SUBSTRING(string, start, length)} Substring of string, 1 based
{fn UCASE(string) } Upper case, leaving original intact
Time and Date Functions
{fn CURDATE()} Current date
{fn CURTIME()} Current time
{fn DAYNAME(date)} Day of week given date. The actual string you get back depends on the SQL engine.
{fn DAYOFMONTH(date)} Day of month given date. Returns an integer in the range of 1..31.
{fn DAYOFWEEK(date)} Day of week as a number given the date. Returns an integer in the range of 1..7. A value of 1 represents the Monday.
{fn DAYOFYEAR(date)} Day of year, given date. Returns integer in the range of 1..366.
{fn HOUR(time)} hour, (0..23) given time
{fn MINUTE(time)} minute, (00..59) given time
{fn MONTH(time)} month, (1..12) given time
{fn MONTHNAME(date)} month name given date. The exact string you get depends on the SQL engine.
{fn NOW()} Current timestamp
{fn QUARTER(date)} Quarter given date. An integer 1..4. 1 represents January 1 through March 31
{fn SECOND(time)} minute, (00..59) given time
{fn TIMESTAMPADD(interval, count, timestamp)} Add count intervals (in seconds, with 6 decimal places) to a timestamp to get another timestamp. The interval parameter is specified as SQL_TSI_YEAR, SQL_TSI_DAY, SQL_TSI_SECOND
{fn TIMESTAMPDIFF(interval, timestamp1, timestamp2)} Difference in two timestamps in seconds, with 6 decimal places (i.e. to the microsecond) The interval parameter is specified as SQL_TSI_YEAR, SQL_TSI_DAY, SQL_TSI_SECOND.
{fn WEEK(date)} Week number of year, 1..53, week 1 is the first week with any days in this year.
{fn YEAR(date) } Year given date
System Functions
{fn DATABASE()} name of the database
{fn IFNULL(expression, replacement)} result is normally expression, but if expression is null, the result is the replacement.
{fn USER() } Who is accessing the SQL database.
Conversion Functions
{fn CONVERT(value, SQLtype) } type may be SQL_BIGINT, SQL_BINARY, SQL_BIT, SQL_CHAR, SQL_DATE, SQL_DECIMAL, SQL_DOUBLE, SQL_FLOAT, SQL_INTEGER, SQL_LONGVARBINARY, SQL_LONGVARCHAR, SQL_REAL, SQL_SMALLINT, SQL_TIME, SQL_TIMESTAMP, SQL_TINYINT, SQL_VARBINARY and SQL_VARCHAR

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

Connection to Derby

Connection to Hypersonic

Connection to Microsoft SQL Server

Connection to MySQL

Connection to ODBC Bridge

Connection to Oracle

Connection to PostgreSQL

Using PreparedStatement

Blobs

JDBC gives you several database-independent ways of fetching binary data from your SQL database using ResultSet. getBinaryStream, .getBlob,.getObject and.. getBytes. To enter binary data, there are corresponding methods: PreparedStatement. setBinaryStream, .setBlob,.setObject and.. setBytes.

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:

Auto Increment

The
confirmnumber INT NOT NULLPRIMARY KEYAUTO_INCREMENT,
When you insert records into the database, you leave that field NULL. You can retrieve
SELECT LAST_INSERT_ID();

Gotchas

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.


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.

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?

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.

Books

Learning More

Oracle’s Javadoc on java.sql package : available:
Oracle’s Javadoc on javax.sql package : available:
Oracle’s Javadoc on Connection class : available:
Oracle’s Javadoc on DriverManager class : available:
Oracle’s Javadoc on Datasource class : available:
Oracle’s Javadoc on PreparedStatement class : available:
Oracle’s Javadoc on Statement class : available:
Oracle’s Javadoc on ResultSet class : available:


This page is posted
on the web at:

http://mindprod.com/jgloss/jdbc.html

Optional Replicator mirror
of mindprod.com
on local hard disk J:

J:\mindprod\jgloss\jdbc.html
Canadian Mind Products
Please the feedback from other visitors, or your own feedback about the site.
Contact Roedy. Please feel free to link to this page without explicit permission.

IP:[65.110.21.43]
Your face IP:[44.197.251.102]
You are visitor number