[jdom-interest] SQL join -> Structured XML

[Bilton, Sasha]

I wrote a class that takes a JDBC ResultSet and produces a JDOM Element from
it when I was teaching some of my team how easy JDOM is. The xml format is
<resultset>
 <$columnname type="$type">$value</$columnname>
 ..
</resultset>

It's not great and I've never used it in anger, but you might get some vague
use from it...


import org.jdom.Element;
import java.sql.*;

/**
 * This class has the single static method <b>toJDOMElement</b> which takes
a
 * JDBC ResultSet and prodices a JDOM Element from it. 
 * @author Sasha Bilton
 * @version 1.0
 */

public class ResultSetConverter {

    public static String RESULTSET = "resultset";
    public static String TYPE = "type";
    public static String ROW = "row";
    public static String EMPTY = "empty";


    public static Element toJDOMElement(ResultSet rs) {
        // root is the parent of all row Elements
        Element root = new Element(RESULTSET);

        try {

            ResultSetMetaData rsmd = rs.getMetaData();


            int columnCount = rsmd.getColumnCount();


            String columnName[] = new String[columnCount];
            String columnType[] = new String[columnCount];

            // get the names and types of each column.
            // It seems that ResultSetMetaData columns start at index 1, not
0
            for (int i = 1; i < columnCount + 1; i++) {

                columnName[i - 1] = rsmd.getColumnName(i);
                columnType[i - 1] = rsmd.getColumnTypeName(i);

            }

            // Go through the data from each row
            while (rs.next()) {

                Element row = new Element(ROW);

                for (int i = 0; i < columnCount; i++) {
                    Element data = new Element(columnName[i]);
                    data.setText(rs.getString(i));
                    data.setAttribute(TYPE, columnType[i]);
                    row.addContent(data);
                }

                root.addContent(row);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
            root.setText(EMPTY);
        }

        return root;


    }

}

> -----Original Message-----
> From: Philippe Lang [mailto:philippe.lang at attiksystem.ch]
> Sent: 25 April 2002 07:18
> To: jdom-interest at jdom.org
> Subject: [jdom-interest] SQL join -> Structured XML
> 
> 
> Hi,
> 
> I have written a set of classes that take as input the result 
> of an SQL
> query, with joined tables. Given some parameters you give to 
> the classes
> (groups, sort order...), very similar to what you can configure in
> Access or Crystal Reports, it outputs an XML file, with the same data,
> but in a structured way. It works with any number of joins.
> 
> I wonder if it would be a good idea to incorporate these classes in
> JDOM? Or does that go beyond the scope of JDOM?
> 
> 
> Example:
> --------
> 
> (CachedRowSet)
> 
> A        P1   P2
> A        P3   P4
> A        P5   P6
> B        P7   P8
> B        P9   P10
> A        P11  P12
> C        P13  P14
> 
> gives...
> 
> 
> (XML)
> 
> ----- A ----- P1   P2
>    |      |-- P3   P4
>    |      |-- P5   P6
>    |      |-- P11  P12
>    |
>    |- B ----- P7   P8
>    |      |-- P9   P10
>    |
>    |- C ----- P13  P14
> 
> 
> 
> -------------------------
> Philippe Lang
> Attik System
> http://www.attiksystem.ch 
> _______________________________________________
> To control your jdom-interest membership:
> http://lists.denveronline.net/mailman/options/jdom-interest/yo
uraddr at yourhost.com


**********************************************************************
Information in this email is confidential and may be privileged. 
It is intended for the addressee only. If you have received it in error,
please notify the sender immediately and delete it from your system. 
You should not otherwise copy it, retransmit it or use or disclose its
contents to anyone. 
Thank you for your co-operation.
**********************************************************************