Source code: com/dghda/kent/reports/SimpleSQLReport.java
1 /* Copyright (C) 2001 Duane Griffin <duanegriffin@users.sourceforge.net>
2 This file is part of Kent.
3
4 Kent is free software; you can redistribute it and/or
5 modify it under the terms of the GNU General Public License as
6 published by the Free Software Foundation; either version 2 of the
7 License, or (at your option) any later version.
8
9 Kent is distributed in the hope that it will be useful,
10 but WITHOUT ANY WARRANTY; without even the implied warranty of
11 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
12 General Public License for more details.
13
14 You should have received a copy of the GNU General Public
15 License along with Kent; see the file COPYING. If not,
16 write to the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
17 Boston, MA 02111-1307, USA.
18 */
19
20 package com.dghda.kent.reports;
21
22 import java.io.*;
23 import java.util.*;
24
25 import org.w3c.dom.*;
26 import org.xml.sax.*;
27
28 import com.dghda.kent.*;
29 import com.dghda.module.*;
30
31 /** A class which makes one or more queries using JDBC, and loads its configuration from an XML file. */
32 public class SimpleSQLReport extends BaseJDBCReport {
33
34 private static final class ColumnMappingData {
35 public ColumnMappingData (String s, String d) throws SAXException {
36 if (s == null)
37 throw new SAXException ("No SQL column name specified in mapping");
38 if (d == null)
39 throw new SAXException ("No report data column name specified in query");
40 sql = s;
41 data = d;
42 }
43 public final String sql;
44 public final String data;
45 }
46
47 private static final class QueryData {
48 public QueryData (String n, String s) throws SAXException {
49 if (n == null)
50 throw new SAXException ("No name specified in query");
51 if (s == null)
52 throw new SAXException ("No SQL statement specified in query");
53 name = n;
54 sql = s;
55 }
56 public final String name;
57 public final String sql;
58 public final LinkedList mappings = new LinkedList();
59 }
60
61 private static final class ConnectionData {
62 public ConnectionData (String to, String user, String pass) throws SAXException {
63 if (to == null)
64 throw new SAXException ("No url specified in connection");
65 url = to;
66 username = user;
67 password = pass;
68 }
69 public final String url;
70 public final String username;
71 public final String password;
72 public final LinkedList queries = new LinkedList();
73 }
74
75 private static final class XMLHandler extends org.xml.sax.helpers.DefaultHandler {
76
77 public XMLHandler (ReportEngine engine) {
78 m_Engine = engine;
79 }
80
81 /** Handle the start of an element. */
82 public void startElement (String uri, String localName, String qName, Attributes attr) throws SAXException {
83 if (qName.equalsIgnoreCase ("SimpleSQLReport")) {
84
85 // Get the reports details
86 getDetails (attr);
87 } else if (qName.equalsIgnoreCase ("Connection")) {
88
89 // Create a new connection
90 startConnection (attr);
91 } else if (qName.equalsIgnoreCase ("Query")) {
92
93 // Create a new query
94 startQuery (attr);
95 } else if (qName.equalsIgnoreCase ("Mapping")) {
96
97 // Add a new mapping to the current query
98 addMapping (attr);
99 } else {
100 throw new SAXException ("Invalid tag while parsing simple report file: " + qName);
101 }
102 }
103
104 /** Handle the end of an element. */
105 public void endElement (String uri, String localName, String qName) throws SAXException {
106 if (qName.equalsIgnoreCase ("Connection")) {
107
108 // Add the connection
109 endConnection();
110 } else if (qName.equalsIgnoreCase ("Query")) {
111
112 // Add the query
113 endQuery();
114 } else {
115
116 // Ignore others
117 }
118 }
119
120 public SimpleSQLReport createReport() throws IOException {
121
122 // Create the new report
123 InputStream input = m_Engine.getResource (m_Template);
124 if (input == null)
125 throw new IOException ("Unable to locate report template " + m_Template);
126 SimpleSQLReport result = new SimpleSQLReport (m_ID, m_Name, m_Description,
127 new Module.ModuleVersion (m_Major, m_Minor, m_Author),
128 m_Engine, input);
129
130 // Iterate through all connections
131 Iterator connections = m_Connections.iterator();
132 while (connections.hasNext()) {
133
134 // Make an actual connection to the DB
135 ConnectionData connectionData = (ConnectionData) connections.next();
136 try {
137 java.sql.Connection connection = java.sql.DriverManager.getConnection (connectionData.url,
138 connectionData.username,
139 connectionData.password);
140
141 // Iterate over all queries using the connection
142 Iterator queries = connectionData.queries.iterator();
143 while (queries.hasNext()) {
144
145 // Create a prepared SQL statement using the query
146 QueryData queryData = (QueryData) queries.next();
147
148 try {
149 java.sql.PreparedStatement stmt = connection.prepareStatement (queryData.sql);
150 SQLQueryMapping query = new SQLQueryMapping (queryData.name, stmt);
151
152 // Iterate over mappings from the result set to the report data format
153 Iterator mappings = queryData.mappings.iterator();
154 while (mappings.hasNext()) {
155
156 // Add each mapping
157 ColumnMappingData mappingData = (ColumnMappingData) mappings.next();
158 query.addMapping (mappingData.sql, mappingData.data);
159 }
160
161 // Add the query to the report
162 result.addQuery (query);
163 } catch (java.sql.SQLException exc) {
164 m_Engine.getEnvironment().log ("An error occurred while preparing the SQL statement " +
165 queryData.sql + " using the connection " + connectionData.url, exc);
166 }
167 }
168 } catch (java.sql.SQLException exc) {
169 m_Engine.getEnvironment().log ("Unable to create a JDBC connection using the URL: " + connectionData.url, exc);
170 }
171 }
172
173 return result;
174 }
175
176 private void getDetails (Attributes attr) throws SAXException {
177
178 // Get the report's ID
179 m_ID = getValue (attr, "ID");
180
181 // Get the report's name
182 m_Name = getValue (attr, "name");
183
184 // Get the report's description
185 m_Description = getValue (attr, "description");
186
187 // Get the report's major version number
188 try {
189 m_Major = Integer.parseInt (getValue (attr, "major"));
190 } catch (NumberFormatException exc) {
191 throw new SAXException ("Non-numeric major version number given");
192 }
193
194 // Get the report's minor version number
195 try {
196 m_Minor = Integer.parseInt (getValue (attr, "minor"));
197 } catch (NumberFormatException exc) {
198 throw new SAXException ("Non-numeric minor version number given");
199 }
200
201 // Get the report's author
202 m_Author = getValue (attr, "author");
203
204 // Get the report's template
205 m_Template = getValue (attr, "template");
206 }
207
208 private void startConnection (Attributes attr) throws SAXException {
209 m_CurrentConnection = new ConnectionData (attr.getValue ("url"),
210 attr.getValue ("username"),
211 attr.getValue ("password"));
212 }
213
214 private void endConnection() {
215 m_Connections.add (m_CurrentConnection);
216 m_CurrentConnection = null;
217 }
218
219 private void startQuery (Attributes attr) throws SAXException {
220 m_CurrentQuery = new QueryData (attr.getValue ("name"),
221 attr.getValue ("sql"));
222 }
223
224 private void endQuery() throws SAXException {
225 m_CurrentConnection.queries.add (m_CurrentQuery);
226 m_CurrentQuery = null;
227 }
228
229 private void addMapping (Attributes attr) throws SAXException {
230 m_CurrentQuery.mappings.add (new ColumnMappingData (attr.getValue ("sql"),
231 attr.getValue ("data")));
232 }
233
234 private String getValue (Attributes attr, String name) throws SAXException {
235 String str = attr.getValue (name);
236 if (str == null)
237 throw new SAXException ("No " + name + " specified");
238 return str;
239 }
240
241 private ReportEngine m_Engine;
242 private String m_ID;
243 private String m_Name;
244 private String m_Description;
245 private int m_Major;
246 private int m_Minor;
247 private String m_Author;
248 private String m_Template;
249 private LinkedList m_Connections = new LinkedList();
250
251 // Working values
252 private QueryData m_CurrentQuery;
253 private ConnectionData m_CurrentConnection;
254 }
255
256 /**
257 Creates a new report object loaded by the given report engine.
258 @param id The unique ID of the module.
259 @param shortName The name of the module.
260 @param description A description of the module.
261 @param version The module's version information.
262 @param engine The report engine the module runs under.
263 @param input The stream to load the report template from.
264 */
265 public SimpleSQLReport (String id, String shortName, String description,
266 Module.ModuleVersion version, ReportEngine engine,
267 InputStream input) throws IOException {
268 super (id, shortName, description, version, engine, input);
269 }
270
271 /** Creates a new report for the given engine by loading it from the given input source. */
272 public static SimpleSQLReport loadReport (ReportEngine engine, InputSource source)
273 throws IOException, SAXException, javax.xml.parsers.ParserConfigurationException {
274
275 // Get an instance of a validating SAX parser
276 javax.xml.parsers.SAXParserFactory spf = javax.xml.parsers.SAXParserFactory.newInstance();
277 spf.setValidating (true);
278 javax.xml.parsers.SAXParser parser = spf.newSAXParser();
279
280 // Parse & create the report
281 XMLHandler handler = new XMLHandler (engine);
282 parser.parse (source, handler);
283 return handler.createReport();
284 }
285 }