Spring boot join query Validation failed for query for method public abstract in using three entity

Question

I have using spring boot. I have used to join three entity to fetch data in repository.But it shows bellow error..

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'menuRightRepo': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List...  

Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List

I have used bellow code..

my first entity named MenuNameEntity is bellow

@Entity
@Table(name = "MenuName")
public class MenuNameEntity {
 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 @Column(name = "id")
 private long id;

 private String parentId;
 private String menuName;
 private String status;
 // getter and setter
 }

my second entity is bellow

@Entity
@Table(name = "MenuChild")
public class MenuChildEntity {

 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 @Column(name = "id")
 private long id;   
 private String parentId;   
 private String childMenuName;  
 private String url;    
 private String status;
 //getter and setter
}

My third entity is bellow

@Entity
@Table(name = "MenuRight")
public class MenuRightEntity {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "id")
  private long id;

  private String companyId; 
  private String userId;    
  private String url;   
  private String status;    
  private String enqMode;   
  private String insertMode;    
  private String updateMode;    
  private String deleteMode;
 //getter and setter
 }

My MenuRightRepository is bellow

 public interface MenuRightRepo extends JpaRepository<MenuRightEntity, Long> {

  @Query("select new com.rms.info.MenuRightResponse(m.companyId, m.userId,m.enqMode,m.insertMode,m.updateMode,m.deleteMode, p.parentId,c.childId,c.childMenuName,c.url) from MenuNameEntity p,MenuChildEntity c,MenuRightEntity m where p.parentId = c.parentId  and p.status =1 and c.status =1 and c.url= m.url ")
 List<MenuRightResponse> getMenuMenuRights();   

}

I have used MenuRightResponse to bind the fetched value from repository

  package com.rms.info;
  public class MenuRightResponse {

private String companyId= "";
private String userId= "";
private String enqMode= "";
private String insertMode= "";
private String updateMode= "";
private String deleteMode= "";
private String parentId= "";
private String childId= "";
private String childMenuName= "";
private String url= "";     

  public MenuRightResponse(String companyId, String userId, String enqMode, String insertMode, String updateMode,
    String deleteMode, String parentId, String childId, String childMenuName, String url) {
super();
this.companyId = companyId;
this.userId = userId;
this.enqMode = enqMode;
this.insertMode = insertMode;
this.updateMode = updateMode;
this.deleteMode = deleteMode;
this.parentId = parentId;
this.childId = childId;
this.childMenuName = childMenuName;
this.url = url;
  }
//getter and setter
 }

when I called getMenuMenuRights() in MenuRightRepo , it shows above error. I did not use any primary & foreign key.what is the problem of the code. Please help me

select new com.rms.info.....??? This should be sql, not java code. If you want to use pure JPA, you have to define your entities. Without defining entities with joins you can use spring jdbc support with mapping capabilities — Alexander.Furer, Jun 20 '20 at 18:07
@Alexander.Furer I have tried also @Query(value = "select m.companyId, m.userId,m.enqMode,m.insertMode,m.updateMode,m.deleteMode, p.parentId,c.childId,c.childMenuName,c.url from MenuName p,MenuChild c,MenuRight m where p.parentId = c.parentId and p.status =1 and c.status =1 and c.url= m.url ", nativeQuery = true) List getMenuMenuRights(); — Enamul Haque, Jun 21 '20 at 14:13
The result set of this select does not map to your entity. You should be using jdbc template and map manually — Alexander.Furer, Jun 21 '20 at 14:59
@Alexander.Furer I need to display value from above query in hibernate how to do it using three entity? — Enamul Haque, Jun 21 '20 at 15:56
@Alexander.Furer I have followed bellow https://github.com/Java-Techie-jt/spring-data-jpa-one2many-join-example — Enamul Haque, Jun 21 '20 at 15:59

score 0 · Answer 1 · answered Jul 02 '20 at 18:25

I have solve the problem bellow like...

I have changed my MenuNameEntity to

@Entity
@Table(name = "MenuName")
public class MenuNameEntity {

 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 @Column(name = "id")
 private long id;

private String parentId;
private String menuName;
private String status;

 @OneToMany(fetch = FetchType.LAZY, mappedBy = "MenuName")  
 private Set<MenuChildEntity> menuChildEntities;

// getter and setter
}

I have changed my MenuChildEntity to

@Entity
@Table(name = "MenuChild")
public class MenuChildEntity {

 @Id
 @GeneratedValue(strategy = GenerationType.IDENTITY)
 @Column(name = "id")
 private long id; 

 @Column(name = "parentId", nullable = false,insertable = false,updatable = false) 
 private String parentId;   
 private String childMenuName;  
 private String url;    
 private String status;

 @OneToMany(fetch = FetchType.LAZY, mappedBy = "MenuChild") 
 private Set<MenuRightEntity> menuRightEntities;

 @ManyToOne
 @JoinColumn(name = "parentId")
 private MenuNameEntity MenuName;

 //getter and setter
 }

I have changed my MenuRightEntity to

@Entity
@Table(name = "MenuRight")
public class MenuRightEntity {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
 @Column(name = "id")
 private long id;

 private String companyId; 
 private String userId;  

@Column(name = "url", nullable = false,insertable = false,updatable = false)  
 private String url;   
 private String status;    
 private String enqMode;   
 private String insertMode;    
 private String updateMode;    
 private String deleteMode;        

 @ManyToOne
 @JoinColumn(name = "url")
 private MenuChildEntity MenuChild;

  //getter and setter

 }

My repository query then work fine...I just join three table unconditional one-to-many & many-to-one and joining column nullable false that works fine

Spring boot join query Validation failed for query for method public abstract in using three entity

1 Answers1