[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] criteria query translation issue (me or defect?)
|
The workaround wouldn't compile but I changed my query a bit and got it to work. Here's my working query in case someone else needs to do a similar thing:
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<CurrentDmrStatusView> criteriaQuery = builder.createQuery(CurrentDmrStatusView.class);
// create and add the root
Root<CurrentDmrStatusView> fromCurrentDmrStatusView = criteriaQuery.from(CurrentDmrStatusView.class);
ParameterExpression<String> mepdesNumberParamExp = builder.parameter(String.class, "mepdesNumber");
Expression<String> dmrStatusExp = fromCurrentDmrStatusView.get(CurrentDmrStatusView_.newDslpCode);
// Restrictions
Predicate mepdesNumberPredicate = builder.equal(fromCurrentDmrStatusView.get(CurrentDmrStatusView_.mepdesNumber), mepdesNumberParamExp);
Predicate statusListPredicate = dmrStatusExp.in(dmrStatusList);
criteriaQuery.where(builder.and(mepdesNumberPredicate, statusListPredicate));
TypedQuery<CurrentDmrStatusView> query = em.createQuery(criteriaQuery);
query.setParameter("mepdesNumber", mepdesNumber);
query.setParameter("dmrStatusList", dmrStatusList);
currentDmrsList = query.getResultList();
Thanks for your help,
Michele
-----Original Message-----
From: eclipselink-users-bounces@xxxxxxxxxxx [mailto:eclipselink-users-bounces@xxxxxxxxxxx] On Behalf Of Christopher Delahunt
Sent: Wednesday, July 25, 2012 11:36 AM
To: eclipselink-users@xxxxxxxxxxx
Subject: Re: [eclipselink-users] criteria query translation issue (me or defect?)
A workaround, or the way it is done in Eclipselink JPA tests would be:
Predicate dmrStatusListPredicate =
builder.in(fromCurrentDmrStatusView.get(CurrentDmrStatusView_.newDslpCode)).value(dmrStatusListParamExp);
Best Regards,
Chris
On 25/07/2012 11:24 AM, Andrei Ilitchev wrote:
> Please log a bug.
>
> The culprit in the generated SQL seems to be the double brackets:
> .. IN ((?,?,?))
>
> When substituted for:
> .. IN (?,?,?)
> the query succeeds.
>
> On 7/25/2012 11:21 AM, Cozart, Michele L wrote:
>> I still get the same error. Any other suggestions?
>>
>> Michele Cozart
>>
>> Senior Programmer Analyst
>>
>> State of Maine, Office of Information Technology
>>
>> michele.l.cozart@xxxxxxxxx
>>
>> (207) 287-7872
>>
>> -----Original Message-----
>> From: eclipselink-users-bounces@xxxxxxxxxxx
>> [mailto:eclipselink-users-bounces@xxxxxxxxxxx] On Behalf Of Andrei
>> Ilitchev
>> Sent: Wednesday, July 25, 2012 10:44 AM
>> To: eclipselink-users@xxxxxxxxxxx
>> Subject: Re: [eclipselink-users] criteria query translation issue (me
>> or defect?)
>>
>> Shouldn't it be:
>>
>> Predicate dmrStatusListPredicate =
>> fromCurrentDmrStatusView.get(CurrentDmrStatusView_.newDslpCode).in(dm
>> rStatusListParamExp);
>>
>>
>> instead of:
>>
>> Predicate dmrStatusListPredicate =
>> builder.and(fromCurrentDmrStatusView.get(CurrentDmrStatusView_.newDsl
>> pCode).in(dmrStatusListParamExp));
>>
>>
>> On 7/25/2012 9:52 AM, Cozart, Michele L wrote:
>>> Would someone please look at my critieria query and the generated
>>> SQL and let me know if this is a defect or if my query is wrong.
>>> There are two parameters in the query. The first is a String and the
>>> second is a List of String.
>>>
>>> I've put the generated SQL in Toad and I get the same error (missing
>>> parenthesis) until I remove one of the right parenthesis at the end
>>> of the query. Is this a defect in eclipselink, JPA 2.0 criteria
>>> query or do I have something wrong with my query. Any hints on
>>> cleaning up the criteria query code would be appreciated as well.
>>>
>>> Query code:
>>>
>>> List<CurrentDmrStatusView> currentDmrsList = new
>>> ArrayList<CurrentDmrStatusView>();
>>>
>>> EntityManager em = null;
>>>
>>> EntityTransaction txn = null;
>>>
>>> try
>>>
>>> {
>>>
>>> em = super.getEntityManager();
>>>
>>> txn = em.getTransaction();
>>>
>>> txn.begin();
>>>
>>> CriteriaBuilder builder = em.getCriteriaBuilder();
>>>
>>> CriteriaQuery<CurrentDmrStatusView> criteriaQuery =
>>> builder.createQuery(CurrentDmrStatusView.class);
>>>
>>> // create and add the root
>>>
>>> Root<CurrentDmrStatusView> fromCurrentDmrStatusView =
>>> criteriaQuery.from(CurrentDmrStatusView.class);
>>>
>>> // Create a typed expression
>>>
>>> ParameterExpression<String> mepdesNumberParamExp =
>>> builder.parameter(String.class, "mepdesNumber");
>>>
>>> ParameterExpression<List> dmrStatusListParamExp =
>>> builder.parameter(List.class,"dmrStatusList");
>>>
>>> // Restrictions
>>>
>>> Predicate mepdesNumberPredicate =
>>> builder.equal(fromCurrentDmrStatusView.get(CurrentDmrStatusView_.mep
>>> de
>>> sNumber),
>>> mepdesNumberParamExp);
>>>
>>> Predicate dmrStatusListPredicate =
>>> builder.and(fromCurrentDmrStatusView.get(CurrentDmrStatusView_.newDs
>>> lp
>>> Code).in(dmrStatusListParamExp));
>>>
>>> criteriaQuery.select(fromCurrentDmrStatusView)
>>>
>>> .where(builder.and(mepdesNumberPredicate, dmrStatusListPredicate));
>>>
>>> TypedQuery<CurrentDmrStatusView> query =
>>> em.createQuery(criteriaQuery);
>>>
>>> query.setParameter("mepdesNumber", mepdesNumber);
>>>
>>> query.setParameter("dmrStatusList", dmrStatusList);
>>>
>>> currentDmrsList = query.getResultList();
>>>
>>> }
>>>
>>> catch (Exception e)
>>>
>>> {
>>>
>>> System.out.println("inside exception: " + e.getMessage());
>>>
>>> logger.debug("Exception caught in: " + this.getClass().getName() + "
>>> Error: " + e.getMessage());
>>>
>>> }
>>>
>>> finally
>>>
>>> {
>>>
>>> if (em.isOpen() || em != null)
>>>
>>> {
>>>
>>> em.close();
>>>
>>> }
>>>
>>> }
>>>
>>> return currentDmrsList;
>>>
>>> Resulting query and error message:
>>>
>>> 2012-07-25 09:42:45.564 DEBUG CurrentDmrStatusViewDAOImpl: Exception
>>> caught in: dep.edmr.dao.impl.CurrentDmrStatusViewDAOImpl Error:
>>>
>>> Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00907:
>>> missing right parenthesis
>>>
>>> Error Code: 907
>>>
>>> Call: SELECT DMRS_ID, CURRENT_STATUS, DMRSH_ID, MEPDES_NUMBER,
>>> NEW_DSLP_CODE, OLD_DSLP_CODE, OUTFALL_NUMBER, REPORT_MONTH,
>>> REPORT_YEAR, SENT_TO_EFIS_DATE, SENT_TO_EFIS_IND, UPDATED_BY_USERID,
>>> UPDATED_DATE FROM CURRENT_DMR_STATUS_VIEW WHERE ((MEPDES_NUMBER = ?)
>>> AND (NEW_DSLP_CODE IN ((?,?,?))))
>>>
>>> bind => [ME0000159, INIT, IPREC, SREC]
>>>
>>> Query: ReadAllQuery(referenceClass=CurrentDmrStatusView sql="SELECT
>>> DMRS_ID, CURRENT_STATUS, DMRSH_ID, MEPDES_NUMBER, NEW_DSLP_CODE,
>>> OLD_DSLP_CODE, OUTFALL_NUMBER, REPORT_MONTH, REPORT_YEAR,
>>> SENT_TO_EFIS_DATE, SENT_TO_EFIS_IND, UPDATED_BY_USERID, UPDATED_DATE
>>> FROM CURRENT_DMR_STATUS_VIEW WHERE ((MEPDES_NUMBER = ?) AND
>>> (NEW_DSLP_CODE IN (?)))")
>>>
>>>
>>>
>>> _______________________________________________
>>> eclipselink-users mailing list
>>> eclipselink-users@xxxxxxxxxxx
>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>> _______________________________________________
>> eclipselink-users mailing list
>> eclipselink-users@xxxxxxxxxxx
>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>> _______________________________________________
>> eclipselink-users mailing list
>> eclipselink-users@xxxxxxxxxxx
>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
