Source code

001/*
002 *  $Id: $
003 *  $HeadURL: svn://svn.webarts.bc.ca/open/trunk/projects/WebARTS/ca/bc/webarts/widgets/ResultSetConverter.java $
004 *  $Revision: 1055 $
005 *  $LastChangedDate: 2016-04-25 20:53:42 -0700 (Mon, 25 Apr 2016) $
006 *  $LastChangedBy: tgutwin $
007 *  Copyright (c) 2014-2016 Tom B. Gutwin P.Eng. North Vancouver BC Canada
008 *
009 *  This program is free software; you can redistribute it and/or
010 *  modify it under the terms of the GNU General Public License
011 *  as published by the Free Software Foundation; either version 3
012 *  of the License, or any later version.
013 *
014 *  This program is distributed in the hope that it will be useful,
015 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
016 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
017 *  GNU General Public License for more details.
018 *
019 *  You should have received a copy of the GNU General Public License
020 *  along with this program; If not, see <http://www.gnu.org/licenses/>.
021 */
022package ca.bc.webarts.widgets;
023
024import org.json.JSONArray;
025import org.json.JSONObject;
026import org.json.JSONException;
027
028import nu.xom.Attribute;
029import nu.xom.Builder;
030import nu.xom.Document;
031import nu.xom.Element;
032import nu.xom.Elements;
033import nu.xom.Node;
034import nu.xom.Nodes;
035import nu.xom.ParsingException;
036import nu.xom.ValidityException;
037import nu.xom.Serializer;
038import nu.xom.Text;
039import nu.xom.XMLException;
040
041import au.com.bytecode.opencsv.CSVReader;
042import au.com.bytecode.opencsv.CSVWriter;
043//import jxl.*;
044//import jxl.write.*;
045
046import java.io.IOException;
047import java.io.InputStream;
048import java.io.Reader;
049import java.lang.Exception;
050import java.sql.Array;
051import java.sql.Blob;
052import java.sql.Clob;
053import java.sql.ResultSet;
054import java.sql.ResultSetMetaData;
055import java.sql.SQLException;
056import java.sql.Types;
057
058
059/** Helper class that converts SQL ResultSets into XML (XOM) object, JSON (org.json) object, CSV file, Delimited StringBuilder, or a Delimited String.
060  * It relys on nu.xom, au.com.bytecode.opencsv,  and org.json libraries .
061**/
062public class ResultSetConverter
063{
064  public static final String SYSTEM_FILE_SEPERATOR = java.io.File.separator;
065
066  /**  A holder for this clients System line termination separator.  */
067  public static final String SYSTEM_LINE_SEPERATOR = System.getProperty("line.separator");
068  /** The Default char ("|") used for delimiting the ResultSet columns. It equals "|".**/
069  public static final String DEFAULT_COLUMN_DELIMITOR = "|";
070
071  /** The class ResultSet that gets converted. **/
072  private ResultSet rs_ = null;
073  // private Builder xmlBuilder_ = new Builder();
074  private Document rsDoc_ = null;
075  /** The class columnDelimitorchar (defualt = "|") used for delimiting the ResultSet columns during conversions to CSV, StringBuilder or String . **/
076  protected static String columnDelimitor = DEFAULT_COLUMN_DELIMITOR;
077
078  /** Constructor that initializes the class resultset. **/
079  public ResultSetConverter(ResultSet rs)
080  {
081    this.rs_ = rs;
082  }
083
084  /**
085   * Set Method for this classes ResultSet.
086   *
087   * @param rs is the value to set this class field to.
088   *
089   **/
090  public void setRs(ResultSet rs)
091  {
092    this.rs_ = rs;
093  }  // setRs Method
094
095
096  /**
097   * Get Method for this classes ResultSet.
098   *
099   * @return ResultSet - The value the class field 'rs'.
100   *
101   **/
102  public ResultSet getRs()
103  {
104    return this.rs_;
105  }  // getRs Method
106
107
108  /**
109    * Set Method for class field 'columnDelimitor' that gets used in deliminating the CSV, StringBuilder or String conversions.
110    * If not set, the DEFAULT_COLUMN_DELIMITOR is used.
111    *
112    * @param columnDelimitor is the value to set this class field to.
113    *
114    **/
115  public  void setColumnDelimitor(String columnDelimitor)
116  {
117    this.columnDelimitor = columnDelimitor;
118  }  // setColumnDelimitor Method
119
120
121  /**
122    * Get Method for class field 'columnDelimitor' that gets used in deliminating the CSV, StringBuilder or String conversions.
123    * If not set, the DEFAULT_COLUMN_DELIMITOR is used.
124    *
125    * @return String - The value the class field 'columnDelimitor'.
126    *
127    **/
128  public String getColumnDelimitor()
129  {
130    return columnDelimitor;
131  }  // getColumnDelimitor Method
132
133
134  /** Converts the class ResultSet into a fully described XML Document (nu.xom.Documnet)
135   * @return the xom Document representing the class ResultSet
136   **/
137  public Document convertToXml() throws IOException, SQLException, JSONException, Exception
138  {
139    if (this.rs_!=null)
140    {
141      System.out.println("convertToXML()");
142      try
143      {
144        ResultSetMetaData metadata = rs_.getMetaData();
145        int numFields = metadata.getColumnCount();
146        System.out.println("convertToXML: numFields="+numFields);
147
148        Element table = new Element("table");
149
150        Element record = null;
151        Element fieldElement = null;
152        int type = 0;
153        String typeName = "";
154        String name = "";
155        Object value = null;
156        int rowCount = 0;
157        while (rs_.next()) // && rowCount < 10)
158        {
159          rowCount++;
160          record = new Element("record");
161          for (int field = 1; field <= numFields; field++)
162          {
163
164            fieldElement = new Element("field");
165            type = metadata.getColumnType(field);
166            typeName = getXmlSchemaType(type);
167            fieldElement.addAttribute(new Attribute("xsi:type", "http://www.w3.org/2001/XMLSchema-instance",
168                                      typeName,
169                                      Attribute.Type.NMTOKEN));
170            name = metadata.getColumnName(field);
171            fieldElement.addAttribute(new Attribute("name", name));
172
173            // Convert nulls to empty elements with xsi:nil="true"
174            value = rs_.getObject(field);
175            if (value == null)
176            {            // null value in database
177              fieldElement.addAttribute(new Attribute("xsi:nil", "http://www.w3.org/2001/XMLSchema-instance", "true"));
178            }
179            else
180            {            // non-null value
181              fieldElement.appendChild(convertToXML(rs_, field, type));
182            }
183            record.appendChild(fieldElement);
184          }
185          table.appendChild(record);
186        }      // end while
187        rsDoc_ =  new Document(table);
188      }
189      catch (SQLException ex)
190      {      // convert exception type
191        throw new XMLException("SQL error", ex);
192      }
193    }
194    else
195    {
196      System.out.println("ERROR: Null or empty ResultSet.");
197      throw new Exception("Null or empty ResultSet.");
198    }
199    return rsDoc_;
200  }
201
202
203  /**
204   * converts the rs data field to a hex encoded Text xml node.
205   * I want the XML document to store values in the standard W3C
206   * XML Schema Language forms. This requires certain conversions
207   * depending on the type of the data
208   **/
209  private Node convertToXML(ResultSet data, int field, int type) throws SQLException, IOException
210  {
211    Node retVal = new Text("null");;
212    //System.out.println("convertToXML(ResultSet , int , int)");
213    //System.out.print(".");
214    switch (type)
215    {
216      case Types.BINARY:
217      case Types.VARBINARY:
218      case Types.LONGVARBINARY:
219        retVal = hexEncode(data.getBinaryStream(field));
220      case Types.BLOB:
221        Blob blob = data.getBlob(field);
222        retVal = hexEncode(blob.getBinaryStream());
223        // String types may contain C0 control characters that are
224        // not legal in XML. If so an exception is thrown.
225      case Types.CLOB:
226        Clob clob = data.getClob(field);
227        Reader r = clob.getCharacterStream();
228        char[] text = new char[1024];
229        int numRead;
230        StringBuffer result = new StringBuffer();
231        while ((numRead = r.read(text, 0, 1024)) != -1)
232        {
233          result.append(text, 0, numRead);
234        }
235        retVal = new Text(result.toString());
236      case Types.ARRAY:
237        Array array = data.getArray(field);
238        retVal = writeXmlArray(array);
239      default:        // All other types can be handled as strings
240        Object o = data.getObject(field);
241        if (o == null)
242        {
243          return new Text("");
244        }
245        retVal = new Text(o.toString());
246    }
247
248    return retVal;
249  }
250
251
252  /** Crerates a HEX encoded Text (String ) object. **/
253  private Text hexEncode(InputStream in) throws IOException
254  {
255
256    StringBuffer result = new StringBuffer();
257
258    int octet;
259    while ((octet = in.read()) != -1)
260    {
261      if (octet < 16)
262      {
263        result.append('0');
264      }
265      result.append(Integer.toHexString(octet));
266    }
267    return new Text(result.toString());
268
269  }
270
271
272  /** creates an XML Array element. **/
273  private Element writeXmlArray(Array array) throws IOException, SQLException
274  {
275
276    Element holder = new Element("array");
277    ResultSet data = array.getResultSet();
278    int type = array.getBaseType();
279    String typeName = getXmlSchemaType(type);
280
281    while (data.next())
282    {
283      Element component = new Element("component");
284      component.addAttribute(new Attribute("xsi:type", "http://www.w3.org/2001/XMLSchema-instance", typeName));
285      component.appendChild(convertToXML(data, 2, type));
286      holder.appendChild(component);
287    }
288    return holder;
289
290  }
291
292
293  /** lookup method to convert the passed in java.sql.Types int into a XML xsi:type, http://www.w3.org/2001/XMLSchema-instance String. **/
294  public static String getXmlSchemaType(int type)
295  {
296    switch (type)
297    {
298      case Types.ARRAY: return "array";
299      case Types.BIGINT: return "xsd:long";
300      case Types.BINARY: return "xsd:hexBinary";
301      case Types.BIT: return "xsd:boolean";
302      case Types.BLOB: return "xsd:hexBinary";
303      case Types.CHAR: return "xsd:string";
304      case Types.CLOB: return "xsd:string";
305      case Types.DATE: return "xsd:date";
306      case Types.DECIMAL: return "xsd:decimal";
307      case Types.DOUBLE: return "xsd:double";
308      case Types.FLOAT: return "xsd:decimal";
309      case Types.INTEGER: return "xsd:int";
310      case Types.JAVA_OBJECT: return "xsd:string";
311      case Types.LONGVARBINARY: return "xsd:hexBinary";
312      case Types.LONGVARCHAR: return "xsd:string";
313      case Types.NUMERIC: return "xsd:decimal";
314      case Types.REAL: return "xsd:float";
315      case Types.REF: return "xsd:IDREF";
316      case Types.SMALLINT: return "xsd:short";
317      case Types.STRUCT: return "struct";
318      case Types.TIME: return "xsd:time";
319      case Types.TIMESTAMP: return "xsd:dateTime";
320      case Types.TINYINT: return "xsd:byte";
321      case Types.VARBINARY: return "xsd:hexBinary";
322        // most general type
323      default: return "xsd:string";
324    }
325
326  }
327
328
329  /** Converts the class ResultSet into a fully described JSON Array Object (org.json.JSONArray)
330   * @return the org.json.JSONArray representing the class ResultSet or an Exception if ResultSet is null
331   **/
332  public JSONArray convertToJson() throws SQLException, JSONException, Exception
333  {
334    JSONArray json = null;
335
336    if (this.rs_!=null)
337    {
338      json = new JSONArray();
339      ResultSetMetaData rsmd = rs_.getMetaData();
340      int numColumns = rsmd.getColumnCount();
341
342      while (rs_.next())
343      {
344
345        JSONObject obj = new JSONObject();
346
347        for (int i = 1; i <= numColumns; i++)
348        {
349          String column_name = rsmd.getColumnName(i);
350
351          if (rsmd.getColumnType(i) == java.sql.Types.ARRAY)
352          {
353            obj.put(column_name, rs_.getArray(i));
354          }
355          else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT)
356          {
357            obj.put(column_name, rs_.getLong(i));
358          }
359          else if (rsmd.getColumnType(i) == java.sql.Types.REAL)
360          {
361            obj.put(column_name, rs_.getFloat(i));
362          }
363          else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN)
364          {
365            obj.put(column_name, rs_.getBoolean(i));
366          }
367          else if (rsmd.getColumnType(i) == java.sql.Types.BLOB)
368          {
369            obj.put(column_name, rs_.getBlob(i));
370          }
371          else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE)
372          {
373            obj.put(column_name, rs_.getDouble(i));
374          }
375          else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT)
376          {
377            obj.put(column_name, rs_.getDouble(i));
378          }
379          else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER)
380          {
381            obj.put(column_name, rs_.getInt(i));
382          }
383          else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR)
384          {
385            obj.put(column_name, rs_.getNString(i));
386          }
387          else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR)
388          {
389            obj.put(column_name, rs_.getString(i));
390          }
391          else if (rsmd.getColumnType(i) == java.sql.Types.CHAR)
392          {
393            obj.put(column_name, rs_.getString(i));
394          }
395          else if (rsmd.getColumnType(i) == java.sql.Types.NCHAR)
396          {
397            obj.put(column_name, rs_.getNString(i));
398          }
399          else if (rsmd.getColumnType(i) == java.sql.Types.LONGNVARCHAR)
400          {
401            obj.put(column_name, rs_.getNString(i));
402          }
403          else if (rsmd.getColumnType(i) == java.sql.Types.LONGVARCHAR)
404          {
405            obj.put(column_name, rs_.getString(i));
406          }
407          else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT)
408          {
409            obj.put(column_name, rs_.getByte(i));
410          }
411          else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT)
412          {
413            obj.put(column_name, rs_.getShort(i));
414          }
415          else if (rsmd.getColumnType(i) == java.sql.Types.DATE)
416          {
417            obj.put(column_name, rs_.getDate(i));
418          }
419          else if (rsmd.getColumnType(i) == java.sql.Types.TIME)
420          {
421            obj.put(column_name, rs_.getTime(i));
422          }
423          else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP)
424          {
425            obj.put(column_name, rs_.getTimestamp(i));
426          }
427          else if (rsmd.getColumnType(i) == java.sql.Types.BINARY)
428          {
429            obj.put(column_name, rs_.getBytes(i));
430          }
431          else if (rsmd.getColumnType(i) == java.sql.Types.VARBINARY)
432          {
433            obj.put(column_name, rs_.getBytes(i));
434          }
435          else if (rsmd.getColumnType(i) == java.sql.Types.LONGVARBINARY)
436          {
437            obj.put(column_name, rs_.getBinaryStream(i));
438          }
439          else if (rsmd.getColumnType(i) == java.sql.Types.BIT)
440          {
441            obj.put(column_name, rs_.getBoolean(i));
442          }
443          else if (rsmd.getColumnType(i) == java.sql.Types.CLOB)
444          {
445            obj.put(column_name, rs_.getClob(i));
446          }
447          else if (rsmd.getColumnType(i) == java.sql.Types.NUMERIC)
448          {
449            obj.put(column_name, rs_.getBigDecimal(i));
450          }
451          else if (rsmd.getColumnType(i) == java.sql.Types.DECIMAL)
452          {
453            obj.put(column_name, rs_.getBigDecimal(i));
454          }
455          else if (rsmd.getColumnType(i) == java.sql.Types.DATALINK)
456          {
457            obj.put(column_name, rs_.getURL(i));
458          }
459          else if (rsmd.getColumnType(i) == java.sql.Types.REF)
460          {
461            obj.put(column_name, rs_.getRef(i));
462          }
463          else if (rsmd.getColumnType(i) == java.sql.Types.STRUCT)
464          {
465            obj.put(column_name, rs_.getObject(i));          // must be a custom mapping consists of a class that implements the interface SQLData and an entry in a java.util.Map object.
466          }
467          else if (rsmd.getColumnType(i) == java.sql.Types.DISTINCT)
468          {
469            obj.put(column_name, rs_.getObject(i));          // must be a custom mapping consists of a class that implements the interface SQLData and an entry in a java.util.Map object.
470          }
471          else if (rsmd.getColumnType(i) == java.sql.Types.JAVA_OBJECT)
472          {
473            obj.put(column_name, rs_.getObject(i));
474          }
475          else
476          {
477            obj.put(column_name, rs_.getString(i));
478          }
479        }
480
481        json.put(obj);
482      }
483    }
484    else
485    {
486      System.out.println("ERROR: Null or empty ResultSet.");
487      throw new Exception("Null or empty ResultSet.");
488    }
489    return json;
490  }
491
492
493  /**
494   *  Converts a ResultSet StringBuffer to a saved file that holds one result per line.
495   *
496   * @param fileName is the name of the file to save
497   * @return a String containing the filename if suucessful, null if not.
498   **/
499  public String convertToCSVFile(String fileName, boolean includeColumnsHeaders)
500  {
501    String retVal = "";
502
503    try
504    {
505      CSVWriter csvW = new CSVWriter(new java.io.FileWriter(fileName), columnDelimitor.charAt(0));
506      if (rs_ != null)
507      {
508        csvW.writeAll(rs_, includeColumnsHeaders);
509      }
510
511      if (csvW != null)
512      {
513        csvW.close();
514      }
515
516      retVal = fileName;
517    }
518    catch (Exception ex)
519    {
520      System.out.println("Could Not write CSV File: " + fileName);
521    }
522
523    return retVal;
524  }
525
526
527  public String convertToCSV(){ return convertToCSV(true);}
528  /**
529   *  Converts a ResultSet  to a csv String that holds one result per line.
530   *
531   * @return a String containing the csv if suucessful, null if not.
532   **/
533  public String convertToCSV(boolean includeColumnsHeaders)
534  {
535    String retVal = "";
536
537    try
538    {
539      java.io.StringWriter sw = new java.io.StringWriter();
540      CSVWriter csvW = new CSVWriter(sw, columnDelimitor.charAt(0), CSVWriter.NO_QUOTE_CHARACTER);
541      if (rs_ != null)
542      {
543        csvW.writeAll(rs_, includeColumnsHeaders);
544      }
545
546      if (csvW != null)
547      {
548        csvW.flush();
549        csvW.close();
550      }
551
552      retVal = sw.toString();
553    }
554    catch (Exception ex)
555    {
556      System.out.println("Could Not parse resultSet to CSV");
557      ex.printStackTrace();
558    }
559
560    return retVal;
561  }
562
563
564  /** Convert to a Delimited StringBuilder.
565   * @return the Delimited StringBuilder representing the class ResultSet
566   **/
567  public StringBuilder convertToStringBuilder()
568  {
569    StringBuilder retVal = new StringBuilder("");
570    if (rs_ != null)
571    {
572      int numRows = 0;
573      try
574      {
575        ResultSetMetaData rsmd = rs_.getMetaData();
576        int numCols = rsmd.getColumnCount();
577        for (int i =1; i <= numCols; i++)
578        {
579          retVal.append(rsmd.getColumnName(i));
580          if (i < numCols)
581          {
582            retVal.append(columnDelimitor);
583          }
584        }
585        retVal.append("\n");
586        while (rs_.next())
587        {
588          for (int i =1; i <= numCols; i++)
589          {
590            retVal.append(rs_.getString(i));
591            if (i < numCols)
592            {
593              retVal.append(columnDelimitor);
594            }
595          }
596          numRows++;
597          retVal.append("\n");
598        }
599      }
600      catch (SQLException noResultsEx)
601      {
602        System.out.println(noResultsEx.toString());
603        retVal = new StringBuilder(noResultsEx.toString());
604      }
605      catch (java.lang.OutOfMemoryError meapMemEx)
606      {
607        System.out.println(meapMemEx.toString());
608        System.out.println("Truncating RS to " + numRows + " rows");
609      }
610    }
611    return retVal;
612  }
613
614  /** Convert to a Delimited String.
615   * @return the Delimited String representing the class ResultSet
616   **/
617  public String toString()
618  { return convertToStringBuilder().toString(); }
619
620}