import java.sql.Connection; import org.w3c.dom.Document; import org.xml.sax.SAXException; import oracle.jdbc.driver.*; import java.sql.*; import oracle.sql.*; import java.net.URL; import java.io.*; import java.sql.*; import XMLDocURLStreamHandlerFactory; import XMLHelper; import ReadCLOB; import WriteCLOB; public class XMLDocuments { // get: Read an XML document from the xml_documents table. public static Document get(Connection conn,String idVal) throws FileNotFoundException, SAXException { Reader r = getReader(conn,idVal); try { return r != null ? XMLHelper.parse(r,null) : null; } catch (FileNotFoundException fnf) { throw fnf; } catch (IOException iox) { } return null; } // Return Reader on XML document named 'docname' from xml_documents table public static Reader getReader(Connection conn, String docname) throws FileNotFoundException { return ReadCLOB.fromColumn(conn,"xml_documents","xmldoc","docname",docname); } // Return InputStream on XML document named 'docname' from xml_documents public static InputStream getInputStream(Connection conn, String docname) throws FileNotFoundException { return ReadCLOB.fromColumnAsInputStream(conn,"xml_documents","xmldoc", "docname",docname); } // Save contents of a Reader into xml_documents with doc name of 'docname' public static void save(Connection conn,String docname,Reader input) throws SQLException, SAXException { // Delete existing row if present delete(conn,docname); // Insert a new row with empty_clob() CallableStatement stmt = conn.prepareCall( "BEGIN " + " INSERT INTO xml_documents( docname, xmldoc) " + " VALUES( ?, empty_clob()) "+ " RETURNING xmldoc INTO ?;" + // Use RETURNING...INTO to get CLOB "END;"); stmt.setString(1,docname); // Bind var in VALUES() stmt.registerOutParameter(2,OracleTypes.CLOB); // RETURNING INTO stmt.execute(); // Do it // Retrieve the returned values of CLOB locator CLOB theXMLClob = ((OracleCallableStatement)stmt).getCLOB(2); stmt.close(); // Write the input to the CLOB WriteCLOB.fromReader( input, theXMLClob ); // Commit the changes and close the connection. conn.commit(); } // Delete XML document named 'docname' from xml_documents public static void delete(Connection conn,String docname) throws SQLException{ PreparedStatement stmt = conn.prepareStatement("DELETE FROM xml_documents"+ " WHERE docname = ?"); stmt.setString(1,docname); stmt.execute(); stmt.close(); conn.commit(); } // Print a list of documents in xml_documents matching 'docname' // Allow either % or * as wildcard character in the name. public static void list(Connection conn,String docname,PrintWriter out) throws SQLException { PreparedStatement ps = conn.prepareStatement("SELECT docname,TO_CHAR(timestamp,'Mon DD HH24:MI')" +" FROM xml_documents" +" WHERE docname LIKE REPLACE(?,'*','%')||'%'" +" ORDER BY docname"); ps.setString(1,docname); ResultSet rs = ps.executeQuery(); while (rs.next()) { out.println(rs.getString(2)+" "+rs.getString(1)); } ps.close(); } // Enable the use of xmldoc:/dir1/dir2/file.xml URL's in this session public static void enableXMLDocURLs() { try { // Give *our* handler first chance to handle URL.openConnection() requests URL.setURLStreamHandlerFactory(new XMLDocURLStreamHandlerFactory()); } catch(java.lang.Error alreadyDefined) { /* Ignore */ } } } |