0

I have two entities AAA and BBB as follows

class AAA{

  @Id private String Id;

 @OneToMany(mappedBy = "aaa", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
  @MapKey(name = "bbbType")
  @Setter
  private Map<BBBType, BBB> bbbDetails;
  
  private String status;
}

and BBB entity as

class BBB {
 @ManyToOne(cascade = CascadeType.ALL)
  @JoinColumn(name = "Id")
  @Setter
  private AAA aaa;

  private BBBType bbbType;  // BBBType is a enum of Strings

  private boolean flag;
}

Now using spring data JPA native query I need to have a query like below(using JPARepository)

@Query(
      "select a.Id from aaa a where 
           a.status = :status and a.bbbDetails['BBBType.FIRST'].flag= false")
  List<String> findAllIdsByTypeAndStatus(@Param("status") String status);

while execution it gives below exception

Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [java.util.List]: Factory method 'loadDataForPartitioner' threw exception; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [/* select a.Id from AAA a where a.status = :status and a.bbbDetails['BBBType.FIRST'].flag= false */ select aaa0_.id as col_0_0_ from aaa aaa0_ cross join bbb_details bbbde1_ where aaa0_.id=bbbde1_.id and bbbde1_.null = 'BBBType.FIRST' and aaa0_.status=? and bbbde1_.flag=false]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement ... ... .... ... ; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

Here the bbbDetails is a map of key(BBBType) value(child entity) pair. How should I query the mapped child entity based on the BBBType value in the data jpa native query?

Sel_va
  • 588
  • 5
  • 25

1 Answers1

1

Use a query like this:

select a.Id 
from aaa a 
join a.bbbDetails b on key(b) = BBBType.FIRST
where a.status = :status and b.flag = false
Christian Beikov
  • 15,141
  • 2
  • 32
  • 58