Overview

Note: This article assumes that you have a basic understanding of database views, Java, DTOs (Data Transfer Objects), DAOs (Data Access Objects), SQL Queries, Spring, JPA and Hibernate, however the examples use concepts that are applicable regardless of tech stack. A lot of the technology-specific boilerplate code is omitted for brevity and readability.

Ever come across a situation where you have a few entities that are very similar to each other, but not quite, yet you need to combine them in a single response? Or perhaps fetching media data that is related to multiple entities, each and every in their respected join table? Having to write an entity class and a DTO for all of them just to be able to query the data? Lets take a look at a practical example.

Lets say we have an entity called MediaData which is represented as follows:

MediaData
-----------------
- id
- url
- fileName
- fileType
- createdAt
-----------------

Lets assume that MediaData is related to many other entities. For example a table that represents all of the UserMediaData, like so:

UserMediaData
-----------------
- user_id
- media_data_id
-----------------

A table that represents all of the CompanyMediaData, like so:

CompanyMediaData
-----------------
- company_id
- media_data_id
-----------------

Wouldn't it be convenient if instead of querying multiple tables and mapping the results to their corresponding DTOs, you could just summarize everything in one place, so instead of mapping everything individually, you could use a single DTO that maps all of the relevant information, from each of those queries? This is where Database Views come into play.

By definition - a view is the result set of a stored query on the data which can be queried just as you can query any other persistent database collection object. This means that these views can be mapped to entities! Enough talk, lets create the views that we will use to retrieve the relevant data and then map them to our DTO.

Our MediaDataDto would look something like this (note that I added a few additional fields that are use case specific, such as the entityQualifier, which help distinguish between the objects themselves):

public class MediaDataDto {

    private String id;
    private String entityId;
    private EntityQualifier entityQualifier; // EntityQualifier is an ENUM containing all of the valid values for the entityQualifier property.
    private String url;
    private String fileName;
    private FileType fileType; // FileType is another ENUM containing all of the valid values for the fileType property.
    private Instant createdAt;
    
    // Getters and setters are omitted for brevity
}

How these views are created depends on the technology used. If you use a database versioning tool like Liquibase for example, the changeSet (migration) would look like this:

- changeSet:  
id: ticket-ID  
author: benjamin.bajic@antcolony.io  
changes:    
  - createView:  
      viewName: media_data_lookup  
      relativeToChangelogFile: true  
      path: view/media-data-lookup.sql

And finally, our corresponding database view (media-data-lookup.sql):

SELECT media_data.id AS id,
'USER' AS entity_qualifier,
user_id AS entity_id,
file_name AS file_name,
file_type AS file_type,
createdAt AS createdAt

FROM media_meta_data
RIGHT JOIN user_media_data rel ON media_data.id = rel.media_data_id

UNION

SELECT media_data.id AS id,
'COMPANY' AS entity_qualifier,
company_id AS entity_id,
file_name AS file_name,
file_type AS file_type,
createdAt AS createdAt
FROM media_data
RIGHT JOIN company_media_data rel ON media_data.id = rel.media_data_id

Now we can create our Entity class with its corresponding DAO and use it to query the data from the view.

Entity class:

@Entity
public class MediaDataLookup implements Serializable {
    private static final long serialVersionUID = 1L;
    
    @Id
    private String id;
    
    @Enumerated(EnumType.STRING)
    private EntityQualifier entityQualifier;
    
    @Enumerated(EnumType.STRING)
    private FileType fileType;
    
    @CreationTimestamp
    private Instant createdAt;
    
    private String fileName;
    private String entityId;
    
}

DAO interface:

public interface MediaDataLookupDao extends JpaRepository<MediaDataLookup, String> {}

Thats it! You can use your DAO class to query data, apply Spring Data JPA pagination, sorting and more, like so:

public MediaDataDto getMediaById (String id) {
    var media = mediaDataLookupDao.findById(id)
    .orElseThrow(EntityNotFoundException::new);
    
    return dtoMapper.toMediaDataDto(media); // Your DTO mapper implementation  
}