Example 2.15. Example properties for MySQL
openjpa.ConnectionDriverName: com.mysql.jdbc.Driver openjpa.ConnectionURL: jdbc:mysql://SERVER_NAME/DB_NAME
MySQL has support for "query hints", which are keywords embedded in SQL that provide some hint for how the query should be executed. These hints are usually designed to provide suggestions to the MySQL query optimizer for how to efficiently perform a certain query, and aren't typically needed for any but the most intensive queries. OpenJPA supports hints to be placed between SELECT keyword and column list.
Example 2.16. Using MySQL Hints
Query query = em.createQuery(...);
query.setHint("openjpa.hint.MySQLSelectHint", "SQL_NO_CACHE");
List results = query.getResultList();
The default table types that MySQL uses do not support transactions, which will
prevent OpenJPA from being able to roll back transactions. Use the
InnoDB table type for any tables that OpenJPA will access.
MySQL does not support sub-selects in versions prior to 4.1, so
some operations (such as the isEmpty() method in a
query) will fail due to this.
Rollback due to database error or optimistic lock violation is not supported
unless the table type is one of the MySQL transactional types. Explicit calls to
rollback() before a transaction has been committed,
however, are always supported.
Floats and doubles may lose precision when stored in some datastores.
When storing a field of type java.math.BigDecimal, some
datastores will add extraneous trailing 0 characters, causing an equality
mismatch between the field that is stored and the field that is retrieved.
When using large result sets with MySQL there are a number of documented limitations. Please read the section titled "ResultSet" in the "MySQL JDBC API Implementation Notes". The net effect of these limitations is that you will have to read all of the rows of a result set (or close the connection) before you can issue any other queries on the connection, or an exception will be thrown. Setting openjpa.FetchBatchSize to any value greater than zero will enable streaming result sets.
The use of LOB streaming is limited. When reading LOB data from the database, the MySQL JDBC driver will actually load all the data into memory at the same time.