sql - 如何编写 JPQL 查询以查找此连接中未找到的记录？

对于我的生活，我无法弄清楚如何构建这个 JPA 查询。

我需要找到未在给定 SyncSendingConfig 下传输的 TransactionLogs，按 ID 排序。

在 SO 上进行研究，我认为应该可以在 SQL 中进行外连接，其中一侧的 ID 为空，如下图所示:



这是我必须与之合作的实体。

@Entity
public class SyncSendingConfig {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private long id;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "sendingConfig")
    private Set<SyncJob> sendJobs = new HashSet<>();
}

@Entity
public class SyncJob {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "sending_config_id")
    private SyncSendingConfig sendingConfig;

    @ManyToMany(cascade = { CascadeType.ALL })
    @JoinTable(
        name = "SyncJob_TransactionLog",
        joinColumns = { @JoinColumn(name = "sync_job_id") },
        inverseJoinColumns = { @JoinColumn(name = "transaction_log_id") }
    )
    private Set<TransactionLog> transmitted = new HashSet<>();
}

@Entity
public class TransactionLog {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private long id;

    @ManyToMany(mappedBy = "transmitted")
    private Set<SyncJob> syncJobs = new HashSet<>();
}

我正在尝试编写的 DAO:

public interface SyncSendingConfigDao extends JpaRepository<SyncSendingConfig, Long> {

    // TODO: This is the query I'm trying to get to work
    /** Returns those transactions that were never sent for the given SyncSenderConfig, ordered by ID */
    @Query("SELECT tl FROM SyncJob sj "+
        "JOIN SyncSendingConfig ssc ON sj.sendingConfig = ssc.id AND ssc.id= :sendingConfigId "+
        "RIGHT JOIN TransactionLog tl on tl.syncJobs = sj "+
        "WHERE sj.id is null"
    )
    Stream<TransactionLog> findTransactionsNotSentForSyncSendingConfigId(@Param("sendingConfigId") long sendingConfigId);

    // If this part is relevant, this join shows how I can get only those SyncJobs which are related to the SyncSendingConfig of interest
    @Query("SELECT sj FROM SyncJob sj JOIN SyncSendingConfig ssc ON sj.sendingConfig = ssc.id WHERE ssc.id= :sendingConfigId ")
    @QueryHints(value = @QueryHint(name = org.hibernate.jpa.QueryHints.HINT_FETCH_SIZE, value = "500"))
    Stream<SyncJob> findJobs(@Param("sendingConfigId") long sendingConfigId);

}

上面关于 DAO 的查询显示了我正在尝试做的事情。我真的不确定如何将 SQL 转换为 JPQL ......尤其是在连接条件和顺序上。

更新:

这是我正在尝试翻译的确切 SQL 查询。它匹配 hibernate 在上面的类中定义的所有关系。

select tl.*
from sync_job sj
  join sync_sending_config ssc
    on ssc.id = sj.sending_config_id and ssc.id=2
  join sync_job_transaction_log sjtl
    on sjtl.sync_job_id = sj.id
  RIGHT JOIN transaction_log tl
    on tl.id = sjtl.transaction_log_id
  where sjtl.sync_job_id is null

当直接运行此查询时，它会返回正在寻找的确切结果。

如果有人可以提供帮助，我将不胜感激。我一直在靠墙试图弄清楚 JPQL 语法。

谢谢

更新 2

使用“@S B”后，似乎 JPQL 不支持右连接。由于没有找到如何使用左连接(如果可能)在 JPQL 中编写此内容，我使用了 native 查询:

@Query(value = "select tl.* from sync_job sj "+
                "join sync_sending_config ssc on ssc.id = sj.sending_config_id and ssc.id = :sendingConfigId "+
                "join sync_job_transaction_log sjtl on sjtl.sync_job_id = sj.id "+
                "RIGHT JOIN transaction_log tl on tl.id = sjtl.transaction_log_id "+
                "where sjtl.sync_job_id is null",
                nativeQuery = true)
@QueryHints(value = @QueryHint(name = org.hibernate.jpa.QueryHints.HINT_FETCH_SIZE, value = "500"))
Stream<TransactionLog> findTransactionsNotSentForSyncSendingConfigId(@Param("sendingConfigId") long sendingConfigId);

最佳答案

假设以下虚拟数据设置:

事务日志 ID:1、2、3、4

SyncSendingConfig ID:1、2

同步作业:

ID 1，SyncSendingConfigID 1

ID 2，SyncSendingConfigID 1

ID 3，SyncSendingConfigID 2

ID 4，SyncSendingConfigID 2

sync_job_transaction_log

SyncJobId 1，TransactionLogId 1

SyncJobId 1，TransactionLogId 2

SyncJobId 2，TransactionLogId 1

SyncJobId 2，TransactionLogId 2

TransactionLogs 1 和 2 根据 sync_job_transaction_log 表中的映射在 SyncSendingConfig ID 1 下传输。
因此，在 SyncSendingConfig ID 1 下传输的 TransactionLogs 而不是 将是 3 和 4 。
因此，为了找到在给定的 SyncSendingConfig 下尚未传输的 TransactionLogs，对应的 JPQL 是 -

@Query("select t from TransactionLog t where t not in (" +
        "select t1 from TransactionLog t1 join t1.syncJobs tsj where tsj in "
        + "(select sj from SyncJob sj where sj.sendingConfig.id = :sendingConfigId)"
        + ")")

将 JPQL 视为应用于 Java 对象的 SQL，实体表示表，属性表示列，has-a 关系表示映射关系。
现在，当您要连接两个表时，只需引用相应的实体，只要正确指定了连接列，就会在连接表和列上正确形成 SQL 查询。
示例 SQL -

select column(s) from table1 <type of> join table2 on table1.column1 = table2.column1 where <filter conditions here>

对应的 JPQL 设置 -

Entity1 (corresponds to table1) ->
    property1 (corresponds to column)
    property2 (corresponds to mapping relationship, has @JoinColumn details)

上述设置的 JPQL -

select property1 from entity1 <type of> join entity1.property2 where <filter condition here>

在评论中讨论后更新 -
由于无法在当前设置中正确连接，建议根据性能参数评估 JPQL，或者将工作 SQL 用作 nativeQuery。

@Query(value = "select tl.* from sync_job sj "+
                "join sync_sending_config ssc on ssc.id = sj.sending_config_id "+
                "join sync_job_transaction_log sjtl on sjtl.sync_job_id = sj.id "+
                "RIGHT JOIN transaction_log tl on tl.id = sjtl.transaction_log_id "+
                "where sjtl.sync_job_id is null and ssc.id = :sendingConfigId",
                nativeQuery = true)

关于sql - 如何编写 JPQL 查询以查找此连接中未找到的记录？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59910507/