Ok, I have tried to change it to a JPQL query and I am now getting an
Exception -
The code looks like this
final Query query = this.em.createQuery("SELECT DISTINCT(e) FROM
CpSecured e WHERE e.cpRoleSecureds.id.roleCode IN :ROLES");
query.setParameter("ROLES", roles);
return query.getResultList();
My entities look like -
@Entity
@Table(name = "CP_SECURED")
public class CpSecured implements java.io.Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "FEATURE_ID", unique = true, nullable = false,
precision = 11, scale = 0)
private Long featureId;
@Column(name = "FEATURE_NAME", nullable = false)
private String featureName;
....
@OneToMany(cascade = CascadeType.ALL, mappedBy = "cpSecured")
@PrivateOwned
private Set<CpRoleSecured> cpRoleSecureds = new
HashSet<CpRoleSecured>();
...
@Entity
@Table(name = "CP_ROLE_SECURED")
public class CpRoleSecured implements java.io.Serializable {
@ManyToOne()
@JoinColumn(name = "FEATURE_ID", updatable = false, insertable = false)
private CpSecured cpSecured;
@EmbeddedId
@AttributeOverrides( {
@AttributeOverride(name = "featureId", column = @Column(name
= "FEATURE_ID", nullable = false, precision = 11, scale = 0)),
@AttributeOverride(name = "roleCode", column = @Column(name
= "ROLE_CODE", nullable = false, length = 10)) })
private CpRoleSecuredId id;
....
And it throws this Exception -
Error compiling the query [SELECT DISTINCT(e) FROM CpSecured e WHERE
e.cpRoleSecureds.id.roleCode IN :ROLES], line 1, column 44: invalid
navigation expression [e.cpRoleSecureds.id], cannot navigate collection
valued association field [cpRoleSecureds].; nested exception is
java.lang.IllegalArgumentException: An exception occurred while creating
a query in EntityManager:
thanks
------------------------------------------------------------------------
*From:* eclipselink-users-bounces@xxxxxxxxxxx
[mailto:eclipselink-users-bounces@xxxxxxxxxxx] *On Behalf Of
*Christopher Delahunt
*Sent:* Monday, August 30, 2010 2:28 PM
*To:* EclipseLink User Discussions
*Subject:* Re: [eclipselink-users] NamedNativeQuery using in
Hi Keven,
Named parameters are not defined in the JPA spec for native queries.
Even if they were, EclipseLink will not break up native query parameter
collections into its components. You need to use JPQL for your query,
or add a parameter for each list entry.
Best Regards,
Chris
On 30/08/2010 2:24 PM, Kevin Haskett wrote:
Ok I changed it to use a named parameter but I am getting an error now -
[8/30/10 13:20:53:544 CDT] 0000003b SystemOut O [EL Fine]:
2010-08-30
13:20:53.544--ClientSession(1354453179)--Connection(1397445451)--Thread(Thread[WebContainer
: 0,5,main])--select distinct s.FEATURE_ID, FEATURE_NAME,
FEATURE_DESC, FEATURE_TYPE, LICENSE_CHECK, PRIVILEGE_LEVEL from
cp_secured s, cp_role_secured rs where s.feature_id = rs.feature_id
and rs.role_code IN :ROLES
[8/30/10 13:20:53:669 CDT] 0000003b SystemOut O [EL Fine]:
2010-08-30
13:20:53.669--ClientSession(1354453179)--Thread(Thread[WebContainer :
0,5,main])--VALUES(1)
[8/30/10 13:20:53:747 CDT] 0000003b SystemOut O [EL Warning]:
2010-08-30
13:20:53.747--UnitOfWork(1351307403)--Thread(Thread[WebContainer :
0,5,main])--Exception [EclipseLink-4002] (Eclipse Persistence Services
- 2.2.0.v20100731-r7961):
org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: [SQL0312] Variable ROLES
not defined or not usable.
Error Code: -312
Call: select distinct s.FEATURE_ID, FEATURE_NAME, FEATURE_DESC,
FEATURE_TYPE, LICENSE_CHECK, PRIVILEGE_LEVEL from cp_secured s,
cp_role_secured rs where s.feature_id = rs.feature_id and rs.role_code
IN :ROLES
Query: ReadAllQuery(name="findCpSecuredFeatureListRole"
referenceClass=CpSecuredFeature sql="select distinct s.FEATURE_ID,
FEATURE_NAME, FEATURE_DESC, FEATURE_TYPE, LICENSE_CHECK,
PRIVILEGE_LEVEL from cp_secured s, cp_role_secured rs where
s.feature_id = rs.feature_id and rs.role_code IN :ROLES ")
Here's the code, roles is a List<String>
*final* Query query =
*this*.em.createNamedQuery("findCpSecuredFeatureListRole");
query.setParameter("ROLES", roles);
Thanks
Kevin
------------------------------------------------------------------------
*From:* eclipselink-users-bounces@xxxxxxxxxxx
[mailto:eclipselink-users-bounces@xxxxxxxxxxx] *On Behalf Of *douglas
clarke
*Sent:* Monday, August 30, 2010 12:17 PM
*To:* eclipselink-users@xxxxxxxxxxx
*Subject:* Re: [eclipselink-users] NamedNativeQuery using in
Kevin,
Making some assumptions about your mapped model I am thinking the
following may meet your needs.
SELECT s FROM CpSecuredFeature s JOIN s.roles r WHERE r.code IN :CODES
In the Employee demo where Employee has a 1:M to PhoneNumber I can
query on the types of the phones using a variable length collection
with an IN as:
Query query = em.createQuery("SELECT DISTINCT(e) FROM Employee e JOIN
e.phoneNumbers p WHERE p.type IN :TYPES");
Collection<String> types = new ArrayList<String>();
types.add("WORK");
types.add("CELL");
query.setParameter("TYPES", types);
List<Employee> emps = query.getResultList();
Doug
On 30/08/2010 12:17 PM, Kevin Haskett wrote:
I am using the IN Keyword as the list will vary. So I am not sure
how I could create a query that would dynamically have place holders
for the number of values coming in.
I am using a tool called Squirrell to run the query outside
of Eclipselink, so it looks like .... rs.role_code in ('GA', 'GRN',
'PA', 'SA') when I run it there.
Not sure how to convert this using JPQL easily.
------------------------------------------------------------------------
*From:* eclipselink-users-bounces@xxxxxxxxxxx
[mailto:eclipselink-users-bounces@xxxxxxxxxxx] *On Behalf Of
*Christopher Delahunt
*Sent:* Monday, August 30, 2010 11:07 AM
*To:* EclipseLink User Discussions
*Subject:* Re: [eclipselink-users] NamedNativeQuery using in
Hello Kevin,
Native SQL gets executed exactly as you have defined it, with each
parameter passed in passed directly to the driver as a single
parameter. Drivers treat the parameter as a single serialized
object, which will not match to your role_code field.
Try using a single parameter for each component in the list. How are
you running this outside of EclipseLink, and does it work if you use
JPQL instead of a native query?
Best Regards,
Chris
On 30/08/2010 11:50 AM, Kevin Haskett wrote:
I have a NamedNativeQuery that is using the "in" SQL statement -
@NamedNativeQuery(name = "findCpSecuredFeatureListRole", query =
"select distinct s.FEATURE_ID, FEATURE_NAME, FEATURE_DESC,
FEATURE_TYPE, LICENSE_CHECK, PRIVILEGE_LEVEL "
+ "from cp_secured s, cp_role_secured rs where s.feature_id =
rs.feature_id and rs.role_code in (?) ", resultClass =
com.gmrc.jpa.domain.CpSecuredFeature.*class*)
in my code I call -
*final* Query query =
*this*.em.createNamedQuery("findCpSecuredFeatureListRole");
query.setParameter(1, roles);
*return* _query.getResultList()_;
where roles is a string representing - 'GA', 'PA', 'GRN'
If I execute this outside of Eclipselink it seems to run fine. But
when it runs in EclipseLink I get nothing returned in my results. I
tried changing this to a List<String> with each as a seperate entity
in the List but that gave me a Data Type mismatch.
Thanks,
Kevin
This message (including any attachments) is intended only for the
use of the individual or entity to which it is addressed and may
contain information that is non-public, proprietary, privileged,
confidential, and exempt from disclosure under applicable law or may
constitute as attorney work product. If you are not the intended
recipient, you are hereby notified that any use, dissemination,
distribution, or copying of this communication is strictly
prohibited. If you have received this communication in error, notify
us immediately by telephone and (i) destroy this message if a
facsimile or (ii) delete this message immediately if this is an
electronic communication. Thank you.
------------------------------------------------------------------------
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
This message (including any attachments) is intended only for the use
of the individual or entity to which it is addressed and may contain
information that is non-public, proprietary, privileged,
confidential, and exempt from disclosure under applicable law or may
constitute as attorney work product. If you are not the intended
recipient, you are hereby notified that any use, dissemination,
distribution, or copying of this communication is strictly
prohibited. If you have received this communication in error, notify
us immediately by telephone and (i) destroy this message if a
facsimile or (ii) delete this message immediately if this is an
electronic communication. Thank you.
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
This message (including any attachments) is intended only for the use
of the individual or entity to which it is addressed and may contain
information that is non-public, proprietary, privileged, confidential,
and exempt from disclosure under applicable law or may constitute as
attorney work product. If you are not the intended recipient, you are
hereby notified that any use, dissemination, distribution, or copying
of this communication is strictly prohibited. If you have received
this communication in error, notify us immediately by telephone and
(i) destroy this message if a facsimile or (ii) delete this message
immediately if this is an electronic communication. Thank you.
------------------------------------------------------------------------
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
This message (including any attachments) is intended only for the use of
the individual or entity to which it is addressed and may contain
information that is non-public, proprietary, privileged, confidential,
and exempt from disclosure under applicable law or may constitute as
attorney work product. If you are not the intended recipient, you are
hereby notified that any use, dissemination, distribution, or copying of
this communication is strictly prohibited. If you have received this
communication in error, notify us immediately by telephone and (i)
destroy this message if a facsimile or (ii) delete this message
immediately if this is an electronic communication. Thank you.
------------------------------------------------------------------------
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
