前端之家

  1. 首页
  2. 问答

ORA-00942：Spring Boot中的Spring JDBC模板不存在表或视图

• 问答

在尝试通过Spring JDBC模板执行数据库代码时，我遇到了一个奇怪的ORA-00942: table or view does not exist异常：

2019-12-26 22:01:36.863[0;39m [31mERROR[0;39m [35m12232[0;39m [2m---[0;39m [2m[ctor-http-nio-3][0;39m [36ma.w.r.e.AbstractErrorWebExceptionHandler[0;39m [2m:[0;39m [ca8305eb] 500 Server Error for HTTP GET "/exs/acs/accounts-links?limit=20&q=632626&showactive=false&systemName=IMMS"

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select a.FaccIN,a.FaccKEY,a.FaccsnAME,b.LNKSYSTEM,b.LNKLOANKEY,a.FaccSTATUS,a.FaccCOND from BNYMACS.accOUNT a left outer join BNYMACS.LINKS b on a.FaccIN = b.lnkacc where (upper(a.FaccKEY) like ? or upper(FaccsnAME) like ? or (b.LNKLOANKEY like ? )) and b.LNKSYSTEM =?  and rownum<=? order by 3 ]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235) ~[spring-jdbc-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    Suppressed: reactor.core.publisher.FluxOnAssembly$OnAssemblyException: 
Error has been observed at the following site(s):
    |_ checkpoint ? org.springframework.boot.actuate.metrics.web.reactive.server.MetricsWebFilter [DefaultWebFilterChain]
    |_ checkpoint ? HTTP GET "/exs/acs/accounts-links?limit=20&q=632626&showactive=false&systemName=IMMS" [ExceptionHandlingWebHandler]
Stack trace:
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235) ~[spring-jdbc-5.2.0.RELEASE.jar:5.2.0.RELEASE]

。 。 。 。 。 。 

Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at com.p6spy.engine.wrapper.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:78) ~[p6spy-3.8.2.jar:na]
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:678) ~[spring-jdbc-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669) ~[spring-jdbc-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) ~[spring-jdbc-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712) ~[spring-jdbc-5.2.0.RELEASE.jar:5.2.0.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:763) ~[spring-jdbc-5.2.0.RELEASE.jar:5.2.0.RELEASE]

由于使用了Oracle驱动程序，因此oracle.jdbc.driver.OraclePreparedStatementWrapper的源代码已被开发人员关闭，因此我无法真正调试ojdbc8中发生的事情。

奇怪的是，来自Spring JDBC模板的查询进入Oracle驱动程序，在调试会话期间使用其参数将其提取并复制到外部SQL客户端中时，在这里就可以了：

select a.FaccIN,a.FaccCOND from BNYMACS.accOUNT a left outer join BNYMACS.LINKS b on a.FaccIN = b.lnkacc where (upper(a.FaccKEY) like '632626%' or upper(FaccsnAME) like '632626%' or (b.LNKLOANKEY like '632626%' )) and b.LNKSYSTEM ='IMMS'  and rownum<=20 order by 3 ;

并生成多个结果记录。

这是有问题的Spring存储库：

import static cwp.services.adhoc_processor.domain.acs.account.*;
import static cwp.services.adhoc_processor.domain.acs.Links.*;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;


import cwp.services.adhoc_processor.domain.acs.accountLinkDetail;
import cwp.services.adhoc_processor.domain.acs.accountLinkDetail.accountLinkDetailBuilder;

import cwp.services.adhoc_processor.domain.acs.accountLinksList;
import lombok.extern.slf4j.Slf4j;

@Slf4j
public class accountLinksRepositoryCustomImpl implements accountLinksRepositoryCustom {

    private static final String TERMINATED = "TERMINATED";
    private static final String STOPPED = "STOPPED";

    private final static String STATIC_QUERY_PART = "select a.FaccIN,a.FaccCOND "
            + "from BNYMACS.accOUNT a left outer join BNYMACS.LINKS b "
            + "on a.FaccIN = b.lnkacc where (upper(a.FaccKEY) like ? or upper(FaccsnAME) like ? "
            + "or (b.LNKLOANKEY like ? )) and b.LNKSYSTEM =? ";

    private final static String activeSuffix = "and upper(a.FaccSTATUS) <> 'TERMINATED' and upper(a.FaccCOND) <> 'STOPPED' ";

    private final static String orderbyClause = " and rownum<=? order by 3 ";

    @Autowired
    @Qualifier("acsJdbcTemplate")
    private JdbcTemplate template;

    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.template = new JdbcTemplate(dataSource);
    }

    @Override
    public accountLinksList searchByaccountOrLinks(String searchTerm,int limit,boolean isactive,String systemName) {
        accountLinksList accountLinksList = new accountLinksList();

        try {
            String dynQuery = STATIC_QUERY_PART;

            if (isactive)
                dynQuery += activeSuffix;

            dynQuery += orderbyClause;

            List<accountLinkDetail>  accountDetailsList = template.<accountLinkDetail>query(
                    dynQuery,new Object[] { searchTerm,searchTerm,systemName,limit },new RowMapper<accountLinkDetail>() {
                        @Override
                        public accountLinkDetail mapRow(ResultSet rs,int currentRow) throws SQLException {
                            accountLinkDetailBuilder accountDetails = accountLinkDetail.builder();
                            accountDetails
                                    .accountId(rs.getLong(ID_COLUMN_NAME))
                                    .accountKey(rs.getString(accOUNT_KEY_COLUMN_NAME))
                                    .accountName(rs.getString(accOUNT_SHORT_NAME_COLUMN_NAME))
                                    .loanKey(rs.getString(LOAN_KEY_COLUMN_NAME))
                                    .systemName(rs.getString(LINK_SYSTEM_COLUMN_NAME));
                            if (rs.getString(CONDITION_COLUMN_NAME) != null
                                    && !rs.getString(CONDITION_COLUMN_NAME).equalsIgnoreCase(STOPPED)
                                    && rs.getString(STATUS_COLUMN_NAME) != null
                                    && !rs.getString(STATUS_COLUMN_NAME).equalsIgnoreCase(TERMINATED)) {
                                accountDetails.isactive(true);
                            }
                            return accountDetails.build();
                        }



                    });
            accountLinksList.setaccountLinkDetail(accountDetailsList);
        } catch (Exception e) {
            log.error("Technical Exception",e);
            throw e;
        }
        return accountLinksList;
    }

}

这是配置数据库的方式：

@Configuration
@EnableTransactionmanagement
@EnableJpaRepositories(
        basePackages = {"cwp.services.adhoc_processor.repository.acs" },entityManagerFactoryRef = "acsEntityManagerFactory",transactionmanagerRef = "acsTransactionmanager")

public class AcsDatasourceConfiguration {

    @Autowired
    private AcsDbProperties properties;

    @Validated
    @Component
    @ConfigurationProperties(prefix = "acs.read.datasource")
    public class AcsDbProperties extends HikariDBProperties {
    }

    @ConditionalOnBean(value = AcsDbProperties.class)
    @Bean(name = "acsDataSource",destroyMethod = "")
    public DataSource acsDataSource() {
        return new HikariDataSource(new HikariConfig(properties.getProperties()));
    }

    @Bean("acsJdbcTemplate")
    public JdbcTemplate pmtTemplate () throws Exception {
        return new JdbcTemplate(acsDataSource());
    }

    @Bean(name = "acsEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean acsEntityManagerFactory(EntityManagerFactoryBuilder builder) {
        return builder.dataSource(acsDataSource())
                .packages("cwp.services.adhoc_processor.domain.acs" )
                .persistenceUnit("acs")
                .build();
    }

    /*
     * Leaky requirement for the unsatisfied transaction manager bean stemming from nxn-workflow-services#NWFProcessEngineConfiguration.java dependency.
     * 
     * N.B. MUST BE CALLED THIS Exact NAME,else will get 
     * org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 'org.springframework.transaction.PlatformTransactionmanager' available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true),@org.springframework.beans.factory.annotation.Qualifier(value=transactionmanager)}
     * 
     */
    @Bean("transactionmanager") // <-- must be called `transactionmanager`.
    public PlatformTransactionmanager acsTransactionmanager
            (
            @Qualifier("acsEntityManagerFactory")
            final  LocalContainerEntityManagerFactoryBean acsEntityManagerFactory
            ) {

                return new JpaTransactionmanager(acsEntityManagerFactory.getObject());
    }


}

这是用例的调用：

curl --location --request GET 'http://localhost:7010/exs/acs/accounts-links?limit=20&q=632626&showactive=false&systemName=IMMS' \
--header 'Content-Type: application/json'

该表似乎是同义词：

ORA-00942：Spring Boot中的Spring JDBC模板不存在表或视图

ORA-00942：Spring Boot中的Spring JDBC模板不存在表或视图

，运行时用户为 WEBSVC_READ ：

ORA-00942：Spring Boot中的Spring JDBC模板不存在表或视图

直接查询数据库时，我使用相同的 WEBSVC_READ 模式，并且运行良好：

ORA-00942：Spring Boot中的Spring JDBC模板不存在表或视图

N.B。：该行为似乎仅与JDBC模板有关。以下JPA查询可以正常运行：

"select  a from account a,Links l where l.accountId = a.id and l.loanKey = :loanKey and l.linkSystem = :systemName and(upper(a.status) <> 'TERMINATED' or upper(a.condition) <>'STOPPED' )";

application.properties中包含以下内容：

spring.jpa.properties.hibernate.default_schema=BNYMACS

和这样配置的实体（无显式模式）：

@Entity(name="account")
@Table(name="accOUNT")
public class account implements Serializable{...}


@Entity
@Table(name="LINKS")
@Data
public class Links implements Serializable{...}

这可能是什么问题？这个ORA-00942是有意义的错误，还是其他内容的占位符？也许有人可以给我一些有用的指导，以帮助您深入了解本教程。

谢谢。

lanybo 回答：ORA-00942：Spring Boot中的Spring JDBC模板不存在表或视图

罪魁祸首是我的存储库中的以下错误且不必要的定义：

@Autowired
public void setDataSource(DataSource dataSource) {
        this.template = new JdbcTemplate(dataSource);
    }

它已经在Configuration类中定义，其重新定义引起了所有破坏。

jdbcojdbcoraclespring-dataspring-jdbc
本文链接：https://www.f2er.com/2855465.html

大家都在问