[FIXED] javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query with heroku PostgreSQL

Issue

I am trying to use database in my app but keep getting this error. Its working fine on local machine when I uploaded it to heroku it keep giving this error. I spent my all day to get any solution but no success. So if anyone can help me with this would be really helpful.

You can also find it on github. : Github

Error

HTTP Status 500 - Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query

type Exception report

message Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query

description The server encountered an internal error that prevented it from fulfilling this request.

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:965)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:844)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:829)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
root cause

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
    org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1389)
    org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1317)
    org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:255)
    org.hibernate.ejb.criteria.CriteriaQueryCompiler$3.getResultList(CriteriaQueryCompiler.java:253)
    com.spring.app.service.impl.UserServiceImpl.listUser(UserServiceImpl.java:25)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:606)
    org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
    org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
    org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
    com.sun.proxy.$Proxy23.listUser(Unknown Source)
    com.spring.app.HomeController.home(HomeController.java:34)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:606)
    org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:953)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:844)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:829)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
root cause

org.hibernate.exception.SQLGrammarException: could not execute query
    org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
    org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    org.hibernate.loader.Loader.doList(Loader.java:2545)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    org.hibernate.loader.Loader.list(Loader.java:2271)
    org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:459)
    org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:365)
    org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268)
    org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
    org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:246)
    org.hibernate.ejb.criteria.CriteriaQueryCompiler$3.getResultList(CriteriaQueryCompiler.java:253)
    com.spring.app.service.impl.UserServiceImpl.listUser(UserServiceImpl.java:25)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:606)
    org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
    org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
    org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
    com.sun.proxy.$Proxy23.listUser(Unknown Source)
    com.spring.app.HomeController.home(HomeController.java:34)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:606)
    org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:953)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:844)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:829)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
root cause

org.postgresql.util.PSQLException: ERROR: column user0_.id does not exist
  Position: 8
    org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
    org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
    org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
    org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
    org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
    org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
    org.hibernate.loader.Loader.doQuery(Loader.java:802)
    org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
    org.hibernate.loader.Loader.doList(Loader.java:2542)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    org.hibernate.loader.Loader.list(Loader.java:2271)
    org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:459)
    org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:365)
    org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268)
    org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
    org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:246)
    org.hibernate.ejb.criteria.CriteriaQueryCompiler$3.getResultList(CriteriaQueryCompiler.java:253)
    com.spring.app.service.impl.UserServiceImpl.listUser(UserServiceImpl.java:25)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:606)
    org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
    org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
    org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
    com.sun.proxy.$Proxy23.listUser(Unknown Source)
    com.spring.app.HomeController.home(HomeController.java:34)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:606)
    org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:953)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:844)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:829)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
note The full stack trace of the root cause is available in the Apache Tomcat/7.0.34 logs.
Apache Tomcat/7.0.34

ServiceImpl Class

@Service
public class UserServiceImpl implements UserService {

    @PersistenceContext
    private EntityManager em;

    @Transactional
    public List<User> listUser() {
        CriteriaQuery<User> c = em.getCriteriaBuilder().createQuery(User.class);
        c.from(User.class);
        return em.createQuery(c).getResultList();
    }

    @Transactional
    public void addUser(User user) {
        em.persist(user);
    }

    @Transactional
    public void deleteUser(int id) {
        User user = em.find(User.class, id);
        if (null != user) {
            em.remove(user);
        }
    }
}

Controller class

@Controller
public class HomeController {

    private static final Logger logger = LoggerFactory.getLogger(HomeController.class);

    @Autowired private UserService userService;

    @RequestMapping(value = "/", method = RequestMethod.GET)
    public String home(@ModelAttribute("user") User user, Map<String, Object> map) {
        logger.info("Welcome home! ");
        map.put("userList", userService.listUser());
        return "home";
    }
    @RequestMapping(value = "/add", method = RequestMethod.POST)
    public String addUser(@ModelAttribute("user") User user, BindingResult result) {
        userService.addUser(user);
        return "redirect:/";
    }

