javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: 无法使用 heroku PostgreSQL 执行查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25705713/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-11 00:57:55 来源:igfitidea点击:
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query with heroku PostgreSQL
提问by Sanjay Kumar
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.
我正在尝试在我的应用程序中使用数据库，但不断收到此错误。当我将它上传到 heroku 时，它在本地机器上工作正常，它不断出现此错误。我花了一整天的时间来获得任何解决方案，但没有成功。因此，如果有人可以帮助我解决这个问题，那将非常有帮助。
You can also find it on github. : Github
你也可以在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.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.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.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.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.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.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
ServiceImpl 类
@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
Spring 配置 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
采纳答案by Avinav Mishra
We also faced the same issue. We were having create in the xml and @GeneratedValueon the idcolumn. The resolution is remove the @GeneratedValueannotation and put the value of the idmanually, also the jpa takes long by default so give long value e.g 1l.
我们也面临同样的问题。我们在 xml 和列@GeneratedValue上进行了创建id。解决方法是删除@GeneratedValue注释并id手动放置值，默认情况下 jpa 也需要很长时间，因此请给出 long 值，例如1l.
To do the auto generation follow some another rule.
要进行自动生成，请遵循另一条规则。
The issue around the JPA related auto generated Id is resolved as below:
围绕 JPA 相关的自动生成的 Id 的问题解决如下：
Modify the Person.javamodel class to have the following annotations for the Id attribute:
修改Person.java模型类，为 Id 属性添加以下注释：
@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_GNERATORwhich 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.
这将在调用的 mysql 模式中创建一个表，该表T_GNERATOR将跟踪 Id 的下一个值，并且 JPA 通过 hibernate 知道如何检索该值。假设 Id 的初始值为 1，并且在每次新插入时增加 1，这从注释的属性中可以明显看出。
回答by Eugen
I had the same problem on JavaDB/Derby. The solution was to use explicit table name != "user": @Table(name = "my_user_table"). But it should be also fixed with a explicit schema because "user" is a system table
我在 JavaDB/Derby 上遇到了同样的问题。解决方案是使用显式表名 != "user": @Table(name = "my_user_table")。但它也应该使用显式架构进行修复，因为“用户”是一个系统表
回答by Damir Olejar
In my case, solution was quite simple. The column that was registered in MySQL did not exist in a model. Therefore, I was getting the mentioned error message. Took me a bit of a time to troubleshoot.
就我而言，解决方案非常简单。在 MySQL 中注册的列在模型中不存在。因此，我收到了提到的错误消息。花了我一点时间来排除故障。
回答by Alejandro Herrera
This error occurs when you want to insert a field that does not exist in the MySql database. For example the database field was deleted but it was not deleted from the model
当您要插入 MySql 数据库中不存在的字段时，会发生此错误。例如数据库字段被删除，但没有从模型中删除
