Java – How to properly serve large video files stored outside a MySQL database

databasejavajdbcsql

I'm trying to build a Java application that connects to a remote database that stores and retrieves video files that can be over 200MB. I could store/retrieve these files in MySQL directly as LONGBLOBs, yet I read again and again how that's bad practice for various reasons. One solution commonly offered is to store the files on the filesystem and have the database store the location file and serve it up from the server directly.

My problem is, I'm very new to storage and am completely unsure of how to go about doing that. Should I make an FTP connection to the front end once a video file has been requested, then deliver that video over that connection? Or are people usually talking about something else when they say to pull it from the filesystem?

I'm using Java and JDBC to make calls to MySQL.

Best Answer

No, store the files directly on a filesystem and save relative paths to them. This has several benefits:

faster database migration as it does not contain TBs of data,
not being directly limited by the location of the storage space - you simply prepend the root to the relative path and work with that one (serve that one to the application clients),
for certain resources, native HTTP caching thanks to Last-Modified/ETag and If-Modified-Since/If-None-Match headers, thus saving client resources¹,
in your case easier implementation of content streaming - by allowing clients of the application to retrieve sections of the video files, by cutting the video file and returning only specific byte range of it by the server.

With files being stored on the filesystem you need to make sure they are in a directory which is publicly available (should it be, of course). You then serve these files directly from the filesystem from their path.

Let's say your application is located at /home/dyslexit/www, where www is a publicly available directory from the browser. Within the www directory you would create a new one, eg. uploads, which would also be publicly available and used to store the files. Should you then serve some file to a client of the application it would be as easy as providing them a URI: https://domain.com/uploads/videos/my-video.mp4, which would translate to the actual /home/dyslexit/www/uploads/videos/my-video.mp4 file on the server.

It is however not required to directly copy the structure. Should you need it instead of directly serving the files from a HDD when a file is requested you could instead forward the request to a script checking permissions of the requesting user, denying them access should they not be authorized to access a resource or forwarding them to the file being located anywhere respectively.

_{¹ Obviously this can also be implemented if you store files in a database but requires additional setup and custom header manipulation. When serving files directly from the file system this is mostly native.}

Either way, if your business goal is not actually storing files - such as your are creating a data-storage system - but it is merely a side-effect of the business (let's say you want videos in a Facebook-like application), the better choice would probably be to completely outsource file storage to a 3rd party system dedicated to this feature - such as Amazon S3.

Related Solutions

Database – How to add image support to client-server database application

Storing images in the database is not always a bad idea. If you have a small amount of images, say in the tens of thousands range, and they are small in size, sometimes the convenience of storing the images in the database outweighs the added complexity introduced by adding an additional storage server that needs to be written and supported into the mix. Is storing the image in the db the most efficient means? No. Does that matter if you only have 10 client applications accessing a couple hundred images a day? Probably not. In that case, live with the inefficiency and save yourself a lot of effort.

If you have a lot of images (millions, tens of millions,...I've worked on systems that track billions), then you'll want a seperate server application that can handle storage and retrieval of files. At the most basic level this can be (for example) simply a tcp/ip server app tha can handle requests for files, as well as accept them from across the network (a web server running a PHP script could be adapted for this). You'd just access the images based on an id in the database for a row that stores metadata about that image. Remember, if this is across a public wire, you'll need to implement your authorization system to access the images. More complicated servers might handle mirroring, file type conversions, and the migration of files to and from longer term (and cheaper) storage mediums.

Java – Manage query strings in JDBC methods

Consider JDBI

If you want to work closely with JDBC so that you create your own SQL and have your application manage the binding between the query parameters, then JDBI might be a good approach for you.

Essentially, you create an annotated interface that describes your DAO, and JDBI will provide an implementation at runtime. A typical DAO could look like this:

public interface MyDAO
{
  @SqlUpdate("create table something (id int primary key, name varchar(100))")
  void createSomethingTable();

  @SqlUpdate("insert into something (id, name) values (:id, :name)")
  void insert(@Bind("id") int id, @Bind("name") String name);

  @SqlQuery("select name from something where id = :id")
  String findNameById(@Bind("id") int id);

  /**
   * close with no args is used to close the connection
   */
  void close();
}

This approach lends itself to a very lightweight handling of SQL. If your needs are more geared towards supporting multiple databases or abstracting those databases away from the SQL and into a pure object model, then JPA is probably a better approach.

Best Answer

Related Solutions

Database – How to add image support to client-server database application

Java – Manage query strings in JDBC methods

Related Topic