java - org.postgresql.util.PSQLException: 错误:关系"app_user" 不存在

我有一个使用Spring Boot和postgres的应用程序,当我尝试创建用户时,出现此错误。

在数据库上运行此查询时,出现同样的错误:


select * from APP_USER

ERROR: relation"app_user" does not exist
LINE 1: select * from APP_USER
 ^
********** Error **********

ERROR: relation"app_user" does not exist
SQL state: 42P01

但是如果我把它改成:


select * from"APP_USER"

它可以工作了。

如何在Spring Boot应用程序中配置它?

pom.xml中的依赖项:


<dependencies>

 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-web</artifactId>
 </dependency>

 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-actuator</artifactId>
 </dependency>
 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-data-jpa</artifactId>
 </dependency>
 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-security</artifactId>
 </dependency>
 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-thymeleaf</artifactId>
 </dependency>
 <dependency>
 <groupId>org.thymeleaf.extras</groupId>
 <artifactId>thymeleaf-extras-tiles2</artifactId>
 <version>2.1.1.RELEASE</version>
 </dependency>

 <dependency>
 <groupId>org.postgresql</groupId>
 <artifactId>postgresql</artifactId>
 <version>9.4-1201-jdbc41</version>
 <scope>runtime</scope>
 </dependency>
 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-test</artifactId>
 <scope>test</scope>
 </dependency>

</dependencies>

application.properties :


spring.datasource.driverClassName=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/boek
spring.datasource.username=postgres
spring.datasource.password=ABCD123$

spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.generate-ddl=false

#spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true

我的实体:


@Entity
@Table(name ="APP_USER")
public class User implements Serializable {

 private static final long serialVersionUID = -1152779434213289790L;

 @Id
 @Column(name="ID", nullable = false, updatable = false)
 @GeneratedValue(strategy=GenerationType.AUTO) 
 private long id;

 @Column(name="NAME", nullable = false)
 private String name;

 @Column(name="USER_NAME", nullable = false, unique = true)
 private String username;

 @Column(name="PASSWORD", nullable = false)
 private String password;

 @Column(name="EMAIL", nullable = false, unique = true)
 private String email;

 @Column(name="ROLE", nullable = false)
 private RoleEnum role;

我从表单调用这个操作:


<form role="form" action="#" th:action="@{/user/create}" th:object="${userDTO}" method="post">

这是我的控制器:


@RequestMapping(value ="/user/create", method = RequestMethod.POST)
 public String handleUserCreateForm(@Valid @ModelAttribute("form") UserDTO form, BindingResult bindingResult) {
 if (bindingResult.hasErrors()) {
 return"user_create";
 }
 try {
 userService.create(form);
 } catch (DataIntegrityViolationException e) {
 bindingResult.reject("email.exists","Email already exists");
 return"user_create";
 }
 return"redirect:/users";
 }

解密错误的验证程序:


private void validateEmail(Errors errors, UserDTO form) {
 if (userService.getUserByEmail(form.getEmail()).isPresent()) {
 errors.reject("email.exists","User with this email already exists");
 }
 }

UserServiceImpl (@Service ):


@Override
 public Optional<User> getUserByEmail(String email) {
 return userRepository.findOneByEmail(email);
 }

存储库是一个CrudRepository接口,没有实现:


@Repository
public interface UserRepository extends CrudRepository<User, Serializable> {

 Optional<User> findOneByEmail(String email);
}

并调试验证程序,我可以得到这个堆栈:


org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
 at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:238)
 at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:221)
 at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417)
 at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
 at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
 at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
 at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:122)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
 at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
 at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
 at com.sun.proxy.$Proxy75.findOneByEmail(Unknown Source)
 at com.myapp.service.impl.UserServiceImpl.getUserByEmail(UserServiceImpl.java:32)
 at com.myapp.model.validator.UserValidator.validateEmail(UserValidator.java:40)
 at com.myapp.model.validator.UserValidator.validate(UserValidator.java:30)
 at org.springframework.validation.DataBinder.validate(DataBinder.java:785)
 at org.springframework.web.method.annotation.ModelAttributeMethodProcessor.validateIfApplicable(ModelAttributeMethodProcessor.java:164)
 at org.springframework.web.method.annotation.ModelAttributeMethodProcessor.resolveArgument(ModelAttributeMethodProcessor.java:111)
 at org.springframework.web.method.support.HandlerMethodArgumentResolverComposite.resolveArgument(HandlerMethodArgumentResolverComposite.java:77)
 at org.springframework.web.method.support.InvocableHandlerMethod.getMethodArgumentValues(InvocableHandlerMethod.java:162)
 at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:129)
 at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:776)
 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:705)
 at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
 at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
 at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
 at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:967)
 at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:869)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
 at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:843)
 at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at org.springframework.boot.actuate.autoconfigure.EndpointWebMvcAutoConfiguration$ApplicationContextHeaderFilter.doFilterInternal(EndpointWebMvcAutoConfiguration.java:295)
 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at org.springframework.boot.actuate.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:102)
 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
 at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
 at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:154)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:110)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.csrf.CsrfFilter.doFilterInternal(CsrfFilter.java:105)
 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:57)
 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:50)
 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
 at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
 at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
 at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:85)
 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at org.springframework.boot.actuate.autoconfigure.MetricsFilter.doFilterInternal(MetricsFilter.java:68)
 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
 at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
 at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
 at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:668)
 at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1521)
 at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1478)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
 at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
 at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
 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.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
 at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91)
 at org.hibernate.loader.Loader.getResultSet(Loader.java:2066)
 at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863)
 at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
 at org.hibernate.loader.Loader.doQuery(Loader.java:910)
 at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
 at org.hibernate.loader.Loader.doList(Loader.java:2554)
 at org.hibernate.loader.Loader.doList(Loader.java:2540)
 at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
 at org.hibernate.loader.Loader.list(Loader.java:2365)
 at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:497)
 at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387)
 at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:236)
 at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1300)
 at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)
 at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
 at org.hibernate.jpa.internal.QueryImpl.getSingleResult(QueryImpl.java:495)
 at org.hibernate.jpa.criteria.compile.CriteriaQueryTypeQueryAdapter.getSingleResult(CriteriaQueryTypeQueryAdapter.java:71)
 at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:202)
 at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:74)
 at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:99)
 at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:90)
 at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:415)
 at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:393)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
 at org.springframework.data.repository.core.support.RepositoryFactorySupport$DefaultMethodInvokingMethodInterceptor.invoke(RepositoryFactorySupport.java:506)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
 at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
 at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
 at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
 at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
 ... 98 more
Caused by: org.postgresql.util.PSQLException: ERROR: relation"app_user" does not exist
 Posição: 177
 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
 at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:305)
 at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
 ... 129 more

感谢你的帮助!

时间:

PostgreSQL遵循SQL标准,在这种情况下,表示标识符(表名,列名等)被强制为小写,除非被引号包含,因此,当你创建这样一个表时:


CREATE TABLE APP_USER ...

你实际上得到了一个app_user表,


CREATE TABLE"APP_USER" ...

然后你就得到了一个"APP_USER"表。

通过读取PostgreSQL日志文件来检查:

由于您无法控制Spring如何从实体构建查询,因此最好使用小写标识符(SQL标准)。

...