oracle JpaRepository - SQLException的问题：无效列名

7eumitmz  于 2023-10-16  发布在  Oracle
关注(0)|答案(2)|浏览(123)

我在使用JpaRepository和自定义查询时遇到了问题，我不知道是什么失败了。
Mensaje.java类

@Entity
public class Mensaje implements Serializable{
    private static final long serialVersionUID = 1L;
    
    @Id
    private int ID;

    private String idCampaign;

    private String platform;

    private String deviceID;

    private String lineNumber;
    
    
    public int getID() {
        return ID;
    }
    public void setID(int iD) {
        ID = iD;
    }
    public String getIdCampaign() {
        return idCampaign;
    }
    public void setIdCampaign(String idCampaign) {
        this.idCampaign = idCampaign;
    }
    public String getPlatform() {
        return platform;
    }
    public void setPlatform(String platform) {
        this.platform = platform;
    }
    public String getDeviceID() {
        return deviceID;
    }
    public void setDeviceID(String deviceID) {
        this.deviceID = deviceID;
    }
    public String getLineNumber() {
        return lineNumber;
    }
    public void setLineNumber(String lineNumber) {
        this.lineNumber = lineNumber;
    }
    public Mensaje(int iD, String idCampaign, String platform, String deviceID, String lineNumber) {
        super();
        ID = iD;
        this.idCampaign = idCampaign;
        this.platform = platform;
        this.deviceID = deviceID;
        this.lineNumber = lineNumber;
    }
    
    public Mensaje() {
        super();
        // TODO Auto-generated constructor stub
    }
    
    
}

服务实现

@Override
    public String procesarCampaign(String Idcampaign) {
        List<Mensaje> filas = new ArrayList<>(); 
        filas = gestionBaseDeDatos.buscarCampaign(Idcampaign);
        System.out.println("Registros recuperados: "+filas.size());
        for(int i=0; i<filas.size(); i++) {
            Mensaje aux = filas.get(i);
            producer.sendMessageColaPrincipal(aux);
        }
        return "Procesados "+filas.size()+"mensajes";
                
    }

jpaRepositiry接口

@Repository
public interface GestionBBDD extends JpaRepository<Mensaje, Long>{
    
    @Query(value="select * from CAMPAIGN where IDCAMPAIGN = "+":idCampaign", nativeQuery = true)
    List<Mensaje> buscarCampaign(@Param("idCampaign")String idCampaign);
    

}

Oracle表

CREATE TABLE CAMPAIGN(

    ID integer not null,
    idCampaign VARCHAR2(10),
    platform VARCHAR2(1),
    deviceID VARCHAR2(10),
    lineNumber VARCHAR2(9),
        
    CONSTRAINT PK_CAMPAIGN PRIMARY KEY (ID) 

);

application.properties

# Oracle settings
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@172.17.0.3:1521:xe
spring.datasource.username=system
spring.datasource.password=oracle
hibernate.globally_quoted_identifiers=true
hibernate.show_sql=true
#spring.datasource.driver-class-oracle.jdbc.driver.OracleDriver
## Hibernate Properties
# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.Oracle12cDialect

# logging
logging.pattern.console=%d{yyyy-MM-dd HH:mm:ss} %-5level %logger{36} - %msg%n
logging.level.org.hibernate.SQL=debug

问题是我总是得到相同的错误消息，问题在哪里？
错误日志：

2020-12-02 08:49:34 DEBUG org.hibernate.SQL - select * from CAMPAIGN

2020-12-02 08:49:34 WARN o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 17006, SQLState: 99999

2020-12-02 08:49:34 ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Invalid column name

2020-12-02 08:49:34 ERROR o.a.c.c.C.[.[.[.[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query] with root cause

java.sql.SQLException: Invalid column name

at oracle.jdbc.driver.OracleStatement.getColumnIndexPrimitive(OracleStatement.java:3899)

at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3857)

at oracle.jdbc.driver.InsensitiveScrollableResultSet.findColumn(InsensitiveScrollableResultSet.java:

谢谢.
已编辑的完整日志

. ____ _ __ _ _

/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \

( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \

\\/ ___)| |_)| | | | | || (_| | ) ) ) )

