[eclipselink-users] Query using ExpressionBuilder, JPQL or NamedQuery do

["Christian (VuuRWerK) Seifert" <vuurwerk.christian@xxxxxxxxxxxxxx>]

Hi list,

I tried the entire day to query a database using ExpressionBuilder,
JPQL or NamedQueries. Nothing of these returns any result. While when
I try to query (using the generated sql statement from eclipselink!)
immediate against the database, I got the expected result ... Could it
be a problem that the Entity is an association entity of a ManyToMany
relation? The Entity is named UserRoleAssociation, link User and Roles
to a m:n and provide some additional properties(columns).
I want to search this Entity (the underlaying table) for a username.

My entities (truncated):
<snip>

// The User entity
@Entity
@Table(name = "USERS")
public class User extends AbstractActiveRecord<User> {
  // ...

  @OneToMany(mappedBy = "user", cascade = {
    CascadeType.PERSIST
  })
  private List<UserRoleAssociation> _roles = new
ArrayList<UserRoleAssociation>();

  // ...
}

// The role entity
@Entity
@Table(name = "ROLES")
public class Role extends AbstractActiveRecord<Role> {
  // ...

   @OneToMany(mappedBy = "role", cascade = {
    CascadeType.PERSIST
  })
  private List<UserRoleAssociation> _users = new
ArrayList<UserRoleAssociation>();

  // ...
}

// The UserRoleAssociation entity
@Entity
@Table(name = "USERS_ROLES")
@IdClass(UserRoleAssociationId.class)
@NamedQueries({
  @NamedQuery(name = "UserRoleAssociation.findFirstByUsername", query
= "SELECT ura FROM UserRoleAssociation ura WHERE ura._username =
:username", hints = {
    @QueryHint(name = QueryHints.CACHE_USAGE, value = CacheUsage.NoCache)
  })
})
public class UserRoleAssociation extends
    AbstractActiveRecord<UserRoleAssociation> {
  @Id
  @Column(name = "USER_ID")
  private long _userId;

  @Id
  @Column(name = "ROLE_ID")
  private long _roleId;

  // ...

  @ManyToOne(cascade = {
    CascadeType.PERSIST
  })
  @PrimaryKeyJoinColumn(name = "FK_USER_ID", referencedColumnName = "USER_ID")
  private User user;

  @ManyToOne(cascade = {
    CascadeType.PERSIST
  })
  @PrimaryKeyJoinColumn(name = "FK_ROLE_ID", referencedColumnName = "ROLE_ID")
  private Role role;

  // ...
}

</snap>

The simple SQL could be: SELECT * FROM USERS_ROLES WHERE username =
'Administrator';

My try using the ExpressionBuilder:
<snip>

ExpressionBuilder builder = new ExpressionBuilder();
Query qry = getJPAEntityManager().createQuery(builder.get("_username").equal(builder.getParameter("searchName")),
UserRoleAssociation.class);
qry.setParameter("searchName", "Administrator");

</snap>

My try using JPQL:
<snip>

Query qry = getJPAEntityManager().createQuery("Select ura from
UserRoleAssociation ura WHERE ura._username = ?1");
qry.setParameter(1, "Administrator");

</snap>

My try using NamedQuery (I tried both, Annotation and orm.xml):
<snip>

@NamedQueries({
  @NamedQuery(name = "UserRoleAssociation.findFirstByUsername", query
= "SELECT ura FROM UserRoleAssociation ura WHERE ura._username =
:username", hints = {
    @QueryHint(name = QueryHints.CACHE_USAGE, value = CacheUsage.NoCache)
  })
})
// using the named query:
Query qry = getEntityManager().createNamedQuery("UserRoleAssociation.findFirstByUsername");
qry.setParameter("username", "Administrator");

</snap>

The logged statement from EL: "SELECT [truncated] FROM USERS_ROLES
WHERE (USERNAME = ?) bind => [Administrator] --
ServerSession(16934810) -- Connection(25132022)"

All of these return either with "qry.getResultList();" an empty List
or while "qry.getSingleResult();" throws a NoResultException.
If I use the statement which is logged bei EL and just replace the
placeholder (?) with my searched name I get the expected result: 1 row
I have no clue why all of these I tried doesn't work :( Hope someone
can help me.

-- Christian