Passing file content InputStreams to JDBC

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Passing file content InputStreams to JDBC

Tristan Austin
While all of my WebDAV file content is stored and sourced from structured data in a relational database, in order for this to work with the macOS Finder I need to capture and store the content of all of these numerous hidden files as well. As these are created, I’m storing them in a dedicated table in the database as a BLOB.

I seem to have this working now, but only after completely reading the input stream into a separate buffer and then sending that buffer to the database as a new ByteArrayInputStream  If I just pass the InputStream to the JDBC query via PreparedStatement setBinaryStream(X, inputStream  it seems to hang. Perhaps waiting for the stream to be closed.

This seems very inefficient since it means iterating over the data twice (once by me, once by the JDBC driver). I’m sure there must be something I can do to address this.

Here’s my method (which I realise could itself be more efficient):

    /*
     * (non-Javadoc)
     *
     * @see io.milton.resource.ReplaceableResource#replaceContent(java.io.InputStream, java.lang.Long)
     */
    @Override
    public void replaceContent(InputStream inputStream, Long length) throws BadRequestException, ConflictException, NotAuthorizedException {
        SystemDefinitionStagingDBServiceImpl dbService = this.getStagingDBService();
        String path = this.getPath();
        try {
            try {
                // Prepare the buffers
                byte[] resultBuff = new byte[0];
                byte[] buff = new byte[1024];
                int numberOfBytesRead = -1;

                // Don't assume the length is present or accurate (learned from experience)
                while ((numberOfBytesRead = inputStream.read(buff, 0, buff.length)) > -1) {
                    byte[] tempBuffer = new byte[resultBuff.length + numberOfBytesRead];
                    System.arraycopy(resultBuff, 0, tempBuffer, 0, resultBuff.length);
                    System.arraycopy(buff, 0, tempBuffer, resultBuff.length, numberOfBytesRead);
                    resultBuff = tempBuffer;
                }

                //Wrap the read data in a new ByteArrayInputStream and send that to the database
                ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(resultBuff);
                dbService.setFileContent(path, byteArrayInputStream);
            } finally {
                // Read in the content of the file
                inputStream.close();
            }
        } catch (Exception exception) {
            throw new BadRequestException("Unable to delete hidden file from repository", exception);
        }
    }



Tristan

_______________________________________________
Milton-users mailing list
[hidden email]
http://lists.justthe.net/mailman/listinfo/milton-users
Reply | Threaded
Open this post in threaded view
|

Passing file content InputStreams to JDBC

Tristan Austin
While all of my WebDAV file content is stored and sourced from structured data in a relational database, in order for this to work with the macOS Finder I need to capture and store the content of all of these numerous hidden files as well. As these are created, I’m storing them in a dedicated table in the database as a BLOB.

I seem to have this working now, but only after completely reading the input stream into a separate buffer and then sending that buffer to the database as a new ByteArrayInputStream  If I just pass the InputStream to the JDBC query via PreparedStatement setBinaryStream(X, inputStream  it seems to hang. Perhaps waiting for the stream to be closed.

This seems very inefficient since it means iterating over the data twice (once by me, once by the JDBC driver). I’m sure there must be something I can do to address this.

Here’s my method (which I realise could itself be more efficient):

    /*
     * (non-Javadoc)
     *
     * @see io.milton.resource.ReplaceableResource#replaceContent(java.io.InputStream, java.lang.Long)
     */
    @Override
    public void replaceContent(InputStream inputStream, Long lengththrows BadRequestException, ConflictException, NotAuthorizedException {
        SystemDefinitionStagingDBServiceImpl dbService = this.getStagingDBService();
        String path = this.getPath();
        try {
            try {
                // Prepare the buffers
                byte[] resultBuff = new byte[0];
                byte[] buff = new byte[1024];
                int numberOfBytesRead = -1;

                // Don't assume the length is present or accurate (learned from experience)
                while ((numberOfBytesRead = inputStream.read(buff, 0, buff.length)) > -1) {
                    byte[] tempBuffer = new byte[resultBuff.length + numberOfBytesRead];
                    System.arraycopy(resultBuff, 0, tempBuffer, 0, resultBuff.length);
                    System.arraycopy(buff, 0, tempBufferresultBuff.lengthnumberOfBytesRead);
                    resultBuff = tempBuffer;
                }

                //Wrap the read data in a new ByteArrayInputStream and send that to the database
                ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(resultBuff);
                dbService.setFileContent(pathbyteArrayInputStream);
            } finally {
                // Read in the content of the file
                inputStream.close();
            }
        } catch (Exception exception) {
            throw new BadRequestException("Unable to delete hidden file from repository"exception);
        }
    }



Tristan

_______________________________________________
Milton-users mailing list
[hidden email]
http://lists.justthe.net/mailman/listinfo/milton-users
Reply | Threaded
Open this post in threaded view
|

Re: Passing file content InputStreams to JDBC

bradmacnz

And making things worse the inputstream you're given has most likely to have been buffered to disk by the web container. And the jdbc driver will probably buffer the inputstream to disk :)

If this code could be used to hold actual file content, then the amount of memory consumed would be at least equal to the file size, which could be a problem if your users will upload large files, eg 500Mb. But if its only for small placeholder files then i would say dont sweat it.

Another consideration is on a GET, ie a download, if you allow large files and clients may have slow connections then you could end up with requests which hold a database connection for several minutes while the blob downloads. This can be a real issue for scaleability and a vector for a denial of service attack.

The approach I've settled on is to always store file content in chunks which can be buffered in memory comfortably, and which can be efficiently retrieved from the db in a connection which is then released. Around 100k seems about right.



On 22/03/17 18:39, Tristan Austin wrote:
While all of my WebDAV file content is stored and sourced from structured data in a relational database, in order for this to work with the macOS Finder I need to capture and store the content of all of these numerous hidden files as well. As these are created, I’m storing them in a dedicated table in the database as a BLOB.

I seem to have this working now, but only after completely reading the input stream into a separate buffer and then sending that buffer to the database as a new ByteArrayInputStream  If I just pass the InputStream to the JDBC query via PreparedStatement setBinaryStream(X, inputStream  it seems to hang. Perhaps waiting for the stream to be closed.

This seems very inefficient since it means iterating over the data twice (once by me, once by the JDBC driver). I’m sure there must be something I can do to address this.

Here’s my method (which I realise could itself be more efficient):

    /*
     * (non-Javadoc)
     *
     * @see io.milton.resource.ReplaceableResource#replaceContent(java.io.InputStream, java.lang.Long)
     */
    @Override
    public void replaceContent(InputStream inputStream, Long lengththrows BadRequestException, ConflictException, NotAuthorizedException {
        SystemDefinitionStagingDBServiceImpl dbService = this.getStagingDBService();
        String path = this.getPath();
        try {
            try {
                // Prepare the buffers
                byte[] resultBuff = new byte[0];
                byte[] buff = new byte[1024];
                int numberOfBytesRead = -1;

                // Don't assume the length is present or accurate (learned from experience)
                while ((numberOfBytesRead = inputStream.read(buff, 0, buff.length)) > -1) {
                    byte[] tempBuffer = new byte[resultBuff.length + numberOfBytesRead];
                    System.arraycopy(resultBuff, 0, tempBuffer, 0, resultBuff.length);
                    System.arraycopy(buff, 0, tempBufferresultBuff.lengthnumberOfBytesRead);
                    resultBuff = tempBuffer;
                }

                //Wrap the read data in a new ByteArrayInputStream and send that to the database
                ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(resultBuff);
                dbService.setFileContent(pathbyteArrayInputStream);
            } finally {
                // Read in the content of the file
                inputStream.close();
            }
        } catch (Exception exception) {
            throw new BadRequestException("Unable to delete hidden file from repository"exception);
        }
    }



Tristan


_______________________________________________
Milton-users mailing list
[hidden email]
http://lists.justthe.net/mailman/listinfo/milton-users


_______________________________________________
Milton-users mailing list
[hidden email]
http://lists.justthe.net/mailman/listinfo/milton-users
Reply | Threaded
Open this post in threaded view
|

Re: Passing file content InputStreams to JDBC

Tristan Austin
In reply to this post by Tristan Austin
I thought I should provide an update on this.

The original problem I was having was based on my initial exploratory implementation of the Milton WebDAV library. I have since completely rewritten this and the specific problem described below is no longer present. I am able to take the InputStream provided via the ReplaceableResource.replaceContent() method and I’m now able to pass that all the way through to a PreparedStatement.setBinaryStream() call. Given that the codebase is completely different, I’m not entirely sure what the fix was, just that the problem is no longer present.

As an added bonus, I am also now dealing with large batch file data that needs to be parsed and written to structured tables within the database and I’m able to do this in a single pass over the stream and all of its contents. I’m not seeing any performance issues with this any more.

Tristan

On 22 Mar 2017, at 4:39 pm, Tristan Austin <[hidden email]> wrote:

While all of my WebDAV file content is stored and sourced from structured data in a relational database, in order for this to work with the macOS Finder I need to capture and store the content of all of these numerous hidden files as well. As these are created, I’m storing them in a dedicated table in the database as a BLOB.

I seem to have this working now, but only after completely reading the input stream into a separate buffer and then sending that buffer to the database as a new ByteArrayInputStream  If I just pass the InputStream to the JDBC query via PreparedStatement setBinaryStream(X, inputStream  it seems to hang. Perhaps waiting for the stream to be closed.

This seems very inefficient since it means iterating over the data twice (once by me, once by the JDBC driver). I’m sure there must be something I can do to address this.

Here’s my method (which I realise could itself be more efficient):

    /*
     * (non-Javadoc)
     *
     * @see io.milton.resource.ReplaceableResource#replaceContent(java.io.InputStream, java.lang.Long)
     */
    @Override
    public void replaceContent(InputStream inputStream, Long lengththrows BadRequestException, ConflictException, NotAuthorizedException {
        SystemDefinitionStagingDBServiceImpl dbService = this.getStagingDBService();
        String path = this.getPath();
        try {
            try {
                // Prepare the buffers
                byte[] resultBuff = new byte[0];
                byte[] buff = new byte[1024];
                int numberOfBytesRead = -1;

                // Don't assume the length is present or accurate (learned from experience)
                while ((numberOfBytesRead = inputStream.read(buff, 0, buff.length)) > -1) {
                    byte[] tempBuffer = new byte[resultBuff.length + numberOfBytesRead];
                    System.arraycopy(resultBuff, 0, tempBuffer, 0, resultBuff.length);
                    System.arraycopy(buff, 0, tempBufferresultBuff.lengthnumberOfBytesRead);
                    resultBuff = tempBuffer;
                }

                //Wrap the read data in a new ByteArrayInputStream and send that to the database
                ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(resultBuff);
                dbService.setFileContent(pathbyteArrayInputStream);
            } finally {
                // Read in the content of the file
                inputStream.close();
            }
        } catch (Exception exception) {
            throw new BadRequestException("Unable to delete hidden file from repository"exception);
        }
    }



Tristan


_______________________________________________
Milton-users mailing list
[hidden email]
http://lists.justthe.net/mailman/listinfo/milton-users
Reply | Threaded
Open this post in threaded view
|

Re: Passing file content InputStreams to JDBC

bradmacnz

Thats great, thanks for sharing.


On 18/06/17 16:22, Tristan Austin wrote:
I thought I should provide an update on this.

The original problem I was having was based on my initial exploratory implementation of the Milton WebDAV library. I have since completely rewritten this and the specific problem described below is no longer present. I am able to take the InputStream provided via the ReplaceableResource.replaceContent() method and I’m now able to pass that all the way through to a PreparedStatement.setBinaryStream() call. Given that the codebase is completely different, I’m not entirely sure what the fix was, just that the problem is no longer present.

As an added bonus, I am also now dealing with large batch file data that needs to be parsed and written to structured tables within the database and I’m able to do this in a single pass over the stream and all of its contents. I’m not seeing any performance issues with this any more.

Tristan

On 22 Mar 2017, at 4:39 pm, Tristan Austin <[hidden email]> wrote:

While all of my WebDAV file content is stored and sourced from structured data in a relational database, in order for this to work with the macOS Finder I need to capture and store the content of all of these numerous hidden files as well. As these are created, I’m storing them in a dedicated table in the database as a BLOB.

I seem to have this working now, but only after completely reading the input stream into a separate buffer and then sending that buffer to the database as a new ByteArrayInputStream  If I just pass the InputStream to the JDBC query via PreparedStatement setBinaryStream(X, inputStream  it seems to hang. Perhaps waiting for the stream to be closed.

This seems very inefficient since it means iterating over the data twice (once by me, once by the JDBC driver). I’m sure there must be something I can do to address this.

Here’s my method (which I realise could itself be more efficient):

    /*
     * (non-Javadoc)
     *
     * @see io.milton.resource.ReplaceableResource#replaceContent(java.io.InputStream, java.lang.Long)
     */
    @Override
    public void replaceContent(InputStream inputStream, Long lengththrows BadRequestException, ConflictException, NotAuthorizedException {
        SystemDefinitionStagingDBServiceImpl dbService = this.getStagingDBService();
        String path = this.getPath();
        try {
            try {
                // Prepare the buffers
                byte[] resultBuff = new byte[0];
                byte[] buff = new byte[1024];
                int numberOfBytesRead = -1;

                // Don't assume the length is present or accurate (learned from experience)
                while ((numberOfBytesRead = inputStream.read(buff, 0, buff.length)) > -1) {
                    byte[] tempBuffer = new byte[resultBuff.length + numberOfBytesRead];
                    System.arraycopy(resultBuff, 0, tempBuffer, 0, resultBuff.length);
                    System.arraycopy(buff, 0, tempBufferresultBuff.lengthnumberOfBytesRead);
                    resultBuff = tempBuffer;
                }

                //Wrap the read data in a new ByteArrayInputStream and send that to the database
                ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(resultBuff);
                dbService.setFileContent(pathbyteArrayInputStream);
            } finally {
                // Read in the content of the file
                inputStream.close();
            }
        } catch (Exception exception) {
            throw new BadRequestException("Unable to delete hidden file from repository"exception);
        }
    }



Tristan



_______________________________________________
Milton-users mailing list
[hidden email]
http://lists.justthe.net/mailman/listinfo/milton-users


_______________________________________________
Milton-users mailing list
[hidden email]
http://lists.justthe.net/mailman/listinfo/milton-users
Reply | Threaded
Open this post in threaded view
|

Release Notes

Tristan Austin
In reply to this post by Tristan Austin
I haven’t been able to find any release notes associated with each version of the milton library.

Is there somewhere that I have missed that details the updates in each version?

Tristan

_______________________________________________
Milton-users mailing list
[hidden email]
http://lists.justthe.net/mailman/listinfo/milton-users