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

Question

对于我的生活，我无法弄清楚如何构建这个 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

使用“@SB”后，JPQL 似乎不支持右连接。没有找到如何用左连接（如果可能）在 JPQL 中编写这个，我使用了一个原生查询：

@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);

标签： sqlhibernatejpqlouter-join

Answer 1

假设以下虚拟数据设置：

1. 事务日志 ID：1、2、3、4
2. SyncSendingConfig ID：1、2
3. 同步作业：
   • ID 1，同步发送配置 ID 1
   • ID 2，同步发送配置 ID 1
   • ID 3，同步发送配置 ID 2
   • ID 4，同步发送配置 ID 2
4. sync_job_transaction_log
   • SyncJobId 1，TransactionLogId 1
   • SyncJobId 1，TransactionLogId 2
   • SyncJobId 2，TransactionLogId 1
   • SyncJobId 2，TransactionLogId 2

TransactionLogs1并根据sync_job_transaction_log 表中的映射在2SyncSendingConfig ID 下传输。1因此，未在 SyncSendingConfig ID 下传输的TransactionLogs1将是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)