I want to store BLOB data into MariaDB using MariaDB connector and I try to find the most efficient solution. Now I'm using setBlob() or setBinaryStream() method to store data. But it is so slow comparing to direct streaming of data to database. I used streaming for Oracle database and everything worked fast and perfectly. But since I changed the database to MariaDB, direct streaming doesn't work.
The code for direct streaming is the following:
Blob localBlob = lrs.getBlob("MyData");
try {
los = localBlob.setBinaryStream(1);
} catch (Throwable t) {
}
int countBytesRead;
// md5 hash
InputStream dis = new DigestInputStream(inputStreamArgument, localHash);
byte[] localBuffer = new byte[BUFFER_SIZE];
while ((countBytesRead = dis.read(localBuffer)) >= 0) {
los.write(localBuffer, 0, countBytesRead);
}
los.close();
inputStreamArgument.close();
lstmt.close();
解决方案
Short answer - you cannot stream blobs as of today. this connector always reads at least one entire row into memory. Moreover, the MariaDB Server( and MySQL as well) does not handle blobs very efficiently, it also would load the entire blog into memory, only on the server side. If you want do-it-yourself streaming, maybe you can split the blob into smaller chunks of say 4K, and store them in different rows in a dedicated table. you can SELECT * from my_blob to read that as multiple rows, and use e.g setFetchSize(1) to read one chunk at a time.