Hibernate/JPA doesn't detect positional parameters

Question

I'm using Native Query, but Hibernate/JPA doesn't work, it always throws

javax.ejb.EJBTransactionRolledbackException: Parameter with that position 1 did not exist

Original SQL Script: (it works fine in SQL Editor)

SELECT CAST(filteredValue.measured_at AS DATE) AS DATE,date_part('hour', filteredValue.measured_at) AS HOUR, filteredValue.source_id, AVG( filteredValue.value ) AS avg_concentration, filteredValue.code
FROM ( SELECT * FROM pollutant_value
         WHERE measured_at >= '2017-06-27 11:00:00' AND measured_at <= '2017-06-28 11:00:00'
    ) filteredValue
GROUP BY filteredValue.source_id, filteredValue.code, CAST(filteredValue.measured_at AS DATE),date_part('hour', filteredValue.measured_at)
ORDER BY CAST(filteredValue.measured_at AS DATE) DESC, date_part('hour', filteredValue.measured_at) DESC, filteredValue.source_id ASC, filteredValue.code ASC

Java Entity Class

@Entity
@Table(name = "pollutant_value")
@NamedQueries({
    @NamedQuery(name = PollutantValueEntity.FIND_ALL_BY_SOURCE_ID, query = "SELECT p FROM PollutantValueEntity p WHERE p.sourceId = :sourceId"),
    @NamedQuery(name = PollutantValueEntity.COUNT_BY_CODE, query = "SELECT COUNT(p.id) FROM PollutantValueEntity p WHERE p.code = :code"),
    @NamedQuery(name = PollutantValueEntity.FIND_ALL, query = "SELECT p FROM PollutantValueEntity p")})

@NamedNativeQueries({
    @NamedNativeQuery(name = PollutantValueEntity.FIND_AVG_CONCENTRATION_BY_HOUR_IN_DAY, query = "SELECT CAST(filteredValue.measured_at AS date) AS date, date_part('hour', filteredValue.measured_at) AS hour, filteredValue.source_id, AVG(filteredValue.value) AS avg_concentration, filteredValue.code "
        + "FROM (SELECT * FROM pollutant_value" + "WHERE measured_at >= ?1 AND measured_at <= ?2) filteredValue"
        + "GROUP BY filteredValue.source_id, filteredValue.code, CAST (filteredValue.measured_at AS date), date_part('hour', filteredValue.measured_at)"
        + "ORDER BY CAST(filteredValue.measured_at AS date) DESC, date_part('hour', filteredValue.measured_at) DESC, filteredValue.source_id ASC, filteredValue.code ASC ", resultSetMapping = PollutantValueEntity.AVG_POLLUTANT_VALUE_RESULT_MAPPER) })
@SqlResultSetMapping(name = PollutantValueEntity.AVG_POLLUTANT_VALUE_RESULT_MAPPER, classes = {
    @ConstructorResult(targetClass = AvgGroupedPollutantValueByHourEntity.class, columns = {
        @ColumnResult(name = "date"), @ColumnResult(name = "hour"), @ColumnResult(name = "source_id"),
        @ColumnResult(name = "avgConcentration"), @ColumnResult(name = "code") }) })
public class PollutantValueEntity extends GenericEntity implements Serializable {

    private static final String PREFIX = "com.axonactive.iot.sniffer.entity.PollutantValueEntity";

    public static final String FIND_ALL_BY_SOURCE_ID = PREFIX + ".findBySniffer";

    public static final String FIND_ALL = PREFIX + ".findAll";

    public static final String COUNT_BY_CODE = PREFIX + ".countByCode";

    public static final String FIND_AVG_CONCENTRATION_BY_HOUR_IN_DAY = PREFIX + ".findAvgConcentrationByHourInDay";

    public static final String AVG_POLLUTANT_VALUE_RESULT_MAPPER = PREFIX + ".averagePollutantResult";

And the service call

