Store and retrieve word documents with MySQL

Question

Hi Fellas,

I need to store and retrieve MS Word documents into MySQL 5.1 with Servlets. I've the code to upload a file, but I don't know can I feed into the table. I've used BLOB for the field I've to insert .doc files.

Here's my code snippet to upload files:

protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");
    PrintWriter out = response.getWriter();
    try {
        // get access to file that is uploaded from client
        Part p1 = request.getPart("file");
        String type=p1.getContentType();
        String name=p1.getName();
        long size = p1.getSize();
        InputStream is = p1.getInputStream();
        //FileInputStream fis = is.

        // read filename which is sent as a part
        Part p2  = request.getPart("name");
        Scanner s = new Scanner(p2.getInputStream());
        String filename = s.nextLine();    // read filename from stream

        // get filename to use on the server
        String outputfile = this.getServletContext().getRealPath(filename);  // get path on the server
        FileOutputStream os = new FileOutputStream (outputfile);

        // write bytes taken from uploaded file to target file
        int ch = is.read();
        while (ch != -1) {
             os.write(ch);
             ch = is.read();
        }
        os.close();
        out.println("<h3>File : '" + name + "' Type : '" + type + "' "
                + "of Size : " + ((double) size/1024) + "KB uploaded successfully!</h3>");
    }
    catch(Exception ex) {
       out.println("Exception -->" + ex.getMessage());
    }
    finally {
        out.close();
    }
}

Here, I've used Servlets 3.0 feature for uploading a file... My table schema :

resources
  - UserID [varchar(15)]
  - Document [mediumblob]

Could anyone help me how can I store the document into the table and though BLOB is a type representing binary data, how can I retrieve as a Word Document (*.doc)?

Answer 1

I agree with Archimedix... Instead of putting them into MySQL as BLOB, you can store the file on the disk and store its path in MYSQL as TEXT field. This way your retrieval time will be low. If you are space conscious then you can zip the doc and save it on the disk and on request uncompress and send it.

UPDATE

From your code it appears that you already have the handle of the file and you are able to save it on the server. Now to save space you can zip it using default java zip utility. You might face a problem when two people upload two different files with the same name. To avoid scenarios like this you can either rename your archived document with an uuid (use java 6 uuid class) or you can generate SHA1 for that file and use that for name.

Now you can use the absolute path of the archived (and renamed file) for storing in the MySQL.

Instead of table schema

resources

• UserID [varchar(15)]
• Document [mediumblob]

You can use this

resources

• UserID [varchar(15)]
• Document [varchar(512)]

So for a query like this: Select Document from table Documents WHERE UserID = 'abcd';

you will now get an absolute path for the zipped file. Uncompress this file and send it.

Answer 2

A partial answer on storing the Word documents in files:
You don't need any additional column to save the file name as the document's record ID can serve as the file name.

When saving a new document, do in a database transaction so that you can undo the process when something goes wrong.

In pseudo code, this would look like this:

begin transaction;

try {
    save new record for document;
    save Word document in predefined directory, using record's ID as the filename;
} catch (Exception e) {
    rollback transaction;
    throw e; // Rethrow exception
}

commit transaction;

The code above assumes that an exception is thrown when an error occurs.