CODEWITHSUNDEEP
playframeworkjpql
fdelsert
fdelsert

Reputation: 808

JPQL equivalent of SQL query with EXISTS and relationships

I'm using Play Framwork 1.2.4

I have created 3 Model classes with relationships :

Header <1 - n> Line - 1> LineType 

@Entity
public class Header extends Model {
   @OneToMany(mappedBy="header" , cascade=CascadeType.ALL)
   public List<Line> lines;
   ...
}

@Entity
public class Line extends Model{
   @ManyToOne
   public Header header ;

  @ManyToOne
   publicLineType lineType;
   ...
}

@Entity
public class LineType extends Model {

   public Integer code ;
   ...
}

I want to make a filter on the linetype during a header search.

How to write the equivalent of this SQL query using JPQL (or other) in Play Framework ?

 Select * from header where exists(select 1 from line,typeline where line.header=header.id and  line.lineType=linetype.id and linetype.code = 'X')

Upvotes: 0

Views: 692

Answers (1)

fdelsert
fdelsert

Reputation: 808

I answer my own question :

Query query = JPA.em().createQuery("select header from Header as header where exists(select line from Line line JOIN ligne.lineType as type where ligne.header=header and type.code='X')");
List<Header> headers = query.getResultList();

Inner join : http://docs.oracle.com/html/E24396_01/ejb3_langref.html#ejb3_langref_inner_joins

Exists : http://docs.oracle.com/html/E24396_01/ejb3_langref.html#ejb3_langref_exists

Upvotes: 1

Related Questions