Not able to read xlsx file saved in postgres database (as bytea) using apache POI

I am using apache POI to read a xlsx file and upload the data in the database. I have to do this work in a scheduler(at scheduled time) running on Jboss. As scheduler is running on different server from the one file was uploaded, I am using postgres bytea datatype to save the file in database using below code PreparedStatement ps = con.prepareStatement( "UPDATE tk_tablename SET tk_filecolumnname = ? WHERE primarykey = '" + fileAttachment.getPrimaryKey() + "';" );

FilePathAssociation filePathAssociation = fileAttachment.getFilePathAssociation();

if ( filePathAssociation != null )

{

File blobFile = new File( filePathAssociation.getPhysicalFilePath() );
   FileInputStream fis = new FileInputStream( blobFile );
   ps.setBinaryStream( 1, fis, (int)blobFile.length() );
   ps.executeUpdate();
   ps.close();
   fis.close();
}

This is working fine and the file is saved in database.

But while reading the file on server using below code

ResultSet rs =
                        s开发者_高级运维tmt.executeQuery( "SELECT tk_filecolumnname FROM tk_tablename WHERE primarykey = '"
                            + fileAttachment.getPrimaryKey() + "';" );
                    if ( rs != null && rs.next() )
                    {
                        InputStream fileInputStream = rs.getBinaryStream( 1 );
                        Workbook workbook = WorkbookFactory.create( fileInputStream ); // apache POI code to read a xlsx file.
                        rs.close();
                        return file;
                    }

It is giving below error,

java.lang.IllegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream

I know to read a xlsx file, POI needs a stream backed by OOXML. But why

ResultSet.getBinaryStream()

method is not returning the same input stream that was saved while creating file.

Please help or share your knowledge.

thanks, Amit.

Do the following:

1) Assert your code is reading workbook correctly from the file (just open the FileInputStream).

2) Once your code is working with files, and not the stream from database, it may be connected with the way the library is processing the stream. You can try saving whole stream into temporary file, what gives you additionally the advantage, you can faster release db connection.

1.you might be using postgres 9.0. in postgres 9.0, the default for bytea is hex. simple setByte in a prepared statement wont work. A workaround is to edit postgresql.conf

bytea_output = 'escape'

upgrading your jdbc driver might also fix this but haven't tried.

2.try using oid. this is for storing large files to your database. you can find more information here. http://jdbc.postgresql.org/documentation/80/binary-data.html

Java API can be found http://jdbc.postgresql.org/documentation/publicapi/org/postgresql/largeobject/LargeObjectManager.html

I had the same issue (the suggested changes on PostgreSQL's config level or picking a new driver did not work) and now just solved it by wrapping the input stream that is read from the PostgreSQL DB into a buffered input stream by using

import org.apache.commons.io.IOUtils;

IOUtils.toBufferedInputStream(file.getBinaryStream()));

Hope this helps!

Regards,

Niko Wittenbeck

Make sure your classpath is not filtered by Maven. This post solved the same issue : https://stackoverflow.com/a/13969688/704246