OpenJPA is an implementation of the JPA (Java Persistence API) standards. Initially, I used OpenJPA with Derby which is also known as JavaDB. It works well. However, when i tried to use MySQL as the backend database, I encountered a few issues. Here are a few small tips i would like to share when using OpenJPA with MySQL:
Change the default storage engine
By default, OpenJPA uses innodb as the storage engine. While my application does not need to support transactions and other fancy db features, i’d like to use more plain and “traditional” myisam storage engine. To do so, I just need to set the property “openjpa.jdbc.DBDictionary” with the value “TableType=myisam”. This property object is the one passed to the method “Persistence.createEntityManagerFactory(String, Properties)”.
Allow to store bigger objects (blobs)
By default, OpenJPA maps the java object to mysql data type “blob”. However, as you may be aware of, blob type in mysql only can store up to 65536(216) bytes of data (check http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html), which is quite smaller than the blob in JavaDB or Derby (2G).
Mysql does provide other data types which supports larger storage. mediumblob supports up to 16M while longblob up to 4G. So if I want to store objects other than 64K, I need to change the mapping in OpenJPA. To change the mapping, just set the same property “openjpa.jdbc.DBDictionary”, but with value “blobTypeName=mediumblob”.
If I want both myisam as storage engine and mediumblob as mapped blob type, the property should be set as: “TableType=myisam,blobTypeName=mediumblob”.
Avoid the error “Packet too large”
Now i can save objects up to 16M in size to the database. I run my program, only to find another exception popped up. It says something like “Packet too large”.
This requires the change of mysql server setting as indicated in mysql documentation. The setting “max_allowed_packet“, default to 1M bytes, is too small in my case.
