Package org.dellroad.stuff.sql

Class XMLResultSetWriter

java.lang.Object
org.dellroad.stuff.sql.XMLResultSetWriter
public class XMLResultSetWriter extends Object
Outputs the result of an SQL query as XML.

For example, the query SELECT ID, LAST_NAME AS LAST, FIRST_NAME AS FIRST FROM EMPLOYEE might become: 

  <result-set>
      <query><![CDATA[SELECT ID, LAST_NAME, FIRST_NAME FROM EMPLOYEE]]></query>
      <columns>
          <column index="1" name="ID" label="ID" precision="20" type="BIGINT" typeName="BIGINT" nullable="false"/>
          <column index="2" name="LAST_NAME" label="LAST" precision="255" type="VARCHAR" typeName="VARCHAR" nullable="false"/>
          <column index="3" name="FIRST_NAME" label="FIRST" precision="255" type="VARCHAR" typeName="VARCHAR" nullable="false"/>
      </columns>
      <data>
          <row>
              <column index="1">1302</column>
              <column index="2">Washington</column>
              <column index="3">George</column>
          </row>
          <row>
              <column index="1">1303</column>
              <column index="2">Lincoln</column>
              <column index="3">Abraham</column>
          </row>
          ...
      </data>
  </result-set>

If you turn on column name tags via setColumnNameTags(), the output would look like this: 

  <result-set>
      <query><![CDATA[SELECT ID, LAST_NAME, FIRST_NAME FROM EMPLOYEE]]></query>
      <columns>
          <column index="1" name="ID" label="ID" precision="20" type="BIGINT" typeName="BIGINT" nullable="false"/>
          <column index="2" name="LAST_NAME" label="LAST" precision="255" type="VARCHAR" typeName="VARCHAR" nullable="false"/>
          <column index="3" name="FIRST_NAME" label="FIRST" precision="255" type="VARCHAR" typeName="VARCHAR" nullable="false"/>
      </columns>
      <data>
          <row>
              <ID index="1">1302</ID>
              <LAST index="2">Washington</LAST>
              <FIRST index="3">George</FIRST>
          </row>
          <row>
              <ID index="1">1303</ID>
              <LAST index="2">Lincoln</LAST>
              <FIRST index="3">Abraham</FIRST>
          </row>
          ...
      </data>
  </result-set>

When a row contains a null value in a column, that column is omitted entirely.

  • Field Details

    • TYPE_NAMES

      public static final Map<Integer,String> TYPE_NAMES
      Mapping from result set Types values to their corresponding names.

  • Constructor Details

    • XMLResultSetWriter

      public XMLResultSetWriter(XMLStreamWriter writer, int indent)
      Constructor.
      Parameters:
      writer - where to write the XML
      indent - number of spaces for indentation
      Throws:
      IllegalArgumentException - if writer is null

  • Method Details

    • setColumnNameTags

      public void setColumnNameTags(boolean columnNameTags)
      Set whether to use the SQL column labels as the XML element names for the data columns instead of <column>. Default is false.
      Parameters:
      columnNameTags - true to name XML elements using the corresponding SQL column names

    • write

      public void write(ResultSet resultSet) throws SQLException, XMLStreamException
      Write the given ResultSet out within a <result-set> XML element.

      This is a convenience method, equivalent to:

      write(null, resultSet)
      element is included.
      Parameters:
      resultSet - query result set
      Throws:
      SQLException - if accessing resultSet throws an exception
      XMLStreamException - if XML output fails

    • write

      public void write(String query, ResultSet resultSet) throws SQLException, XMLStreamException
      Write the given ResultSet out within a <result-set> XML element including the query. The given SQL query will be present as a <query> first child element, followed by a <columns> element containing column meta-data, and finally the <data> element containing each returned row.

      This method just outputs an XML element. To output an entire XML document, you would do something like this: 

        XMLResultSetWriter xmlResultSetWriter = new XMLResultSetWriter(writer, 4);
  writer.writeStartDocument("UTF-8", "1.0");
  xmlResultSetWriter.write(query, resultSet);
  writer.writeEndDocument();

      The result set is iterated in a forward direction only.

      Parameters:
      query - the SQL query, or null to omit
      resultSet - result of the query
      Throws:
      XMLStreamException - if there is an error writing out the XML
      SQLException - if there is an error reading from resultSet