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