Returning XML from query result in servlet

Question

I'm trying to return an XML file based on my query results. I'm very new to this so I'm not really sure where I'm going wrong. Is this a realistic way to go about doing this or is there something simpler? Right now I'm getting these exceptions:

Error performing query: javax.servlet.ServletException: org.xml.sax.SAXParseException: Content is not allowed in prolog.

If I run my query in isql*plus, it does execute

import java.io.*;
import java.util.*;
import java.sql.*;           // JDBC packages
import javax.servlet.*;
import javax.servlet.http.*;
import javax.xml.parsers.*;
import org.xml.sax.*;
import org.xml.sax.helpers.*;

public class step5 extends HttpServlet {

   public static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
   public static final String URL = "jdbc:odbc:rreOracle";
   public static final String username = "cm485a10";
   public static final String password = "y4e8f7s5";

   SAXParserFactory factory;

   public void init() throws ServletException {

      factory = SAXParserFactory.newInstance();
   }

   public void doGet (HttpServletRequest  request,
                      HttpServletResponse response)
      throws ServletException, IOException
      {
      PrintWriter out = response.getWriter();
      Connection con = null;

      try {

            Class.forName(DRIVER);

            con = DriverManager.getConnection(URL,username,password);

            try {
               Statement stmt = con.createStatement();
               ResultSet rs = stmt.executeQuery("SELECT sale_id, home_id, agent_id, customer_id FROM sale");

               String xml = "";
               xml = xml + "<sales_description>";
               xml = xml + "<sale>";

               boolean courseDataDone = false;
               while (rs.next()) {
                  String sale_id = rs.getString(1);
                  String home_id = rs.getString(2);
                  String agent_id = rs.getString(3);
                  String customer_id = rs.getString(4);

                  if (!courseDataDone) {
                     xml = xml + "<sale_id>" + sale_id + "</sale_id>" +
                                 "<home_id>" + home_id + "</home_id>" +
                                 "<agent_id>" + agent_id + "</agent_id>" +
                                 "<customer_id>" + customer_id + "</customer_id>" +
                                 "" +
                                 "";
                     courseDataDone = true;
                  }

               }

               xml = xml + "</sale>" +
                           "</sales_description>";

               try {
                  SAXParser parser = factory.newSAXParser();
                  InputSource input = new InputSource(new StringReader(xml));
                  parser.parse(input, new DefaultHandler());
               } catch (ParserConfigurationException e) {
                  throw new ServletException(e);
               } catch (SAXException e) {
                  throw new ServletException(e);
               }

               response.setContentType("text/xml;charset=UTF-8");
               out.write(xml);

            } catch(Exception ex) {
               out.println("Error performing query: " + ex);
               con.close();
               return;
            }

         } catch(Exception ex) {
            out.println("Error performing DB connection: " + ex);
            return;
         }

   }
}

Any help/tips would be appreciated.

Answer 1

You're missing the prolog. Add this to beginning of your XML:

<?xml version="1.0" encoding="UTF-8"?>

By the way, you don't need the SAX parser here. You aren't modifying the XML at all. Get rid of the parser and just write xml directly to the response. You are also not handling JDBC resources correctly in try-with-resources. Here's a basic example of the improvement:

response.setContentType("text/xml;charset=UTF-8");
PrintWriter writer = response.getWriter();
writer.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
writer.append("<sales_description>");

try (
    Connection connection = dataSource.getConnection();
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery("SELECT sale_id, home_id, agent_id, customer_id FROM sale");
) {
    if (resultSet.next()) {
        writer.append("<sale>");
        writer.append("<sale_id>").append(resultSet.getString("sale_id")).append("</sale_id>");
        writer.append("<home_id>").append(resultSet.getString("home_id")).append("</home_id>");
        writer.append("<agent_id>").append(resultSet.getString("agent_id")).append("</agent_id>");
        writer.append("</sale>");
    }
} catch (SQLException e) {
    throw new ServletException(e);
}

writer.append("</sales_description>");

To write all records, just replace if (resultSet.next()) by while (resultSet.next()).

To handle the exception more gracefully, i.e. throwing an ServletException which ends in an error page instead of a halfbaked XML, you'd like to build the XML using StringBuilder. Just replace PrintWriter by new StringBuilder() and then at end, do response.getWriter().write(builder.toString()).