时间戳

首页 > 解决方案 > 无法使用 JPArepository 和 Spring 框架从 Postgres 表中检索数据

java - 无法使用 JPArepository 和 Spring 框架从 Postgres 表中检索数据

问题描述

这是我的服务调用 findAll() 方法时不断收到的错误。当我使用本机查询“getSubmissionByCoreId”检索数据时，我得到了同样的错误。请注意，我可以使用“保存”方法成功插入“提交”表，没有任何问题：

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:259)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
    at com.sun.proxy.$Proxy55.getSubmissionByCoreId(Unknown Source)
    at com.ibm.fda.splfhirpoc.audit.SubmissionService.getSubmissionByCoreId(SubmissionService.java:93)
    at com.ibm.fda.splfhirpoc.audit.SubmissionService.updateSubmission(SubmissionService.java:160)
    at com.ibm.fda.splfhirpoc.audit.SubmissionControllerNew.updateSubmission(SubmissionControllerNew.java:91)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:179)
    at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96)
    at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:201)
    at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:104)
    at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:59)
    at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:96)
    at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:308)
    at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121)
    at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:265)
    at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:234)
    at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:208)
    at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:160)
    at org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:225)
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:298)
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doPost(AbstractHTTPServlet.java:217)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:652)
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:273)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:346)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:887)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1684)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.base/java.lang.Thread.run(Thread.java:836)
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2287)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2045)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2007)
    at org.hibernate.loader.Loader.doQuery(Loader.java:953)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
    at org.hibernate.loader.Loader.doList(Loader.java:2810)
    at org.hibernate.loader.Loader.doList(Loader.java:2792)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2624)
    at org.hibernate.loader.Loader.list(Loader.java:2619)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:396)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1410)
    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1558)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1526)
    at org.hibernate.query.internal.AbstractProducedQuery.getSingleResult(AbstractProducedQuery.java:1574)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:196)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryQueryMethodInvoker$$Lambda$591/0x0000000000000000.invoke(Unknown Source)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:152)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:131)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
    ... 53 more
Caused by: org.postgresql.util.PSQLException: ERROR: column submission0_1_.sub_id does not exist
  Hint: Perhaps you meant to reference the column "submission0_.sub_id" or the column "submission0_1_.sps_id".
  Position: 1302
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
    ... 86 more

这是我的实体类：

import java.io.Serializable;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.SecondaryTable;
import javax.persistence.Table;

@Entity(name = "mysubmission")
@Table(name = "submission", schema = "splfhirpoc")
@SecondaryTable(name = "submission_processing_status", schema = "splfhirpoc")
public class SubmissionEntityTest implements Serializable {
    
    private static final long serialVersionUID = 1L;
    
    
    public static long getSerialversionuid() {
        return serialVersionUID;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "sub_id", updatable = false, nullable = false)
    private Long subId ;
    
    @Column(name = "sub_core_id", updatable = false, nullable = false)
    private String subCoreId;
    
    @Column(name = "sub_type_code", updatable = false, nullable = false)
    private String subTypeCode ;
    
    @Column(name = "sub_doc_type_code", updatable = false, nullable = false)
    private String subDocTypeCode;
    
    @Column(name = "sub_doc_type_text", updatable = false, nullable = false)
    private String subDocTypeText ;
    
    @Column(name = "sub_organization_duns_number", updatable = false, nullable = false)
    private String subOrganizationDunsNumber ;
    
    @Column(name = "sub_upload_timestamp", updatable = false, nullable = false)
    private String subUploadTimestamp;
    
    @Column(name = "sub_fhir_file_name")
    private String subFhirFileName;
    
    @Column(name = "sub_fhir_file_type")
    private String subFhirFileType ;
    
    @Column(name = "sub_fhir_file_url")
    private String subFhirFileUrl;
    
    @Column(name = "sub_fhir_bundle_id")
    private String subFhirBundleId ;
    
    @Column(name = "sub_fhir_version")
    private String subFhirVersion;
    
    @Column(name = "sub_fhir_transformed_timestamp")
    private String subFhirTransformedTimestamp;
    
    @Column(name = "sub_spl_set_id")
    private String subSplSetId;
    
    @Column(name = "sub_spl_doc_id")
    private String subSplDocId ;
    
    @Column(name = "sub_spl_file_name")
    private String subSplFileName;
    
    @Column(name = "sub_spl_file_type")
    private String subSplFileType ;
    
    @Column(name = "sub_spl_file_url")
    private String subSplFileUrl;
    
    @Column(name = "sub_spl_version")
    private String subSplVersion;
    
    @Column(name = "sub_spl_transformed_timestamp")
    private String subSplTransformedTimestamp;
    
    @Column(name = "sub_create_timestamp", updatable = false, nullable = false)
    private String subCreateTimestamp;
    
    @Column(name = "sub_update_timestamp")
    private String subUpdateTimestamp;
    
    @Column(name = "sub_created_by", updatable = false, nullable = false)
    private String subCreatedBy;
    
    @Column(name = "sub_updated_by")
    private String subUpdatedBy;
    
    @OneToMany(mappedBy="submissionEntityTest", cascade=CascadeType.ALL)
    private Set<SubmissionProcessingStatusEntityTest> subStatusList;

    
    

//Getter and Setter methods
    
}

这是我的存储库界面：

import java.util.List;
import java.util.Optional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.ibm.fda.model.SubmissionEntityTest;

@Repository
@Transactional(timeout = 600)
public interface SubmissionRepositoryTest extends JpaRepository<SubmissionEntityTest, Long> {
    
    List<SubmissionEntityTest> findAll();
    
