Project

General

Profile

1 301 bojilova
/**
2
 *  '$RCSfile$'
3
 *    Purpose: A Class that implements utility methods like:
4
 *             1/ Reding all doctypes from db connection
5
 *             2/ Reading Lore type Data Guide from db connection
6
 *  Copyright: 2000 Regents of the University of California and the
7
 *             National Center for Ecological Analysis and Synthesis
8 315 bojilova
 *    Authors: Jivka Bojilova
9 349 jones
 *    Release: @release@
10 301 bojilova
 *
11
 *   '$Author$'
12
 *     '$Date$'
13
 * '$Revision$'
14
 */
15
16
package edu.ucsb.nceas.metacat;
17
18
import java.sql.Connection;
19
import java.sql.SQLException;
20
import java.sql.PreparedStatement;
21
import java.sql.ResultSet;
22
import java.util.Hashtable;
23
import java.util.Enumeration;
24
import java.util.Vector;
25
26
/**
27
 * A suite of utility classes for quering DB
28
 */
29
public class DBUtil {
30
31
  private Connection	conn = null;
32
33
  /**
34
   * main routine used for testing.
35
   * <p>
36
   * Usage: java DBUtil <-dt|-dg>
37
   *
38
   * @param -dt for selecting all doctypes
39
   *        -dg for selecting DataGuide
40
   */
41
  static public void main(String[] args) {
42
43
     if (args.length < 1)
44
     {
45
        System.err.println("Wrong number of arguments!!!");
46 315 bojilova
        System.err.println("USAGE: java DBUtil <-dt | -dg doctype>");
47 301 bojilova
        return;
48
     } else {
49
        try {
50
51
          // Open a connection to the database
52
          MetaCatUtil   util = new MetaCatUtil();
53
          Connection dbconn = util.openDBConnection();
54
55
          DBUtil dbutil = new DBUtil( dbconn );
56
57
          if ( args[0].equals("-dt") ) {
58
            String doctypes = dbutil.readDoctypes();
59
            System.out.println(doctypes);
60
          } else if ( args[0].equals("-dg") ) {
61 315 bojilova
            String doctype = null;
62
            if ( args.length == 2 ) { doctype = args[1]; }
63
            String dataguide = dbutil.readDataGuide(doctype);
64 301 bojilova
            System.out.println(dataguide);
65
          } else {
66 315 bojilova
            System.err.println("USAGE: java DBUtil <-dt | -dg doctype>");
67 301 bojilova
          }
68
69
        } catch (Exception e) {
70
          System.err.println("EXCEPTION HANDLING REQUIRED");
71
          System.err.println(e.getMessage());
72
          e.printStackTrace(System.err);
73
        }
74
     }
75
  }
76
77
  /**
78
   * Construct an instance of the utility class
79
   */
80
  public DBUtil( Connection conn ) {
81
    this.conn = conn;
82
  }
83
84
  /**
85
   * read all doctypes from db connection in XML format
86
   * select all Public Id from xml_catalog table
87
   */
88
  public String readDoctypes()
89
        throws SQLException  {
90
91
    Vector doctypeList = new Vector();
92
93
    try {
94
95
      PreparedStatement pstmt =
96
        conn.prepareStatement("SELECT public_id FROM xml_catalog");
97
98
      pstmt.execute();
99
      ResultSet rs = pstmt.getResultSet();
100
      boolean tableHasRows = rs.next();
101
      while (tableHasRows) {
102
           doctypeList.addElement(rs.getString(1));
103
           tableHasRows = rs.next();
104
      }
105
106
      pstmt.close();
107
108
    } catch (SQLException e) {
109
      System.out.println("DBUtil.readDoctypes(): " + e.getMessage());
110
      throw e;
111
    }
112
113
    return formatToXML(doctypeList, "doctype");
114
  }
115
116
  /**
117
   * read Data Guide for a given doctype from db connection in XML format
118
   * select all distinct absolute paths from xml_index table
119
   */
120
  public String readDataGuide( String doctype )
121
        throws SQLException  {
122
123
    Vector dataguide = new Vector();
124
    String path;
125 315 bojilova
    PreparedStatement pstmt;
126
127 301 bojilova
    try {
128
129 315 bojilova
        if ( doctype != null ) {
130
            pstmt = conn.prepareStatement("SELECT distinct path, nodeid " +
131
                                          "FROM xml_index " +
132
                                          "WHERE path LIKE '/%' " +
133
                                          "AND doctype LIKE ? " +
134
                                          "ORDER BY nodeid");
135
            pstmt.setString(1, doctype);
136
        } else {
137
            pstmt = conn.prepareStatement("SELECT distinct path, nodeid " +
138
                                          "FROM xml_index " +
139
                                          "WHERE path LIKE '/%' " +
140
                                          "ORDER BY nodeid");
141
        }
142
143
        pstmt.execute();
144
        ResultSet rs = pstmt.getResultSet();
145
        boolean tableHasRows = rs.next();
146
        while (tableHasRows) {
147
            path = rs.getString(1);
148
            if ( dataguide.indexOf(path) == -1 ) {
149
                dataguide.addElement(path);
150
            }
151
            tableHasRows = rs.next();
152
        }
153 301 bojilova
154 315 bojilova
        pstmt.close();
155 301 bojilova
156
    } catch (SQLException e) {
157
      System.out.println("DBUtil.readDoctypes(): " + e.getMessage());
158
      throw e;
159
    }
160
161
    return formatToXML(dataguide, "path");
162
  }
163
164
  /**
165
   * format the ResultSet to XML
166
   */
167
  private String formatToXML(Vector resultset, String tag) {
168
169
    String val = null;
170
    String doctype = null;
171
    StringBuffer result = new StringBuffer();
172
    Enumeration rs = resultset.elements();
173
174
    result.append("<?xml version=\"1.0\"?>\n");
175
    result.append("<resultset>\n");
176
    while (rs.hasMoreElements()) {
177
        val = (String)rs.nextElement();
178
        result.append("   <" + tag + ">" + val + "</" + tag + ">\n");
179
    }
180
    result.append("</resultset>\n");
181
182
    return result.toString();
183
  }
184
185
186
}