spring data jpa native query with join

Question

How do I execute a native query in spring data jpa, fetching child entities at the same time? If I have Eager FetchType on the child entity object, spring data is executing 2 queries. 1 for the parent and 1 for the child entities.

Is there a way to execute only 1 native query to get the parent and child entities?

parent:

@Entity
public class Parent {
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private long id;

   @Temporal(TemporalType.TIMESTAMP)
   private Date ts;

   @ManyToOne(fetch=FetchType.LAZY)
   private Child child;
}

child:

@Entity
public class Child {
   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private long id;

   @OneToMany(fetch=FetchType.LAZY, mappedBy="parent")
   private Parent parent;
}

query:

public interface ParentRepository extends Repository<Parent, Integer> {
    @Query(value = "SELECT * from parents p inner join children c on c.id=p.childId where TIMESTAMPDIFF(SECOND, p.ts, CURRENT_TIMESTAMP) < :interval", nativeQuery = true)
    Parent findOneByInterval(@Param("interval") long interval);
}

I am using Hibernate 5, and MySQL along with Spring Data JPA.

I also tried adding a @NamedEntityGraph to the Parent class and @EntityGraph to the query method with no luck.

Answer 1

If u want the Parent object, u can try like this

public interface ParentRepository extends Repository<Parent, Integer> {

    @Query(value = "SELECT p.* from parents p inner join children c on c.id=p.childId where TIMESTAMPDIFF(SECOND, p.ts, CURRENT_TIMESTAMP) < :interval", nativeQuery = true)
    Parent findOneByInterval(@Param("interval") long interval);
}

(Or) Spring data JPA will return the array of objects for native Query So,

public interface ParentRepository extends Repository<Parent, Integer> {

    @Query(value = "SELECT * from parents p inner join children c on c.id=p.childId where TIMESTAMPDIFF(SECOND, p.ts, CURRENT_TIMESTAMP) < :interval", nativeQuery = true)
    List<Object[]> findOneByInterval(@Param("interval") long interval);
}

Answer 2

The solution that worked for me is the following

 @Query(value = "select distinct f.* " + 
        "from form as f " + 
        "inner join form_action as fa on f.id= fa.form_id " + 
        "where  rfa.rol_id = :rol_id", nativeQuery = true)
List<Form> findFormByRolIdParamsNative(@Param("rol_id") Long rol_id);