1

I've a little problem; when I trying to download data (picture,audio...) from Mysql database.I'm getting damaged file so the file size is 1kb.I've changed file extension just in case but always got same results.

Please can anyone help me?

I've tried with this code :

    import java.io.File;
    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 DownloadFileFromDb {

        public static void main(String[] args) {

        final String host = "jdbc:mysql://localhost/onurDB";
        final String user = "onur";
        final String pass = "onurdb958";
        String SQL = "SELECT FILENAME FROM `PIC_STORE` WHERE `ID`=?";

        Connection conn = null;
        java.sql.PreparedStatement smt = null;
        InputStream input = null;
        FileOutputStream output = null;
        ResultSet rs = null;

        try {

            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Connecting...");

            conn = DriverManager.getConnection(host, user, pass);
            System.out.println("Connection successful..\nNow creating query...");

            smt = conn.prepareStatement(SQL);
            smt.setString(1, "4");  //in this row we have a png picture
            rs = smt.executeQuery();

            output = new FileOutputStream(new File("/Users/MacbookPro/Downloads/pic.png"));
            System.out.println("Getting file please be patient..");

            while (rs.next()) {

                input = rs.getBinaryStream("FILENAME"); //get it from col name
                int r = 0;

    /*
    *there I've tried with array but nothing changed..Like this :
    * byte[] buffer = new byte[2048];
    * int r = 0;
    * while((r = input.read(buffer)) != -1){
    *        out.write(buffer,0,r);}
    */

                while ((r = input.read()) != -1) {
                    output.write(r);

                }
            }
            System.out.println("File writing complete !");

        } catch (ClassNotFoundException e) {
            System.err.println("Class not found!");
            e.printStackTrace();
        } catch (SQLException e) {
            System.err.println("Connection failed!");   
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            System.err.println("File not found!");
            e.printStackTrace();
        } catch (IOException e) {
            System.err.println("File writing error..!");
            e.printStackTrace();
        }finally {
            if(rs != null){
                try {
                    input.close();
                    output.flush();
                    output.close();
                    smt.close();
                    conn.close();
                } catch (SQLException e) {
                    System.err.println("Connot close connecton!");
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }

            }
        }

    }

}

there is downloaded file : pic.png file size is 12 byte.

THANKS FOR ALL.

7
  • FILENAME is the content of your File? Commented Aug 30, 2016 at 15:12
  • @NicolasFilotto yes My Table columns are ID,FILENAME,PICTURE,FILETYPE. Commented Aug 30, 2016 at 15:16
  • show your table description Commented Aug 30, 2016 at 15:17
  • 1
    the content of your image is not PICTURE ? It would be more obvious than FILENAME Commented Aug 30, 2016 at 15:18
  • @NicolasFilotto tahnks for replay , now i try to correct it Commented Aug 30, 2016 at 15:23

1 Answer 1

3

Solved :

import java.io.File;
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 DownloadFileFromDb {

    public static void main(String[] args) {

    final String host = "jdbc:mysql://localhost/onurDB";
    final String user = "onur";
    final String pass = "onurdb958";
    String SQL = "SELECT PICTURE FROM `PIC_STORE` WHERE `ID`=?"; //Here I mistakenly wrote 'FILANAME' here instead of a 'PICTURE'

    Connection conn = null;
    java.sql.PreparedStatement smt = null;
    InputStream input = null;
    FileOutputStream output = null;
    ResultSet rs = null;

    try {

        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("Connecting...");

        conn = DriverManager.getConnection(host, user, pass);
        System.out.println("Connection successful..\nNow creating query...");

        smt = conn.prepareStatement(SQL);
        smt.setString(1, "4");  //in this row we have a png picture
        rs = smt.executeQuery();

        output = new FileOutputStream(new File("/Users/MacbookPro/Downloads/pic.png"));
        System.out.println("Getting file please be patient..");

        while (rs.next()) {

            input = rs.getBinaryStream("FILENAME"); //get it from col name
            int r = 0;
            while ((r = input.read()) != -1) {
                output.write(r);

            }
        }
        System.out.println("File writing complete !");

    } catch (ClassNotFoundException e) {
        System.err.println("Class not found!");
        e.printStackTrace();
    } catch (SQLException e) {
        System.err.println("Connection failed!");   
        e.printStackTrace();
    } catch (FileNotFoundException e) {
        System.err.println("File not found!");
        e.printStackTrace();
    } catch (IOException e) {
        System.err.println("File writing error..!");
        e.printStackTrace();
    }finally {
        if(rs != null){
            try {
                input.close();
                output.flush();
                output.close();
                smt.close();
                conn.close();
            } catch (SQLException e) {
                System.err.println("Connot close connecton!");
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        }
    }

}

}
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.