Joining unrelated tables via JPQL

Question

I have the following query which works. All the tables in this query have relations in some way.

@Repository(value = "ARepository")
public interface ARepository extends JpaRepository<CardEntity, String> {
    @Query("SELECT xref.shortUtlTx FROM CardEntity card " +
            "JOIN card.apexUrlCrossRef xref " +
            "JOIN xref.sampleProdOffer offer " +
            "WHERE xref.apexCard.cardNumber = :cardNum " +
            "AND offer.apexeeOfferId = :myCode"
    )
    List<String> getAllValues(@Param("cardNum") String cardNum, @Param("myCode") String myCode);
}

But I also wish to join another table (Entity name -> UrlCountEntity) to this query but that table has no relation to the other tables in this query. Is there a way I could do this?

Based on reading a blog, I tried the following but throws errors. Added this line to the query:

AND EXISTS (SELECT referCount FROM UrlCountEntity referCount WHERE referCount.url.urlTx = xref.shortUtlTx)

@Repository(value = "ARepository")
public interface ARepository extends JpaRepository<CardEntity, String> {

    @Query("SELECT xref.shortUtlTx FROM CardEntity card " +
            "JOIN card.apexUrlCrossRef xref " +
            "JOIN xref.sampleProdOffer offer " +
            "WHERE xref.apexCard.cardNumber = :cardNum " +
            "AND offer.apexeeOfferId = :myCode " +
            "AND EXISTS (SELECT referCount FROM UrlCountEntity referCount WHERE referCount.url.urlTx = xref.shortUtlTx)"
    )
    List<String> getAllValues(@Param("cardNum") String cardNum, @Param("myCode") String myCode);

}

Error as follows:

Method threw 'org.springframework.dao.InvalidDataAccessResourceUsageException' exception.

could not extract ResultSet; SQL [n/a]

qwertzivan qwertzivan · Accepted Answer · 2019-05-15T14:39:28

Based on this article: Using Hibernate 5.1 or newer you can join two unrelated tables via JQPL same way you would do it in SQL:

SELECT first
FROM First first JOIN
    Second second ON first.property = second.property
WHERE first.property = :param

So you would need to change your query to something like this:

@Query("SELECT xref.shortUtlTx FROM CardEntity card " +
        "JOIN card.apexUrlCrossRef xref " +
        "JOIN xref.sampleProdOffer offer " +
        "JOIN UrlCountEntity referCount ON referCount.url.urlTx = xref.shortUtlTx" +
        "WHERE xref.apexCard.cardNumber = :cardNum " +
        "AND offer.apexeeOfferId = :myCode")
List<String> getAllValues(@Param("cardNum") String cardNum, @Param("myCode") String myCode);

Joining unrelated tables via JPQL

1 Answers