2 Pull News Items from the Database

The following code, from xmlnews.admin.AdminServlet.performGeneration and xmlnews.admin.AdminServlet.staticProcessingHtml, shows how the application queries the database for news items in each available category and converts each result set to a XML document.

The database stores the XML for each category as a CLOB (Character Large OBject), so the application can handle very long lists.

public void performGeneration(String p_user, String p_genType,
      HttpServletResponse p_response)
      throws ServletException, IOException {
...
      try {
      String l_fileSep = System.getProperty("file.separator");
      String l_message = ""; // Holds status message

      if (p_genType.equals("BATCH_GEN")) { // Batch Generation
      String l_htmlFile = "BatchGeneration";
      String l_xslFile  = "BatchGeneration";
      String l_xmlFile  = "BatchGeneration";

      // Generate the XML and HTML content and save it in a file
         this.staticProcessingHtml(
         m_dynNewsEnv.m_dynNewsHome+l_fileSep+l_htmlFile+".html",
         m_dynNewsEnv.m_dynNewsHome+l_fileSep+m_dynNewsEnv.m_batchGenXSL,
         m_dynNewsEnv.m_dynNewsHome+l_fileSep+l_xmlFile+".xml"
      );
      ...
    }
 ...
 }

The method xmlnews.admin.AdminServlet.staticProcessingHtml defines and executes a query to fetch the news items. Then it uses the Oracle XML-SQL Utility (XSU) to build an XML document from the result set and create an HTML page by applying an XSLT transformation.

public void staticProcessingHtml(String p_htmlFile,String p_xslfile,
   String p_xmlfile) throws Exception {
   String l_query = "select a.id, a.title, a.URL, a.DESCRIPTION, " +
   " to_char(a.ENTRY_DATE, 'DD-MON-YYYY'), a.CATEGORY_ID, b.name, 
                   a.SUB_CATEGORY_ID, c.name, a.Type_Id, d.name, " +
" a.Submitted_By_Id, e.name, to_char(a.expiration_date, 'DD-MON-YYYY'),
                                                            a.approved_flag " +
" from news_items a, categories b, sub_categories c, types d, users e where " +
" a.category_id is not null and a.sub_category_id is not null and "+
" a.type_id is not null and a.EXPIRATION_DATE is not null and "+
" a.category_id = b.id  AND a.SUB_CATEGORY_ID = c.id AND a.Type_ID = d.id 
                          AND " +
" a.SUBMITTED_BY_ID = e.id AND "+
" a.EXPIRATION_DATE > SYSDATE AND "+
" a.APPROVED_FLAG = \'A\' ORDER BY b.name, c.name ";

 Statement l_stmt = m_connection.createStatement();
 ResultSet l_result = l_stmt.executeQuery(l_query);
// Construct the XML Document using Oracle XML SQL Utility
   XMLDocument l_xmlDocument = m_xmlHandler.constructXMLDoc(l_result);
   l_stmt.close();

// Get the HTML String by applying corresponding XSL to XML.
   String l_htmlString = m_xmlHandler.applyXSLtoXML(l_xmlDocument,p_xslfile);

 File l_file = new File(p_htmlFile);
 FileOutputStream l_fileout = new FileOutputStream(l_file);
 FileOutputStream l_xmlfileout = new FileOutputStream(new File(p_xmlfile));.
 l_fileout.write(l_htmlString.getBytes());
 l_xmlDocument.print(l_xmlfileout);

 l_fileout.close();
 l_xmlfileout.close();
}