SQLException when I try to use SQLXML instead of XMLType as output type from Oracle function

huangapple
go评论135阅读模式
英文:

SQLException when I try to use SQLXML instead of XMLType as output type from Oracle function

问题

我正在尝试重构我的 SpringBoot 应用程序，并且希望在一些与 XMLType 相关的方法已被弃用且我想要重写它的情况下，将其替换为使用 java.sql.SQLXML。我在文档中发现，我应该在替代以下已弃用的代码：

  1. Document doc = xmltype.getDocument();

使用：

  1. DOMSource domSource = sqlxml.getSource(DOMSource.class);
  2. Document document = (Document) domSource.getNode();

但是我遇到了问题。当我尝试这样做时，我会得到一个异常。

这是我代码的一部分片段：

  1.             String xmlInStr;  
  2.             XMLType xmlIn = null;  
  3.             XMLType xmlOut = null;  
  4.             SQLXML sqlxmlIn = null;  
  5.             SQLXML sqlxmlOut = null;  
  6.             OracleConnection conn = null;  
  7.             Connection hikariConn = null;  
  8.     (...)  
  9.             try {  
  10.                 hikariConn = jdbcTemplate.getDataSource().getConnection();  
  11.                 sqlxmlIn = hikariConn.createSQLXML();  
  12.                 sqlxmlIn.setString(xmlInStr);  
  13.                 logger.debug("输入的 XML 已设置。");  
  14.                 // 输入参数  
  15.                 SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)  
  16.       
  17.                     .withFunctionName("getQuestionnaireByType")  
  18.                     .withReturnValue()  
  19.                     .withoutProcedureColumnMetaDataAccess()  
  20.                     .declareParameters(  
  21.                             new SqlOutParameter("RETURN", OracleTypes.SQLXML),  
  22.                             new SqlParameter("vLang", OracleTypes.VARCHAR),  
  23.                             new SqlParameter("vRefId", OracleTypes.VARCHAR),  
  24.                             new SqlParameter("vSrcId", OracleTypes.VARCHAR),  
  25.                             new SqlParameter("vUserId", OracleTypes.VARCHAR),  
  26.                             new SqlParameter("vQueType", OracleTypes.VARCHAR),  
  27.                             new SqlParameter("vXmlDataIn", OracleTypes.SQLXML)  
  28.                     )  
  29.                 ;  
  30.                 jdbcCall.setAccessCallParameterMetaData(false);  
  31.                 jdbcCall.setReturnValueRequired(true);  
  32.                 jdbcCall.withSchemaName("atr_adap");  
  33.       
  34.                 SqlParameterSource in = new MapSqlParameterSource()  
  35.                         .addValue("vLang", rhData.getLocale().getLanguage())  
  36.                         .addValue("vRefId", rhData.getRequestId())  
  37.                         .addValue("vSrcId", rhData.getSrcId())  
  38.                         .addValue("vUserId", rhData.getUserId())  
  39.                         .addValue("vQueType", queType)  
  40.                         .addValue("vXmlDataIn", sqlxmlIn);  
  41.                           
  42.                 // 调用数据库（使用 XMLType）  
  43.                 xmlOut = jdbcCall.executeFunction(XMLType.class, in);  
  44.                   
  45.                 logger.debug("已执行存储过程 {}（使用 XMLType）", jdbcCall.getProcedureName());  
  46.                 String xmlOutStr = null;  
  47.                 xmlOutStr = xmlOut.getString();  
  48.                 logger.info("xml 输出（使用 XMLType）\r\n{}", xmlOutStr);  
  49.                 doc = xmlOut.getDocument(); // 一切正常（但 getDocument 已弃用 ...）  
  50.                   
  51.                 // 调用数据库（使用 SQLXML）  
  52.                 sqlxmlOut = jdbcCall.executeFunction(java.sql.SQLXML.class, in);  
  53.                 logger.debug("已执行存储过程 {}（使用 SQLXML）", jdbcCall.getProcedureName());  
  54.                 xmlOutStr = sqlxmlOut.getString();  
  55.                 logger.info("xml 输出（使用 SQLXML）\r\n{}", xmlOutStr); // 一切正常  
  56.                 DOMSource domSource = sqlxmlOut.getSource(DOMSource.class); // 从这里抛出以下异常  
  57.                 doc = (Document) domSource.getNode();   
  58. (...) 
  1. java.sql.SQLException: 尝试读取不可读取的 SQLXML。 
  2. 	at oracle.xdb.XMLType.getSource(XMLType.java:5159)
  3. 	at my.package.JdbcQuestionnairesRepository.findByType(JdbcQuestionnairesRepository.java:239)
  4. 	at my.package.JdbcQuestionnairesRepository$$FastClassBySpringCGLIB$$a9555145.invoke(<generated>)
  5. 	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
  6. 	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746)
  7. 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
  8. 	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
  9. 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
  10. 	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
  11. 	at my.package.JdbcQuestionnairesRepository$$EnhancerBySpringCGLIB$$a184900c.findByType(<generated>)
  12. 	at my.package.QuestionnairesService.getQuestionnaireByType(QuestionnairesService.java:48)
  13. (...)

