Hi all,
Recently I'm migrating one of my application from mysql to postgresql. I'm using hibernate as ORM.
But the migration is not quite easy. There are some Named HQL that run perfectly with mysql but not with postgresql.
Below is an example of such and query -
Code:
select NEW com.abc.dd.dto.PublishedAtomDTO(d.id, d.externalRef, r, d.createdTime, pa.publishPoint.id, pa.publishPoint.name, pa.createdTime, d.provider.wapPushUrl, d.provider.webUrl) from DigitalItem d LEFT JOIN d.reporter r, PublishAction pa where (:state is null or pa.digitalItemVariant.state = :state) and pa.digitalItemVariant.digitalItem.id = d.id and (:unrestrictedOnly = false or d.restricted = false) and (:publishPointId <= 0 or pa.publishPoint.id = :publishPointId) and (:publishPointName is null or pa.publishPoint.name = :publishPointName) and d.id = d.diId and d.provider.name = :provider and (:groupName is null or d.diGroup.name = :groupName) GROUP BY d, pa.publishPoint ORDER BY pa.createdTime desc
This query work perfectly in myql but not in postgresql. The reason is simple. Mysql allow not to use all the select column in group by section.
But Postgresql need to put all the select column in the group by section.
Another observation is when I'm comparing a string with null value then postgres give me a type casting exception.
So my question is - Who actually convert the HQL to the corresponding SQL?
What is the role of JDBC drive here with hibernate?
Why Hibernate is not capable to generate the exact equivalent sql query for postgresql for the above query?
Login
FAQ
Search