JarCastingTools for working with BLOBs in different databases
Library allows to work with BLOBs in streaming mode (particularly - write to BLOB as to OutputStream) in different databases using the same API (created initially for PostgreSQL). Supports transparent compression of BLOB data.
Depends on spring-jdbc.
Library is available in Maven central:
<dependency>
<groupId>com.alexkasko.springjdbc</groupId>
<artifactId>blob-tool</artifactId>
<version>1.0</version>
</dependency>
Javadocs for the latest release are available here.
Usage example with Spring and PostgreSQL
Blob tool setup:
@Bean
public BlobTool blobTool() {
return new PostgresBlobTool(dataSource(), compressor());
}
Create BLOB and write data into it:
OutputStreamBlob blob = null;
try {
// create server-side BLOB
blob = blobTool.create();
// use OutputSteam from BLOB
createHugeReport(blob.outputStream());
// save BLOB id in application table for subsequent use
save(new AppObj(foo, bar, blob.getId()));
} finally {
// release DB resources
closeQuietly(blob);
}
Read data from BLOB:
InputStreamBlob blob = null;
try {
// open server-side BLOB by ID
blob = blobTool.load(id);
// read stored data
processStoredData(blob.inputStream());
} finally {
// release DB resources
closeQuietly(blob);
}
Working with BLOBs through their IDs
Library was initially created for PostgreSQL and uses it's approach when long BLOB ID (OID in postgres) is stored in application table and blob data should be accessed using this ID explicitly. For other RDBMSes that doesn't allow direct control over BLOB ID (when BLOB itself is "stored" in application table) library uses additional tables like this:
create table blob_storage (
id int primary key,
data blob
);
Streaming BLOBs in JDBC
This library uses BLOBs exclusively in streaming mode without loading them into memory on any operation.
Most RDBMSes support streaming read of BLOB data through JDBC API. But proper streaming write to BLOBs is less supported. To write into server-side BLOBs in streaming mode using OutputStream you may use this JDBC method Blob#setBinaryStream.
Besides its usage is not intuitive (set method returns OutputStream):
Blob b = ...
OutputStream blobStream = b.setBinaryStream(1);
it's also (among popular RDBMSes) implemented properly (with real server-side BLOB) only in PostgreSQL and in Oracle.
This library contains different implementations for RDBMSes that support server side streaming write and for those that does not. Fallback implementation (for MSSQL, MySQL etc.) uses temporary file for writing to BLOB as to OutputStream and then flushes data to server. This method may not suit highload applications, but may be useful e.g. for using H2 database instead of PostgreSQL or Oracle in testing/prototyping.
BLOBs compression
Library supports transparent compression of BLOB data. Supported compression methods:"
GzipCompressor: GZIP compression using JDK GZIP implementationSnappyCompressor: Snappy compressionXzCompressor: LZMA2 compressionNoCompressor: compression is disabled
These libraries are required for Snappy and XZ compression:
<dependency>
<groupId>org.iq80.snappy</groupId>
<artifactId>snappy</artifactId>
<version>0.3</version>
</dependency>
<dependency>
<groupId>org.tukaani</groupId>
<artifactId>xz</artifactId>
<version>1.0</version>
</dependency>
License information
This project is released under the Apache License 2.0
Changelog
1.0 (2013-03-15)
- initial public version