Searching across of 2x entities JPA

Question

I have two entities of JPA, Game and GameScore which are connected. If player join game a new GameScore is made for him. But i need to get Games where is not joined player(does not have GameScore). How I can do that? I tried by NamedQueries and CriteraBulder but without result.

Game endity:

package prayForHunt.model;

import java.io.Serializable;
import java.util.Date;
import java.util.List;
import javax.persistence.*;
@Entity
@NamedQueries({
@NamedQuery(name = Game.Q_GET_ALL_GAMES, query = "SELECT c FROM Game c"),
})
  public class Game implements Serializable {

//--------------------------------------------
@Id
@GeneratedValue
private int gameId;
//--------------------------------------------      
@OneToMany(cascade = CascadeType.ALL, mappedBy = "game")
private List<GameScore> gameScore;
}

GameScore entity

package prayForHunt.model;

import java.io.Serializable;
import java.util.List;
import javax.persistence.*;
import javax.persistence.metamodel.SingularAttribute;

@Entity
@NamedQueries(
@NamedQuery(name = GameScore.Q_GET_ALL_GAMESCORE, query = "SELECT c FROM GameScore c")
)
public class GameScore implements Serializable {

@Id
@GeneratedValue
private int gameId;
@ManyToOne
protected Game game;
@ManyToOne
protected Player player;

 }

score 0 · Accepted Answer · answered Jun 10 '12 at 06:07

So what is needed as result is all such a instances of Game entity where gameScore is empty. JPQL query for fetching Games without score is (can also be used in named query):

SELECT g FROM Game g WHERE g.gameScore IS EMPTY

Equivalent criteria query is as follows:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Game> cq = cb.createQuery(Game.class);
Root<Game> member = cq.from(Game.class);
cq.select(member)
  .where(cb.isEmpty(member.<Collection>get("gameScore")));        
em.createQuery(cq).getResultList();

Searching across of 2x entities JPA

1 Answers1