how to insert and retrieve pdf from blob using Java

Question

I am trying to build some Java code that uses JDBC to:

1) Insert a PDF into a longblob column of MySQL and the filename into a varchar column.

2) Retrieve a PDF using the filename (consider it is the primary key) and show it to the user.

As is clear from above, my table has two columns:

filename  pdf_file 
--------  ---------
stock     stock.pdf 
kids      kid.pdf 

Here is the code that i have written:

private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {                                         
        JFileChooser chooser = new JFileChooser();
        chooser.showOpenDialog(null);
        File f = chooser.getSelectedFile();
        String filename = f.getAbsolutePath();
        path = filename;
         newpath = path.replace('\\', '/');
    }                                        


 private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         

        try{
        File newpdf = new File(newpath);
        FileInputStream fis = new FileInputStream(newpdf);
        ByteArrayOutputStream baos= new ByteArrayOutputStream();
        byte[] buff = new byte[2048000];
        for(int readNum; (readNum=fis.read(buff)) !=-1 ; ){
            baos.write(buff,0,readNum);
        }

        userpdf=baos.toByteArray();


    }
    catch(Exception e){
        JOptionPane.showMessageDialog(null, e);
    }
 PreparedStatement pstmt = null;

        try{
             Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/ppl","root","");
             String proj_name = JOptionPane.showInputDialog("Please enter name of the file");
             /*
             String insert = "INSERT INTO project VALUES ('" + login.admission + "','" + login.yr + "','" + proj_name + "','" + userpdf + "')";

             java.sql.PreparedStatement pst = con.prepareStatement(insert);
             pst.executeUpdate(insert);*/

             String sql = "INSERT INTO project"+"VALUES (?,?,?,?)";


        pstmt = (PreparedStatement) con.prepareStatement(sql);
        pstmt.setString(1, login.admission);
        pstmt.setString(2, login.yr);
        pstmt.setString(3, proj_name);
        pstmt.setBlob(4, userpdf); //This line has an error may be because of userpdf.Plz //suggest

        pstmt.executeUpdate();


        JOptionPane.showMessageDialog(null, "Saved");
    }
    catch(Exception e){
        JOptionPane.showMessageDialog(null, e);
    } 

The problems I am facing are:

1. If I insert a PDF of 175 kb, the MySQL table shows that it's size is 10 or 11 bytes. Why is this happening?
2. When I try to retrieve the PDF I get a message that it is corrupt. (I have not included retrieval code.)

Please explain using the above scenario as I am a newbie in Java. Why is it that my whole pdf is not going into mysql table?

Solution 1

you must declare a private variable of type

Byte[](private byte[] usjerpdf = null;) and change your pstmt.setBlob(4, userpdf) to pstmt.setBytes(4, userpdf) and it will work fine.

Solution 2

// I have an table Multipart which has blob column PDFFile // Below simple code works perfectly fine. I'm passing the list of Documents ID's

    private boolean updateDocuments(List<Integer> list) {
    try {
        Connection con = App.getMySQLDBConnection();
        for (Integer id : list) {
            PreparedStatement stmt = con.prepareStatement("UPDATE Multipart set PDFFile=? WHERE ID=?");
            File file2 = new File(NEW_FILES_DIR + id.toString() + ".pdf");
            FileInputStream fis = new FileInputStream(file2);
            stmt.setBlob(1, fis);
            stmt.setInt(2, id);
            stmt.executeUpdate();
            stmt.close();
        }
        return true;
    } catch (Exception e) {
        System.out.println("Something Went Wrong! Please check stacktrace");
        e.printStackTrace();
    }
    return false;
}

private boolean createBackup(List<Integer> list) {
    System.out.println("Creating a backup of all " + list.size() + " Documents!");
    try {
        Connection con = App.getMySQLDBConnection();
        Statement stmt = con.createStatement();

        for (Integer id : list) {
            ResultSet rs = stmt.executeQuery("SELECT FileName, PDFFile FROM Multipart WHERE ID=" + id);
            while (rs.next()) {
                File file2 = new File(BACKUP_DIR + id + ".pdf");
                FileOutputStream fos = new FileOutputStream(file2);
                fos.write(rs.getBytes("PDFFile"));
                fos.close();
            }
        }
        return true;
    } catch (Exception e) {
        System.out.println("Something Went Wrong! Please check stacktrace");
        e.printStackTrace();
    }
    return false;
}

Solution 3

For inserting the PDF file into the MySQL database the following code seems to work fine for me:

File pdfFile = new File("C:/Users/Gord/Desktop/zzTest.pdf");
byte[] pdfData = new byte[(int) pdfFile.length()];
DataInputStream dis = new DataInputStream(new FileInputStream(pdfFile));
dis.readFully(pdfData);  // read from file into byte[] array
dis.close();

String myConnectionString =
        "jdbc:mysql://localhost:3307/mydb";
dbConnection = DriverManager.getConnection(myConnectionString, "root", "whatever");
PreparedStatement ps = dbConnection.prepareStatement(
        "INSERT INTO project (" +
                "filename, " +
                "pdf_file " +
            ") VALUES (?,?)");
ps.setString(1, "testpdf");
ps.setBytes(2, pdfData);  // byte[] array
ps.executeUpdate();