如何通过JDBC将数据流传输到MariaDB [英] How to stream data to MariaDB over JDBC

问题描述

我想使用MariaDB连接器将BLOB数据存储到MariaDB中，我试图找到最有效的解决方案.现在，我正在使用setBlob()或setBinaryStream()方法来存储数据.但是，与将数据直接流传输到数据库相比，它是如此之慢.我为Oracle数据库使用了流式传输，并且一切都快速而完美地工作.但是由于我将数据库更改为MariaDB，所以直接流式传输不起作用.

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();

推荐答案

简短的回答-到目前为止，您无法流式处理Blob.该连接器始终至少将整行读取到内存中.而且，MariaDB服务器(以及MySQL)不能非常有效地处理blob，它也只会将整个博客加载到内存中，而仅在服务器端.如果您想自己做流式传输，也许您可​​以将Blob分成较小的块，例如4K，并将它们存储在专用表的不同行中.您可以从my_blob中选择*以将其读取为多行，然后使用例如setFetchSize(1)一次读取一个块.

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.

这篇关于如何通过JDBC将数据流传输到MariaDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！