PostgreSQL and hibernate - Function rownumber() doesn't exist

Question

I am currently trying to use Hibernate generated functions for a PostgreSQL Database. It all works correctly except for when I use the functions setFirstResult() and setMaxResults() on my hibernate queries.

Here is a sample of my Java code :

String queryString = "select h from History h " ;
Query onePageQuery = getEntityManager().createQuery(queryString)
                .setFirstResult(rowMin).setMaxResults(PAGE_SIZE);
onePageQuery.getResultList();

The generated query is the following :

select
    * 
from
( select
    rownumber() over(
order by
    history0_.DTMTC desc) as rownumber_,
    history0_.ID as ID2_,
    history0_.CCOUL as CCOUL2_,
    history0_.CDIAM as CDIAM2_,
    history0_.CODAV as CODAV2_,
    history0_.COOPVM as COOPVM2_,
    history0_.COOPVT as COOPVT2_,
    history0_.CPOTA as CPOTA2_,
    history0_.DTMTC as DTMTC2_,
    history0_.TYCOUP as TYCOUP2_ 
from
    F23VCM2D history0_ 
order by
    history0_.DTMTC desc ) as temp_ 
where
    rownumber_ <= ?

Before I used a PostgreSQL database, I was using a DB2 Database. I have a jpa.xml file in which I declare that I am using a POSTGRESQL database as follows :

<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
    <property name="database" value="POSTGRESQL" />
    <!-- <property name="database" value="DB2" /> -->
  </bean>
</property>

I think that maybe the generated query is not adapted to PostgreSQL, because the error I have is : (roughly translated from French)

Caused by: org.postgresql.util.PSQLException: ERROR: the function rownumber() Doesn't exist
  Hint : There's no function corresponding to the given name or the arguments types.
You need to add explicit type conversion.

My questions are :

1) Is the generated query the one that should be generated ? I doubt it cause I tried to send it via SQL Developper and it failed.

2) Is there something that is obvious I didn't do with my hibernate set up?

score 2 · Accepted Answer · 2018-11-06 09:37:13Z

2

Modify jpaVendorAdapter like this :

<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
     <property name="databasePlatform" value="org.hibernate.dialect.PostgreSQL95Dialect"/>
  </bean>
</property>

in order to correctly set the hibernate dialect.

edited Nov 6, 2018 at 9:37

answered Nov 6, 2018 at 9:27

user10527814

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

That should be PostgreSQL95Dialect not the outdated 8.2 version

user10527814 Over a year ago

@a_horse_with_no_name Whatever, the idea is to set the dialect.

Licia · Accepted Answer · 2018-11-06 16:32:00Z

The problem was on my side : there were two jpa.xml files. I didn't know this because it's a legacy project where I'm trying to change only the database connection. Hence, I was trying to modify the wrong jpa.xml file so there were no impact on the generated queries.

Once I had removed the "DB2" line :

<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
    <property name="database" value="DB2" />
  </bean>
</property>

and replaced it with the "POSTGRE" line, in the right file, it worked and the query was generated successfully. So the following version of the jpaVendorAdapter did it for me :

<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
    <property name="database" value="POSTGRESQL" />
  </bean>
</property>

Thank you for the other input, it helped me find the answer. There didn't seem to need to add the dialect part, so I didn't add it in the end.

Collectives™ on Stack Overflow

PostgreSQL and hibernate - Function rownumber() doesn't exist

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related