英文:
SQLException when I try to use SQLXML instead of XMLType as output type from Oracle function
问题
我正在尝试重构我的 SpringBoot 应用程序,并且希望在一些与 XMLType 相关的方法已被弃用且我想要重写它的情况下,将其替换为使用 java.sql.SQLXML。我在文档中发现,我应该在替代以下已弃用的代码:
Document doc = xmltype.getDocument();
使用:
DOMSource domSource = sqlxml.getSource(DOMSource.class);
Document document = (Document) domSource.getNode();
但是我遇到了问题。当我尝试这样做时,我会得到一个异常。
这是我代码的一部分片段:
String xmlInStr;
XMLType xmlIn = null;
XMLType xmlOut = null;
SQLXML sqlxmlIn = null;
SQLXML sqlxmlOut = null;
OracleConnection conn = null;
Connection hikariConn = null;
(...)
try {
hikariConn = jdbcTemplate.getDataSource().getConnection();
sqlxmlIn = hikariConn.createSQLXML();
sqlxmlIn.setString(xmlInStr);
logger.debug("输入的 XML 已设置。");
// 输入参数
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withFunctionName("getQuestionnaireByType")
.withReturnValue()
.withoutProcedureColumnMetaDataAccess()
.declareParameters(
new SqlOutParameter("RETURN", OracleTypes.SQLXML),
new SqlParameter("vLang", OracleTypes.VARCHAR),
new SqlParameter("vRefId", OracleTypes.VARCHAR),
new SqlParameter("vSrcId", OracleTypes.VARCHAR),
new SqlParameter("vUserId", OracleTypes.VARCHAR),
new SqlParameter("vQueType", OracleTypes.VARCHAR),
new SqlParameter("vXmlDataIn", OracleTypes.SQLXML)
)
;
jdbcCall.setAccessCallParameterMetaData(false);
jdbcCall.setReturnValueRequired(true);
jdbcCall.withSchemaName("atr_adap");
SqlParameterSource in = new MapSqlParameterSource()
.addValue("vLang", rhData.getLocale().getLanguage())
.addValue("vRefId", rhData.getRequestId())
.addValue("vSrcId", rhData.getSrcId())
.addValue("vUserId", rhData.getUserId())
.addValue("vQueType", queType)
.addValue("vXmlDataIn", sqlxmlIn);
// 调用数据库(使用 XMLType)
xmlOut = jdbcCall.executeFunction(XMLType.class, in);
logger.debug("已执行存储过程 {}(使用 XMLType)", jdbcCall.getProcedureName());
String xmlOutStr = null;
xmlOutStr = xmlOut.getString();
logger.info("xml 输出(使用 XMLType)\r\n{}", xmlOutStr);
doc = xmlOut.getDocument(); // 一切正常(但 getDocument 已弃用 ...)
// 调用数据库(使用 SQLXML)
sqlxmlOut = jdbcCall.executeFunction(java.sql.SQLXML.class, in);
logger.debug("已执行存储过程 {}(使用 SQLXML)", jdbcCall.getProcedureName());
xmlOutStr = sqlxmlOut.getString();
logger.info("xml 输出(使用 SQLXML)\r\n{}", xmlOutStr); // 一切正常
DOMSource domSource = sqlxmlOut.getSource(DOMSource.class); // 从这里抛出以下异常
doc = (Document) domSource.getNode();
(...)
java.sql.SQLException: 尝试读取不可读取的 SQLXML。
at oracle.xdb.XMLType.getSource(XMLType.java:5159)
at my.package.JdbcQuestionnairesRepository.findByType(JdbcQuestionnairesRepository.java:239)
at my.package.JdbcQuestionnairesRepository$$FastClassBySpringCGLIB$$a9555145.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
at my.package.JdbcQuestionnairesRepository$$EnhancerBySpringCGLIB$$a184900c.findByType(<generated>)
at my.package.QuestionnairesService.getQuestionnaireByType(QuestionnairesService.java:48)
(...)
我做错了什么?
最好的问候,
koli
英文:
I am trying to refactor my SpringBoot application and replace using XMLType in favor of java.sql.SQLXML as some of the methods related to XMLType are already deprecated and I want to rewrite it.
I found in the documentation that I should - instead of deprecated:<br />
Document doc = xmltype.getDocument();
use:
DOMSource domSource = sqlxml.getSource (DOMSource.class);
Document document = (Document) domSource.getNode();
But I have a problem with that. When I try to do this, I get an exception.
Here is fragment of my code:
String xmlInStr;
XMLType xmlIn = null;
XMLType xmlOut = null;
SQLXML sqlxmlIn = null;
SQLXML sqlxmlOut = null;
OracleConnection conn = null;
Connection hikariConn = null;
(...)
try {
hikariConn = jdbcTemplate.getDataSource().getConnection();
sqlxmlIn = hikariConn.createSQLXML();
sqlxmlIn.setString(xmlInStr);
logger.debug("Input xml has been set.");
// input params
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
.withFunctionName("getQuestionnaireByType")
.withReturnValue()
.withoutProcedureColumnMetaDataAccess()
.declareParameters(
new SqlOutParameter("RETURN",OracleTypes.SQLXML),
new SqlParameter("vLang",OracleTypes.VARCHAR),
new SqlParameter("vRefId",OracleTypes.VARCHAR),
new SqlParameter("vSrcId",OracleTypes.VARCHAR),
new SqlParameter("vUserId",OracleTypes.VARCHAR),
new SqlParameter("vQueType",OracleTypes.VARCHAR),
new SqlParameter("vXmlDataIn",OracleTypes.SQLXML)
)
;
jdbcCall.setAccessCallParameterMetaData(false);
jdbcCall.setReturnValueRequired(true);
jdbcCall.withSchemaName("atr_adap");
SqlParameterSource in = new MapSqlParameterSource()
.addValue("vLang", rhData.getLocale().getLanguage())
.addValue("vRefId", rhData.getRequestId())
.addValue("vSrcId", rhData.getSrcId())
.addValue("vUserId", rhData.getUserId())
.addValue("vQueType", queType)
.addValue("vXmlDataIn", sqlxmlIn);
// calling db (with XMLType)
xmlOut = jdbcCall.executeFunction(XMLType.class, in);
logger.debug("Stored Procedure {} executed (XMLType)", jdbcCall.getProcedureName());
String xmlOutStr = null;
xmlOutStr = xmlOut.getString();
logger.info("xml out (XMLType)\r\n{}", xmlOutStr);
doc = xmlOut.getDocument(); // everything ok (but getDocument deprecated ...)
// calling db (with SQLXML)
sqlxmlOut = jdbcCall.executeFunction(java.sql.SQLXML.class, in);
logger.debug("Stored Procedure {} executed (SQLXML)", jdbcCall.getProcedureName());
xmlOutStr = sqlxmlOut.getString();
logger.info("xml out (SQLXML)\r\n{}", xmlOutStr); // everything ok
DOMSource domSource = sqlxmlOut.getSource(DOMSource.class); // the following exception is throwing from here
doc = (Document) domSource.getNode();
(...)
java.sql.SQLException: Attempt to read a SQLXML that is not readable.
at oracle.xdb.XMLType.getSource(XMLType.java:5159)
at my.package.JdbcQuestionnairesRepository.findByType(JdbcQuestionnairesRepository.java:239)
at my.package.JdbcQuestionnairesRepository$$FastClassBySpringCGLIB$$a9555145.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
at my.package.JdbcQuestionnairesRepository$$EnhancerBySpringCGLIB$$a184900c.findByType(<generated>)
at my.package.QuestionnairesService.getQuestionnaireByType(QuestionnairesService.java:48)
(...)
What I am doing wrong?
Best regards,
koli
答案1
得分: 0
问题似乎出在这行代码:xmlOutStr = sqlxmlOut.getString();
。删除此行以及将xmlOutStr
写入日志的那行代码,你的代码应该可以工作。
JDBC SQLXML接口的<a href="https://docs.oracle.com/javase/8/docs/api/java/sql/SQLXML.html">JavaDoc</a> 还提到了以下内容:
> 一旦调用了free()或任何读取API(getBinaryStream()、getCharacterStream()、getSource()和getString()),状态从可读更改为不可读。
我无法解释为什么在调用.getString()
之后XMLType.getDocument()
不会抛出异常。
英文:
The problem appears to be the line xmlOutStr = sqlxmlOut.getString();
. Remove this line, and the line which writes xmlOutStr
to the log, and your code should work.
The <a href="https://docs.oracle.com/javase/8/docs/api/java/sql/SQLXML.html">JavaDoc for the JDBC SQLXML interface</a> also mentions the following:
> The state moves from readable to not readable once free() or any of the reading APIs are called: getBinaryStream(), getCharacterStream(), getSource(), and getString().
I can't say why XMLType.getDocument()
doesn't throw an exception even after .getString()
is called.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论