Store and view pdf file to/from database

I am currently using FileDescriptor to handle file saving and viewing in my application. However, I want to save the file directly to the database instead of using file storage mechanism of FileDescriptor.

To save Image the following code works fine to upload employee photo to the database (field byteArray):

@Subscribe("employeeImageUpload")
public void onEmployeeImageUploadFileUploadSucceed(FileUploadField.FileUploadSucceedEvent event) {

    if (employeeImageUpload.getFileContent() != null) {
        try {
            getEditedEntity().setEmplPhoto(IOUtils.toByteArray(employeeImageUpload.getFileContent()));
        } catch (IOException e) {
            notifications.create(Notifications.NotificationType.TRAY).withDescription("Image file upload error").show();
        }
    }
    employeeImageUpload.setValue(null);
}

However, Now I want to have multiple documents of each employee where I have a composite entity named: employeeAttachment where I have a byteArray field named fileImage. I want to save the pdf files here. When I used the following code:

  @Subscribe("uploadField")
public void onUploadFieldFileUploadSucceed(FileUploadField.FileUploadSucceedEvent event) {
    EmployeeAttachment attachment = dataContext.create(EmployeeAttachment.class);

    if (uploadField.getFileContent() != null) {
        try {
            attachment.setFileImage(IOUtils.toByteArray(uploadField.getFileContent()));
        } catch (IOException e) {
            notifications.create(Notifications.NotificationType.TRAY).withDescription("Image file upload error").show();
        }
    }

    attachment.setEmployee(getEditedEntity());
    employeeAttachmentDc.getMutableItems().add(attachment);

    uploadField.clear();
}

It seems it’s working as I see the byte array in the employeeAttachmentTable as follows:

image

Now my questions is, how can is display the file image on the rightside when I select the table row on the left side of the screen (above image)?

 @Subscribe("employeeAttachmentTable")
public void onEmployeeAttachmentTableSelection(Table.SelectionEvent<EmployeeAttachment> event) {
    fileImageField.setValueSource(
            new ContainerValueSource<>(employeeAttachmentDc, "fileImage")
    );

}

Hi,

Did you have a chance to look at the guide on working with images in CUBA?

I’m not sure if CUBA can render PDFs properly in the image component, you might try to render a PNG/JPG/BMP preview using external libraries like PDFBox and show it in the screen. And also you can add a link to download the PDF if needed.

Hi @belyaev
Thanks for your response. Yes I looked at that link before but that is mostly on FileDescriptor mechanism.
I can preview pdf within CUBA when it is from FileDescriptor, not from ByteArray. Will this make a difference?

I need to check component internals, but I don’t think there is a simple way to preview the PDF when it is stored as a byte array in the database. I guess it is required to reimplement the component’s API, but we need to check. I believe that it is much simple to generate previews as FileDescriptor for your PDFs

May I ask you why did you decide to store docs in the database directly? Usually it is considered as a bad practice unless you have some special reasons to store files in the DB.

Hi Andrey
Thanks for asking that question. The reason I thought would be a good idea to store directly to the database is when I use the file descriptor mechanism, database backup doesn’t contain those uploaded files as a result if I need to restore to some other system (to a new server) then I will not have any such files but get error message. Am I clear?

Yes, it makes perfect sense. Do you use a standard file storage for your files or something a bit more advanced like a cloud storage?

The price for storing files in the DB is a significant DB size increase, and reading a big amount of data might affect DB performance. Hope it is not your case.

As for the Image component loaded from byte[]- you can try the solution described in this topic, hope this will help.

Hi
I am not using any file storage solutions, just standard database table. That’s a good point in terms of DB size but my application will only store limited volume of files, therefore, it should be still ok to save it to the same database.

Thanks for the link, but not sure how that piece of code can be embedded into my CUBA application, any further help would be appreciated (I’m not a coding expert!).

I used the following code to download the saved file from database in one of my legacy application (java desktop):

