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