Error while querying in JPA

Question

I have created a DataBase in MySQL and accessing the DB by using JPA in my servlet. These are the details

1. Entity Name -> RegisteredUser

2. field id , type -> Integer

so as per my query I am trying to find the record whose id is 1001.

EntityManager em = HibernateUtil.getInstance().getEntityManager();
 Query q = em
 .createQuery("SELECT record FROM RegisteredUser record WHERE record.id = 1001");
 RegisteredUser r = (RegisteredUser) q.getSingleResult(); 

But while doing so I get the following error!

 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.access0ドル(UserStatusServlet.java:188)
 at com.aces.servlets.UserStatusServlet1ドル.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

My Entity Class (It was generated by eclipse)

import java.io.Serializable;
import javax.persistence.*;
@Entity
@Table(name="registered_users")
@NamedQuery(name="RegisteredUser.findAll", query="SELECT r FROM RegisteredUser r")
public class RegisteredUser implements Serializable {
 private static final long serialVersionUID = 1L;
 @Id
 private int id;
 @Column(name="current_status")
 private byte currentStatus;
 private String password;
 private String username;
 public RegisteredUser() {
 }
 public int getId() {
 return this.id;
 }
 public void setId(int id) {
 this.id = id;
 }
 public byte getCurrentStatus() {
 return this.currentStatus;
 }
 public void setCurrentStatus(byte currentStatus) {
 this.currentStatus = currentStatus;
 }
 public String getPassword() {
 return this.password;
 }
 public void setPassword(String password) {
 this.password = password;
 }
 public String getUsername() {
 return this.username;
 }
 public void setUsername(String username) {
 this.username = username;
 }
}

Thanks in advance :)

• chrylis -cautiouslyoptimistic-: Turn on SQL output from Hibernate; we can only see a little bit of the statement MySQL doesn't like. And you really ought to be using typed queries; in this case where you actually know the ID, you can even go straight to em.find(RegisteredUser.class, Integer.valueOf(1001)).
• chrylis -cautiouslyoptimistic-: Also, can you post the whole Entity class? It looks like Hibernate's translating the SQL into something MySQL objects to, and it may be that you have a field definition that's making it complain.
• Ashwin Surana: @chrylis Okay just after trying different things for some more time I realised that the problem is not with the query but the problem seems to be with RegisteredUser r = (RegisteredUser) q.getSingleResult()
• Ashwin Surana: And also instead of doing that if I print the value of q.getResultList().size() , I don't get any error. OS that implies that the query statement is absolutely correct

Answer 1

The error is in your query:

SELECT record FROM RegisteredUser record WHERE record.id LIKE 1001;

You are confusing the sql interpreter using record for two different things. First for the column and second for the fetched row .

Try this:

SELECT * FROM RegisteredUser record WHERE record.id LIKE 1001;

Also I believe LIKE keyword words with a string, I am not sure whether your record.id is an integer or a varchar.

If you share more details about your table and what exactly you need to fetch,i can provide better inputs.

• Brian: He said it's an integer, so I guess record.id = 1001 is correct. +1
• Ashwin Surana: id is an integer. Okay I removed Like and instead of that I used'=' still I get the same error
• Juned Ahsan: @AshwinSurana have u removed the 'record' from one place as i mentioned
• Ashwin Surana: ERROR: line 1:8: unexpected token: * line 1:8: unexpected token: * at org.hibernate.hql.internal.antlr.HqlBaseParser.selectClause(HqlBaseParser.java:1256)
• DataNucleus: This is JPQL, not SQL

Answer 2

In order to get an object you should use the JPA object(...) syntax. Try this:

SELECT object(record) FROM RegisteredUser as record WHERE record.id = 1001

• chrylis -cautiouslyoptimistic-: Not necessary if the RegisteredUser class is a JPA Entity. The preferred syntax for that piece is as shown, SELECT o FROM EntityType o.