CODEWITHSUNDEEP
javasqlhibernatejpa
ThangLeQuoc
ThangLeQuoc

Reputation: 3073

Hibernate/JPA doesn't detect positional parameters

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 enter image description here

Upvotes: 0

Views: 1585

Answers (2)

Neil Stockton
Neil Stockton

Reputation: 11531

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

Upvotes: 2

Manuel Drieschmanns
Manuel Drieschmanns

Reputation: 734

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

Upvotes: 0

Related Questions