Storing files of any-type in database

Question

I am going to develop a portal in J2EE for students to upload and download data. Data can be any thing like PDF file, doc file, ppt file or can be Image or Video also.

How can i manage Data in database?

Answer 1

Looks like you need all ground up. I will just give direction, you need work on it, and if there is a problem come with specific issue.

1. Upload files to your file system, or storage, or someplace like Amazon S3. For this use, something like Apache FileUpload

2. Once upload is done, get the location of the file where you saved it on the storage-server. Say, /mnt/media/uploads/file.extension

3. Have your database with a table that stores the metadata about the file. In general it would look like:

   upload_id | file_name | file_location | user_id | timestamp
   

   where upload_id is the PK, file_name is the uploaded file's original name, file_location is the actual full path of the file where it resides in your storage, user_id is the uploader's Id, a FK from users table.

4. The display part: Have your logic to use this database to display file_name.

5. To download, you can have something like this How to config Tomcat to serve images from an external folder outside webapps? , note that this is not limited to serving image file. It can serve any type of file.

Now, you would wonder, why on earth am I doing so much painful things instead of just saving it as binary data in database? Well, I do not want to dive into discussion, I personally prefer this approach as I think databases are for data, file-systems are for files. See a good discussion here: Storing Images in DB - Yea or Nay?

Hope this helps.

Answer 2

I'm going to guess that you don't care what format the data is in (which is to say, you won't query the data at the database level), in which case you should look at the binary data types.

I can't be more specific without knowing what database back-end you're using, but some common names for this data type are BLOB, binary, varbinary, raw or image.

More here: http://onlamp.com/onlamp/2001/09/13/aboutSQL.html

Answer 3

Store the file in the file system renamed to the record id where the file name is stored. This is less overhead and allows a better virus scanning; "you uploaded the xyz virus".

You can even do tricks like storing large files on another server. Backups are easier.