0

We are using Spring Data JPA + Hibernate approach for implementation.

For complex queries with joins we have created a common mapping xml where we are defining those queries.

We are not creating each entity class for these complex queries.

How can we use JPARepository and execute these query?
Is there any way? Below is one of the query written in orm.xml :

SELECT FMT_NAME( pers.id ) AS customer_name, first_name, mid_name, 
                    last_name, 
                    addr.line_1_addr, 
                    addr.line_2_addr, 
                    RTRIM( LTRIM( addr.city_name || ', ' || addr.state_code || ' ' || 
                    addr.zip_code_num, ', ') || '-' || addr.zip_code_suffix, '-' ) AS line_3_addr 
               FROM pers, (SELECT pers_addr.pers_id , 
                            addr.line_1_addr, addr.line_2_addr, 
                            addr.city_name, addr.state_code, 
                            addr.zip_code_num, addr.zip_code_suffix 
                       FROM pers_addr, addr WHERE addr.id = pers_addr.addr_id 
                        AND TRUNC(SYSDATE) BETWEEN pers_addr.beg_date AND pers_addr.end_date 
                        AND pers_addr.type_code = 'ML' 
                    ) addr WHERE pers.id = ?  AND pers.id = addr.PERS_ID
SSK
  • 3,444
  • 6
  • 32
  • 59
Sanu
  • 1
  • 2
  • Do you have any Entity with the corresponded Repository? – xTheDoctah Aug 13 '21 at 14:36
  • No, we do not have to create so many entity classes , so trying to follow the xml approach. – Sanu Aug 13 '21 at 15:25
  • You cannot use the JpaRepository without an entity as far as i know, so you can try this : https://stackoverflow.com/questions/60871898/spring-data-repository-without-specifying-entity or you can create a class with the @Repository annotation but to do queries you have to use the Entity manager (here an example)https://stackoverflow.com/questions/55513776/create-spring-repository-without-entity – xTheDoctah Aug 13 '21 at 15:33
  • Just to clarify, JpaRepository its just an interface, that provides some crud. Nothing less nothing more. So without an entity u cannot use that interface, because the interface needs those info(entity and id type) – xTheDoctah Aug 13 '21 at 15:36

1 Answers1

0

You can make use select new the resource for your ordinary class. For example, I have the mapping classes Student, Team, School, Subject and want to get some data from these entities. And build an ordinary class StudentData So I can do a query(@Query or @NamedQuery) like that:

select new com.my.package.vo.StudentData(student, teacher, subject) from Stutdent student inner join student.team inner join team.teacher ....

Fernando Oliveira
  • 46
  • 4