Blobstore API implementation that uses standard JDBC API and Blob type.
All artifacts are available on maven-central.
JDBC blobstore has been tested with the following database engines:
- Apache Derby
- HSQL Database Engine
- MySQL
- Oracle
- PostgreSQL
- Microsoft SQL Server
It might be possible that Blobstore works with other database engines as well. There is a possibility to pass a configuration object to the JdbcBlobstore class that allows to handle specialties of the different database engines.
SQL scripts of tested databases are available in the distributed JAR file.
However, it is advised to use Liquibase to populate the database. The Liquibase changelog XML is available in the distributed JAR file as well.
For OSGi environments, Liquibase changelog is also provided as liquibase.schema Bundle capability.
// Get the JTA aware dataSource from any place
DataSource myDataSoure = getDataSource();
// Create the blobstore instance
Blobstore blobstore = new JdbcBlobstore(myDataSource);
Now that you have a blobstore instance, you can use it in the way as it is described in the documentation of the blobstore-api project.
One of the best JTA aware datasource implementations is BasicManagedDataSource from the commons-dbcp project.
- PostgreSQL uses LargeObject API inside.
- To be able to provide consistent data, the implementation uses FOR SHARE row level lock during reading the blob.
- In case other XA resources participate in the same JTA transaction, the max_prepare_transactions should be set to the number of allowed connections.
In case you are free to choose, postgreSQL is suggested as the backend database.
Oracle is the only known database that allows starting the update of a blob while reading operations are in progress. Oracle is 3-6 times faster than other database engines. They must know something for their money :-).
MySQL has two modes:
The whole content of a blob is copied into the memory of the client program when MysqlXADataSource.setEmulateLocators(false); is used. This is the default mode.
Blob chunks are modified with SQL statements when MysqlXADataSource.setEmulateLocators(true); is used. In this mode, the content of the whole blob will not be held in the memory of the client application. However, every time a tiny modification or read is made, the whole blob is processed on the server side.
Usage of MySQL is suggested only in case of small size blobs or in case the whole content of the blob is processed (read or written) always. In case only chunks of the blob is processed or seeking is used frequently, the application can get seriously slow.
SQLServer holds the whole blob in the memory on the client side. Therefore the usage of SQLServer is only suggested for small blobs.
As this blobstore uses distributed transactions, in case of using the proprietary jdbc driver, you need to install XA support for your SQLServer.
Based on the stress tests it seems that pessimistic lock is not working well in Derby. It is possible that one transaction accesses the expired data that was locked for update and than modified by other transaction. From the point of view of Blobstore it means that the version of a blob that is modified on multiple threads will not get incremented as many times as it was modified.
Passed the stress tests.
H2 does not support seeking in blobs at all. Until this is implemented, H2 will not be supported by JdbcBlobstore.
Wrap your blobstore instance with blobstore-cache as it can improve reading performance a lot.