CODEWITHSUNDEEP
javaspringjpapagination
200OK
200OK

Reputation: 792

Query different paginated entities using JPA

I have table Folders and Files. Folders and Files are mapped to appropriate entities Folder and File.

I need to make a paginated request to retrieve folders and files as separate entities. As a result it should look like a json:

   {
      folders: [...],
      files: [...],
   }

Moreover folders entities should come always first. E.g. if we have total 10 folders and 10 files and request is ...?pageNumber=1&itemsPerPage=15 the result should be:

   {
      folders: [...], // 10 folders
      files: [...] // 5 files
   }

Folder and File have common fields except Folder have field 'parentFolderKey' and File entity don't. Also File entity have field 'folderKey', but Folder don't.

If it would be just one entity it will be ok but how to make a paginated query to build such response with combined entities which have different fields (parentFolderKey, folderKey)? I'm also using JPA repositories and Spring. What can you suggest?

Upvotes: 1

Views: 231

Answers (2)

Tobias Liefke
Tobias Liefke

Reputation: 9022

If I would be able to define the table structure myself, I would go with the inheritance approach. As File and Folder share many properties, this is the most obvious way.

But if there is already a table structure, I would use a @Subselect in Hibernate in combination with a UNION:

@Entity
@Subselect("SELECT concat('folder.', id) fid, parentFolderKey parentFolder_Id, ... FROM Folder"
         + " UNION SELECT concat('file.', id) fid, folderKey parentFolder_Id, ... FROM File")
@Synchronize({ "Folder", "File" })
public FolderOrFile {
  
  @Id
  private String fid;

  @ManyToOne
  private Folder parentFolder;

  ... Common properties of files and folders

  public boolean isFile() { return fid.startsWith("file."); }

}

(The @Synchronize annotation is for ensuring that the cache for FolderOrFile is cleared, when a Folder or File entity is changed.)

You can now create a query for that entity even with paging.

Upvotes: 1

crizzis
crizzis

Reputation: 10716

Well, the obvious answer would be to make them 'one entity' by tying Folder and File with an inheritance hierarchy. Perhaps both could inherit from something like a FileSystemEntry?

You could then query FileSystemEntry and ORDER BY the discriminator column to get Folders on top. Splitting the list into files and folders is simply a matter of finding the first File in the result.

Upvotes: 1

Related Questions