Download file from Mysql database

[puk284]

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.

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);
			}
			}
	}
}

[Eranga]

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.

[Eranga]

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

[Eranga]

About your SQL statement,

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?

[puk284]

The id is an int in the save_file table

[Tolls]

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?

[puk284]

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

[Eranga]

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

[Eranga]

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 ;)

[Eranga]

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?

[Tolls]

OK:

				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??

[puk284]

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

[Tolls]

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.).

[puk284]

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

[Tolls]

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

Anyway, why not simply:

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.

[puk284]

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

[puk284]

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