Можно ли использовать Bulk Copy Program (BCP) для извлечения более одной записи VARBINARY?

Вопрос или проблема

У меня есть приложение с бэкендом на Java, которое извлекает содержимое файла (PDF, XLSX и т. д.), чтобы отправить его на фронтэнд по запросу.

Каждый файл хранится как одна запись в таблице базы данных, с полем VARBINARY, в котором содержится фактическое содержимое файла в виде блоба.

В настоящее время приложение извлекает только один файл за раз, вызывая хранимую процедуру, которая получает содержимое для одного файла в виде массива байтов.

Вот как работает хранимая процедура ‘doc_retrieve’ для извлечения содержимого поля VARBINARY ‘content’ из таблицы ‘document_table’ с использованием поля первичного ключа ‘doc_id’

CREATE OR ALTER PROCEDURE [dbo].[doc_retrieve] 

    @DOC_ID                 INT

AS

    select  content 
    from    document_table  
    where   doc_id = @DOC_ID

Поле ‘doc_id’ является уникальным первичным ключом, хранимая процедура всегда будет возвращать только одну запись.

Код на Java вызывает хранимую процедуру и помещает содержимое файла в массив byte[], чтобы отобразить его пользователю следующим образом:

try (var statement = connection.prepareCall("{call dbo.doc_retrieve (?)}")) {
    statement.setObject(DOC_ID, 1234);
    ResultSet resultSet = statement.executeQuery();
    List<byte[]> contentList = new ArrayList<>();
    while (resultSet.next()) {
        contentList.add((byte[])resultSet.getObject(1));
    }
    if(contentList != null && !contentList.isEmpty()) {
        return contentList.get(0);
    }
    return null;
} catch (SQLServerException e) {
            
}

Что бы мне хотелось сделать, так это вернуть содержимое более чем одного файла за раз.

Например, если пользователь захочет вернуть все PDF файлы, будет создана новая хранимая процедура:

CREATE OR ALTER PROCEDURE [dbo].[doc_retrieve_by_file_extension] 

    @FILE_EXTENSION     VARCHAR(20)         

AS

    select  content 
    from    document_table  
    where   file_extension = @FILE_EXTENSION

и она будет вызвана в Java следующим образом:

try (var statement = connection.prepareCall("{call dbo.doc_retrieve_by_file_extension (?)}")) {
    statement.setObject(FILE_EXTENSION, '.PDF');
    ResultSet resultSet = statement.executeQuery();

Тогда будет извлечено более одного файла и показано пользователям.

Кто-нибудь знает, если BCP это правильный путь?

Большое спасибо.

Ответ или решение

Использование Bulk Copy Program (BCP) для извлечения нескольких записей с типом VARBINARY из базы данных не является оптимальным решением. BCP предназначен в первую очередь для назначения и извлечения больших объемов данных, а не для работы с обменом файлов, сохраняемых как BLOB в базе данных. Ваша текущая реализация, в которой используется хранимая процедура для выборки нескольких файлов, является более подходящей.

Вот шаги, которые помогут вам правильно реализовать данное функциональное требование:

Создание хранимой процедуры для извлечения файлов с определенным расширением:
Ваша хранимая процедура уже выглядит достаточно хорошо. Она позволяет извлекать контент на основе расширения файла:
```
CREATE OR ALTER PROCEDURE [dbo].[doc_retrieve_by_file_extension] 
   @FILE_EXTENSION VARCHAR(20)         
AS
BEGIN
   SELECT content 
   FROM document_table  
   WHERE file_extension = @FILE_EXTENSION
END
```

Обновление Java-кода для работы с несколькими записями:
В вашем Java-коде необходимо изменить логику, чтобы он мог извлекать и обрабатывать несколько записей. Здесь представлен пример того, как можно изменять текущий код:

try (var statement = connection.prepareCall("{call dbo.doc_retrieve_by_file_extension (?)}")) {
   statement.setObject(1, ".PDF");
   ResultSet resultSet = statement.executeQuery();

   List<byte[]> contentList = new ArrayList<>();
   while (resultSet.next()) {
       contentList.add((byte[]) resultSet.getObject(1));
   }

   if (!contentList.isEmpty()) {
       return contentList; // Вернёт список всех файлов
   }
   return Collections.emptyList(); // Вернет пустой список, если файлов нет
} catch (SQLServerException e) {
   e.printStackTrace(); // Обработка исключений
}

Возврат и работа с извлечёнными данными:
После получения списка файлов, вы можете работать с каждым файлом по отдельности или отображать их одновременно в интерфейсе пользователя. Например, вы можете отправить массив байтов для каждого файла на фронтенд, чтобы пользователь мог их загрузить.
Проверка параметров и производительность:
Обязательно учитывайте производительность базы данных. Если в таблице document_table много записей с определённым расширением, ваши запросы могут занимать значительное время. Рассмотрите возможность добавления индексов на столбец file_extension, чтобы ускорить поиск.
Предостережения по поводу BCP:
Хотя BCP может быть полезен для массовой загрузки и выгрузки данных, его использование для извлечения VARBINARY данных нецелесообразно. BCP более подходит для сценариев, связанных с большими объемами текстовых данных или структуры данных, а не для работы с отдельными записями BLOB. Использование BCP для извлечения файлов может не предоставить необходимую гибкость и удобство работы, которые вам нужны.

Следуя этим рекомендациям, вы сможете более эффективно извлекать и обрабатывать несколько записей VARBINARY из вашей базы данных, не прибегая к BCP.