I am having a hard time joining between unrelated domains in JPA

Question

I am experiencing various things while studying JPA, but I am too unfamiliar with it, so I would like to get some advice.

The parts I got stuck in during my study were grouped into three main categories. Could you please take a look at the code below?

@Repository
public interface TestRepository extends JpaRepository<TestEntity,Long> {
    @Query(" SELECT
             , A.test1 
             , A.test2
             , B.test1 
             , B.test2
             FROM TEST_TABLE1 A
             LEFT JOIN TEST_TABLE2 B
             ON A.test_no = B.test_no
             WHERE A.test3 = ?1   # Here's the first question
             if(VO.test4 is not null) AND B.test4 = ?2") # Here's the second question
    List<Object[] # Here's the third question> getTestList(VO);
}

First, is it possible to extract test3 from the VO received when using native sql?

Usually, String test1 is used like this, but I wonder if there is any other way other than this.

Second, if extracting is possible in VO, can you add a query in @QUERY depending on whether Test4 is valued or not?

Thirdly, if I use List<Object[]>, can the result of executing a query that is not in the already created entity (eg, test1 in TEST_TABLE2, which is not in the entity of TEST_TABLE1) can be included?,

Answer 1

First, is it possible to extract test3 from the VO received when using native sql? Usually, String test1 is used like this, but I wonder if there is any other way other than this.

Yes, it is possible.

You must use, eg where :#{[0].test3} is equals vo.test3 [0] is position the first param, past for method annotated with @Query

@Query(value = "SELECT a.test1, a.test2, b.test1, b.test2 
FROM test_table1 a 
LEFT JOIN test_table2 b ON a.test_no = b.test_no 
WHERE a.test3 = :#{[0].test3}", nativeQuery = true)
List<Object[]> getList(VO);

Second, if extracting is possible in VO, can you add a query in @QUERY depending on whether Test4 is valued or not?

You can use a trick eg:

SELECT ... FROM table a
LEFT JOIN table b ON a.id = b.id
WHERE a.test3 = :#{[0].test3}
AND (:#{[0].test4} IS NOT NULL AND b.test4 = :#{[0].test4})

Thirdly, if I use List<Object[]>, can the result of executing a query that is not in the already created entity (eg, test1 in TEST_TABLE2, which is not in the entity of TEST_TABLE1) can be included?

Sorry, but I not understand the third question.

Maybe this tutorial will help you: https://www.baeldung.com/jpa-queries-custom-result-with-aggregation-functions