| [eclipselink-users] criteria query translation issue (me or defect?) |
|
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_.mepdesNumber), mepdesNumberParamExp); Predicate dmrStatusListPredicate = builder.and(fromCurrentDmrStatusView.get(CurrentDmrStatusView_.newDslpCode).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 (?)))") |