 public List<AvgGroupedPollutantValueByHourEntity> getAvgPollutantValuesByLowerBoundAndUpperBound( Date lowerBound, Date upperBound){
    Query query = getEm().createNativeQuery(PollutantValueEntity.FIND_AVG_CONCENTRATION_BY_HOUR_IN_DAY, PollutantValueEntity.AVG_POLLUTANT_VALUE_RESULT_MAPPER);
    query.setParameter(1, "2017-06-27 11:00:00");
    query.setParameter(2, "2017-06-28 11:00:00");
    return query.getResultList();
    }

I don't know why the NamedNativeQuery PollutantValueEntity.FIND_AVG_CONCENTRATION_BY_HOUR_IN_DAY doesn't take the parameters as intended.

Did I miss something ?

Edit #1:

Because createNativeQuery method of EntityManager take in a SQL Query string, but in the entity class, I declared it with a name. So how can I still keep the name of this named native query with the annotation

@NamedNativeQuery

and create it from EntityManager em , since em doesn't have the method like createNamedNativeQuery

@OHGODSPIDERS No it's not. Please refer to 10.2.5.4.1. JPQL Positional Parameters — ThangLeQuoc
– ThangLeQuoc, Commented Jun 28, 2017 at 12:02
Your query is written in a bad way. Since you prolly used eclipse formatting, you are missing a lot of white space characters at the end of your query "lines". Also, try sticking to the named parameters and see if the error persists. — Vanja Lee
– Vanja Lee, Commented Jun 28, 2017 at 12:12

Neil Stockton · Accepted Answer · 2017-06-28 17:42:02Z

Your problem is that you defined a NAMED query, and then invoked it like this

Query query = em.createNativeQuery(PollutantValueEntity.FIND_AVG_CONCENTRATION_BY_HOUR_IN_DAY, PollutantValueEntity.AVG_POLLUTANT_VALUE_RESULT_MAPPER);
query.setParameter(1, "2017-06-27 11:00:00");
query.setParameter(2, "2017-06-28 11:00:00");
return query.getResultList();

which is basically creating a query with SQL for whatever String is "PollutantValueEntity.FIND_AVG_CONCENTRATION_BY_HOUR_IN_DAY" i.e "com.axonactive.iot.sniffer.entity.PollutantValueEntity.findAvgConcentrationByHourInDay" which is not SQL clearly. If using that method you pass the SQL in directly to the createNativeQuery method.

A NAMED query should be instantiated like this

query = em.createNamedQuery(PollutantValueEntity.FIND_AVG_CONCENTRATION_BY_HOUR_IN_DAY);
query.setParameter(1, "2017-06-27 11:00:00");
query.setParameter(2, "2017-06-28 11:00:00");
return query.getResultList();

and then you should be able to set your parameters on it, since that has the actual SQL to be invoked

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

Don't use a number after the ?. Write ?, not ?1.

@NamedNativeQuery(name = PollutantValueEntity.FIND_AVG_CONCENTRATION_BY_HOUR_IN_DAY, query = "SELECT CAST(filteredValue.measured_at AS date) AS date, date_part('hour', filteredValue.measured_at) AS hour, filteredValue.source_id, AVG(filteredValue.value) AS avg_concentration, filteredValue.code "
    + "FROM (SELECT * FROM pollutant_value" + "WHERE measured_at >= ? AND measured_at <= ?) filteredValue"
    + "GROUP BY filteredValue.source_id, filteredValue.code, CAST (filteredValue.measured_at AS date), date_part('hour', filteredValue.measured_at)"
    + "ORDER

Checkout the the Hibernate Documentation: https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html

16.1.7. Parameters

Native SQL queries support positional as well as named parameters:

Query query = sess.createSQLQuery("SELECT * FROM CATS WHERE NAME like ?").addEntity(Cat.class);
List pusList = query.setString(0, "Pus%").list();

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 28, 2017 at 12:25

Manuel Drieschmanns

7445 silver badges29 bronze badges

4 Comments

Neil Stockton Over a year ago

Actually the JPA spec uses positional (as well as named) parameters, so using the JDBC style (?) is not strictly valid.

Manuel Drieschmanns Over a year ago

At least it works in EclipseLink 2.5.0. Give it a shot.

Neil Stockton Over a year ago

... and be aware that your code is non-portable. And your link to HIbernate documentation is not for JPA, yet the question is

Manuel Drieschmanns Over a year ago

I agree. But if the query will likely never be excecuted by another JPA Implementation, it doesn't matter.

Collectives™ on Stack Overflow

Hibernate/JPA doesn't detect positional parameters

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related