JPQL TypedQuery - setParamer does not work

Question

I am trying to fetch an entity which has a one-to-one relation using a named Query. My "where" condition is on the relation entity. I am giving a named parameter. When I execute the query it ignores the parameter passed and giving me all the records. I tried with positional parameter, it too didn't work.

Query

@NamedQuery(
   name = "country.by.region",
   query = " select c from Country c join Region r on r.id = :regid"
)

Country Entity

public class Country {

    @Id
    @Column(name = "COUNTRY_ID")
    private String id;
    @Column(name = "COUNTRY_NAME")
    private String name;
    @OneToOne(targetEntity = Region.class, cascade = CascadeType.ALL)
    @JoinColumn(name = "REGION_ID")
    private Region region;
    // ...
}

Region Entity

public class Region {

    @Id
    @Column(name = "REGION_ID")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "regSeq")
    private int id;
    @Column(name = "REGION_NAME")
    private String name;
    // ...
}

DAO Impl

@Override
public List<Country> findBy(Region region) {
    TypedQuery<Country> query = getEntityManager().createNamedQuery("country.by.region", Country.class);
    query.setParameter("regid", region.getId());
    query.setMaxResults(30);
    return query.getResultList();
}

Answer 1

Try to correct your query in this way:

select c from Country c join c.region r where r.id = :regid

See also this section of the documentation.