I have tried to add some Order to my Criteria like this.



And test on my System using a MsAccess database with HSQLDialect And i get



So i tried to show the query, it reads like this



the thing is, i used the @OrderBy annotation on my collection before, which produce almost the same query, and this works :


But with the attributes in my Entity, it causes exception.

By the way, if i try to test the same programm with MYSQLServer, it works perfectly.

Any idea why or how could i avoid this ?

Many thanks !

Why are you using the HSQLDialect with MsAccess? If you want to use MsAccess, you need to use a 3rd party Dialect that's built for Access since Hibernate does not support MsAccess.



There is no MYSQLServer database. There is MySQL and SQL Server though. Either way, for MySQL you need to use a MySQL Dialect while for SQL Server you need to use a SQL Server Dialect.

Because i use ucanaccess as driver http://ucanaccess.sourceforge.net/site.html to connect with the MS Accesss database, and this middleware uses HSQLDialect.

There is no Hibernate supported Dialect for MsAccess, so i have no other choice.

Then you should have mentioned this instead of MsAccess.

The reason why you got an exception is easy to understand if you know the execution order of SQL clauses. ORDER BY is executed after SELECT so it can only reference selected columns. Therefore, you must make sure you always select that column you use for ordering the result set.