|
1
64
65 package com.jcorporate.expresso.ext.controller;
66
67 import com.jcorporate.expresso.core.controller.ErrorCollection;
68 import com.jcorporate.expresso.core.controller.Block;
69 import com.jcorporate.expresso.core.controller.ControllerException;
70 import com.jcorporate.expresso.core.controller.ControllerRequest;
71 import com.jcorporate.expresso.core.controller.ControllerResponse;
72 import com.jcorporate.expresso.core.controller.DBController;
73 import com.jcorporate.expresso.core.controller.Input;
74 import com.jcorporate.expresso.core.controller.Output;
75 import com.jcorporate.expresso.core.controller.State;
76 import com.jcorporate.expresso.core.controller.Transition;
77 import com.jcorporate.expresso.core.db.DBConnection;
78 import com.jcorporate.expresso.core.db.DBConnectionPool;
79 import com.jcorporate.expresso.core.db.DBException;
80 import com.jcorporate.expresso.core.dbobj.ValidValue;
81 import com.jcorporate.expresso.core.misc.StringUtil;
82 import java.util.StringTokenizer  ;
83 import java.sql.ResultSetMetaData ;
84 import java.util.Enumeration ;
85 import java.util.Vector ;
86 import com.jcorporate.expresso.core.controller.NonHandleableException;
87 import java.sql.*;
88
89
95 public class RunSQL extends DBController {
96
97
100 public RunSQL() {
101 State prompt = new State("prompt", "Prompt for Query");
102 addState(prompt);
103 setInitialState("prompt");
104
105 State run = new State("run", "Run Query");
106 run.addRequiredParameter("SQLQuery");
107 addState(run);
108 this.setSchema(com.jcorporate.expresso.core.ExpressoSchema.class);
109
110 State definition = new State("getDefinition", "Get Table Definition");
111 definition.addRequiredParameter("SQLQuery");
112 addState(definition);
113 }
114
115
122 public void runPromptState(ControllerRequest request,
123 ControllerResponse response) throws ControllerException {
124 Input query = new Input("SQLQuery");
125 query.setLabel("Enter Query");
126 query.setAttribute("type", "text");
127 query.setAttribute("textarea", "Y");
128 response.addInput(query);
129
130 Input style = new Input();
131 style.setLabel("Choose Style Of Results");
132 style.setName("styleType");
133
134 Vector v2 = new Vector ();
135 v2.addElement(new ValidValue("", "Default"));
136 v2.addElement(new ValidValue("xml", "XML Document"));
137 v2.addElement(new ValidValue("excel", "Excel Spreadsheet"));
138 style.setValidValues(v2);
139 response.addInput(style);
140
141 response.addTransition(new Transition("run", this));
142 response.addTransition(new Transition("getDefinition",this));
143 }
144
145
146
147
154 public void runGetDefinitionState(final ControllerRequest request,
155 final ControllerResponse response) throws ControllerException, NonHandleableException {
156 String tableName = request.getParameter("SQLQuery");
157 if (tableName == null || tableName.length() == 0) {
158 response.addError("You need to enter a table name in the "
159 + "'sql query' input to get the definition");
160 }
161
162 try {
163 response.add(getTableDefinition(request.getDataContext(), tableName));
164 } catch (DBException ex) {
165 throw new ControllerException("Database Error querying table structure: " + response);
166 }
167
168 }
169
170
179 public Block getTableDefinition(String dataContext,
180 String def) throws DBException {
181 String tableName = StringUtil.replace(def, "\r\n","");
182 Block b = new Block("definition");
183 b.add(new Output("Table Definition for table: " + def));
184 Block resultTable = new Block("table");
185 resultTable.setAttribute("table", "Y");
186 resultTable.setAttribute("header-row", "Field Name|Type|Null Allowed");
187 b.add(resultTable);
188
189
190 DBConnection connection = DBConnectionPool.getInstance(dataContext).getConnection("Table Definition Query");
191 try {
192 DatabaseMetaData metadata = connection.getDBMetaData();
193 ResultSet rs = metadata.getColumns(null, null, tableName, null);
194 if (rs == null) {
195 resultTable.add(new Output("No Table Definition Found for table name: " + def));
196 } while (rs.next()) {
197 Block oneRow = new Block();
198 resultTable.add(oneRow);
199 oneRow.setAttribute("row", "Y");
200 oneRow.add(new Output(rs.getString(4))); String columnType = rs.getString(6); int dataSize = rs.getInt(7); int decimalSize = rs.getInt(9); if (dataSize > 0) {
205 if (decimalSize > 0) {
206 columnType = columnType + "(" + dataSize + "," + decimalSize + ")";
207 } else {
208 columnType = columnType + "(" + dataSize + ")";
209 }
210 }
211 oneRow.add(new Output(columnType));
212 int nullable = rs.getInt(11); switch (nullable) {
214 case DatabaseMetaData.columnNoNulls:
215 oneRow.add(new Output("No"));
216 break;
217
218 case DatabaseMetaData.columnNullable:
219 oneRow.add(new Output("Yes"));
220 break;
221
222 case DatabaseMetaData.columnNullableUnknown:
223 oneRow.add(new Output("Unknown"));
224 break;
225
226 }
227 }
228 } catch (java.sql.SQLException e) {
229 throw new DBException("Error querying database for table definition", e);
230 } finally {
231 connection.release();
232 }
233
234 return b;
235 }
236
237
245 public void runRunState(ControllerRequest req, ControllerResponse res) throws ControllerException {
246 DBConnection myConnection = null;
247 DBConnectionPool myPool = null;
248 String styleString = StringUtil.notNull(req.getParameter("styleType"));
249
250 if (styleString.length() > 0) {
251 req.setParameter("style", styleString);
252
253 if (styleString.equals("xml")) {
254 req.setParameter("xsl", "none");
255 }
256 }
257 try {
258 myPool = DBConnectionPool.getInstance(req.getDataContext());
259 myConnection = myPool.getConnection("RunSQLQuery");
260
261 String queryToRun = StringUtil.notNull(req.getParameter("SQLQuery"));
262
263 if (queryToRun.equals("")) {
264 ErrorCollection ec = new ErrorCollection();
265 ec.addError("No Query To Run");
266 res.saveErrors(ec);
267 transition("prompt", req, res);
268 return;
269 }
270
271 res.add(new Output("query", queryToRun));
272 boolean isSelected = executeQuery(myConnection, queryToRun);
273
274 if (isSelected) {
275 int columnCount;
276 Vector columnNames = new Vector (5);
277 Vector columnTitles = new Vector (5);
278
279 try {
280 ResultSetMetaData myMeta = myConnection.getMetaData();
281
282 if (myMeta != null) {
283 columnCount = myMeta.getColumnCount();
284
285 for (int i = 1; i <= columnCount; i++) {
286 columnNames.addElement(myMeta.getColumnName(i));
287 columnTitles.addElement(myMeta.getColumnLabel(i));
288 }
289 } else {
290 throw new DBException("Unable to get meta-data from query");
291 }
292 } catch (java.sql.SQLException se) {
293 throw new DBException("Error getting meta-data", se);
294 }
295
296 Block resultTable = new Block("result table");
297 resultTable.setAttribute("table", "Y");
298 res.add(resultTable);
299
300 int column = 1;
301
302
303
304
305 Block oneRow = buildResultHeaderRow(columnNames, columnTitles, resultTable);
306
307 while (myConnection.next()) {
308 oneRow = new Block();
309 oneRow.setAttribute("row", "Y");
310 resultTable.add(oneRow);
311
312 for (column = 1; column <= columnCount; column++) {
313 oneRow.add(new Output(StringUtil.notNull(myConnection.getString(column))));
314 }
315 }
316 } else {
317 res.add(new Output("Database successfully updated:"));
318 res.add(new Output("Rows Affected: " + myConnection.getUpdateCount()));
319 res.add(new Output("Enter the table name and hit 'Get Table Definition' to see the new definition"));
320
321 }
322 } catch (Exception de) {
323 throw new ControllerException(de);
324 } finally {
325 if (myPool != null && myConnection != null) {
326 myPool.release(myConnection);
327 }
328 }
329 }
330
331
338 private Block buildResultHeaderRow(Vector columnNames, Vector columnTitles, Block resultTable) {
339 boolean dupTitles = true;
340 String oneName = null;
341 String oneTitle = null;
342 Enumeration e2 = columnNames.elements();
343
344 for (Enumeration e1 = columnTitles.elements();
345 e1.hasMoreElements(); ) {
346 oneName = (String ) e2.nextElement();
347 oneTitle = (String ) e1.nextElement();
348
349 if (!oneName.equalsIgnoreCase(oneTitle)) {
350 dupTitles = false;
351 }
352 }
353
354
355 if (!dupTitles) {
356
357 StringBuffer titleString = new StringBuffer ();
358 boolean needPipe = false;
359
360 for (Enumeration eTitles = columnTitles.elements();
361 eTitles.hasMoreElements(); ) {
362 if (needPipe) {
363 titleString.append("|");
364 } else {
365 needPipe = true;
366 }
367
368 titleString.append((String ) eTitles.nextElement());
369 }
370
371 resultTable.setAttribute("header-row", titleString.toString());
372 }
373
374
375
376 StringBuffer headerString = new StringBuffer ();
377 boolean needPipe = false;
378
379 for (Enumeration eNames = columnNames.elements();
380 eNames.hasMoreElements(); ) {
381 if (needPipe) {
382 headerString.append("|");
383 } else {
384 needPipe = true;
385 }
386
387 headerString.append((String ) eNames.nextElement());
388 }
389
390 resultTable.setAttribute("header-row", headerString.toString());
391 return resultTable;
392 }
393
394
401 private boolean executeQuery(DBConnection myConnection, String queryToRun) throws DBException {
402 String firstToken = (new StringTokenizer (queryToRun)).nextToken();
403 if ("SELECT".equalsIgnoreCase(firstToken)) {
404 myConnection.execute(queryToRun);
405 return true;
406 } else {
407 myConnection.executeUpdate(queryToRun);
408 return false;
409 }
410 }
411
412
413
416 public String getTitle() {
417 return ("Run SQL Query");
418 }
419
420 }
421
422
423 |
Popular Tags |
Java API By Example, From Geeks To Geeks.