Storing Files Indexed by a Database

1. Overview

When we are building some sort of content management solution, we need to solve two problems. We need a place to store the files themselves, and we need some sort of database to index them.

It’s possible to store the content of the files in the database itself, or we could store the content somewhere else and index it with the database.

In this article, we’re going to illustrate both of these methods with a basic Image Archive Application. We’ll also implement REST APIs for upload and download.

2. Use Case

Our Image Archive Application will allow us to upload and download JPEG images.

When we upload an image, the application will create a unique identifier for it. Then we can use this identifier to download it.

We’ll use a relational database, with Spring Data JPA and Hibernate.

3. Database Storage

Let’s start with our database.

3.1. Image Entity

First, let’s create our Image entity:

@Entity
class Image {

    @Id
    @GeneratedValue
    Long id;

    @Lob
    byte[] content;

    String name;
    // Getters and Setters
}

The id field is annotated with @GeneratedValue. This means the database will create a unique identifier for each record we add. By indexing the images with these values, we don’t need to worry about multiple uploads of the same image conflicting with each other.

Second, we have the Hibernate @Lob annotation. It’s how we tell JPA our intention of storing a potentially large binary.

3.2. Image Repository

Next, we need a repository to connect to the database.

We’ll use the spring JpaRepository:

@Repository
interface ImageDbRepository extends JpaRepository<Image, Long> {}

Now we’re ready to save our images.  We just need a way to upload them to our application.

4. REST Controller

We will use a MultipartFile to upload our images. Uploading will return the imageId we can use to download the image later.

4.1. Image Upload

Let’s start by creating our ImageController to support upload:

@RestController
class ImageController {

    @Autowired
    ImageDbRepository imageDbRepository;

    @PostMapping
    Long uploadImage(@RequestParam MultipartFile multipartImage) throws Exception {
        Image dbImage = new Image();
        dbImage.setName(multipartImage.getName());
        dbImage.setContent(multipartImage.getBytes());

        return imageDbRepository.save(dbImage)
            .getId();
    }
}

The MultipartFile object contains the content and original name of the file. We use this to construct our Image object for storing in the database.

This controller returns the generated id as the body of its response.

4.2. Image Download

Now, let’s add a download route:

@GetMapping(value = "/image/{imageId}", produces = MediaType.IMAGE_JPEG_VALUE)
Resource downloadImage(@PathVariable Long imageId) {
    byte[] image = imageRepository.findById(imageId)
      .orElseThrow(() -> new ResponseStatusException(HttpStatus.NOT_FOUND))
      .getContent();

    return new ByteArrayResource(image);
}

The imageId path variable contains the id that was generated at upload. If an invalid id is provided, then we’re using ResponseStatusException to return an HTTP response code 404 (Not Found). Otherwise, we’re wrapping the stored file bytes in a ByteArrayResource which allows them to be downloaded.

5. Database Image Archive Test

Now we’re ready to test our Image Archive.

First, let’s build our application:

mvn package

Second, let’s start it up:

java -jar target/image-archive-0.0.1-SNAPSHOT.jar

5.1. Image Upload Test

After our application is running, we’ll use the curl command-line tool to upload our image:

curl -H "Content-Type: multipart/form-data" \
  -F "[email protected]" http://localhost:8080/image

As the upload service response is the imageId, and this is our first request, the output will be:

1

5.2. Image Download Test

Then we can download our image:

curl -v http://localhost:8080/image/1 -o image.jpeg

The -o image.jpeg option will create a file named image.jpeg and store the response content in it:

% Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0*   Trying ::1...
* TCP_NODELAY set
* Connected to localhost (::1) port 8080 (#0)
> GET /image/1 HTTP/1.1
> Host: localhost:8080
> User-Agent: curl/7.54.0
> Accept: */*
> 
< HTTP/1.1 200 
< Accept-Ranges: bytes
< Content-Type: image/jpeg
< Content-Length: 9291

We got an HTTP/1.1 200, which means that our download was successful.

We could also try downloading the image in our browser by hitting http://localhost:8080/image/1.

6. Separate Content and Location

So far, we’re capable of uploading and downloading images within a database.

Another good option is uploading the file content to a different location. Then we save only its filesystem location in the DB.

For that we’ll need to add a new field to our Image entity:

String location;

This will contain the logical path to the file in some external storage. In our case, it will be the path on our server’s filesystem. 

However, we can equally apply this idea to different Stores. For example, we could use cloud storage – Google Cloud Storage or Amazon S3. The location could also use a URI format, for example, s3://somebucket/path/to/file.

Our upload service, rather than writing the bytes of the file to the database, will store the file in the appropriate service – in this case, the filesystem – and will then put the location of the file into the database.

7. Filesystem Storage

Let’s add the capability to store the images in the filesystem to our solution.

7.1. Saving in the Filesystem

First, we need to save our images to the filesystem:

@Repository
class FileSystemRepository {

    String RESOURCES_DIR = FileSystemRepository.class.getResource("/")
        .getPath();

    String save(byte[] content, String imageName) throws Exception {
        Path newFile = Paths.get(RESOURCES_DIR + new Date().getTime() + "-" + imageName);
        Files.createDirectories(newFile.getParent());

        Files.write(newFile, content);

        return newFile.toAbsolutePath()
            .toString();
    }
}

One important note – we need to make sure that each of our images has a unique location defined server-side at upload time. Otherwise, our uploads may overwrite each other.

The same rule would apply to any cloud storage, where we should create unique keys. In this example, we’ll add the current date in milliseconds format to the image name:

/workspace/archive-achive/target/classes/1602949218879-baeldung.jpeg

7.2. Retrieving From Filesystem

Now let’s implement the code to fetch our image from the filesystem:

FileSystemResource findInFileSystem(String location) {
    try {
        return new FileSystemResource(Paths.get(location));
    } catch (Exception e) {
        // Handle access or file not found problems.
        throw new RuntimeException();
    }
}

Here we’re looking for the image using its location. Then we return a FileSystemResource.

Also, we’re catching any exception that may happen while reading our file. We might also wish to throw exceptions with particular HTTP statuses.

7.3. Data Streaming and Spring’s Resource

Our findInFileSystem method returns a FileSystemResource, an implementation of Spring’s Resource interface.

It will start reading our file only when we use it. In our case, it’ll be when sending it to the client via the RestController. Also, it’ll stream the file content from the filesystem to the user, saving us from loading all the bytes into memory.

This approach is a good general solution for streaming files to a client. If we’re using cloud storage instead of the filesystem, we can replace the FileSystemResource for another resource’s implementation, like the InputStreamResource or ByteArrayResource.

8. Connecting the File Content and Location

Now that we have our FileSystemRepository, we need to link it with our ImageDbRepository.

8.1. Saving in the Database and Filesystem

Let’s create a FileLocationService, starting with our save flow:

@Service
class FileLocationService {

    @Autowired
    FileSystemRepository fileSystemRepository;
    @Autowired
    ImageDbRepository imageDbRepository;

    Long save(byte[] bytes, String imageName) throws Exception {
        String location = fileSystemRepository.save(bytes, imageName);

        return imageDbRepository.save(new Image(imageName, location))
            .getId();
    }
}

First, we save the image in the filesystem. Then we save the record containing its location in the database.

8.2. Retrieving From Database and Filesystem

Now, let’s create a method to find our image using its id:

FileSystemResource find(Long imageId) {
    Image image = imageDbRepository.findById(imageId)
      .orElseThrow(() -> new ResponseStatusException(HttpStatus.NOT_FOUND));

    return fileSystemRepository.findInFileSystem(image.getLocation());
}

First, we look for our image in the database. Then we get its location and fetch it from the filesystem.

If we don’t find the imageId in the database, we’re using ResponseStatusException to return an HTTP Not Found response.

9. Filesystem Upload and Download

Finally, let’s create the FileSystemImageController:

@RestController
@RequestMapping("file-system")
class FileSystemImageController {

    @Autowired
    FileLocationService fileLocationService;

    @PostMapping("/image")
    Long uploadImage(@RequestParam MultipartFile image) throws Exception {
        return fileLocationService.save(image.getBytes(), image.getOriginalFilename());
    }

    @GetMapping(value = "/image/{imageId}", produces = MediaType.IMAGE_JPEG_VALUE)
    FileSystemResource downloadImage(@PathVariable Long imageId) throws Exception {
        return fileLocationService.find(imageId);
    }
}

First, we made our new path start with “/file-system“.

Then we created the upload route similar to that in our ImageController, but without the dbImage object.

Lastly, we have our download route, which uses the FileLocationService to find the image and returns the FileSystemResource as the HTTP response.

10. Filesystem Image Archive Test

Now, we can test our filesystem version the same way we did with our database version, though the paths now start with “file-system“:

curl -H "Content-Type: multipart/form-data" \
  -F "[email protected]" http://localhost:8080/file-system/image

1

And then we download:

curl -v http://localhost:8080/file-system/image/1 -o image.jpeg

11. Conclusion

In this article, we learned how to save file information in a database, with the file content either in the same row or in an external location.

We also built and tested a REST API using multipart upload, and we provided a download feature using Resource to allow streaming the file to the caller.