Monday, July 22, 2013

Store a file to a bytea column in postgres and retrieve it from postgres and show

I have explained below an example of how to store a file in bytea column in postgres and how to retrieve it and show.

Lets assume i have a postgres table sample with fields id,File,filename

Upload a file to bytea column in postgres
    sampleEntityTable tbl = new sampleEntityTable ();
    File file = new File(path);
      byte[] b = new byte[(int) file.length()];
            try {
                  FileInputStream fileInputStream = new FileInputStream(file);
                  fileInputStream.read(b);
             } catch (FileNotFoundException e) {
                         System.out.println("File Not Found.");
                         e.printStackTrace();
             }
             catch (IOException e1) {
                      System.out.println("Error Reading The File.");
                       e1.printStackTrace();
             }
           
            tbl.setFile(b);

Download a bytea column from postgres to file

    public void processRequest(HttpServletRequest request, HttpServletResponse response) throws IOException{
     response.setContentType("application/pdf");
     response.setHeader("Content-Disposition","attachment; filename="+fileName);
     sampleEntityTableImpl = (sampleEntityTableImpl) applicationContext.getBean("sampleEntityTableImpl");
     long id=8054L;
     sampleEntityTable tbl = sampleEntityTableImpl.getSampleEntityTable(id);
       byte[] b = new byte[(int) tbl.getFile().length];
             try {
                   FileInputStream fileInputStream = new FileInputStream(file);
                   fileInputStream.read(b);
          }
              } catch (FileNotFoundException e) {
                          System.out.println("File Not Found.");
                          e.printStackTrace();
              }
              catch (IOException e1) {
                       System.out.println("Error Reading The File.");
                        e1.printStackTrace();
              }
             ServletOutputStream output = response.getOutputStream();
              output.write(b);
     }

  Another Method

package org.in.my.data;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBConnection {
    public void connect() {
        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        Connection connection = null;

        try {
            connection = DriverManager.getConnection(
                    "jdbc:postgresql://localhost:5432/testing", "postgres",
                    "postgres");
            
            File file = new File("d:\\samnew.pdf");
            FileInputStream fis = new FileInputStream(file);
            fis.close();
             
            
            InputStream is = null;
            StringBuffer out = null;
            ResultSet rs = connection.createStatement().executeQuery(
                            "SELECT filedata FROM documents where id=1");
            byte[] imgBytes = null;
            if (rs != null) {
                while (rs.next()) {
                    imgBytes = rs.getBytes(1);
                }

                FileOutputStream os = new FileOutputStream("d:\\samoutput.pdf");
                os.write(imgBytes);
                os.flush();
                os.close();
            }

            rs.close();
            connection.close();

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

}

If you have tried the example and it is not working. If the file size is double the original size, Then execute the below command in postgres database and then try again

ALTER DATABASE testing SET bytea_output = 'escape';

No comments: