CODEWITHSUNDEEP
jpa
Afreen Banu Fiza
Afreen Banu Fiza

Reputation: 21

how to write native query in jpa

I am getting the following error when I press the search button:

An Error Occurred:

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLException: Missing IN or OUT parameter at index:: 1 Error Code: 17041 Call: SELECT * FROM CRM_DAILY_SHEET WHERE to_char(reported_on,'dd-MM-yy') = :reportedOn Query: ReadAllQuery(referenceClass=CrmDailySheet sql="SELECT * FROM CRM_DAILY_SHEET WHERE to_char(reported_on,'dd-MM-yy') = :reportedOn")

Code:

public List < CrmDailySheet > searchres() throws Exception {
    SimpleDateFormat ddMMMyyFormat = new SimpleDateFormat("dd/MM/yy");
    Date d1 = searchsht.getReportedOn();
    String date_to_string = ddMMMyyFormat.format(d1);
    return crmsrvc.searchDailysht(date_to_string);
}

try {
    Query query = em.createNativeQuery("SELECT * FROM CRM_DAILY_SHEET  
 WHERE   to_char  (reported_on,'dd-MM-yy')    =      :reportedOn", CrmDailySheet.class);
    query.setParameter("reportedOn", reportedOn);
    List < CrmDailySheet > res = query.getResultList();
    return res;
} finally {
    em.close();
}

Can anyone find the solution please?

Upvotes: 1

Views: 1837

Answers (1)

David Levesque
David Levesque

Reputation: 22441

You are trying to use the JPQL parameter syntax (the ":reportedOn") with a native query. Try using ? instead and set the parameter value by position, e.g.:

Query query = em.createNativeQuery("SELECT * FROM CRM_DAILY_SHEET
    WHERE to_char (reported_on,'dd-MM-yy') = ?", CrmDailySheet.class);
query.setParameter(1, reportedOn);

Upvotes: 2

Related Questions