Query method with Enum and JPA Repository

Issue

I have entity Driver and enum DriverLanguage. I want to get a list of drivers who can speak choosen language by parameter in repository method.

...
@Entity
public class Driver{

private Long id;
private firstName;

@ElementCollection(targetClass = DriverLanguage.class)
@Enumerated(EnumType.STRING)
@CollectionTable(name = "driver_language",
    joinColumns = @JoinColumn(name = "driver_language_id"))
@Column(name = "driver_language")
private Set<DriverLanguage> languages = new HashSet<>();
}

public enum DriverLanguage {
  ENGLISH,
  GERMAN
}

Now I'm using @Query method.

My first approach:

@Repository
public interface DriverRepository extends JpaRepository<Driver, Long> {
  @Query("SELECT d "
      + "FROM Driver d "
      + "JOIN DriverLanguage dl on d.id = dl.driver_language_id "
      + "WHERE dl.driver_language = ?1")
    List<Driver> findByLanguage(String language);
}
ERROR: Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Path expected for join!

Then I read that maybe I shouldn't use JOIN so I tried this:

@Repository
public interface DriverRepository extends JpaRepository<Driver, Long> {
  @Query("SELECT d "
      + "FROM Driver d, DriverLanguage dl "
      + "WHERE d.id = dl.driver_language_id AND dl.driver_language = ?1")
    List<Driver> findByLanguage(String language);
}
ERROR: Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: DriverLanguage is not mapped

What I'm missing here?

Solution

The straightahead JPQL Query could be

@Repository
public interface DriverRepository extends JpaRepository<Driver, Long> {

    @Query("SELECT d "
      + "FROM Driver d "
      + "WHERE ?1 MEMBER OF d.languages")
    List<Driver> findByLanguage(DriverLanguage language);
}

The error messaegs are telling you:

• the path is d.languages(the attribute name)
• DriverLanguage is not an entity

Answered By - Turo