BLOB

You can emulate BLOBs with locators by adding the propertyemulateLocators=trueto 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 BLOB implementation does not allow in-place modification (they are copies, as reported by theDatabaseMetaData.locatorsUpdateCopies()方法)。贝科use of this, use the correspondingPreparedStatement.setBlob()orResultSet.updateBlob()(in the case of updatable result sets) methods to save changes back to the database.

Connection

TheisClosed()method does not ping the server to determine if it is available. In accordance with the JDBC specification, it only returns true ifclosed()has been called on the connection. If you need to determine if the connection is still valid, issue a simple query, such as选择1. The driver will throw an exception if the connection is no longer valid.

DatabaseMetaData

Foreign keyinformation (getImportedKeys()/getExportedKeys()andgetCrossReference()) is only available fromInnoDBtables. The driver usesSHOW CREATE TABLEto retrieve this information, so if any other storage engines add support for foreign keys, the driver would transparently support them as well.

PreparedStatement

Two variants of prepared statements are implemented by Connector/J, the client-side and the server-side prepared statements. Client-side prepared statements are used by default because early MySQL versions did not support the prepared statement feature or had problems with its implementation. Server-side prepared statements and binary-encoded result sets are used when the server supports them. To enable usage of server-side prepared statements, setuseServerPrepStmts=true.

Be careful when using a server-side prepared statement withlargeparameters that are set usingsetBinaryStream(),setAsciiStream(),setUnicodeStream(),setCharacterStream(),setNCharacterStream(),setBlob(),setClob(), orsetNCLob(). To re-execute the statement with any large parameter changed to a nonlarge parameter, callclearParameters()and set all parameters again. The reason for this is as follows:

Consequently, to change the type of a parameter to a nonlarge one, you must callclearParameters()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 aStatementinstance 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 ofInteger.MIN_VALUEserves 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 beMyISAMtable-level locks or row-level locks in some other storage engine such asInnoDB) 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.

Another alternative is to use cursor-based streaming to retrieve a set number of rows each time. This can be done by setting the connection propertyuseCursorFetchto true, and then callingsetFetchSize(int)withintbeing the desired number of rows to be fetched each time:

conn = DriverManager.getConnection("jdbc:mysql://localhost/?useCursorFetch=true", "user", "s3cr3t"); stmt = conn.createStatement(); stmt.setFetchSize(100); rs = stmt.executeQuery("SELECT * FROM your_table_here");

Statement

Connector/J includes support for bothStatement.cancel()andStatement.setQueryTimeout(). Both require a separate connection to issue theKILL QUERYstatement. In the case ofsetQueryTimeout(), the implementation creates an additional thread to handle the timeout functionality.

MySQL does not support SQL cursors, and the JDBC driver does not emulate them, sosetCursorName()has no effect.

Connector/J also supplies two additional methods: