java.sql.SQLException：无效的列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41810305/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
java.sql.SQLException: Invalid column name
提问by Shervin Asgari
I cannot figure out why I am getting "Invalid column name"here.
我不明白为什么我在这里得到“无效的列名”。
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.
我们直接在 Oracle 中尝试了 sql 的一个变体，它工作正常，但是当我使用 jdbcTemplate 尝试它时，出现了问题。
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();
});
The entire sql that works on Oracle is this:
在 Oracle 上运行的整个 sql 是这样的：
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
The entire stacktrace:
整个堆栈跟踪：
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 at org.apache.commons.dbcp2.DelegatingResultSet.getString(DelegatingResultSet.java:267)
at no.gjensidige.bank.datavarehus.kontonrinfridd.Application.lambda$runCFUSERENGINE51.PROCESSENGINE.applicationid,
CFUSERENGINE51.PROCESSENGINE.lastupdatetime
CFUSERENGINE51.PROCESSENGINE.phasecacheid
CFUSERENGINE51.PROCESSENGINE.processengineguid
CFUSERENGINE51.DATADOCUMENTXML.datadocumentid
CFUSERENGINE51.DATADOCUMENTXML.datadocumentxml
CFUSERENGINE51.DATADOCUMENTXML.processengineguid
(Application.java:69)
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93)
(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.JdbcTemplateQueryStatementCallback.doInStatement(JdbcTemplate.java:463)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408)
... 16 more
采纳答案by Luke Woodward
The problem isn't the query. The query is running fine.
问题不在于查询。查询运行良好。
The problem is in the row-mapping that converts a row from the ResultSetinto a domain object. It seems that as part of the row-mapping in your application you are trying to read out of the ResultSeta value from a column that it doesn't contain.
问题在于将行从ResultSet转换为域对象的行映射。似乎作为应用程序中行映射的一部分，您试图ResultSet从它不包含的列中读出a 值。
The key lines of your stacktrace are the following three, near the bottom:
堆栈跟踪的关键行是以下三行，靠近底部：
##代码##The middle of these three lines would appear to be in your code. Line 69 of your Applicationclass 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.
这三行的中间似乎在您的代码中。您Application班级的第 69 行包含一个正在调用 的 lambda ResultSet.getString()，但由于这会导致“无效的列名”错误，那么 (a) 您传递的是列名的字符串而不是数字列索引，以及 (b)结果集中不存在您传入的列名。
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").
既然您已经编辑了您的问题以包含对 的调用jdbcTemplate.query()，特别是负责将结果集行映射到对象的 lambda，问题就更清楚了。在调用rs.getInt(...)或rs.getString(...)使用列名而不是索引时，不要包含前缀，例如p.或x.。而不是写rs.getInt("p.applicationid")or rs.getInt("x.datadocumentid")，写rs.getInt("applicationid")or rs.getInt("datadocumentid")。
回答by sax
You need the table definition to find out where the problem is. Install / run Oracle SQL Developer(it is free), setup JDBC connection and investigate the schema.
您需要表定义来找出问题所在。安装/运行Oracle SQL Developer（它是免费的），设置 JDBC 连接并调查模式。
You need to check existence of following columns:
您需要检查以下列是否存在：
##代码##