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

score 0 · Answer 1 · edited May 23 '17 at 11:44

0

By default, at least using Hibernate, the default fetch type is Lazy, so you need to do a join fetch instead of a join. Also, you should have select distinct. Try:

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

References: Default fetch type for one-to-one, many-to-one and one-to-many in Hibernate

edited May 23 '17 at 11:44

Community

1
1

answered May 28 '16 at 00:29

K.Nicholas

10,956
4
46
66

Thanks Nicholas. I was looking at fetch. If I simplify the query (just to see whether the join is working) to "SELECT a FROM AName a LEFT JOIN a.aNameRoleList r WHERE r.inactiveflag='N'" (no fetch) it returns records. It appears that I don't need the fetch at least not with EclipseLink. I appreciate that you looked over the code, though. It gave me more confidence in the named query and so I tried out some other things which I'll put into a separate answer. – Steve May 31 '16 at 17:45

score 0 · Accepted Answer · edited May 23 '17 at 12:24

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.

JPA NamedQuery with Join returning empty list

2 Answers2