Announcement Announcement Module
Collapse
No announcement yet.
Problem querying Oracle XMLType Using Spring Query Methods Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem querying Oracle XMLType Using Spring Query Methods

    Hello,

    I have a class that extends JdbcDaoSupport. When I query an Oracle table for XML data (in a column of type XMLTYPE) using getJdbcTemplate().queryForMap I get the following exception:

    Code:
    java.lang.NullPointerException
    	at oracle.xdb.XMLType.getConnType(XMLType.java:2308)
    	at oracle.xdb.XMLType.initConn(XMLType.java:2290)
    	at oracle.xdb.XMLType.<init>(XMLType.java:945)
    	at oracle.xdb.XMLType.createXML(XMLType.java:525)
    But, when I query it using pure JDBC everything works fine. I have retrieved values from different (non-xml) columns in the very same query. So, it seems to only be a problem with the XMLTYPE column. Below are the two versions of the same method.

    Spring Version:
    Code:
        public Document getTargetDiagnosticXml(String sql, Object[] params)
            throws RemoteException {
    
            Map tdMap = getJdbcTemplate().queryForMap(sql,params);
    
            try {
                oracle.sql.OPAQUE value = (oracle.sql.OPAQUE)tdMap.get("TD_XML");
                oracle.xdb.XMLType xml = oracle.xdb.XMLType.createXML(value);
                return xml.getDOM();
            } catch(Throwable t) {
                throw new RemoteException(t.getMessage());
            }
        }
    Pure JDBC Method:
    Code:
        public Document getTargetDiagnosticXml(String sql)
            throws RemoteException {
            XMLType xml = null;
    
            Connection con = null;
            Statement stmt = null;
            OracleResultSet rs = null;
    
            try {
                con = getConnection(); //from JdbcTemplate
                stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                    ResultSet.CONCUR_READ_ONLY);
                rs = (OracleResultSet)stmt.executeQuery(sql);
                if(rs.next()) {
                    xml = XMLType.createXML(rs.getOPAQUE("TD_XML"));
                }
                return xml.getDOM();
    
            } catch(Throwable t) {
                throw new RemoteException(t.getMessage());
            }
        }
    Can someone look at the Spring version and tell me if I am doing it correctly or if there is an alternative way of doing it? I would prefer not to have to resort to the pure JDBC method. Thanks in advance.
    Last edited by srhahn; Jun 10th, 2008, 05:50 PM.

  • #2
    Solution

    Thanks to a colleague of mine, I was able to solve this problem by using a RowMapper. Below is the snippet in case anyone else runs into the same problem. By the way, if anyone can shed some light on why this way works and the other does not, it would be very helpful.

    Code:
    public Document getTargetDiagnosticXml(String sql, Object[] params) throws RemoteException {
    
      try {
        Document doc = (Document) getJdbcTemplate().queryForObject(sql, params, new RowMapper() {
            public Object mapRow(ResultSet rs, int rowNum)
              throws SQLException {
    
              XMLType xml = XMLType.createXML((oracle.sql.OPAQUE)rs.getObject("TD_XML"));
              return xml.getDOM();
            }
        });
        return doc;
      } catch (Exception e) {
        throw new RemoteException(e.getMessage());
      }
    }

    Comment

    Working...
    X