' |____| .__|_| |_|_| |_\__, | / / / /

=========|_|==============|___/=/_/_/_/

:: Spring Boot :: (v2.4.0)

2020-12-02 11:49:18 INFO c.t.ajpn.GestionColasApplication - Starting GestionColasApplication v0.0.1-SNAPSHOT using Java 1.8.0_212 on 1ef21643a295 with PID 1 (/gestionColas.jar started by spring in /)

2020-12-02 11:49:18 INFO c.t.ajpn.GestionColasApplication - No active profile set, falling back to default profiles: default

2020-12-02 11:49:21 INFO o.s.d.r.c.RepositoryConfigurationDelegate - Bootstrapping Spring Data JPA repositories in DEFERRED mode.

2020-12-02 11:49:21 INFO o.s.d.r.c.RepositoryConfigurationDelegate - Finished Spring Data repository scanning in 170 ms. Found 1 JPA repository interfaces.

2020-12-02 11:49:23 INFO o.s.b.w.e.tomcat.TomcatWebServer - Tomcat initialized with port(s): 8181 (http)

2020-12-02 11:49:23 INFO o.a.catalina.core.StandardService - Starting service [Tomcat]

2020-12-02 11:49:23 INFO o.a.catalina.core.StandardEngine - Starting Servlet engine: [Apache Tomcat/9.0.39]

2020-12-02 11:49:23 INFO o.a.c.c.C.[Tomcat].[localhost].[/] - Initializing Spring embedded WebApplicationContext

2020-12-02 11:49:23 INFO o.s.b.w.s.c.ServletWebServerApplicationContext - Root WebApplicationContext: initialization completed in 4562 ms

2020-12-02 11:49:23 INFO o.s.s.c.ThreadPoolTaskExecutor - Initializing ExecutorService 'applicationTaskExecutor'

2020-12-02 11:49:23 INFO o.h.jpa.internal.util.LogHelper - HHH000204: Processing PersistenceUnitInfo [name: default]

2020-12-02 11:49:24 INFO org.hibernate.Version - HHH000412: Hibernate ORM core version 5.4.23.Final

2020-12-02 11:49:24 WARN o.s.b.a.o.j.JpaBaseConfiguration$JpaWebConfiguration - spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning

2020-12-02 11:49:24 INFO o.h.annotations.common.Version - HCANN000001: Hibernate Commons Annotations {5.1.2.Final}

2020-12-02 11:49:24 INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...

2020-12-02 11:49:25 INFO o.s.b.w.e.tomcat.TomcatWebServer - Tomcat started on port(s): 8181 (http) with context path ''

2020-12-02 11:49:25 INFO o.s.d.r.c.DeferredRepositoryInitializationListener - Triggering deferred initialization of Spring Data repositories…

2020-12-02 11:49:26 INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.

2020-12-02 11:49:26 INFO org.hibernate.dialect.Dialect - HHH000400: Using dialect: org.hibernate.dialect.Oracle12cDialect

2020-12-02 11:49:30 INFO o.h.e.t.j.p.i.JtaPlatformInitiator - HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]

2020-12-02 11:49:30 INFO o.s.o.j.LocalContainerEntityManagerFactoryBean - Initialized JPA EntityManagerFactory for persistence unit 'default'

2020-12-02 11:49:32 INFO o.s.d.r.c.DeferredRepositoryInitializationListener - Spring Data repositories initialized!

2020-12-02 11:49:32 INFO c.t.ajpn.GestionColasApplication - Started GestionColasApplication in 16.484 seconds (JVM running for 18.063)

2020-12-02 11:49:38 INFO o.a.c.c.C.[Tomcat].[localhost].[/] - Initializing Spring DispatcherServlet 'dispatcherServlet'

2020-12-02 11:49:38 INFO o.s.web.servlet.DispatcherServlet - Initializing Servlet 'dispatcherServlet'

2020-12-02 11:49:38 INFO o.s.web.servlet.DispatcherServlet - Completed initialization in 7 ms

