JPA NamedQuery with Join returning empty list

Question

I've been struggling with a namedquery for a few days. The named query has an inner join to a 2nd table. One added complexity is that the primary key on the 2nd table is a composite key. I have the two tables simplified here:

Table: aname
nameIdx  number(9),
firstName  varchar2(40),
lastName  varchar2(40),

primary key is nameIdx

Table: aname_role
nameIdx number(9), --foreign key to name table
nameType  char(2),
inactiveFlag char(1)

composite primary key is on nameIdx and nameType

I am trying to emulate the following sql query in JPQL:

select * from aname n 
   left join aname_role nr on n.nameidx=nr.nameidx
where nr.nametype='5' 
   and nr.inactiveflag='N';

This query works as expected in Oracle returning many records. In Java I have these JPA entities:

@Entity
@Table(name="ANAME")
@NamedQueries({
  @NamedQuery(name = "AName.findActiveSalesPersons", query = "SELECT a FROM AName a LEFT JOIN a.aNameRoleList r WHERE r.inactiveflag='N' and r.ANameRolePK.nametype='5' ")})
public class AName implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @NotNull
    @Column(name = "NAMEIDX")
    private Integer nameidx;
    @Column(name = "FIRSTNAME")
    private String firstname;
    @Column(name = "LASTNAME")
    private String lastname;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "aName")
    private List<ANameRole> aNameRoleList;
    //getters and setters here

and

@Entity
@Table(name = "ANAME_ROLE")
public class ANameRole implements Serializable {

    private static final long serialVersionUID = 1L;
    @EmbeddedId
    protected ANameRolePK aNameRolePK;
    @Basic(optional = false)
    @NotNull
    @Column(name = "INACTIVEFLAG")
    private Character inactiveflag;
    @JoinColumn(name = "NAMEIDX", referencedColumnName = "NAMEIDX", insertable = false, updatable = false)
    @ManyToOne(optional = false)
    private AName aName;
    //getters and setters here

There is also a primary key class ANameRolePK

@Embeddable
public class ANameRolePK implements Serializable {

    @Basic(optional = false)
    @NotNull
    @Column(name = "NAMEIDX")
    private int nameidx;
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 2)
    @Column(name = "NAMETYPE")
    private String nametype;
    //getters and setters here

With this setup, including the named query specified in the AName entity above, the following returns an empty result list:

em.createNamedQuery("AName.findActiveSalesPersons").getResultList();

Can anyone point me to what I am doing wrong in this named query?

SELECT a FROM AName a LEFT JOIN a.aNameRoleList r WHERE r.inactiveflag='N' and r.aNameRolePK.nametype='5'

Thanks,

Steve

Answer 1

After more testing, I realized the join was working, but not the "r.aNameRolePK.nametype='5'". But if I changed that to "r.aNameRolePK.nameidx=1" it works. So, it was just the nametype field, which we have defined as a char(2) in the database. The problem is with the spaces in a char field and it is discussed here: Java NamedQuery String Problem. It looks like the recommended way to resolve this is to implement an EclipseLink SessionCustomizer. For testing I changed the named query to

SELECT a 
FROM AName a LEFT JOIN a.aNameRoleList r 
WHERE r.inactiveflag='N' and trim(trailing from r.aNameRolePK.nametype)=5

This returns the expected records.