For the life of me, I can't figure out how to construct this JPA query.
I need to find TransactionLogs which have not been transmitted under a given SyncSendingConfig, ordered by ID.
Researching it on SO, I figure it should be possible in SQL to do an outer join where the IDs are null for the one side, as in this diagram:
Here's the Entities I have to work with.
@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<>();
}
And the DAO I'm trying to write:
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);
}
The query above on the DAO shows what I'm attempting to do. I'm really unsure of how to translate SQL to JPQL... especially on the join conditions and order.
Update:
Here's the exact SQL query which I'm trying to translate. It matches all the relationships defined by hibernate in the classes above.
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
When this query is run directly, it returns the exact results being sought.
If anyone can offer help, I'd greatly appreciate it. I've been running against a wall trying to figure the JPQL syntax out.
Thanks
Update 2
After working with '@S B', it appears that JPQL doesn't support a right join. Short of finding out how to write this in JPQL with a left join (if possible), I went with a native query:
@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);