我做错了什么？

最好的问候，
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 />

  1. Document doc = xmltype.getDocument();

use:

  1. DOMSource domSource = sqlxml.getSource (DOMSource.class);
  2. 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:

  1.             String xmlInStr;  
  2.             XMLType xmlIn = null;  
  3.             XMLType xmlOut = null;  
  4.             SQLXML sqlxmlIn = null;  
  5.             SQLXML sqlxmlOut = null;  
  6.             OracleConnection conn = null;  
  7.             Connection hikariConn = null;  
  8.     (...)  
  9.             try {  
  10.                 hikariConn = jdbcTemplate.getDataSource().getConnection();  
  11.                 sqlxmlIn = hikariConn.createSQLXML();  
  12.                 sqlxmlIn.setString(xmlInStr);  
  13.                 logger.debug(&quot;Input xml has been set.&quot;);  
  14.                 // input params  
  15.                 SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)  
  16.       
  17.                     .withFunctionName(&quot;getQuestionnaireByType&quot;)  
  18.                     .withReturnValue()  
  19.                     .withoutProcedureColumnMetaDataAccess()  
  20.                     .declareParameters(  
  21.                             new SqlOutParameter(&quot;RETURN&quot;,OracleTypes.SQLXML),  
  22.                             new SqlParameter(&quot;vLang&quot;,OracleTypes.VARCHAR),  
  23.                             new SqlParameter(&quot;vRefId&quot;,OracleTypes.VARCHAR),  
  24.                             new SqlParameter(&quot;vSrcId&quot;,OracleTypes.VARCHAR),  
  25.                             new SqlParameter(&quot;vUserId&quot;,OracleTypes.VARCHAR),  
  26.                             new SqlParameter(&quot;vQueType&quot;,OracleTypes.VARCHAR),  
  27.                             new SqlParameter(&quot;vXmlDataIn&quot;,OracleTypes.SQLXML)  
  28.                     )  
  29.                 ;  
  30.                 jdbcCall.setAccessCallParameterMetaData(false);  
  31.                 jdbcCall.setReturnValueRequired(true);  
  32.                 jdbcCall.withSchemaName(&quot;atr_adap&quot;);  
  33.       
  34.                 SqlParameterSource in = new MapSqlParameterSource()  
  35.                         .addValue(&quot;vLang&quot;, rhData.getLocale().getLanguage())  
  36.                         .addValue(&quot;vRefId&quot;, rhData.getRequestId())  
  37.                         .addValue(&quot;vSrcId&quot;, rhData.getSrcId())  
  38.                         .addValue(&quot;vUserId&quot;, rhData.getUserId())  
  39.                         .addValue(&quot;vQueType&quot;, queType)  
  40.                         .addValue(&quot;vXmlDataIn&quot;, sqlxmlIn);  
  41.                           
  42.                 // calling db (with XMLType)  
  43.                 xmlOut = jdbcCall.executeFunction(XMLType.class, in);  
  44.                   
  45.                 logger.debug(&quot;Stored Procedure {} executed (XMLType)&quot;, jdbcCall.getProcedureName());  
  46.                 String xmlOutStr = null;  
  47.                 xmlOutStr = xmlOut.getString();  
  48.                 logger.info(&quot;xml out (XMLType)\r\n{}&quot;, xmlOutStr);  
  49.                 doc = xmlOut.getDocument(); // everything ok (but getDocument deprecated ...)  
  50.                   
  51.                 // calling db (with SQLXML)  
  52.                 sqlxmlOut = jdbcCall.executeFunction(java.sql.SQLXML.class, in);  
  53.                 logger.debug(&quot;Stored Procedure {} executed (SQLXML)&quot;, jdbcCall.getProcedureName());  
  54.                 xmlOutStr = sqlxmlOut.getString();  
  55.                 logger.info(&quot;xml out (SQLXML)\r\n{}&quot;, xmlOutStr); // everything ok  
  56.                 DOMSource domSource = sqlxmlOut.getSource(DOMSource.class); // the following exception is throwing from here  
  57.                 doc = (Document) domSource.getNode();   
  58. (...) 
  1. java.sql.SQLException: Attempt to read a SQLXML that is not readable. 
  2. 	at oracle.xdb.XMLType.getSource(XMLType.java:5159)
  3. 	at my.package.JdbcQuestionnairesRepository.findByType(JdbcQuestionnairesRepository.java:239)
  4. 	at my.package.JdbcQuestionnairesRepository$$FastClassBySpringCGLIB$$a9555145.invoke(&lt;generated&gt;)
  5. 	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
  6. 	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746)
  7. 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
  8. 	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
  9. 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
  10. 	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
  11. 	at my.package.JdbcQuestionnairesRepository$$EnhancerBySpringCGLIB$$a184900c.findByType(&lt;generated&gt;)
  12. 	at my.package.QuestionnairesService.getQuestionnaireByType(QuestionnairesService.java:48)
  13. (...)

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.

huangapple
  • 本文由 发表于 2020年9月5日 02:05:29
  • 转载请务必保留本文链接：https://go.coder-hub.com/63746089.html
  • java
  • spring-boot
  • sqlxml
  • xmltype
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定