2020-12-02 11:49:39 DEBUG org.hibernate.SQL - select mensaje0_.id as id1_0_, mensaje0_.deviceid as deviceid2_0_, mensaje0_.id_campaign as id_campaign3_0_, mensaje0_.line_number as line_number4_0_, mensaje0_.platform as platform5_0_ from campaign mensaje0_ where mensaje0_.id_campaign=?

2020-12-02 11:49:39 WARN o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 904, SQLState: 42000

2020-12-02 11:49:39 ERROR o.h.e.jdbc.spi.SqlExceptionHelper - ORA-00904: "MENSAJE0_"."ID_CAMPAIGN": invalid identifier

2020-12-02 11:49:39 ERROR o.a.c.c.C.[.[.[.[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

oracle.jdbc.OracleDatabaseException: ORA-00904: "MENSAJE0_"."ID_CAMPAIGN": invalid identifier

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)

at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)

at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)

at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)

at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)

at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807)

at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)

at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)

at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713)

at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167)

at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)

at org.hibernate.loader.Loader.getResultSet(Loader.java:2304)

at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2057)

at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2019)

at org.hibernate.loader.Loader.doQuery(Loader.java:948)

at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)

at org.hibernate.loader.Loader.doList(Loader.java:2850)

at org.hibernate.loader.Loader.doList(Loader.java:2832)

at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2664)

at org.hibernate.loader.Loader.list(Loader.java:2659)

at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506)

at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400)

at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)

at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1414)

at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1565)

at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1533)

at org.hibernate.query.Query.getResultList(Query.java:165)

at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:126)

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.doInvoke(RepositoryMethodInvoker.java:137)

at org.springframework.dat

a.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.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:371)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:134)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)

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.$Proxy90.buscarCampaign(Unknown Source)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)

at com.sun.proxy.$Proxy64.buscarCampaign(Unknown Source)

at com.telefonica.ajpn.service.impl.ColaServiceImpl.procesarCampaign(ColaServiceImpl.java:40)

at com.telefonica.ajpn.controller.ColaController.procesarCampaign(ColaController.java:27)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:197)

at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:141)

at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:893)

at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:807)

at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1061)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:961)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)

at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:626)

at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

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.core.StandardEngineValve.invoke(StandardEngineValve.java:78)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)

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:868)

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590)

at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Thread.java:748)
oracle

2条答案

按热度按时间
k2fxgqgv

k2fxgqgv1#

检查下面的查询：-

@Query(SELECT u FROM CAMPAIGN u WHERE u.IDCAMPAIGN = ?1)
    List<Mensaje> buscarCampaign(@Param("idCampaign")String idCampaign);

让您的查询简单：--

CREATE TABLE CAMPAIGN(

    ID integer not null,
    id_Campaign VARCHAR2(10),
    platform VARCHAR2(1),
    device_ID VARCHAR2(10),
    line_Number VARCHAR2(9),
        
    PRIMARY KEY (ID) 

);

属性文件：--

spring.jpa.hibernate.ddl-auto= none // if you want to create manually tbl

//pojo Mensaje表名是CAMPAIGN，对吗？

@Entity
@Table(name="CAMPAIGN")        // **you have missed it**
public class Mensaje implements Serializable{
    private static final long serialVersionUID = 1L;
    

//field
//setter
//getter
}

Jpa提供了各种方法，但仍然是为什么你要使用JPQL，虽然它是基本的查询。
希望这能解决你的问题。

赞(0）分享  回复(0）举报  2023-10-16
unguejic

unguejic2#

解决了！
我做了些改变，
将类Mensaje.java命名为Campaign.java（与Oracle表中的类相同的nafe），并将其中的字段名称重新命名。
查询它一直是这样的

@Repository
public interface GestionBBDD extends JpaRepository<Campaign, Long>{
    
    @Query("SELECT u FROM Campaign u WHERE u.campaign = ?1")
    List<Campaign> buscarCampaign(@Param("idCampaign")String idCampaign);

}

谢谢大家，我很感激！

赞(0）分享  回复(0）举报  2023-10-16
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com