Storing Files Indexed by a Database

Storing Files Indexed by a Database

Learn how to store files inside a databases as Large Objects, or how to use a database as an index of a filesystem. 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.

Learn how to store files inside a databases as Large Objects, or how to use a database as an index of a filesystem.

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.

database programming developer

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

How long does it take to develop/build an app?

This article covers A-Z about the mobile and web app development process and answers your question on how long does it take to develop/build an app.

Developer Career Path: To Become a Team Lead or Stay a Developer?

For a developer, becoming a team leader can be a trap or open up opportunities for creating software. Two years ago, when I was a developer, ... by Oleg Sklyarov, Fullstack Developer at Skyeng company

Tracking a Developer’s Journey From Documentation Visit

Measuring website activity provides only half the story. See how to best track the developer's journey and what funnel stages makes sense for API-first products

Offshore Software Development - Best Practices

To make the most out of the benefits of offshore software development, you should understand the crucial factors that affect offshore development.

Hire Ruby on Rails Developer | Hire RoR Developer

#1 Ruby on Rails development company. Hire Ruby on rails developer or a team to build secure, scalable and complex web solutions with a quick turnaround time.