    Optional<SubmissionEntityTest> findById(long id);
    
    
    @Query(" FROM mysubmission sub WHERE sub.subCoreId = :coreId ")
    Optional<SubmissionEntityTest> getSubmissionByCoreId(@Param("coreId") String coreId);

}

这是服务类：

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.exception.ConstraintViolationException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Service;

import com.ibm.fda.model.AuditEvent;
import com.ibm.fda.model.SubmissionEntityTest;
import com.ibm.fda.model.SubmissionProcessingStatusEntityTest;

@Service
public class SubmissionService implements ISubmissionService {
    
    private static final Logger LOGGER = LogManager.getLogger(SubmissionService.class.getName());
    private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    
    private SubmissionEntityTest submission = new SubmissionEntityTest();
    private SubmissionEntityTest outSubmission = new SubmissionEntityTest();
    private SubmissionProcessingStatusEntityTest outSpsSubmission = new SubmissionProcessingStatusEntityTest();
    private SubmissionProcessingStatusEntityTest spsSubmission = new SubmissionProcessingStatusEntityTest();
    
    @Autowired
    private SubmissionRepositoryTest submissionRepositoryTest;
    
    @Autowired
    private SubmissionProcessingStatusRepository submissionProcessingStatusRepository;

    @Override
    public List<SubmissionEntityTest> getAllSubmissions() throws ResourceNotFoundException {

        LOGGER.trace("Received getAllSubmissions request");
        
        try {
            Iterable<SubmissionEntityTest> it = submissionRepositoryTest.findAll();

            ArrayList<SubmissionEntityTest> submissions = new ArrayList<SubmissionEntityTest>();
            it.forEach(e -> submissions.add(e));

            return submissions;
        }
         catch (ConstraintViolationException ce)
        {
            ce.printStackTrace();
            System.out.println(" ConstraintViolation exception when trying to retrieve all submissions : " + ce.getMessage());
            throw new RuntimeException(ce);
        }
        catch (HibernateException he) 
        {
            he.printStackTrace();
            System.out.println(" Hibernate exception  when trying to retrieve all submissions : " + he.getMessage());
            throw new RuntimeException(he);
        }
        catch (DataAccessException de) {
            de.printStackTrace();
            System.out.println(" Data Access exception  when trying to retrieve all submissions : " + de.getMessage());
            throw new RuntimeException(de);
        }
        
    }

这是我的控制器类：

import java.util.List;

import javax.ws.rs.Consumes;
import javax.ws.rs.GET;
import javax.ws.rs.HeaderParam;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;

import com.ibm.fda.model.AuditEvent;
import com.ibm.fda.model.SubmissionEntityTest;

@Path("/submission")

public class SubmissionControllerNew {
    
    private static final Logger LOGGER = LogManager.getLogger(SubmissionControllerNew.class.getName());
    
    @Autowired
    private ISubmissionService submissionService;

    @GET
    @Path("getAll")
    @Produces(MediaType.APPLICATION_JSON)
    public List<SubmissionEntityTest> getAllSubmissions(@HeaderParam("userID") String userID,
                                                        @HeaderParam("org") String org) throws ResourceNotFoundException {
        List<SubmissionEntityTest> list = submissionService.getAllSubmissions();
        
        LOGGER.trace("Received getAllSubmissions request");
 
        return list;
    }

....和我的 application.properties

spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.initialization-mode=always
spring.datasource.url=jdbc:postgresql://localhost:9001/fhirdb
spring.datasource.username=
spring.datasource.password=
spring.jpa.show-sql=true

# Hibernate Properties
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

# Hibernate ddl auto (create, create-drop, validate, update)

spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.default_schema=splfhirpoc
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
#
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true

最后，Postgres 表在“splfhirpoc”模式中具有以下布局：

Create table splfhirpoc.submission (
sub_id serial not null primary key,
sub_core_id varchar(100) not null ,
sub_type_code varchar(10) not null ,
sub_doc_type_code varchar(20) not null ,
sub_doc_type_text varchar(100) not null ,
sub_organization_duns_number varchar(100) not null ,
sub_upload_timestamp varchar(50) not null ,
sub_fhir_file_name varchar(250),
sub_fhir_file_type varchar(10),
sub_fhir_file_url varchar(250),
sub_fhir_bundle_id varchar(100),
sub_fhir_version varchar(10),
sub_fhir_transformed_timestamp varchar(50),
sub_spl_set_id varchar(100) not null ,
sub_spl_doc_id varchar(100) not null ,
sub_spl_file_name varchar(250),
sub_spl_file_type varchar(10),
sub_spl_file_url varchar(250),
sub_spl_version varchar(10),
sub_spl_transformed_timestamp varchar(50),
sub_create_timestamp varchar(50) not null ,
sub_update_timestamp varchar(50),
sub_created_by varchar(100) not null ,
sub_updated_by varchar(100)
)

标签： javaspringpostgresqlfindall

解决方案

“这是我的存储库接口”在这里您不需要放置 findAll() 方法。JpaRepo 将为您提供实现。

您的回购应如下所示：

import java.util.List;
import java.util.Optional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.ibm.fda.model.SubmissionEntityTest;

@Repository
@Transactional(timeout = 600)
public interface SubmissionRepositoryTest extends JpaRepository<SubmissionEntityTest, Long> {
    
    @Query(" FROM mysubmission sub WHERE sub.subCoreId = :coreId ")
    Optional<SubmissionEntityTest> getSubmissionByCoreId(@Param("coreId") String coreId);

}

只有您的自定义 Jpa 查询应该出现在此处。

推荐阅读