CODEWITHSUNDEEP
javahibernatepostgresqljpa
CodeSamurai777
CodeSamurai777

Reputation: 3355

JPA NamedNativeQuery syntax error with Hibernate, PostgreSQL 9

I am having a rather odd problem. I have native query which runs perfectly when executed on sql server:

SELECT  date_time, GREATEST(sum(count_up - count_down) OVER (PARTITION BY date_trunc('day', result.date_time) ORDER BY date_time),0) AS cum_amt FROM   peoplecounting.result order  BY date_time;

However, using this query as native query in JPA results in syntax error

@NamedNativeQueries({@NamedNativeQuery(
        name = "Result.getTotal",
        query = "SELECT  date_time, GREATEST(sum(count_up - count_down) OVER (PARTITION BY date_trunc('day', peoplecounting.result.date_time) ORDER BY date_time),0) AS cum_amt FROM   peoplecounting.result order  BY date_time;")})

public class Result implements Serializable {...

///

@Entity
@SqlResultSetMapping(name = "TotalResultMapping", entities = {@EntityResult(entityClass = TotalResult.class, fields = {@FieldResult(name = "total", column = "cum_amt"), @FieldResult(name = "date", column = "date_time")})})

public class TotalResult {

private static final long serialVersionUID = 1L;

public TotalResult() {
}


@Id Date date;

public Date getDate() {
    return date;
}

public void setDate(Date date) {
    this.date = date;
}

public Integer getTotal() {
    return total;
}

public void setTotal(Integer total) {
    this.total = total;
}

Integer total;

}

When calling: em.createNativeQuery("Result.getTotal","TotalResultMapping").getResultList();

I get:

INFO  [stdout] (default task-44) Hibernate: Result.getTotal
WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-44) SQL Error: 0, SQLState: 42601
ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-44) Error: Syntax error at or near "Result"

Upvotes: 0

Views: 1939

Answers (2)

CodeSamurai777
CodeSamurai777

Reputation: 3355

So the problem was that em.createNativeQuery(...) was not the correct invocation of a NamedNativeQuery in order to do that I should've invoked em.createNamedQuery(...). However, seeing that em.createNativeQuery(...) does not accept @SqlResultSetMapping it is very difficult to map the result to a custom class.

The end solution was to use

 return em.createNativeQuery("SELECT  peoplecounting.result.date_time, GREATEST(sum(peoplecounting.result.count_up - peoplecounting.result.count_down) " +
            "OVER (PARTITION BY date_trunc('day', peoplecounting.result.date_time) " +
            "ORDER BY peoplecounting.result.date_time),0) AS cum_amt" +
            " FROM   peoplecounting.result" +
            " order  BY peoplecounting.result.date_time",
            "TotalResultMapping").getResultList();

This way I can use my @SqlResultSetMapping and all is good.

Upvotes: 1

darioo
darioo

Reputation: 47203

SQLState is 42601, this is a syntax error, according to PosgreSQL's list of error codes.

Try removing the semicolon at the end of your Named native query declaration. Although you are using the semicolon when issuing your query on the database itself, the semicolon is NOT a part of the query itself.

So, this might work (not tested, but Oracle had similiar problems when you tried to add a semicolon to a plain JDBC query):

@NamedNativeQueries({@NamedNativeQuery(
    name = "Result.getTotal",
    query = "SELECT  date_time, GREATEST(sum(count_up - count_down) OVER (PARTITION BY date_trunc('day', peoplecounting.result.date_time) ORDER BY date_time),0) AS cum_amt FROM   peoplecounting.result order  BY date_time")})

Upvotes: 0

Related Questions