org.hibernate.exception.SQLGrammarException: could not prepare statement

Question

I have created two entities and am trying to fill one with data after loading it, to show it as a drop down list.

I got the error

org.hibernate.exception.SQLGrammarException: could not prepare statement

The Group entity that must be in the drop down list is:

@Entity
@Table(name="GROUP")
public class Group implements Serializable,Lifecycle{

    /**
     * 
     */
    private static final long serialVersionUID = 5551707547269388327L;
    @Id
    @Column(name="ID")
    @GeneratedValue
    private int id;
    @Column(name="E_NAME")
    private String eName;
    @Column(name="A_NAME")
    private String aName;

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String geteName() {
        return eName;
    }
    public void seteName(String eName) {
        this.eName = eName;
    }
    public String getaName() {
        return aName;
    }
    public void setaName(String aName) {
        this.aName = aName;
    }





    @Override
    public boolean onDelete(Session arg0) throws CallbackException {
        // TODO Auto-generated method stub
        return false;
    }
    @Override
    public void onLoad(Session session, Serializable arg1) {
        // TODO Auto-generated method stub
        Group adminGroup =new Group();
        Group sectionAdminGroup =new Group();
        Group userGroup =new Group();



        adminGroup.seteName("Admin");
        sectionAdminGroup.seteName("Section Admin");
        userGroup.seteName("User");

        adminGroup.setaName("مسشرف عام");
        sectionAdminGroup.setaName("مشرف قطاع");
        userGroup.setaName("مستخدم");

        session.save(adminGroup);
        session.save(sectionAdminGroup);
        session.save(userGroup);
    }
    @Override
    public boolean onSave(Session arg0) throws CallbackException {
        // TODO Auto-generated method stub
        return false;
    }
    @Override
    public boolean onUpdate(Session arg0) throws CallbackException {
        // TODO Auto-generated method stub
        return false;
    }

}

The stack trace is:

Caused by:

org.hibernate.exception.SQLGrammarException: could not prepare statement at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:196) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:160) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1884) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1861) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838) at org.hibernate.loader.Loader.doQuery(Loader.java:909) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354) at org.hibernate.loader.Loader.doList(Loader.java:2551) at org.hibernate.loader.Loader.doList(Loader.java:2537) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2367) at org.hibernate.loader.Loader.list(Loader.java:2362) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:126) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1678) at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:380) at org.gaca.gms.dao.GenericDAOImpl.getAll(GenericDAOImpl.java:56) at org.gaca.gms.services.GenericServiceImpl.getAllObjects(GenericServiceImpl.java:61) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) at com.sun.proxy.$Proxy39.getAllObjects(Unknown Source) at org.gaca.gms.controllers.UsersController.listUsers(UsersController.java:43) at org.gaca.gms.controllers.UsersController$$FastClassBySpringCGLIB$$29260f80.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:708) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644) at org.gaca.gms.controllers.UsersController$$EnhancerBySpringCGLIB$$de07b585.listUsers() at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:689) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852) at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837) at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487) at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:362) at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216) at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181) at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726) at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405) at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:206) at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114) at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152) at org.mortbay.jetty.Server.handle(Server.java:324) at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505) at org.mortbay.jetty.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:829) at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:514) at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211) at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380) at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395) at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488) Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT THIS_.ID AS ID1_0_0_, THIS_.A_NAME AS A_NAME2_0_0_, THIS_.E_NAME AS E_NAME3_0_0_ FROM GROUP[*] THIS_ "; expected "identifier"; SQL statement: select this_.ID as ID1_0_0_, this_.A_NAME as A_NAME2_0_0_, this_.E_NAME as E_NAME3_0_0_ from GROUP this_ [42001-178] at org.h2.message.DbException.getJdbcSQLException(DbException.java:344) at org.h2.message.DbException.getSyntaxError(DbException.java:204) at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:3024) at org.h2.command.Parser.readTableFilter(Parser.java:1185) at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1859) at org.h2.command.Parser.parseSelectSimple(Parser.java:1968) at org.h2.command.Parser.parseSelectSub(Parser.java:1853) at org.h2.command.Parser.parseSelectUnion(Parser.java:1674) at org.h2.command.Parser.parseSelect(Parser.java:1662) at org.h2.command.Parser.parsePrepared(Parser.java:434) at org.h2.command.Parser.parse(Parser.java:306) at org.h2.command.Parser.parse(Parser.java:278) at org.h2.command.Parser.prepareCommand(Parser.java:243) at org.h2.engine.Session.prepareLocal(Session.java:442) at org.h2.engine.Session.prepareCommand(Session.java:384) at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188) at org.h2.jdbc.JdbcPreparedStatement.(JdbcPreparedStatement.java:73) at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:276) at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281) at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:162) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186) ... 73 more

