getSingleResult() in JPA thorws exceptions

Question

I was querying using JPA, and when I used

query.getSingleResult();

It was throwing exceptions. I am well aware that if more than one record matching the parameters the method throws an exception, but in my case the parameter was a primary key , so that implies the result would have been unique. But instead of the above method , when i used

query.getResultList();

I had no such issues. I am confused. Similarly when I used the method

query.setMaxResults(some value);

and then tried to get the result list, My program throws hell lot of errors..!

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: 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 '2 registered0_.id as id0_, registered0_.current_status as current2_0_, registere' at line 1
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1360)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1288)
    at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:313)
    at com.aces.servlets.UserStatusServlet.getStatus(UserStatusServlet.java:193)
    at com.aces.servlets.UserStatusServlet.access$0(UserStatusServlet.java:188)
    at com.aces.servlets.UserStatusServlet$1.onComplete(UserStatusServlet.java:50)
    at org.apache.catalina.core.AsyncListenerWrapper.fireOnComplete(AsyncListenerWrapper.java:40)
    at org.apache.catalina.core.AsyncContextImpl.fireOnComplete(AsyncContextImpl.java:119)
    at org.apache.coyote.AsyncStateMachine.asyncPostProcess(AsyncStateMachine.java:190)
    at org.apache.coyote.AbstractProcessor.asyncPostProcess(AbstractProcessor.java:116)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:593)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
Caused by: org.hibernate.exception.SQLGrammarException: 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 '2 registered0_.id as id0_, registered0_.current_status as current2_0_, registere' at line 1
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:83)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    at com.sun.proxy.$Proxy54.executeQuery(Unknown Source)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1962)
    at org.hibernate.loader.Loader.doQuery(Loader.java:829)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
    at org.hibernate.loader.Loader.doList(Loader.java:2447)
    at org.hibernate.loader.Loader.doList(Loader.java:2433)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2263)
    at org.hibernate.loader.Loader.list(Loader.java:2258)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1161)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:280)
    ... 12 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '2 registered0_.id as id0_, registered0_.current_status as current2_0_, registere' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2293)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
    ... 27 more

You should provide a [SSCCE](http://www.sscce.org) for better help, you even don't post your error or stacktrace.., you should provide some code for better help. — nachokk, Aug 21 '13 at 15:07
@nachokk All my stack trace and error are posted in my previous question. Just click on the link at the end of my question. — Ashwin Surana, Aug 21 '13 at 15:09

score 2 · Accepted Answer · edited May 23 '17 at 12:09

2

JPA getSingleResult() or null

http://sysout.be/2011/03/09/why-you-should-never-use-getsingleresult-in-jpa/

http://www.objectdb.com/java/jpa/query/execute

edited May 23 '17 at 12:09

Community

1
1

answered Aug 21 '13 at 15:21

Prabhakaran Ramaswamy

25,706
10
57
64

score 0 · Answer 2 · answered Aug 21 '13 at 15:17

getSingleResult() throws NoResultException if query returns no results.
This behaviour makes getSingleResult() unsuitable for situations when you expect your query to return zero or one results. You need to use getResultList() in this case.
Your problem with setMaxResults() seems to be caused by incorrect SQL dialect setting

getSingleResult() in JPA thorws exceptions

2 Answers2