14
List<Lahetys> last = session.createQuery("from lahetys order by lahetysNro DESC LIMIT 1").list();

and in the log I got:

INFO: Hibernate: select  from order by  lahetysNro DESC LIMIT 1
WARN: SQL Error: 1064, SQLState: 42000
ERROR: You have an error in your SQL syntax; check the manual that corresponds to your       MySQL server version for the right syntax to use near 'from order by  lahetysNro DESC LIMIT 1' at line 1

What has happend to "from LAHETYS"? What is the best practice to handle that with HQL or/and with SQL?

Another problem:

Lahetys last = (Lahetys)session.createSQLQuery("select * from lahetys order by lahetysNro DESC LIMIT 1").uniqueResult();
session.getTransaction().commit();

and I get a exception:

Ljava.lang.Object; cannot be cast to Lahetys

So I can't cast an object to my Lahetys-object, weird?

Thank you! Sami

Sami
  • 2,311
  • 13
  • 46
  • 80

2 Answers2

30

Your HQL query is invalid. LIMIT is not a valid HQL clause. To do that in Hibernate, just do

Query query = session.createQuery("from lahetys order by lahetysNro DESC");
query.setMaxResults(1);
Lahetys last = (Lahetys) query.uniqueResult();
JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • Thanks for answering!I got the same exception: ---AsekorjausHelper.getLastLahetysNro()---- INFO: Hibernate: select from order by lahetysNro DESC limit ? WARN: SQL Error: 1064, SQLState: 42000 ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from order by lahetysNro DESC limit 1' at line 1 INFO: EXCEPTION OCCURED----->You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from order by lahetysNro DESC limit 1' at line 1 – Sami Oct 25 '12 at 14:23
  • 1
    See @RAS answer. HQL uses entities, mapped fields/properties and associations. Never table and column names. – JB Nizet Oct 25 '12 at 15:07
  • @JBNizet why did you use uniqueResult() with setMaxResults(1) when according to documentation uniqueResult method returns single instance – Jawad Hassan Soomro Oct 10 '19 at 16:16
  • 1
    uniqueResult() expects the query to return a single result, and throws an exception if it's not the case. setMaxResult() tells the database to limit the results to a single row, that I can then get using singleResult(). – JB Nizet Oct 10 '19 at 17:27
8

When you're using HQL, you should specify fully qualified className instead of tableName. The same way you should specify propertyName instead of columnName. Also keep in mind that both are case - sensitive.

Looking at your queries & the exception you're getting, I'm assuming that lahetys is your table name & lahetysNro is your column name.

You should use for example: If your Lahetys class is located at com folder:

List<Lahetys> last = session.createQuery("from com.Lahetys order by lahetysNro DESC LIMIT 1").list();

For your 2nd question:

Here you have used SQL instead of HQL. When you use SQL with hibernate in such a way, it always returns List<Object[]> & not List<Lahetys[]>.

RAS
  • 8,100
  • 16
  • 64
  • 86
  • 3
    wrong solution: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: limit near line 1.... Use .setMaxResults(1) – demon101 Jan 23 '15 at 15:19
  • 3
    This answer is not correct. See @demon101's comment. – Kenci Mar 26 '15 at 18:47
  • @demon101, I'm not focusing on `limit` related things over here. That is something OP has used and I'm not talking about it. I'm just explaining the exceptions OP has received and how should he fix them. Usage of `limit` is entirely a different point as well as off-the-context here. – RAS Jun 19 '15 at 11:52
  • This solution doenst work, please unmark this as the preferred answer. – Jess Oct 30 '15 at 20:22