java persistence native sql not accepting parameters

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

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();

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.