英文:
Query different paginated entities using JPA
问题
我有一个名为"Folders"和"Files"的表。"Folders"和"Files"分别被映射到适当的实体"Folder"和"File"。
我需要进行分页请求，以分开的实体形式检索文件夹和文件。结果应该类似于json：
{
folders: [...],
files: [...],
}
此外，文件夹实体应始终在前。例如，如果总共有10个文件夹和10个文件，并且请求是
...?pageNumber=1&itemsPerPage=15
结果应为：
{
folders: [...], // 10个文件夹
files: [...] // 5个文件
}
"Folder"和"File"具有共同的字段，除了"Folder"有字段"parentFolderKey"，而"File"实体则没有。此外，"File"实体有字段"folderKey"，但"Folder"则没有。
如果只有一个实体，这就没问题了，但如何进行分页查询以构建具有不同字段（parentFolderKey、folderKey）的组合实体响应呢？我还在使用JPA存储库和Spring。你有什么建议？
英文:
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?
答案1
得分: 1
好的，以下是翻译好的内容：
显而易见的答案是通过建立继承层次结构，将 Folder 和 File 关联起来，使它们成为“一个实体”。也许两者都可以继承自诸如 FileSystemEntry 的某个东西？
然后，您可以查询 FileSystemEntry 并按照鉴别器列进行 ORDER BY，以便将 Folder 放在顶部。将列表分割为 files 和 folders 只是找到结果中第一个 File 的问题。
英文:
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.
答案2
得分: 1
如果我能够自己定义表结构，我会采用继承方法。由于File和Folder共享许多属性，这是最明显的方式。
但如果已经有了表结构，我会在Hibernate中使用@Subselect与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;
... 文件和文件夹的共同属性
public boolean isFile() { return fid.startsWith("file."); }
}
（@Synchronize注解用于确保在更改Folder或File实体时清除FolderOrFile的缓存。）
现在，您甚至可以为该实体创建带有分页的查询。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论