What is DML?
DML is an abbreviation for Data Manipulation Language.
This portion of the SQL standard is concerned with
manipulating the data in a database as opposed to the
structure of a database. The core verbs for DML are
SELECT, INSERT, DELETE, UPDATE, COMMIT and ROLLBACK.
What is the significance of
DataBaseMetaData.tableIndexStatistics? How to obtain and
To answer the second question first, the
tableIndexStatistic constant in the TYPE column will
identify one of the rows in the ResultSet returned when
DatabaseMetaData.getIndexInfo() is invoked. If you
analyze the wordy API, a tableIndexStatistic row will
contain the number of rows in the table in the
CARDINALITY column and the number of pages used for the
table in the PAGES column.
What types of DataSource objects are specified in the
* Basic - Provides a standard Connection object.
* Pooled - Provides a Connection pool and returns a
Connection that is controlled by the pool.
* Distributed - Provides a Connection that can
participate in distributed transactions ( more than one
DBMS is involved). It is anticipated, but not enforced,
that a distributed DataSource will also provide pooling.
However, there are no standard methods available in the
DataSource class to determine if one has obtained a
pooled and/or distributed Connection.
What is a JDBC 2.0 DataSource?
The DataSource class was introduced in the JDBC 2.0
Optional Package as an easier, more generic means of
obtaining a Connection. The actual driver providing
services is defined to the DataSource outside the
application ( Of course, a production quality app can
and should provide this information outside the app
anyway, usually with properties files or ResourceBundles
). The documentation expresses the view that DataSource
will replace the common DriverManager method.
Does the database server have to be running Java or have
Java support in order for my remote JDBC client app to
access the database?
The answer should always be no. The two critical
requirements are LAN/internet connectivity and an
appropriate JDBC driver. Connectivity is usually via
TCP/IP, but other communication protocols are possible.
Unspoken, but assumed here is that the DBMS has been
started to listen on a communications port. It is the
JDBC driver's job to convert the SQL statements and JDBC
calls to the DBMS' native protocol. From the server's
point of view, it's just another data request coming
into the port, the programming language used to send the
data is irrelevant at that point.
Which Java and java.sql data types map to my specific
JDBC is, of necessity, reliant on the driver and
underlying DBMS. These do not always adhere to standards
as closely as we would like, including differing names
for standard Java types. To deal with this, first, there
are a number of tables available in the JDK JDBC
documentation dealing with types.
Are the code examples from the JDBC API Tutorial and
Reference, Second Edition available online?
When an SQL select statement doesn't return any rows, is
an SQLException thrown?
No. If you want to throw an exception, you could wrap
your SQL related code in a custom class and throw
something like ObjectNotFoundException when the returned
ResultSet is empty.
Why should I consider optimistic versus pessimistic
approaches to database updates?
In a modern database, possibly the two most important
issues are data integrity and concurrency ( multiple
users have access to and can update the data ). Either
approach can be appropriate, depending on the
application, but it is important to be aware of possible
consequences to avoid being blindsided.
A pessimistic approach, with locks, is usually seen as
good for data integrity, although it can be bad for
concurrency, especially the longer a lock is held. In
particular, it guarantees against 'lost updates' -
defined as an update performed by one process between
the time of access and update by another process, which
overwrites the interim update. However, other users are
blocked from updating the data and possibly reading it
as well if the read access also tries to acquire a lock.
A notorious problem can arise when a user accesses data
for update and then doesn't act on it for a period of
time. Another situation that occurred with one of my
clients is that a batch ( non-interactive ) process may
need to update data while an interactive user has an
update lock on the same data. In that case, data
integrity goes out the window and, depending on how the
application is written, more problems may be introduced.
( No, we did not write the interactive update program
and yes, we had recovery procedures in place. )
An optimstic approach can alleviate lock concurrency
problems, but requires more code and care for integrity.
The "optimistic" definition usually says that
expectations of update clashes are rare, but I view them
as normal occurrances in a heavily used database. The
basics are that any changes between time of access and
time of update must be detected and taken into account.
This is often done by comparing timestamps, but one must
be sure that the timestamp is always changed for an
update and, of course, that the table contains a
timestamp column. A more involved, but more complete
method involves saving the original columns and using
them in the 'Where' clause of the Update statement. If
the update fails, the data has changed and the latest
data should be reaccessed.
What is optimistic concurrency?
An optimistic approach dispenses with locks ( except
during the actual update ) and usually involves
comparison of timestamps, or generations of data to
ensure that data hasn't changed between access and
update times. It's generally explained that the term
optimistic is used because the expectation is that a
clash between multiple updates to the same data will
What is pessimistic concurrency?
With a pessimistic approach, locks are used to ensure
that no users, other than the one who holds the lock,
can update data. It's generally explained that the term
pessimistic is used because the expectation is that many
users will try to update the same data, so one is
pessimistic that an update will be able to complete
properly. Locks may be acquired, depending on the DBMS
vendor, automatically via the selected Isolation Level.
Some vendors also implement 'Select... for Update',
which explicitly acquires a lock.
Can I get information about a ResultSet's associated
Statement and Connection in a method without having or
adding specific arguments for the Statement and
Yes. Use ResultSet.getStatement(). From the resulting
Statement you can use Statement.getConnection().
How can I tell if my JDBC driver normalizes
java.sql.Date and java.sql.Time objects?
To actually determine the values, the objects must be
converted to a java.util.Date and examined. See What
does normalization mean for java.sql.Date and
java.sql.Time? for the definition of normalization.
Notice that even a debugger will not show whether these
objects have been normalized, since the getXXX methods
in java.sql.Date for time elements and in java.sql.Time
for date elements throw an exception.
So, while a java.sql.Date may show 2001-07-26, it's
normalized only if the java.util.Date value is:
Thu Jul 26 00:00:00 EDT 2001
and while a java.sql.Time may show 14:01:00, it's
normalized only if the java.util.Date value is:
Thu Jan 01 14:01:00 EST 1970
Page Numbers :