How can I store large amount of data from a database to XML (speed problem, part three)?
- by Andrija
After getting some responses, the current situation is that I'm using this tip: http://www.ibm.com/developerworks/xml/library/x-tipbigdoc5.html (Listing 1. Turning ResultSets into XML), and XMLWriter for Java from http://www.megginson.com/downloads/
. Basically, it reads date from the database and writes them to a file as characters, using column names to create opening and closing tags. While doing so, I need to make two changes to the input stream, namely to the dates and numbers.
// Iterate over the set
while (rs.next()) {
w.startElement("row");
for (int i = 0; i < count; i++) {
Object ob = rs.getObject(i + 1);
if (rs.wasNull()) {
ob = null;
}
String colName = meta.getColumnLabel(i + 1);
if (ob != null ) {
if (ob instanceof Timestamp) {
w.dataElement(colName, Util.formatDate((Timestamp)ob, dateFormat));
}
else if (ob instanceof BigDecimal){
w.dataElement(colName, Util.transformToHTML(new Integer(((BigDecimal)ob).intValue())));
}
else {
w.dataElement(colName, ob.toString());
}
} else {
w.emptyElement(colName);
}
}
w.endElement("row");
}
The SQL that gets the results has the to_number command (e.g. to_number(sif.ID) ID ) and the to_date command (e.g. TO_DATE (sif.datum_do, 'DD.MM.RRRR') datum_do). The problems are that the returning date is a timestamp, meaning I don't get 14.02.2010 but rather 14.02.2010 00:00:000 so I have to format it to the dd.mm.yyyy format. The second problem are the numbers; for some reason, they are in database as varchar2 and can have leading zeroes that need to be stripped; I'm guessing I could do that in my SQL with the trim function so the Util.transformToHTML is unnecessary (for clarification, here's the method):
public static String transformToHTML(Integer number) {
String result = "";
try {
result = number.toString();
} catch (Exception e) {}
return result;
}
What I'd like to know is
a) Can I get the date in the format I want and skip additional processing thus shortening
the processing time?
b) Is there a better way to do this? We're talking about XML files that are in the 50 MB - 250 MB filesize category.
