Results 1 to 17 of 17
  1. #1
    puk284 is offline Member
    Join Date
    Apr 2009
    Posts
    16
    Rep Power
    0

    Default Download file from Mysql database

    Hi,

    I have written the following code to download a file from a MySql database using a Servlet.The problem i have is that i havnt done it right. Can someone please point me in the right direction. Any help will be greatly appreciated. Thank you for your help.

    Java Code:
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.io.PrintWriter;
    import java.sql.Blob;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.Statement;
    
    import javax.servlet.ServletException;
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    /**
     * Servlet implementation class datadownload
     */
    public class datadownload extends HttpServlet {
    	private static final long serialVersionUID = 1L;
           
        /**
         * @see HttpServlet#HttpServlet()
         */
        public datadownload() {
            super();
            // TODO Auto-generated constructor stub
        }
    
    	/**
    	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
    	 */
    	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    		// TODO Auto-generated method stub
    		
    	}
    
    	/**
    	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
    	 */
    	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    		// TODO Auto-generated method stub
    		response.setContentType("text/html");
    		PrintWriter out = response.getWriter();
    		String connectionURL = "jdbc:mysql://localhost/filestore";
    		Connection con = null;
    		Statement stmt= null;
    		ResultSet res = null;
    		response.setContentType("application/octet-stream");
    		
    		try{
    			Class.forName("com.mysql.jdbc.Driver");
    			con = DriverManager.getConnection(connectionURL, "root", "");
    			stmt=con.createStatement();
    			 res =stmt.executeQuery("select * from save_file where id='15'");
    			if (res .next()) {
    				Blob File=res .getBlob("file");
    				InputStream x=File.getBinaryStream();
    				int size=x.available();
    				ServletOutputStream Fileout = response.getOutputStream();
    				FileInputStream f = new FileInputStream("");
    				int nextByte = f.read();
    
    					while ( nextByte != -1 ) {
    						out.write(nextByte);
    						nextByte = f.read();
    					}
    					out.flush();
    			}
    		}
    				
    
    			
    		
    			catch(Exception e){
    			System.out.println("Exception :"+e);
    			}
    			finally{
    			try{
    			stmt.close();
    			con.close();
    			}
    			catch(Exception e){
    			System.out.println(e);
    			}
    			}
    	}
    }
    Last edited by Eranga; 04-24-2010 at 02:15 PM. Reason: code tags added

  2. #2
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    20

    Default

    First things is that please use code tags when you are posting a code segment here in the forum. Unformated codes are really hard to read. If you don't know how to do that, check on my forum signature. You can find a link to relevant page.

  3. #3
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    20

    Default

    Regarding your question, what happen when you run the code? Any error or unexpected things happen?

  4. #4
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    20

    Default

    About your SQL statement,

    Java Code:
    res =stmt.executeQuery("select * from save_file where id='15'");
    Is that id is an int or a char type in your database table save_file?

  5. #5
    puk284 is offline Member
    Join Date
    Apr 2009
    Posts
    16
    Rep Power
    0

    Default

    The id is an int in the save_file table

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    Then the single quotes are unecessary.
    However, the important question was Eranga's previous one...what exactly do you mean by "I haven't done it right"?
    What exactly is the problem?

  7. #7
    puk284 is offline Member
    Join Date
    Apr 2009
    Posts
    16
    Rep Power
    0

    Default

    The problem is that nothing happens. Im using eclipse and there is a yellow line below the fileout and size variables. The thing that should happen is that i should get a dialogue box asking me if i should open or download the file

  8. #8
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    20

    Default

    Did you have data in the table you are using to retrieve? Means that did you debug the code to test results?

  9. #9
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    20

    Default

    Quote Originally Posted by Tolls View Post
    However, the important question was Eranga's previous one...what exactly do you mean by "I haven't done it right"?
    What exactly is the problem?
    Yeah, that's what I mentioned in post#3 ;)

  10. #10
    Eranga's Avatar
    Eranga is offline Moderator
    Join Date
    Jul 2007
    Location
    Colombo, Sri Lanka
    Posts
    11,372
    Blog Entries
    1
    Rep Power
    20

    Default

    Quote Originally Posted by puk284 View Post
    The problem is that nothing happens. Im using eclipse and there is a yellow line below the fileout and size variables. The thing that should happen is that i should get a dialogue box asking me if i should open or download the file
    Did you correct what Tolls says in his previous post, on your code?

  11. #11
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    OK:
    Java Code:
    				Blob File=res .getBlob("file");
    				InputStream x=File.getBinaryStream();
    				int size=x.available();
    				ServletOutputStream Fileout = response.getOutputStream();
    				FileInputStream f = new FileInputStream("");
    				int nextByte = f.read();
    
    					while ( nextByte != -1 ) {
    						out.write(nextByte);
    						nextByte = f.read();
    					}
    					out.flush();
    You're getting the data from the db as a Blob (and variables start with a lower case...File is also the name of a class and therefore very confusing). You get an input stream from this Blob. You get the output stream for the response. So far, so good.

    And then you create a new FileInputStream, aimed at a file called " ", and try and read from that. Um? Surely you want to be reading from the Blob stream??

  12. #12
    puk284 is offline Member
    Join Date
    Apr 2009
    Posts
    16
    Rep Power
    0

    Default

    How can i read from the blob stream? That is the part where i am getting it wrong I think.

  13. #13
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    Um, you're already reading from a stream...well, you would be if that file stream was pointng at anything. And you already get the input stream ("x")...and the size.

    I would suggest reading in clumps (ie declare a byte[] of some size...I tend to use 8k for some reason as a start point, or possibly even simply doing one of "size", but that depends how big the image actually is.).

  14. #14
    puk284 is offline Member
    Join Date
    Apr 2009
    Posts
    16
    Rep Power
    0

    Default

    Well this is the code that i have so far im trying to combine the two so that when a user clicks a down load link they will get a dialogue box that asks if they want to open or save the file

    [code = java ]

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
    response.setContentType("text/html");
    PrintWriter out = response.getWriter();

    String connectionURL = "jdbc:mysql://localhost/filestore";
    Connection con = null;
    Statement stmt= null;
    ResultSet res = null;
    response.setContentType("application/octet-stream");

    try{
    Class.forName("com.mysql.jdbc.Driver");
    con = DriverManager.getConnection(connectionURL, "root", "");
    stmt=con.createStatement();
    res =stmt.executeQuery("select * from save_file where id=1");
    if (res.next()) {
    Blob file=res.getBlob("file");
    InputStream x=file.getBinaryStream();
    int size=x.available();
    byte b[]= new byte[size];
    x.read(b);
    }
    }catch(Exception e){
    System.out.println("Exception :"+e);
    }
    finally{
    try{
    stmt.close();
    con.close();
    }
    catch(Exception e){
    System.out.println(e);
    }
    }
    }


    private void downloadFile(String fileName, HttpServletRequest request, HttpServletResponse response) throws IOException {

    // Set the content type to the byte-stream type (since we don't know it)
    // see http://en.wikipedia.org/wiki/MIME_type for meaning of MIME type
    response.setContentType("application/octet-stream");

    // Set header: allows us to specify the download filename to the browser
    // see http://en.wikipedia.org/wiki/List_of_HTTP_headers for header types
    response.setHeader("Content-Disposition", "attachment; filename=" + new File(fileName).getName());

    ServletOutputStream out = response.getOutputStream();

    // Now copy requested file to the response output stream
    // prefix filename with location of our files
    FileInputStream f = new FileInputStream(fileName);

    int nextByte = f.read();
    // note: there is a faster way to do this using alternative read() methods

    while ( nextByte != -1 ) {
    out.write(nextByte);
    nextByte = f.read();
    }
    out.flush();

    }
    }

    [/code]

    I want to get the data of the blob first and then pass it to the servlet

  15. #15
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    Your code tags didn't work.
    And you haven't closed your result set.
    Nitpicking over...:)

    Anyway, why not simply:
    Java Code:
    InputStream x=file.getBinaryStream();
    byte[] bytes = new byte[1024]; // for sake of argument.
    
    while (read bytes into byte[]) {
    output those bytes to the response.
    }
    close your input stream.
    close your other resources (conn, st, rs) in the finally block.
    ETA: I just don't understand where you get this whole file stream idea from.

  16. #16
    puk284 is offline Member
    Join Date
    Apr 2009
    Posts
    16
    Rep Power
    0

    Default

    Could you please show me an example using code of what you mean. thanks

  17. #17
    puk284 is offline Member
    Join Date
    Apr 2009
    Posts
    16
    Rep Power
    0

    Default

    I mean i konw how to close the connections the part i would like an example of is the while loop and how to output the bytes to the response. Thank you

Similar Threads

  1. mysql download
    By hannes in forum JDBC
    Replies: 2
    Last Post: 04-18-2010, 11:19 AM
  2. Replies: 0
    Last Post: 03-05-2010, 04:21 AM
  3. Replies: 2
    Last Post: 02-11-2009, 04:43 AM
  4. MySQL Database and Java
    By shaggymac in forum Advanced Java
    Replies: 1
    Last Post: 05-01-2008, 09:01 PM
  5. connecting to mysql database
    By javagal in forum NetBeans
    Replies: 2
    Last Post: 08-04-2007, 12:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •