Spring "Left Join Fetch" data query returning null
My request looks like this:
@Query("SELECT p FROM Pilot p LEFT JOIN FETCH p.playerShips WHERE p.nickname = (:nickname)")
So far so good. I am getting a Pilot instance even when playerShips is empty.
Now I only want to get inactive ships, so I modified my query to look like this:
@Query("SELECT p FROM Pilot p LEFT JOIN FETCH p.playerShips s WHERE p.nickname = (:nickname) AND s.active = false")
and I am getting null as a pilot, so it clearly doesn't work.
I would be glad if someone could explain to me how to create a JOIN FETCH query with a WHERE clause applicable to the children. Thanks in advance.
source to share
After a lot of research, I decided to implement a custom repository to use Hibernate Filters and now it works.
PilotRepository:
@Repository
public interface PilotRepository extends JpaRepository<Pilot, Long>, PilotRepositoryCustom{
/*spring data methods here*/
}
PilotRepositoryCustom:
public interface PilotRepositoryCustom {
public Pilot findByNicknameFetchInactiveShips(String nickname);
}
PilotRepositoryImpl:
public class PilotRepositoryImpl implements PilotRepositoryCustom{
@PersistenceContext
EntityManager entityManager;
@Override
public Pilot findByNicknameFetchInactiveShips(String nickname) {
Session session = entityManager.unwrap(Session.class);
session.enableFilter("active").setParameter("active", false);
Query query = entityManager.createQuery(
"SELECT p FROM Pilot p " +
"LEFT JOIN FETCH p.playerShips ps " +
"LEFT JOIN FETCH ps.ship s " +
"WHERE p.nickname = (:nickname)");
query.setParameter("nickname", nickname);
return (Pilot)query.getSingleResult();
}
}
What is this about spring data. Now filter config:
Experimental entity:
@OneToMany(fetch = FetchType.LAZY, mappedBy = "pilot")
@Filter(name="active", condition = "active = :active")
private List<PlayerShip> playerShips;
PlayerShip object:
@Entity
@Table(name="player_ship")
@FilterDef(name="active", parameters=@ParamDef(name="active",type="java.lang.Boolean"))
public class PlayerShip {
/*...*/
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "pilot_id")
private Pilot pilot;
/*...*/
}
IMPORTANT:
If you are using Boolean in @ParamDef, be sure to enter java.lang.Boolean instead of Boolean . I spent over one hour trying to find out why I keep getting the parameter not found / not defined error .
source to share