    @RequestMapping("/delete/{id}")
    public String deleteUser(@PathVariable("id") Integer id) {

        userService.deleteUser(id);
        return "redirect:/";
    }

}

Spring Config Xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:beans="http://www.springframework.org/schema/beans"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    xmlns:p="http://www.springframework.org/schema/p"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
        http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd">


    <!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->

    <!-- Enables the Spring MVC @Controller programming model -->
    <context:annotation-config />
    <mvc:annotation-driven />

    <!-- Handles HTTP GET requests for /resources/** by efficiently serving up static resources in the ${webappRoot}/resources directory -->
    <mvc:resources mapping="/resources/**" location="/resources/" />

    <!-- Resolves views selected for rendering by @Controllers to .jsp resources in the /WEB-INF/views directory -->
    <beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <beans:property name="prefix" value="/WEB-INF/views/" />
        <beans:property name="suffix" value=".jsp" />
    </beans:bean>

    <context:component-scan base-package="com.spring.app" />

    <!-- Database Connection -->
    <tx:annotation-driven />
    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory" />
        <property name="dataSource" ref="dataSource"/>

    </bean>

    <beans profile="default">
        <jdbc:embedded-database id="dataSource"/>        
        <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
            <property name="dataSource" ref="dataSource"/>
            <property name="jpaVendorAdapter">
                <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"/>
            </property>
            <property name="jpaProperties">
                <props>
                    <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
                    <prop key="hibernate.hbm2ddl.auto">create</prop>
                </props>
            </property>
        </bean>
    </beans>

    <beans profile="prod">
        <bean class="java.net.URI" id="dbUrl">
            <constructor-arg value="#{systemEnvironment['DATABASE_URL']}"/>
        </bean>

        <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
            <property name="url" value="#{ 'jdbc:postgresql://' + @dbUrl.getHost() + ':' + @dbUrl.getPort() + @dbUrl.getPath() }"/>
            <property name="username" value="#{ @dbUrl.getUserInfo().split(':')[0] }"/>
            <property name="password" value="#{ @dbUrl.getUserInfo().split(':')[1] }"/>
        </bean>

        <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
            <property name="dataSource" ref="dataSource"/>
            <property name="jpaVendorAdapter">
                <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"/>
            </property>
            <property name="jpaProperties">
                <props>
                    <prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>
                    <prop key="hibernate.show_sql">true</prop>
                    <!-- change this to 'verify' before running as a production app -->
                    <prop key="hibernate.hbm2ddl.auto">create</prop>
                </props>
            </property>
        </bean>
    </beans>

</beans>

Model Class

@Entity 
public class User {

    @Id 
    @Column
    private int id;
    private String firstName;
    private String lastName;

    //Setter and Getter

Solution

We also faced the same issue. We were having create in the xml and @GeneratedValue on the id column. The resolution is remove the @GeneratedValue annotation and put the value of the id manually, also the jpa takes long by default so give long value e.g 1l.

To do the auto generation follow some another rule.

The issue around the JPA related auto generated Id is resolved as below:

Modify the Person.java model class to have the following annotations for the Id attribute:

@Id
@TableGenerator(name="TABLE_GEN",table="T_GENERATOR",pkColumnName="GEN_KEY",pkColumnValue="TEST",valueColumnName="GEN_VALUE",initialValue=1,allocationSize=1)
@GeneratedValue(strategy=GenerationType.TABLE, generator="TABLE_GEN")
public Long Id;

This will create a table in the mysql schema called T_GNERATOR which will have the tracking of the next value for Id and JPA over hibernate knows how to retrieve this value. The assumtion is that the initial value for the Id is 1 and it is incremented by 1 on each new insertion into it as is obvious from the attributes of the annotation.

Answered By - Avinav Mishra
Answer Checked By - Dawn Plyler (JavaFixing Volunteer)