如何使用JdbcTemplate(或普通JDBC)从Oracle中获取包含多个字符的列的生成键

92vpleto  于 2023-11-17  发布在  Oracle
关注(0)|答案(2)|浏览(121)

如果我创建一个这样的表：

create table standard (
    id NUMBER GENERATED by default on null as IDENTITY PRIMARY KEY, 
    text varchar2(200)
)

字符串
我可以插入一些数据，并使用如下方法获取生成的id：

Object insertAndReturnStandardId() {

    GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
    template.update("insert into standard (text) values('test text')",new MapSqlParameterSource(), keyHolder, new String[]{"id"});

    return  keyHolder.getKey();
}

型
这里的template是一个NamedParameterJdbcTemplate，但由于没有参数，它应该等价于对JdbcTemplate的类似调用。
但是如果我使用一个稍微不同的表，其中id的列名是小写的：

create table lowercase (
    "id" NUMBER GENERATED by default on null as IDENTITY PRIMARY KEY, 
    text varchar2(200)
)

型
传递列名id失败：

Object insertAndReturnLowerCaseId() {
    GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
    template.update("insert into lowercase (text) values('test text')",new MapSqlParameterSource(), keyHolder, new String[]{"id"});

    return  keyHolder.getKey();
}

型
With（下面是完整的调用堆栈）

PreparedStatementCallback; bad SQL grammar [insert into lowercase (text) values('test text')]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00904: "ID": invalid identifier

型
传递带引号的列名"id"也失败：

private Object insertAndReturnLowerCaseId() {
    GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
    template.update("insert into lowercase (text) values('test text')",new MapSqlParameterSource(), keyHolder, new String[]{"\"id\""});

    return  keyHolder.getKey();
}

型
With（下面是完整的调用堆栈）

PreparedStatementCallback; uncategorized SQLException for SQL [insert into lowercase (text) values('test text')]; SQL state [99999]; error code [17068]; Invalid argument(s) in call; nested exception is java.sql.SQLException: Invalid argument(s) in call

型
于是：当id列是小写时，我如何从Oracle数据库中获取生成的值？基于NamedParameterJdbcTemplate或JdbcTemplate的解决方案是首选，但我也采用基于普通JDBC的答案。

update调用中id不加引号时的完整调用堆栈

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [insert into lowercase (text) values('test text')]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00904: "ID": invalid identifier

    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:893) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:349) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at de.schauderhaft.dbtestclient.DbtestclientApplication.insertAndReturnLowerCaseId(DbtestclientApplication.java:61) ~[classes/:na]
    at de.schauderhaft.dbtestclient.DbtestclientApplication.run(DbtestclientApplication.java:45) ~[classes/:na]
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:795) ~[spring-boot-2.3.0.RELEASE.jar:2.3.0.RELEASE]
    ... 5 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "ID": invalid identifier

    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.executeForRows(T4CPreparedStatement.java:970) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205) ~[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.executeLargeUpdate(OraclePreparedStatement.java:3756) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3736) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1063) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at org.springframework.jdbc.core.JdbcTemplate.lambda$update$1(JdbcTemplate.java:894) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    ... 10 common frames omitted
Caused by: oracle.jdbc.OracleDatabaseException: ORA-00904: "ID": invalid identifier

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    ... 26 common frames omitted

型

update调用中id被引用时的完整调用堆栈

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [insert into lowercase (text) values('test text')]; SQL state [99999]; error code [17068]; Invalid argument(s) in call; nested exception is java.sql.SQLException: Invalid argument(s) in call
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:893) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:349) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at de.schauderhaft.dbtestclient.DbtestclientApplication.insertAndReturnLowerCaseId(DbtestclientApplication.java:61) ~[classes/:na]
    at de.schauderhaft.dbtestclient.DbtestclientApplication.run(DbtestclientApplication.java:45) ~[classes/:na]
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:795) ~[spring-boot-2.3.0.RELEASE.jar:2.3.0.RELEASE]
    ... 5 common frames omitted
Caused by: java.sql.SQLException: Invalid argument(s) in call
    at oracle.jdbc.driver.AutoKeyInfo.getNewSql(AutoKeyInfo.java:189) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:4656) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:230) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    ... 10 common frames omitted

型

oracle

2条答案

按热度按时间
wljmcqd8

wljmcqd81#

有一个JDBC驱动程序错误SR 3-18090632291。一些信息可以在这里找到：

这真的很难解决，因为JDBC驱动程序会篡改SQL字符串，而您对此无能为力。

赞(0）分享  回复(0）举报  2023-11-17
o2g1uqev

o2g1uqev2#

从21 c开始，Oracle JDBC瘦驱动程序支持双引号生成的键。下面的代码片段适用于21 c和23 c驱动程序。对于19 c，它给出错误（Invalid argument(s) in call）。请注意，lowercase表与问题中提到的表相同。

String QUERY = "INSERT INTO lowercase (text) VALUES (?)";
try (PreparedStatement statement = conn.prepareStatement(QUERY, new String[] { "\"id\"" })) {
    statement.setString(1, "Foo");
    int affectedRows = statement.executeUpdate();
    assert (affectedRows==1);
    try (ResultSet keys = statement.getGeneratedKeys()) {
       assert(keys.next());
       System.out.println("keys.getLong(1)="+keys.getLong(1));
    }
      // omitted
}

字符串

赞(0）分享  回复(0）举报  2023-11-17
我来回答

相关问题

    查看更多

    热门标签

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

    最新问答

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