Skip to main content

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
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(dmrStatusListParamExp);


instead of:

Predicate dmrStatusListPredicate =
builder.and(fromCurrentDmrStatusView.get(CurrentDmrStatusView_.newDslpCode).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_.mepde
sNumber),
mepdesNumberParamExp);

Predicate dmrStatusListPredicate =
builder.and(fromCurrentDmrStatusView.get(CurrentDmrStatusView_.newDslp
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


Back to the top