I have spent mucho time trying to get some named parameters working in the “order by” clause of a dynamic sql query that I’m building. The query isn’t even HQL/JPQL. It’s native. And yet, it turns out that you cannot use bind variables, named or ordered, in an order by clause.
I’m working on a project that utilizes JPA/Hibernate as its ORM. I was writing a few named queries for an JPQL/HQL delete and was getting an SQLGrammarException. I soon found out that I couldn’t use inner joins on a delete statement, even at the SQL level. Here’s the query transformation:
Hibernate’s @Enumerated annotation seems much too brittle in the case of using it with EnumType.ORDINAL. If you’re using EnumType.STRING, I think it works beautifully. This is because the annotation can use the name of the enum to make the mapping. In the case of ORDINAL, the value of the enum is used. This presents a couple problems…
The object-relational mapping world of JPA and Hibernate has made a lot of things really nice and easy. As with any framework of this sort, however, it becomes frustrating when the pieces that are supposed to provide magic ease of development do not work as expected. The frustration is only exacerbated when the docs are skimpy.
Type conversions usually happen automatically in JPA, but possibly due to some databases incredibly not having implemented boolean fields yet (Oracle and pre-5.0 MySQL), an extra bit of help in conversion is needed.