Caused by:

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT THIS_.ID AS ID1_0_0_, THIS_.A_NAME AS A_NAME2_0_0_, THIS_.E_NAME AS E_NAME3_0_0_ FROM GROUP[*] THIS_ "; expected "identifier"; SQL statement: select this_.ID as ID1_0_0_, this_.A_NAME as A_NAME2_0_0_, this_.E_NAME as E_NAME3_0_0_ from GROUP this_ [42001-178] at org.h2.message.DbException.getJdbcSQLException(DbException.java:344) at org.h2.message.DbException.getSyntaxError(DbException.java:204) at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:3024) at org.h2.command.Parser.readTableFilter(Parser.java:1185) at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1859) at org.h2.command.Parser.parseSelectSimple(Parser.java:1968) at org.h2.command.Parser.parseSelectSub(Parser.java:1853) at org.h2.command.Parser.parseSelectUnion(Parser.java:1674) at org.h2.command.Parser.parseSelect(Parser.java:1662) at org.h2.command.Parser.parsePrepared(Parser.java:434) at org.h2.command.Parser.parse(Parser.java:306) at org.h2.command.Parser.parse(Parser.java:278) at org.h2.command.Parser.prepareCommand(Parser.java:243) at org.h2.engine.Session.prepareLocal(Session.java:442) at org.h2.engine.Session.prepareCommand(Session.java:384) at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188) at org.h2.jdbc.JdbcPreparedStatement.(JdbcPreparedStatement.java:73) at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:276) at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281) at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:162) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:160) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1884) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1861) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838) at org.hibernate.loader.Loader.doQuery(Loader.java:909) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354) at org.hibernate.loader.Loader.doList(Loader.java:2551) at org.hibernate.loader.Loader.doList(Loader.java:2537) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2367) at org.hibernate.loader.Loader.list(Loader.java:2362) at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:126) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1678) at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:380) at org.gaca.gms.dao.GenericDAOImpl.getAll(GenericDAOImpl.java:56) at org.gaca.gms.services.GenericServiceImpl.getAllObjects(GenericServiceImpl.java:61) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207) at com.sun.proxy.$Proxy39.getAllObjects(Unknown Source) at org.gaca.gms.controllers.UsersController.listUsers(UsersController.java:43) at org.gaca.gms.controllers.UsersController$$FastClassBySpringCGLIB$$29260f80.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:708) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644) at org.gaca.gms.controllers.UsersController$$EnhancerBySpringCGLIB$$de07b585.listUsers() at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:483) at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:689) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852) at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837) at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487) at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:362) at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216) at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181) at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726) at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405) at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:206) at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114) at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152) at org.mortbay.jetty.Server.handle(Server.java:324) at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505) at org.mortbay.jetty.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:829) at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:514) at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211) at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380) at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395) at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)

This is my Spring controller:

@SuppressWarnings("unchecked")
        @RequestMapping(method=RequestMethod.GET)
          public String listUsers(Map<String, Object> map) {

            map.put("user", new User());
            map.put("usersList", usersService.getAllObjects(User.class));
            map.put("groupsList", usersService.getAllObjects(Group.class));
            return "index";
        }

This is my JSP page:

<tr>
                  <td>groups</td>
                  <td><form:select path="selectedGroup">
                  <form:options items="${groupsList}"/> 
                  </form:select></td>
                  </tr>

I am new to this. Can anybody help?

Answer 1

In case of Spring-boot with H2 Database, need to use properties,

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Error was becoz Tables name and columns converted to UpperCase with underscore.

For example

createdOn -> CREATED_ON.

Answer 2

after about 40 minutes I found that user and currentUser should not be used as well

//this cause error
@Column()
private String user; 

this causes: could not prepare statement; SQL [insert into table_progress (id, created, user, progress, updated, xdip) values (default, ?, ?, ?, ?, ?)]

//changed to this and works    
@Column()
private String userP;

Answer 3

The table name you used, GROUP, is a reserved keyword for h2 databases. Rename your table with a name like ADMIN_GROUP.

Here's an extract from the h2 documentation:

Keywords / Reserved Words

There is a list of keywords that can't be used as identifiers (table names, column names and so on), unless they are quoted (surrounded with double quotes). The list is currently:

CROSS, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, DISTINCT, EXCEPT, EXISTS, FALSE, FOR, FROM, FULL, GROUP, HAVING, INNER, INTERSECT, IS, JOIN, LIKE, LIMIT, MINUS, NATURAL, NOT, NULL, ON, ORDER, PRIMARY, ROWNUM, SELECT, SYSDATE, SYSTIME, SYSTIMESTAMP, TODAY, TRUE, UNION, UNIQUE, WHERE

Certain words of this list are keywords because they are functions that can be used without '()' for compatibility, for example CURRENT_TIMESTAMP.