Clever Geek Handbook

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: Failed to execute query with PostgreSQL hero

I am trying to use a database in my application but keep getting this error. Its working on local computer when I loaded it into hero it keeps giving this error. I spent all day trying to get any solution but didn't get it in time. So if anyone can help me with this it will be really helpful.

You can also find it on github .: Github

Mistake

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 Xml Configuration

<?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 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
+3


source to share


3 answers


We also faced the same problem. We created in xml and @GeneratedValue

in column id

. The resolution removes the annotation @GeneratedValue

and puts the value id

manually, also by default jpa takes a long value, eg 1l

.

To do autogeneration, follow the next rule.

The issue related to the auto-generated JPA ID is resolved as follows:



Modify the model class Person.java

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 mysql schema called T_GNERATOR

which will keep track of the next value for Id and JPA versus hibernate knows how to get that value. The initial value for Id is assumed to be 1, and it is incremented by 1 each time it is inserted into it, as seen from the annotation attributes.

+1


source


I had the same problem in JavaDB / Derby. The solution was to use an explicit table name! = "User": @Table (name = "my_user_table"). But it also needs to be fixed with an explicit schema because "user" is a system table



+2


source


In my case, the solution was pretty simple. The column that was registered with MySQL does not exist in the model. So I got the above error message. It took a little time to troubleshoot.

+1


source






More articles:


All Articles