Returning XML from query result in servlet Returning XML from query result in servlet oracle oracle

Returning XML from query result in servlet


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()).


One tip would be layering your code a bit more. Servlets shouldn't be importing from java.sql. Put that code in a separate class, test it, and let your servlet call its methods.

You're creating XML in the most brain dead way possible by concatentating strings that way. Why not use a library like JDOM or at least a StringBuilder?

And skaffman's right: your code makes no sense otherwise.

Here are a few ideas you can think about for layering. Start with a model object for Sale - after all, Java's an object-oriented language:

package badservlet.model;public class Sale{    private String saleId;    private String homeId;    private String agentId;    private String customerId;    public Sale(String saleId, String homeId, String agentId, String customerId)    {        if ((saleId == null) || (saleId.trim().length() == 0)            throw new IllegalArgumentException("sales id cannot be blank or null");        if ((homeId == null) || (homeId.trim().length() == 0)            throw new IllegalArgumentException("home id cannot be blank or null");        if ((agentId == null) || (agentId.trim().length() == 0)            throw new IllegalArgumentException("agent id cannot be blank or null");        if ((customerId == null) || (customerId.trim().length() == 0)            throw new IllegalArgumentException("customer id cannot be blank or null");        this.saleId = saleId;        this.homeId = homeId;        this.agentId = agentId;        this.customerId = customerId;    }    public String getSaleId()    {        return saleId;    }    public String getHomeId()    {        return homeId;    }    public String getAgentId()    {        return agentId;    }    public String getCustomerId()    {        return customerId;    }    @Override    public String toString()    {        return "Sale{" +               "saleId='" + saleId + '\'' +               ", homeId='" + homeId + '\'' +               ", agentId='" + agentId + '\'' +               ", customerId='" + customerId + '\'' +               '}';    }}

For persistence, start with a DAO interface:

package badservlet.persistence;import badservlet.model.Sale;import java.sql.SQLException;import java.util.List;public interface SaleDao{    List<Sale> find() throws SQLException;}

And its implementation:

package badservlet.persistence;import badservlet.model.Sale;import javax.sql.DataSource;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;public class SaleDaoImpl implements SaleDao{    private static final String SELECT_ALL_SQL = "SELECT sale_id, home_id, agent_id, customer_id FROM sale";    private Connection connection;    public SaleDaoImpl(Connection connection)    {        this.connection = connection;    }    public SaleDaoImpl(DataSource dataSource) throws SQLException    {        this(dataSource.getConnection());    }    public List<Sale> find() throws SQLException    {        List<Sale> allSales = new ArrayList<Sale>();        Statement st = null;        ResultSet rs = null;        try        {            st = this.connection.createStatement();            rs = st.executeQuery(SELECT_ALL_SQL);            while (rs.next())            {                String saleId = rs.getString("sale_id");                String homeId = rs.getString("home_id");                String agentId = rs.getString("agent_id");                String customerId = rs.getString("customer_id");                Sale sale = new Sale(saleId, homeId, agentId, customerId);                allSales.add(sale);            }        }        catch (SQLException e)        {            e.printStackTrace();        }        finally        {            try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); }            try { if (st != null) st.close(); } catch (SQLException e) { e.printStackTrace(); }         }        return allSales;    }}

And an object-to-XML unmarshaller:

package badservlet.xml;import badservlet.model.Sale;import org.jdom.Document;import org.jdom.Element;import org.jdom.transform.JDOMResult;import javax.xml.bind.JAXBException;import javax.xml.transform.Result;import java.util.List;public class SaleUnmarshaller{    public void unmarshal(Object object, Result xml) throws JAXBException    {        List<Sale> allSales = (List<Sale>) object;        Document document = new  Document(new Element("sales"));        for (Sale sale : allSales)        {            Element child = new Element("sale");            child.setAttribute("id", sale.getSaleId());            child.addContent(new Element("home", sale.getHomeId()));            child.addContent(new Element("agent", sale.getAgentId()));            child.addContent(new Element("customer", sale.getCustomerId()));            document.addContent(child);        }        JDOMResult result = new JDOMResult();        result.setDocument(document);        xml = result;    }}

Let your servlet instantiate these objects and call their methods.

It might look more complicated - more classes than just one - but you've accomplished two things: you've broken your problem down into smaller pieces, and you can test them separately.


What are you trying to accomplish here? This code looks very confusing for several reasons:

  1. You're presumably trying to build up an XML string, but you're not appending any XML tags to it at all.
  2. There's a lot of no-ops in there, such as xml = xml + ""; which doesn't achieve anything.
  3. I'm not 100% sure what you want to achieve in the try block near the end. This block will have the side-effect of ensuring your xml string is valid XML, but if this is what you want to do there are probably clearer (and more efficient) ways of validating. If you're hoping it will magically transform your String into XML, then it won't (in fact no matter what, it can't modify the contents of the xml variable so this would be a no-op.

Perhaps it would help if you talked through what you're trying to do here, with particular reference to what you expect the state of affairs to be at each stage. Right now, you're building up a string that looks something like:

FirstSaleIDFirstHomeFirstAgentFirstCustomerSecondSaleIDSecondHomeSecondAgentSecondCustomer...

Then you try to parse this as XML. As you might expect, this is not valid XML hence the parser throws the error (in particular "no content in prolog" means that you have character data before the first tag definition).

I would give you advice on how to improve this but I really have no idea what you expect this to do...