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
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
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.
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.
|