Spring Data Rest Left Outer Join non-Entity POJO Null Entity Error

Question

How do i accomplish getting the this interface method to work? i am using a MySQL DB if that matters...

public interface PersonRoleRepository extends CrudRepository<PersonRole,Long>{
  //This causes null entity error from hibernate even though the SQL works outside hibernate
  @Query(value="select * from Role r left outer join Person_Role pr on r.id = pr.role_id and pr.person_id = ? order by pr.expires_date desc", nativeQuery = true)
  List<PersonRoleDto> getAllRolesAndPersonsStatusWithEachRole(int personId);


}

Here is the SQL query that returns what i want in SQL Workbench...

Select r.*, pr.*
from
Role r
left outer join person_role pr on r.id = pr.role_id and pr.person_id = ?
order by pr.expires_date desc;

Important MySQL database structure...

Table: Role
role_id bigint
name varchar
description varchar
...

Table: Person
person_id bigint
first_name varchar
last_name varchar
...

Table Person_Role_Link
person_role_id bigint
role_id bigint
person_id bigint
...
alter table person_Role_Link add constraint l1 foreign key (person_id) references Person(person_id)
alter table person_Role_Link add constraint l2 foreign key (role_id) references Role(role_id)

Here is the entity info...

@Entity
@Table(name="Role")
@EntityListeners(AuditEntityListener.class)
public class Role extends AbstractAuditEntity {
  private static final long serialVersionUID= 44543543543543454543543L;
  @id
  @GeneratedValue(strategy=GenerationType.AUTO)
  @Column(name="role_id")
  private long id;

  @NotNull
  private String fullName  
  ...

  @OneToMany(mappedBy="role",cascade=CascadeType.ALL)
  private Set<PersonRole> personRoles = new HashSet<>();

  ...
}


@Entity
@Table(name="Person_Role_Link")
@EntityListeners(AuditEntityListener.class)
class PersonRole extends AbstractAuditEntry{
  private static final long serialVersion = 54324243242432423L;

  @id
  @GeneratedValue(strategy=GenerationType.AUTO)
  @Column(name="person_role_id")
  private long id;

  @ManyToOne
  @JoinColumn(name="person_id")
  private Person person;

  @ManyToOne
  @JoinColumn(name="role_id")
  private Role role;

  ...
}

@Entity
@Table(name="Person")
@EntityListeners(AuditEntityListener.class)
public class Person extends AbstractAuditEntity{
  private ... serialVersionUID...;

  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  @Column(name="person_id")
  private Long id;
  ...
  @OneToMany(mappedBy="person", cascade=CascadeType.ALL)
  private Set<PersonRole> personRoles = new HashSet<>();
  ...
}

Just for now i made a simple interface...

public interface PersonRoleDto {
  String getFullName();
  String getDescription();
  //i want more attributes but for now i will just see if it works with the basics
}

Here is the latest HQL i tried...

public interface PersonRoleRepository extends CrudRepository<PersonRole,Long>{

  //PersistentEntity must not be null
  @Query("select r.fullName as fullName, r.description as description from Role r left join r.personRoles pr where pr.person = :person")
  List<PersonRoleDto> findAllRolesWithPersonsStatus(@Param("person") Person person);
...
}

Whether I use HQL or native SQL i get a null entity error from hibernate. Also, the SQL generated from the HQL works without error but i still get a null entity error in code and, second, the SQL generated from HQL is slightly off which makes the results off. That's why i was trying so hard to get the native SQL to work.

The relationship is used to figure out how many people are in a role and at other times what roles a person has. This is a circular relationship, i'd say. I work on an Intranet so i had to hand type everything. If there are any problems seen in my code other than with the stated native query as stated then it is most likely because i had to hand type everything and not because the code is buggy. Everything else works so far but this one thing.

All help is appreciated.

UPDATE!!!!

I think this is the answer to my problem but when i try it i still get the error: PersistentEntity must not be null!

Here is how i tried to set it up...

//Added this to the top of PersonRole entity
@SqlResultSetMapping(
  name="allRolesAndPersonsStatusWithEachRole"
  classes={
   @ConstructorResult(
     targetClass=PersonRoleStatus.class,
     columns={
       @ColumnResult(name="full_name"),
       @ColumnResult(name="description"),
       ...
     }
    )
  }
)
@NamedNativeQuery(name="PersonRole.getAllRolesAndPersonsStatusWithEachRole",
  query="Select r.*, pr.* from Role r Left Join person_role_link on r.role_id = pr.role_id and pr.person_id = :id", resultSetMapping="allRolesAndPersonsStatusWithEachRole")

Created my DTO like this...

public class RolePersonStatus {
  private String fullName;
  private String description;
  private String ...

  public RolePersonStatus(String fullName, String description, ...){
    this.fullName = fullName;
    this.description = description;
    ...
  }
}

In my repository i just have:

//No annotation because it stated that the name of the method just needed to match the native query name?!?!?
List<RolePersonStatus> findAllRolesWithPersonStatus(@Param("id" Long id);

What am i missing???????

Answer 1

Try this way:

Entities

@Entity
@Table(name = "parents")
public class Parent {

    @Id 
    @GeneratedValue(strategy = GenerationType.AUTO) 
    private Long id;

    private String name;

    @OneToMany
    private List<Child> children = new ArrayList<>();

    //...
}

@Entity
@Table(name = "children")
public class Child {

    @Id 
    @GeneratedValue(strategy = GenerationType.AUTO) 
    private Long id;

    private String name;

    @ManyToOne(optional = false)
    private Reference reference;

    @OneToMany
    private final List<Toy> toys = new ArrayList<>();

    //...
}

@Entity
@Table(name = "references")
public class Reference {

    @Id 
    @GeneratedValue(strategy = GenerationType.AUTO) 
    private Long id;

    private String description;

    //...
}

@Entity
@Table(name = "toys")
public class Toy {

    @Id 
    @GeneratedValue(strategy = GenerationType.AUTO) 
    private Long id;

    private String name;

    //...
}

DTO

public interface DemoDto {

    String getParentName();
    String getChildName();
    String getToyName();
    String getDescription();
}

Repository

public interface ParentRepo extends JpaRepository<Parent, Long> {

    @Query("select " +
                "p.name as parentName, " +
                "c.name as childName, " +
                "t.name as toyName, " +
                "r.description as description " +
            "from " +
                "Parent p " +
                "join p.children c " +
                "join c.reference r " +
                "join c.toys t " +
            "where c.id = ?1 " +
            "order by r.description desc")
    List<DemoDto> getDto(Long childId);
}

Usage

@RunWith(SpringRunner.class)
@SpringBootTest
public class ParentRepoTest {

    @Autowired
    private ParentRepo parentRepo;

    @Test
    public void getDto() throws Exception {

        List<DemoDto> dtos = parentRepo.getDto(3L);
        dtos.forEach(System.out::println);
    }
}

Result

{parentName=parent2, toyName=Toy7, childName=child3, description=Description1}
{parentName=parent2, toyName=Toy8, childName=child3, description=Description1}
{parentName=parent2, toyName=Toy9, childName=child3, description=Description1}

More info is here.

Working example.