java.sql.SQLException: Invalid column name

Question

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.

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:

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$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

Answer 1

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

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.

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)

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.

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").