java.sql.SQLException:无效的列名 [英] java.sql.SQLException: Invalid column name

问题描述

我无法弄清楚为什么我在这里得到无效的列名" .

I cannot figure out why I am getting "Invalid column name" here.

我们已经在Oracle中直接尝试了sql的变体，并且运行良好，但是当我使用jdbcTemplate尝试时，出了点问题.

We have tried a variant of the sql directly in Oracle, and it works fine, but when I try it using jdbcTemplate then something is wrong.

List<Dataholder> alleXmler = jdbcTemplate.query("select p.applicationid, x.datadocumentid, x.datadocumentxml " +
                        "from CFUSERENGINE51.PROCESSENGINE p " +
                        "left join CFUSERENGINE51.DATADOCUMENTXML x " +
                        "on p.processengineguid = x.processengineguid " +
                        "where x.datadocumentid = 'Disbursment' " +
                        "and p.phasecacheid = 'Disbursed' ",
                (rs, rowNum) -> {
                    return Dataholder.builder()
                            .applicationid(rs.getInt("p.applicationid"))
                            .datadocumentId(rs.getInt("x.datadocumentid"))
                            .xml(lobHandler.getClobAsString(rs, "x.datadocumentxml"))
                            .build();
                });

在Oracle上运行的整个sql是这样的:

The entire sql that works on Oracle is this:

select
process.applicationid,
xml.datadocumentid,
xml.datadocumentxml
from CFUSERENGINE51.PROCESSENGINE process
left join CFUSERENGINE51.DATADOCUMENTXML xml
on process.processengineguid = xml. processengineguid
where xml.datadocumentid = 'Disbursment'
and process.phasecacheid = 'Disbursed'
and process.lastupdatetime > sysdate-14

整个堆栈跟踪:

java.lang.reflect.InvocationTargetException
    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.boot.maven.AbstractRunMojo$LaunchRunner.run(AbstractRunMojo.java:507)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.IllegalStateException: Failed to execute CommandLineRunner
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:803)
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:784)
    at org.springframework.boot.SpringApplication.afterRefresh(SpringApplication.java:771)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:316)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1186)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1175)
    at no.gjensidige.bank.datavarehus.kontonrinfridd.Application.main(Application.java:44)
    ... 6 more
Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select p.applicationid, x.datadocumentid, x.datadocumentxml from CFUSERENGINE51.PROCESSENGINE p left join CFUSERENGINE51.DATADOCUMENTXML x on p.processengineguid = x.processengineguid where x.datadocumentid = 'Disbursment' ]; nested exception is java.sql.SQLException: Invalid column name
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:419)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:474)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:484)
    at no.gjensidige.bank.datavarehus.kontonrinfridd.Application.run(Application.java:61)
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:800)
    ... 12 more
Caused by: java.sql.SQLException: Invalid column name
    at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:4146)
    at oracle.jdbc.driver.InsensitiveScrollableResultSet.findColumn(InsensitiveScrollableResultSet.java:300)
    at oracle.jdbc.driver.GeneratedResultSet.getString(GeneratedResultSet.java:1460)
    at org.apache.commons.dbcp2.DelegatingResultSet.getString(DelegatingResultSet.java:267)
    at org.apache.commons.dbcp2.DelegatingResultSet.getString(DelegatingResultSet.java:267)
    at no.gjensidige.bank.datavarehus.kontonrinfridd.Application.lambda$run$0(Application.java:69)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:60)
    at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:463)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408)
    ... 16 more

推荐答案

问题不是查询.查询运行正常.

The problem isn't the query. The query is running fine.

问题在于行映射将行从ResultSet转换为域对象.看来，在您的应用程序中，作为行映射的一部分，您试图从其不包含的列中读取ResultSet值.

The problem is in the row-mapping that converts a row from the ResultSet into a domain object. It seems that as part of the row-mapping in your application you are trying to read out of the ResultSet a value from a column that it doesn't contain.

stacktrace的关键行是靠近底部的以下三行:

The key lines of your stacktrace are the following three, near the bottom:

    at org.apache.commons.dbcp2.DelegatingResultSet.getString(DelegatingResultSet.java:267)
    at no.gjensidige.bank.datavarehus.kontonrinfridd.Application.lambda$run$0(Application.java:69)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93)

这三行的中间似乎在您的代码中. Application类的第69行包含一个正在调用ResultSet.getString()的lambda，但是由于这会导致无效的列名"错误，因此(a)您正在传递一个用于列名而不是数字列索引的字符串，以及(b)您要传递的列名在结果集中不存在.

The middle of these three lines would appear to be in your code. Line 69 of your Application class contains a lambda which is calling ResultSet.getString(), but as this results in an 'Invalid column name' error, then (a) you are passing a string for a column name rather than a numeric column index, and (b) the column name you're passing in doesn't exist in the result set.

现在，您已经编辑了问题以包括对jdbcTemplate.query()的调用，尤其是负责将结果集行映射到对象的lambda，这个问题更加清楚了.当使用列名而不是索引来调用rs.getInt(...)或rs.getString(...)时，请勿包含诸如p.或x.之类的前缀.代替写rs.getInt("p.applicationid")或rs.getInt("x.datadocumentid")，写rs.getInt("applicationid")或rs.getInt("datadocumentid").

Now that you've edited your question to include the call to jdbcTemplate.query(), and in particular the lambda responsible for mapping a result-set row to an object, the problem is a little clearer. When calling rs.getInt(...) or rs.getString(...) with column names as opposed to indexes, don't include prefixes such as p. or x.. Instead of writing rs.getInt("p.applicationid") or rs.getInt("x.datadocumentid"), write rs.getInt("applicationid") or rs.getInt("datadocumentid").

这篇关于java.sql.SQLException:无效的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！