mysql - 使用一个表/实体的查询结果来查询另一个实体/表

Question

我有两个表，由两个实体表示:

        @Entity
    public class HostEntity
    {
        @NotNull
        private String myGroup;

        @Id
        @NotNull
        private String host;

        @NotNull
        private long inGroupSince;
}

和

    @Entity
    public class GroupEntity
    {
        @NotNull
        private String groupId;

        @Id
        @NotNull
        private String propertiesStr;
}

每个实体/表都有crudRepository。

因此，首先给定两个数字，startTime和finishTime，以及两个字符串，stringA和stringB -获取所有 HostEntity.myGroup(我们称之为 ListA)，使得 HostEntity.inGroupSince 位于 startTime 和 finishTine 之间，然后返回所有 GroupEntity.groupId，使得 GroupEntity.groupdId 位于 ListA 和 GroupEntity.propertiesStr 包含 stringA 和 StringB

实现这一目标的最佳方法是什么？我可以在一个查询中完成它吗？

我正在使用 Spring Boot 和 crudRepository 工作，并且对它有点陌生。

我可以在repostiroy中使用@query注释，例如我得到以下代码:

@Repository
@Profile(Constants.SPRING_PROFILE_DEVELOPMENT)
public interface IGroupsRepositoryDev extends IGroupsRepository
{
    @Query("SELECT j FROM GroupEntity j WHERE LOWER(j.propertiesStr) LIKE %?1%  and LOWER(j.propertiesStr) LIKE %?2% and LOWER(j.propertiesStr) LIKE %?3%"
        + " and LOWER(j.propertiesStr) LIKE %?4% and LOWER(j.propertiesStr) LIKE %?5% and LOWER(j.propertiesStr) LIKE %?6% and LOWER(j.propertiesStr) LIKE %?7%"
        + " and LOWER(j.propertiesStr) LIKE %?8% and LOWER(j.propertiesStr) LIKE %?9% and LOWER(j.propertiesStr) LIKE %?10% and LOWER(j.propertiesStr) LIKE %?11% ")
    List<UUID> findByProperties(String property1,String property2,String property3,String property4,String property5,String property6
        ,String property7,String property8,String property9,String property10,String property11);

}

返回每个 GroupEntity，使得 GroupEntity.propertiesStr 内部包含 11 个字符串

更新

我按照下面的建议使用了以下内容:

    @Query(" SELECT groupId from GroupEntity where groupId IN (SELECT myGroup FROM HostEntity WHERE inGroupSince > ?12 AND inGroupSince < ?13) "
       + "AND LOWER(propertiesStr) LIKE %?1%  and LOWER(propertiesStr) LIKE %?2% and LOWER(propertiesStr) LIKE %?3%"
       + " and LOWER(propertiesStr) LIKE %?4% and LOWER(propertiesStr) LIKE %?5% and LOWER(propertiesStr) LIKE %?6% and LOWER(propertiesStr) LIKE %?7%"
       + " and LOWER(propertiesStr) LIKE %?8% and LOWER(propertiesStr) LIKE %?9% and LOWER(propertiesStr) LIKE %?10% and LOWER(propertiesStr) LIKE %?11% ")
   List<String> findByPropertiesBetweenTime(String property1,String property2,String property3,String property4,String property5,String property6
       ,String property7,String property8,String property9,String property10,String property11,long st,long ft);

我把它放在 GroupEntity 存储库中，但它不起作用。我做错了什么？

Answer 1

[...] get all HostEntity.myGroup (lets call this ListA) such that HostEntity.inGroupSince is between startTime and finishTine [...]

SELECT myGroup FROM HostEntity WHERE inGroupSince > startTime AND inGroupSince < finishTime

[...] then , return all GroupEntity.groupId such that GroupEntity.groupdId is in ListA [...]

使用上面的 SELECT 作为内部选择:

SELECT groupId FROM GroupEntity 
WHERE 
  groupId IN (SELECT myGroup FROM HostEntity WHERE inGroupSince > startTime AND inGroupSince < finishTime)

[...] and GroupEntity.propertiesStr containt stringA and StringB [...]

添加喜欢:

SELECT groupId FROM GroupEntity 
WHERE 
  groupId IN (SELECT myGroup FROM HostEntity WHERE inGroupSince > startTime AND inGroupSince < finishTime)
AND propertiesStr LIKE '%stringA%' 
AND propertiesStr LIKE '%stringB%'