22.3.5.2. JDBC API Implementation Notes

MySQL Connector/J passes all of the tests in the publicly available version of Sun's JDBC compliance test suite. This section gives details on a interface-by-interface level about implementation decisions that might affect how you code applications with MySQL Connector/J. The JDBC specification is vague about how certain functionality should be implemented, or the specification enables leeway in implementation.

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.

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
```
You must also follow these rules:
- The SELECT must reference only one table. The table must have a primary key.
- The SELECT must alias the original BLOB column name, specified as a string, to an alternate name.
- The SELECT must cover all columns that make up the primary key.
The BLOB implementation does not allow in-place modification (they are copies, as reported by the DatabaseMetaData.locatorsUpdateCopies() method). Because of this, use the corresponding PreparedStatement.setBlob() or ResultSet.updateBlob() (in the case of updatable result sets) methods to save changes back to the database.
CallableStatement

Starting with Connector/J 3.1.1, stored procedures are supported when connecting to MySQL version 5.0 or newer using 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, use the PreparedStatement.setClob() method to save changes back to the database. The JDBC API does not have a ResultSet.updateClob() method.
Connection

Unlike the pre-Connector/J JDBC driver (MM.MySQL), the isClosed() method does not ping the server to determine if it is available. 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, 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. The driver uses SHOW CREATE TABLE to retrieve this information, so if any other storage engines add support for foreign keys, the driver would 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 using setBinaryStream(), setAsciiStream(), setUnicodeStream(), setBlob(), or setClob(). To re-execute the statement with any large parameter changed to a nonlarge parameter, 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, using the setBinaryStream(), setAsciiStream(), setUnicodeStream(), setBlob() or setClob() method.
Consequently, 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 cannot 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, 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 must 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, 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.

Note

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.

Note

The MySQL statement KILL QUERY (which is what the driver uses to implement Statement.cancel()) is non-deterministic; thus, avoid the use of Statement.cancel() if possible. If no query is in process, the next query issued will be killed by the server. This race condition is guarded against as of Connector/J 5.1.18.

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 using setLocalInfileInputStream().