public void getPDFData(String fileGroup) {
    String pathName = "";
    try {
        File path = new File(".");
        pathName = path.getCanonicalPath();
        //fileName = pathName + "/docs/ProjDocument.pdf";
        fileName = pathName + "/docs/" + fileGroup + ".pdf";
    } catch (Exception e) {
        e.printStackTrace();
    }

    byte[] fileBytes;
    String query;
    try {
        query = "select FileImage from PdsPropProjectDocuments where PProjId=" + mPProjId.getText() + " AND FileGroup='" + fileGroup + "' AND FileName like '%.pdf%'";
        Statement state = dbConnection.createStatement();
        ResultSet rs = state.executeQuery(query);
        if (rs.next()) {
            fileBytes = rs.getBytes(1);
            //OutputStream targetFile = new FileOutputStream("C:\\inteacc\\docs\\newtest.pdf");
            OutputStream targetFile = new FileOutputStream(fileName);
            targetFile.write(fileBytes);
            targetFile.close();

            //show the file
            try {
                Runtime.getRuntime().exec("rundll32 url.dll, FileProtocolHandler " + fileName);
            } catch (Exception e) {
                JOptionPane.showMessageDialog(this, "Error in showing the file");
                e.printStackTrace();
            }
        }
        rs.close();
        state.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Any suggestions how this can be done in CUBA platform?

Could you explain what exactly do you want to implement in CUBA? File download to a client’s workstation or save it to some other location on the server?

Hi, It will be file download in the client side

Then you can create a service that can get bytes from the database and then you can invoke this service from a screen.

You can get a byte array in the service either by loading the EmployeeAttachment entity with DataManager or EntityManager and getting data from its property EmployeeAttachment.getFileImage() or by using QueryRunner like you did you your desktop code. The QueryRunner gives you access to the ResultSet class.

In the screen controller, you can use ExportDisplay class to initiate downloading.

Thanks. How can i know in this case whether the file was a pdf or jpeg before using export display? If you can share some code example that will be great!

Just store full file name or just extension in you EmployeeAttachement entity and use it when you downloading the file.

Hi @belyaev
Further to our above exchange, fyi, the fdf file is downloading (implemented in my project), how can we display in browserFrame as it works with FileDescriptor? I want to display the pdf file generated from the database download. Did you try BrowserFrame or PdfViewer to view it in this particular case?

No, I haven’t tried it to display PDF’s. Please note that there is no standard for PDF display in a browser frame (in contrast to HTML), so your solution may not work on every browser on every OS.

Hi Andrey
Then may you please share what is the roadmap in CUBA to address this need? I have a project in CUBA PLATFORM that needs this functionality. I know you suggested on 3rd part js library but i would prefer something within CUBA to keep things robust.

Hi,

We do not have any plans on implementing PDF viewer as a platform component. If I were you, I’d start by trying CUBA’s Generic JavaScript Component and implement PDF viewer using pdf.js library.

I’m not sure if this will work, but it looks like a pretty promising combination to me. Hope this will help.

Hey guys,

I’ve fiddled around with PDF Box and came to a working draft, for showing PDF Pages in CUBA using the Image component and a PDF that is stored in the DB. In case someone else wants to do the same, there you go:

I’ve added two Buttons to page up and down and a Label to show current page, if you create image components programmatically, you can probably load all pages in a container and scroll through them:


    @Inject
    private Image PDFpage;
    @Inject
    private Datasource<FileDescriptor> auDokDateiDs;
    @Inject
    private FileStorageService fileStorageService;
    private int showPage = 0;
    @Inject
    private Label labelSeite;

    @Override
    protected void postInit() {
        super.postInit();
        viewImage();
        showPageNumber();
    }

    private void showPageNumber() {
        labelSeite.setValue("Page "+(showPage+1));
    }

    private void viewImage () {
        if (auDokDateiDs.getItem() != null) {
            PDFpage.setSource(StreamResource.class)
                    .setStreamSupplier(() -> {
                        try {
                            return convertPDFPageToImage(showPage);
                        } catch (IOException | FileStorageException e) {
                            e.printStackTrace();
                            return null;
                        }
                    });
        }
    }

    private InputStream convertPDFPageToImage( int page ) throws IOException, FileStorageException {
        byte[] dokumentBytes = new byte[0];
        dokumentBytes = fileStorageService.loadFile(auDokDateiDs.getItem());

        PDDocument doc = null;
        doc = PDDocument.load(dokumentBytes);
        PDFRenderer renderer = new PDFRenderer(doc);

        BufferedImage image = null;
        for (int i = 0; i < doc.getNumberOfPages(); i++) {
            if (i == showPage) {
                image = renderer.renderImageWithDPI(i, 200);
            }
        }
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        ImageIO.write(image, "jpeg", baos);
        byte[] imageBytes = baos.toByteArray();

        ByteArrayInputStream bis = new ByteArrayInputStream(imageBytes);
        return bis;
    }


    public void prevPage() {
        showPage = showPage-1;
        viewImage();
        showPageNumber();
    }

    public void nextPage() {
        showPage = showPage+1;
        viewImage();
        showPageNumber();
    }

Looks good, do you have any plan to test it in Jmix FLOW UI?

Hi,

I am not planning on implementing this in JMIX, it should be easily doable tho. I could setup a demo project with JMIX and provide the solution here, if this would help you guys.

Daniel