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

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

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(&quot;Input xml has been set.&quot;);  
                // input params  
                SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)  
      
                    .withFunctionName(&quot;getQuestionnaireByType&quot;)  
                    .withReturnValue()  
                    .withoutProcedureColumnMetaDataAccess()  
                    .declareParameters(  
                            new SqlOutParameter(&quot;RETURN&quot;,OracleTypes.SQLXML),  
                            new SqlParameter(&quot;vLang&quot;,OracleTypes.VARCHAR),  
                            new SqlParameter(&quot;vRefId&quot;,OracleTypes.VARCHAR),  
                            new SqlParameter(&quot;vSrcId&quot;,OracleTypes.VARCHAR),  
                            new SqlParameter(&quot;vUserId&quot;,OracleTypes.VARCHAR),  
                            new SqlParameter(&quot;vQueType&quot;,OracleTypes.VARCHAR),  
                            new SqlParameter(&quot;vXmlDataIn&quot;,OracleTypes.SQLXML)  
                    )  
                ;  
                jdbcCall.setAccessCallParameterMetaData(false);  
                jdbcCall.setReturnValueRequired(true);  
                jdbcCall.withSchemaName(&quot;atr_adap&quot;);  
      
                SqlParameterSource in = new MapSqlParameterSource()  
                        .addValue(&quot;vLang&quot;, rhData.getLocale().getLanguage())  
                        .addValue(&quot;vRefId&quot;, rhData.getRequestId())  
                        .addValue(&quot;vSrcId&quot;, rhData.getSrcId())  
                        .addValue(&quot;vUserId&quot;, rhData.getUserId())  
                        .addValue(&quot;vQueType&quot;, queType)  
                        .addValue(&quot;vXmlDataIn&quot;, sqlxmlIn);  
                          
                // calling db (with XMLType)  
                xmlOut = jdbcCall.executeFunction(XMLType.class, in);  
                  
                logger.debug(&quot;Stored Procedure {} executed (XMLType)&quot;, jdbcCall.getProcedureName());  
                String xmlOutStr = null;  
                xmlOutStr = xmlOut.getString();  
                logger.info(&quot;xml out (XMLType)\r\n{}&quot;, xmlOutStr);  
                doc = xmlOut.getDocument(); // everything ok (but getDocument deprecated ...)  
                  
                // calling db (with SQLXML)  
                sqlxmlOut = jdbcCall.executeFunction(java.sql.SQLXML.class, in);  
                logger.debug(&quot;Stored Procedure {} executed (SQLXML)&quot;, jdbcCall.getProcedureName());  
                xmlOutStr = sqlxmlOut.getString();  
                logger.info(&quot;xml out (SQLXML)\r\n{}&quot;, 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(&lt;generated&gt;)
	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(&lt;generated&gt;)
	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.

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

发表评论

匿名网友

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

确定