MySQL Connector/J passes all of the tests in the publicly available version of Sun's JDBC compliance test suite. However, in many places the JDBC specification is vague about how certain functionality should be implemented, or the specification allows leeway in implementation.
This section gives details on a interface-by-interface level about how certain implementation decisions may affect how you use MySQL Connector/J.
Blob
Starting with Connector/J version 3.1.0, you can emulate
Blobs with locators by adding the property
'emulateLocators=true' to your JDBC URL. Using this method,
the driver will delay loading the actual Blob data until you
retrieve the other data and then use retrieval methods
(getInputStream()
,
getBytes()
, and so forth) on the blob
data stream.
For this to work, you must use a column alias with the value of the column to the actual name of the Blob, for example:
SELECT id, 'data' as blob_data from blobtable
For this to work, you must also follow these rules:
The Blob implementation does not allow in-place modification
(they are copies, as reported by the
DatabaseMetaData.locatorsUpdateCopies()
method). Because of this, you should use the corresponding
PreparedStatement.setBlob()
or
ResultSet.updateBlob()
(in the case of
updatable result sets) methods to save changes back to the
database.
MySQL Enterprise. MySQL Enterprise subscribers will find more information about type conversion in the Knowledge Base article, Type Conversions Supported by MySQL Connector/J. To subscribe to MySQL Enterprise see http://www.mysql.com/products/enterprise/advisors.html.
CallableStatement
Starting with Connector/J 3.1.1, stored procedures are
supported when connecting to MySQL version 5.0 or newer via
the CallableStatement
interface.
Currently, the getParameterMetaData()
method of CallableStatement
is not
supported.
Clob
The Clob implementation does not allow in-place modification
(they are copies, as reported by the
DatabaseMetaData.locatorsUpdateCopies()
method). Because of this, you should use the
PreparedStatement.setClob()
method to
save changes back to the database. The JDBC API does not
have a ResultSet.updateClob()
method.
Connection
Unlike older versions of MM.MySQL the
isClosed()
method does not ping the
server to determine if it is alive. In accordance with the
JDBC specification, it only returns true if
closed()
has been called on the
connection. If you need to determine if the connection is
still valid, you should issue a simple query, such as
SELECT 1
. The driver will throw an
exception if the connection is no longer valid.
DatabaseMetaData
Foreign Key information
(getImportedKeys()
/getExportedKeys()
and getCrossReference()
) is only
available from InnoDB tables. However, the driver uses
SHOW CREATE TABLE
to retrieve
this information, so when other storage engines support
foreign keys, the driver will transparently support them as
well.
PreparedStatement
PreparedStatements are implemented by the driver, as MySQL
does not have a prepared statement feature. Because of this,
the driver does not implement
getParameterMetaData()
or
getMetaData()
as it would require the
driver to have a complete SQL parser in the client.
Starting with version 3.1.0 MySQL Connector/J, server-side prepared statements and binary-encoded result sets are used when the server supports them.
Take care when using a server-side prepared statement with
large parameters that are
set via setBinaryStream()
,
setAsciiStream()
,
setUnicodeStream()
,
setBlob()
, or
setClob()
. If you want to re-execute
the statement with any large parameter changed to a nonlarge
parameter, it is necessary to call
clearParameters()
and set all
parameters again. The reason for this is as follows:
During both server-side prepared statements and
client-side emulation, large data is exchanged only when
PreparedStatement.execute()
is
called.
Once that has been done, the stream used to read the data on the client side is closed (as per the JDBC spec), and cannot be read from again.
If a parameter changes from large to nonlarge, the
driver must reset the server-side state of the prepared
statement to allow the parameter that is being changed
to take the place of the prior large value. This removes
all of the large data that has already been sent to the
server, thus requiring the data to be re-sent, via the
setBinaryStream()
,
setAsciiStream()
,
setUnicodeStream()
,
setBlob()
or
setClob()
methods.
Consequently, if you want to change the type of a parameter
to a nonlarge one, you must call
clearParameters()
and set all
parameters of the prepared statement again before it can be
re-executed.
ResultSet
By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and can not allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.
To enable this functionality, you need to create a Statement instance in the following manner:
stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE);
The combination of a forward-only, read-only result set,
with a fetch size of Integer.MIN_VALUE
serves as a signal to the driver to stream result sets
row-by-row. After this any result sets created with the
statement will be retrieved row-by-row.
There are some caveats with this approach. You will have to read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown.
The earliest the locks these statements hold can be released
(whether they be MyISAM
table-level locks
or row-level locks in some other storage engine such as
InnoDB
) is when the statement completes.
If the statement is within scope of a transaction, then locks are released when the transaction completes (which implies that the statement needs to complete first). As with most other databases, statements are not complete until all the results pending on the statement are read or the active result set for the statement is closed.
Therefore, if using streaming results, you should process them as quickly as possible if you want to maintain concurrent access to the tables referenced by the statement producing the result set.
ResultSetMetaData
The isAutoIncrement()
method only works
when using MySQL servers 4.0 and newer.
Statement
When using versions of the JDBC driver earlier than 3.2.1,
and connected to server versions earlier than 5.0.3, the
setFetchSize()
method has no effect,
other than to toggle result set streaming as described
above.
Connector/J 5.0.0 and later include support for both
Statement.cancel()
and
Statement.setQueryTimeout()
. Both require
MySQL 5.0.0 or newer server, and require a separate
connection to issue the
KILL QUERY
statement. In the case of
setQueryTimeout()
, the implementation
creates an additional thread to handle the timeout
functionality.
Failures to cancel the statement for
setQueryTimeout()
may manifest
themselves as RuntimeException
rather
than failing silently, as there is currently no way to
unblock the thread that is executing the query being
cancelled due to timeout expiration and have it throw the
exception instead.
MySQL does not support SQL cursors, and the JDBC driver doesn't emulate them, so "setCursorName()" has no effect.
Connector/J 5.1.3 and later include two additional methods:
setLocalInfileInputStream()
sets an
InputStream
instance that will be
used to send data to the MySQL server for a
LOAD DATA
LOCAL INFILE
statement rather than a
FileInputStream
or
URLInputStream
that represents the
path given as an argument to the statement.
This stream will be read to completion upon execution of
a LOAD DATA
LOCAL INFILE
statement, and will automatically
be closed by the driver, so it needs to be reset before
each call to execute*()
that would
cause the MySQL server to request data to fulfill the
request for
LOAD DATA
LOCAL INFILE
.
If this value is set to NULL
, the
driver will revert to using a
FileInputStream
or
URLInputStream
as required.
getLocalInfileInputStream()
returns
the InputStream
instance that will be
used to send data in response to a
LOAD DATA
LOCAL INFILE
statement.
This method returns NULL
if no such
stream has been set via
setLocalInfileInputStream()
.
User Comments
Add your own comment.