java persistence native sql not accepting parameters

Question

my query is

sql = "SELECT SUM(TOTAL_BYTES_DELIVERED) / SUM(TOTAL_TIME_TAKEN_IN_DELIVERY) 
         FROM MV_MFT_TRANSFER 
        WHERE TRANSFER_INITIATION_TIME > :startDate 
          AND TRANSFER_INITIATION_TIME < :endDate"

Query query = em.createNativeQuery(sql);
query.setParameter("startDate", startDate, TemporalType.DATE);
query.setParameter("endDate", endDate, TemporalType.DATE);
query.getResultList();'

When I run this, I get an error

SQLExceptionTHrown: 
<Sep 11, 2012 12:50:46 PM PDT> <Warning> <EclipseLink> <BEA-2005000> <2012-09-11 12:50:46.893--UnitOfWork(1387841584)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.1.v20120804-d768c4f): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Missing IN or OUT parameter at index:: 1
Error Code: 17041
Call: SELECT SUM(TOTAL_BYTES_DELIVERED) / SUM(TOTAL_TIME_TAKEN_IN_DELIVERY) FROM MV_MFT_TRANSFER WHERE TRANSFER_INITIATION_TIME > :startDate AND TRANSFER_INITIATION_TIME < :endDate
Query: DataReadQuery(sql="SELECT SUM(TOTAL_BYTES_DELIVERED) / SUM(TOTAL_TIME_TAKEN_IN_DELIVERY) FROM MV_MFT_TRANSFER WHERE TRANSFER_INITIATION_TIME > :startDate AND TRANSFER_INITIATION_TIME < :endDate")> 
***SQLException in init() TRANSFER METRICS BEAN****
Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.4.1.v20120804-d768c4f): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Missing IN or OUT parameter at index:: 1

@alfasin - just because there is an `aggregate` function doesn't necessarily means it requires a `GROUP BY`. Aggregate functions "often" (not always) need an added `GROUP BY` statement. — Anjan Biswas, Sep 11 '12 at 20:20
I am relatively new to persistence. Is there some syntax error I am doing? I have a similar chunk of code which works when the query is created using em.createQuery() API. — Vinit Asher, Sep 11 '12 at 20:28
Both startDate and endDate are objects of type java.sql.Date and the table column is also of the type Date. — Vinit Asher, Sep 11 '12 at 20:31

score 21 · Accepted Answer · edited May 23 '17 at 11:53

The following solution should work:

sql = "SELECT SUM(TOTAL_BYTES_DELIVERED) / SUM(TOTAL_TIME_TAKEN_IN_DELIVERY) 
         FROM MV_MFT_TRANSFER 
        WHERE TRANSFER_INITIATION_TIME > ?
          AND TRANSFER_INITIATION_TIME < ?"

Query query = em.createNativeQuery(sql);
query.setParameter(1, startDate, TemporalType.DATE);
query.setParameter(2, endDate, TemporalType.DATE);
query.getResultList();

It seems that if you use positional parameters it will work. You cannot combine named parameters and native query. Here are some links:

http://java.boot.by/scbcd5-guide/ch08s05.html

http://www.wisegeek.com/what-are-native-queries.htm

And many more, just google for: "Only positional parameter binding may be portably used for native queries".

EDIT: More links to questions with similar issues:

How to get all the element from JDBC query

JPA/Hibernate Native Queries do not recognize Parameters

As of 2019 :myNamedParameter can be easily combined with Native Query. — Zon, Aug 22 '19 at 13:15

score 2 · Answer 2 · edited Mar 12 '15 at 14:54

This article was really helpful!

http://software-security.sans.org/developer-how-to/fix-sql-injection-in-java-persistence-api-jpa

The gist of the article is:

These are unsafe queries, don't use it! String concatenation is bad:

List results = entityManager.createQuery("Select order from Orders order where order.id = " + orderId).getResultList();
List results = entityManager.createNativeQuery("Select * from Books where author = " + author).getResultList();
int resultCode = entityManager.createNativeQuery("Delete from Cart where itemId = " + itemId).executeUpdate();

These are safe queries.

/* positional parameter in JPQL */
Query jpqlQuery = entityManager.createQuery("Select order from Orders order where order.id = ?1");
List results = jpqlQuery.setParameter(1, "123-ADB-567-QTWYTFDL").getResultList();

/* named parameter in JPQL */
Query jpqlQuery = entityManager.createQuery("Select emp from Employees emp where emp.incentive > :incentive");
List results = jpqlQuery.setParameter("incentive", new Long(10000)).getResultList();

/* named query in JPQL - Query named "myCart" being "Select c from Cart c where c.itemId = :itemId" */
Query jpqlQuery = entityManager.createNamedQuery("myCart");
List results = jpqlQuery.setParameter("itemId", "item-id-0001").getResultList();

/* Native SQL */
Query sqlQuery = entityManager.createNativeQuery("Select * from Books where author = ?", Book.class);
List results = sqlQuery.setParameter(1, "Charles Dickens").getResultList();

score 0 · Answer 3 · answered Jan 08 '17 at 10:59

0

In JPA

when you use this:

Query query = em.createNativeQuery(sql);

you must set index for set parameters. Note that your query have over than one parameter.

answered Jan 08 '17 at 10:59

Morteza Pouladi

49
1
3

java persistence native sql not accepting parameters

3 Answers3