Upload PDF file to mysql BLOB by using java.sql.Pr

Question

I tried to uplad a pdf file using java.sql.PreparedStatement to mysql Blob field using the following code.

        File inFile = new File("Path+BLOCK.pdf");
        byte[] b = new byte[(int)inFile.length()];

        PreparedStatement psmnt = (PreparedStatement) 
        con.prepareStatement("INSERT  INTO 
                        2012DOC (SRNO,DOCUMENT) 
                       VALUES  (?,?)"
                      );   //con is java.sql.Connection object
        psmnt.setString(1, "1200021");
        psmnt.setBytes(2, b);
        psmnt.executeUpdate();

This code executes without error and database shows blob content, but when I try to retrieve the file using the below code it gives a corrupt file which doesn't open.

ResultSet rs=con.Execute("SELECT DOCUMENT FROM 2012DOC");
rs.next();
response.setContentType("application/pdf");
response.setHeader("Content-Disposition", "attachment; filename=kjsahkjd.pdf");

java.sql.Blob blob = rs.getBlob("DOCUMENT");
ServletOutputStream servletOutputStream = response.getOutputStream();
InputStream in = blob.getBinaryStream();
int length = (int) blob.length();
int bufferSize = 1024;
byte[] buffer = new byte[bufferSize];
while ((length = in.read(buffer)) != -1) {

servletOutputStream.write(buffer, 0, length);
}
in.close();
servletOutputStream.flush();
servletOutputStream.close();

It outputs the file with same size as the original,but the file doesn't open. The pdf reader is fired but cannot open the file and gives an error 'the file was either damaged or not supported file type'

Answer 1

Ahhh...After a little debugging I found the code that uploads is troublesome, and finally got the right way to do it.

Here is what I did...I'm posting it so that others with same problem can solve it

After Converting the java.io.File to java.io.FileInputStream

FileInputStream io = new FileInputStream(inFile);

Set the BLOB field using psmnt.setBinaryStream()

psmnt.setBinaryStream(3,  (InputStream)io,(int)inFile.length());

Answer 2

remove " java.sql.Blob blob = rs.getBlob("DOCUMENT"); "

and don't initialize length i.e instead of

int length = (int) blob.length();

just write

int length;

then it downloads file successfully .. enjoy :)