1 package com.explosion.expfmodules.rdbmsconn.dbom.utils;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 import java.io.File;
24 import java.io.IOException;
25 import java.sql.Array;
26 import java.sql.Connection;
27 import java.sql.PreparedStatement;
28 import java.sql.ResultSet;
29 import java.sql.ResultSetMetaData;
30 import java.sql.SQLException;
31 import java.sql.Statement;
32 import java.sql.Timestamp;
33 import java.text.ParseException;
34 import java.text.SimpleDateFormat;
35 import java.util.ArrayList;
36 import java.util.Arrays;
37 import java.util.Date;
38 import java.util.Vector;
39
40 import javax.swing.JOptionPane;
41
42 import org.apache.log4j.LogManager;
43 import org.apache.log4j.Logger;
44
45 import com.explosion.expf.ExpConstants;
46 import com.explosion.expf.preferences.SystemPreferences;
47 import com.explosion.expfmodules.rdbmsconn.RdbmsConnConstants;
48 import com.explosion.expfmodules.rdbmsconn.RdbmsConnModuleManager;
49 import com.explosion.expfmodules.rdbmsconn.dbom.DBEntityColumn;
50 import com.explosion.expfmodules.rdbmsconn.dbom.DataSet;
51 import com.explosion.expfmodules.rdbmsconn.dbom.DataSetColumn;
52 import com.explosion.utilities.ByteUtils;
53 import com.explosion.utilities.GeneralConstants;
54 import com.explosion.utilities.GeneralUtils;
55 import com.explosion.utilities.process.DummySimpleProcess;
56 import com.explosion.utilities.process.threads.Process;
57
58 /***
59 * This class handles queries to a database;
60 * It binds values and handles conversions for types bla bla bla
61 *
62 * @author Stephen Cowx
63 */
64
65 public class SQLEngine
66 {
67
68 private static Logger log = LogManager.getLogger(SQLEngine.class);
69
70 private ArrayList tempFiles = new ArrayList();
71 private ResultSet result = null;
72 private Statement statement = null;
73 private DataSet returnSet = null;
74
75 /***
76 * Date time format for dates "dd/MM/yyyy HH:mm:ss"
77 */
78 private static final String DATE_TIME_FORMAT = "dd/MM/yyyy HH:mm:ss";
79
80 private static SimpleDateFormat format = new SimpleDateFormat(DATE_TIME_FORMAT);
81
82 /***
83 * This method returns the results of a query as a DataSet no matter what. Well, not quite, if there is an SQL
84 * Exceptrion while processing a query it will wrap that up in a Dataset which has no results. If there are other
85 * Exceptions which are not expected (e.g. not connected to database or world is falling over) then it will throw those.
86 */
87 public DataSet executeCommand(Connection conn, String sqlCommand, long fetchSize, Process process, boolean fetchAllData) throws SQLException, IOException
88 {
89 log.debug("executeCommand: " + sqlCommand);
90
91 if (process == null)
92 process = new DummySimpleProcess();
93
94 if (conn == null)
95 throw new java.sql.SQLException("Error: Not connected to a database.");
96
97
98 if (statement == null)
99 {
100 statement = conn.createStatement();
101 }
102 else
103 {
104 try { statement.close(); } catch (SQLException e) {
105 statement = conn.createStatement();
106 }
107
108 if (killed(process))
109 return null;
110
111
112 process.setStatusText("Waiting for database ...");
113
114
115 if (RdbmsConnModuleManager.instance() != null)
116 {
117 statement.setMaxRows(((Integer) RdbmsConnModuleManager.instance().getPreference(RdbmsConnConstants.RDBMS_OPTION_MAXROWSRETURNED).getValue()).intValue());
118 }
119
120
121 boolean errorsFound = false;
122 boolean isResultSet = false;
123 try
124 {
125 isResultSet = statement.execute(sqlCommand);
126 }
127 catch (SQLException e)
128 {
129 errorsFound = true;
130 returnSet = handleExecuteError(e);
131 }
132
133
134 if (killed(process))
135 return null;
136
137 if (!errorsFound)
138 {
139 if (isResultSet)
140 {
141 process.setStatusText("Fetching results...");
142
143 result = statement.getResultSet();
144
145
146 if (killed(process))
147 return null;
148
149
150 returnSet = (DataSet) createDataSet(result, statement, process, fetchAllData);
151
152
153 if (killed(process))
154 return null;
155
156
157 if (statement.getMoreResults())
158 log.debug("Warning: More than one result set was returned from the SQL statement. Only the first result set will be diplayed. Statement was: " + sqlCommand);
159 }
160 else
161 {
162 int count = statement.getUpdateCount();
163 int updateCount = ( count == -1 ? 0 : count);
164
165 returnSet = new DataSet();
166 returnSet.setData(null);
167 returnSet.setNumberOfRowsUpdated(updateCount);
168 returnSet.setMessage(updateCount == 0 ? "Done (0 rows updated)" : "Done (" + updateCount + " rows updated.)");
169 }
170 }
171 return returnSet;
172 }
173
174 /***
175 * This method handles the SQL Exception by wrapping it up in an empty
176 * dataset
177 *
178 * @param e
179 * @throws SQLException
180 */
181 private DataSet handleExecuteError(SQLException e) throws SQLException
182 {
183 int count = statement.getUpdateCount();
184 int updateCount = ( count == -1 ? 0 : count);
185
186 DataSet set = new DataSet();
187 set.setData(null);
188 set.setNumberOfRowsUpdated(updateCount);
189 set.setMessage(updateCount + " rows updated.\nError Code: " + e.getErrorCode() + "\nSQL state: " + e.getSQLState() + "\nError Message: " + e.getMessage() + "Cause: " + ( e.getCause() == null ? "" : e.getCause().getMessage() ));
190
191
192 SQLException error = e;
193 while (error != null)
194 {
195 set.addError(error);
196 error = error.getNextException();
197 }
198
199 return set;
200 }
201
202 public void close()
203 {
204 try
205 {
206 result.close();
207 }
208 catch (Exception e)
209 {
210 }
211 try
212 {
213 statement.close();
214 }
215 catch (Exception e)
216 {
217 }
218 }
219
220 /***
221 * Checks to see process this has been killed or not
222 *
223 * @param process
224 * @return
225 */
226 private boolean killed(Process process)
227 {
228
229 if (process.isStopped())
230 {
231 process.setStatusText("Cancelled.");
232 return true;
233 }
234 else
235 return false;
236 }
237
238 /***
239 * This method populates a dataset object from a resultSet
240 */
241 private Object createDataSet(ResultSet result, Statement statement, Process process, boolean fetchAllData) throws SQLException, IOException
242 {
243 if (process.isStopped())
244 return null;
245
246 process.setStatusText("Parsing result metadata...");
247
248
249
250
251
252 ResultSetMetaData rsmd = result.getMetaData();
253 DataSet dataset = new DataSet();
254
255
256 int numColumns = rsmd.getColumnCount();
257 Vector rows = new Vector();
258 int[] maxColumnWidths = new int[numColumns];
259 String[] longestEntries = new String[numColumns];
260 Arrays.fill(longestEntries, "");
261
262
263 int numRows = 0;
264
265
266 for (int i = 0; i < numColumns; i++)
267 {
268
269 if (process.isStopped())
270 break;
271
272
273 dataset.addColumn(new DataSetColumn(result, i + 1));
274 }
275
276
277 while (result.next())
278 {
279 numRows++;
280 process.setStatusText("Fetching row " + numRows);
281 Vector values = new Vector();
282 for (int i = 0; i < numColumns; i++)
283 {
284
285 if (process.isStopped())
286 break;
287
288
289 values.addElement(decodeElementFromResultSet(result, i + 1, fetchAllData));
290
291
292
293
294
295
296 int length = 0;
297 Object value = values.get(i);
298 if (values.get(i) != null)
299 {
300 String strng = null;
301 if (value instanceof Date)
302 {
303 strng = format.format((Date) value);
304 }
305 else
306 {
307 strng = value.toString();
308 }
309 length = strng.length();
310
311 if (length > maxColumnWidths[i])
312 {
313 longestEntries[i] = strng;
314 maxColumnWidths[i] = length;
315 }
316 }
317 }
318
319
320 if (process.isStopped())
321 break;
322
323
324 rows.addElement(values);
325
326
327 if (Runtime.getRuntime().freeMemory() < 2048)
328 {
329 JOptionPane.showMessageDialog(null, "You have less than 2Kb of free memory available to this application." + GeneralConstants.LS
330 + "This is not enough memory to display all of the results returned from your query." + GeneralConstants.LS
331 + "Those rows that have already been fetched will be displayed, the remaining results will not.", "Memory shortage", JOptionPane.INFORMATION_MESSAGE);
332 break;
333 }
334 }
335
336
337 if (process.isStopped())
338 process.setStatusText("Creating dataset with rows fetched so far.");
339 else
340 process.setStatusText("Creating dataset.");
341
342
343 dataset.setData(rows);
344 dataset.setMessage(numRows + " rows selected.");
345 dataset.setLongestEntries(longestEntries);
346 dataset.setLongestEntryLengths(maxColumnWidths);
347 dataset.setResultSet(result);
348
349 return dataset;
350 }
351
352 /***
353 * This method calls the corrcet ResultSet get Mehtod depending obn the
354 * datatype the element it is supposed to fetch. It will obtain the call the
355 * SQL3Types (e.g. BLOB) if the fetchAllData argument is true, it writes the
356 * data to loacl disk and passes back a local Binary data reference which
357 * can be used to fetchj the data later. If it is set to false, it will
358 * return the class name of the Class of the column being retrived. The
359 * startIndex is 0.
360 */
361 private Object decodeElementFromResultSet(ResultSet resultSet, int columnIndex, boolean fetchAllData) throws SQLException, IOException
362 {
363 int type = resultSet.getMetaData().getColumnType(columnIndex);
364
365 Object object = null;
366 switch (type)
367 {
368 case (java.sql.Types.ARRAY):
369 if (fetchAllData)
370 {
371 Array values = resultSet.getArray(columnIndex);
372 object = (Object[]) values.getArray();
373 }
374 else
375 {
376 object = resultSet.getMetaData().getColumnTypeName(columnIndex);
377 }
378 break;
379 case (java.sql.Types.VARBINARY):
380 if (fetchAllData)
381 {
382 object = writeToFile(resultSet, columnIndex);
383 }
384 else
385 {
386 object = resultSet.getMetaData().getColumnTypeName(columnIndex);
387 }
388 break;
389
390 case (java.sql.Types.BINARY):
391 if (fetchAllData)
392 {
393 object = writeToFile(resultSet, columnIndex);
394 }
395 else
396 {
397 object = resultSet.getMetaData().getColumnTypeName(columnIndex);
398 }
399 break;
400 case (java.sql.Types.BLOB):
401 object = resultSet.getBlob(columnIndex);
402 break;
403 case (java.sql.Types.CLOB):
404 object = resultSet.getClob(columnIndex);
405 break;
406 case (java.sql.Types.DISTINCT):
407 object = new String("Objects of type java.sql.Types.DISTINCT are not supported.");
408 break;
409 case (java.sql.Types.REF):
410 object = new String("Objects of type java.sql.Types.REF are not supported.");
411 break;
412 case (java.sql.Types.STRUCT):
413 object = new String("Object of type java.sql.Types.STRUCT are not supported.");
414 break;
415
416
417 case (java.sql.Types.BIGINT):
418 case (java.sql.Types.SMALLINT):
419 case (java.sql.Types.TINYINT):
420 case (java.sql.Types.REAL):
421 case (java.sql.Types.DECIMAL):
422 case (java.sql.Types.DOUBLE):
423 case (java.sql.Types.FLOAT):
424 case (java.sql.Types.INTEGER):
425 case (java.sql.Types.NUMERIC):
426 object = (Number) resultSet.getObject(columnIndex);
427 break;
428 case (java.sql.Types.JAVA_OBJECT):
429 object = resultSet.getObject(columnIndex);
430 break;
431 case (java.sql.Types.BIT):
432 object = new Boolean(resultSet.getBoolean(columnIndex));
433 break;
434 case (java.sql.Types.CHAR):
435 object = resultSet.getString(columnIndex);
436 break;
437 case (java.sql.Types.VARCHAR):
438 object = resultSet.getString(columnIndex);
439 break;
440 case (java.sql.Types.LONGVARCHAR):
441 object = resultSet.getString(columnIndex);
442 break;
443 case (java.sql.Types.NULL):
444 object = null;
445 case (java.sql.Types.DATE):
446 object = resultSet.getTimestamp(columnIndex);
447 break;
448 case (java.sql.Types.TIMESTAMP):
449 object = resultSet.getTimestamp(columnIndex);
450 break;
451 case (java.sql.Types.TIME):
452 object = resultSet.getTimestamp(columnIndex);
453 break;
454 case (java.sql.Types.LONGVARBINARY):
455 object = resultSet.getString(columnIndex);
456 break;
457 case (java.sql.Types.OTHER):
458 object = resultSet.getObject(columnIndex);
459 break;
460 default:
461 object = resultSet.getObject(columnIndex);
462 if (object != null)
463 log.debug("Unsupported sql TYPE. Datatype:=" + type + "," + "classname=" + object.getClass().getName());
464 break;
465 }
466 return object;
467 }
468
469 /***
470 * Thios method binds the provided type to the preparedStatement
471 *
472 * @param data
473 * @param preparedStatement
474 * @param column
475 * @param parameterIndex
476 * @throws SQLException
477 * @throws ParseException
478 * @throws UnconvertableDataTypeException
479 * @throws UnrecognisedTypeException
480 * @throws UnsupportedTypeException
481 */
482 public void bind(Object data, PreparedStatement preparedStatement, DBEntityColumn column, int parameterIndex) throws UnconvertableDataTypeException, SQLException, ParseException, UnrecognisedTypeException, UnsupportedTypeException
483 {
484 int type = column.getType();
485
486 if (data == null)
487 {
488 preparedStatement.setNull(parameterIndex, type);
489 return;
490 }
491
492 switch (type)
493 {
494 case (java.sql.Types.ARRAY):
495 case (java.sql.Types.BINARY):
496 case (java.sql.Types.BLOB):
497 case (java.sql.Types.CLOB):
498 case (java.sql.Types.DISTINCT):
499 case (java.sql.Types.REF):
500 case (java.sql.Types.STRUCT):
501 case (java.sql.Types.VARBINARY):
502 case (java.sql.Types.LONGVARBINARY):
503 case (java.sql.Types.OTHER) :
504 log.debug("Unbound '"+data+"' unsupported type'");
505 throw new UnsupportedTypeException("Insert of type " + column.getTypeName() + " is not currently supported by this program.");
506 case (java.sql.Types.JAVA_OBJECT):
507 preparedStatement.setObject(parameterIndex, data, java.sql.Types.JAVA_OBJECT);
508 log.debug("Bound '"+data+"' as 'java.sql.Types.JAVA_OBJECT'");
509 break;
510 case (java.sql.Types.BIGINT):
511 preparedStatement.setInt(parameterIndex, (new Integer(data.toString())).intValue());
512 log.debug("Bound '"+data+"' as 'java.sql.Types.BIGINT'");
513 break;
514 case (java.sql.Types.SMALLINT):
515 preparedStatement.setInt(parameterIndex, (new Integer(data.toString())).intValue());
516 log.debug("Bound '"+data+"' as 'java.sql.Types.SMALLINT'");
517 break;
518 case (java.sql.Types.TINYINT):
519 preparedStatement.setInt(parameterIndex, (new Integer(data.toString())).intValue());
520 log.debug("Bound '"+data+"' as 'java.sql.Types.TINYINT'");
521 break;
522 case (java.sql.Types.REAL):
523 preparedStatement.setDouble(parameterIndex, (new Double(data.toString())).doubleValue());
524 log.debug("Bound '"+data+"' as 'java.sql.Types.REAL'");
525 break;
526 case (java.sql.Types.DECIMAL):
527 preparedStatement.setDouble(parameterIndex, (new Double(data.toString())).doubleValue());
528 log.debug("Bound '"+data+"' as 'java.sql.Types.DECIMAL'");
529 break;
530 case (java.sql.Types.DOUBLE):
531 preparedStatement.setDouble(parameterIndex, (new Double(data.toString())).doubleValue());
532 log.debug("Bound '"+data+"' as 'java.sql.Types.DOUBLE'");
533 break;
534 case (java.sql.Types.FLOAT):
535 preparedStatement.setFloat(parameterIndex, (new Float(data.toString())).floatValue());
536 log.debug("Bound '"+data+"' as 'java.sql.Types.FLOAT'");
537 break;
538 case (java.sql.Types.INTEGER):
539 preparedStatement.setInt(parameterIndex, (new Integer(data.toString())).intValue());
540 log.debug("Bound '"+data+"' as 'java.sql.Types.INTEGER'");
541 break;
542 case (java.sql.Types.NUMERIC):
543 preparedStatement.setDouble(parameterIndex, (new Double(data.toString())).doubleValue());
544 log.debug("Bound '"+data+"' as 'java.sql.Types.NUMERIC'");
545 break;
546 case (java.sql.Types.CHAR):
547 preparedStatement.setString(parameterIndex, data.toString());
548 log.debug("Bound '"+data+"' as 'java.sql.Types.CHAR'");
549 break;
550 case (java.sql.Types.BIT):
551 preparedStatement.setBoolean(parameterIndex, GeneralUtils.getLenientBoolean(data.toString()));
552 log.debug("Bound '"+data+"' as 'java.sql.Types.BIT'");
553 break;
554 case (java.sql.Types.VARCHAR):
555 preparedStatement.setString(parameterIndex, data.toString());
556 log.debug("Bound '"+data+"' as 'java.sql.Types.VARCHAR'");
557 break;
558 case (java.sql.Types.LONGVARCHAR):
559 preparedStatement.setString(parameterIndex, data.toString());
560 log.debug("Bound '"+data+"' as 'java.sql.Types.LONGVARCHAR'");
561 break;
562 case (java.sql.Types.DATE):
563 case (java.sql.Types.TIMESTAMP):
564 preparedStatement.setTimestamp(parameterIndex, convertToTimestamp(data));
565 log.debug("Bound '"+data+"' as 'java.sql.Types.TIMESTAMP'");
566 break;
567 default:
568 log.debug("UNrecognized type");
569 throw new UnrecognisedTypeException("Type code "+type+" has not been recognised. Data of this type is not supported." );
570 }
571 }
572
573 /***
574 * This mthod returns a boolean indicating whether this type is a
575 * supported editable type pr not
576 *
577 * If it is supported then true is returned else false is returned.
578 * @param type
579 */
580 public static boolean isSupportedEditableType(int type)
581 {
582 switch (type)
583 {
584 case (java.sql.Types.ARRAY):
585 case (java.sql.Types.BINARY):
586 case (java.sql.Types.BLOB):
587 case (java.sql.Types.CLOB):
588 case (java.sql.Types.DISTINCT):
589 case (java.sql.Types.REF):
590 case (java.sql.Types.STRUCT):
591 case (java.sql.Types.VARBINARY):
592 case (java.sql.Types.LONGVARBINARY):
593 case (java.sql.Types.OTHER):
594 return false;
595 default:
596 return true;
597
598 }
599 }
600
601 /***
602 * Writes the stream to file
603 *
604 * @param resultSet
605 * @param columnIndex
606 * @return @throws SQLException
607 * @throws Exception
608 */
609 private File writeToFile(ResultSet resultSet, int columnIndex) throws SQLException, IOException
610 {
611 File tempFile = new File((File) SystemPreferences.getPreference(ExpConstants.TEMPDIR).getValue(), getTempFileFilename(resultSet, columnIndex));
612 tempFile.deleteOnExit();
613 ByteUtils.readBinaryStreamIntoFile(resultSet.getBinaryStream(columnIndex + 1), tempFile);
614 return tempFile;
615 }
616
617 /***
618 * Builds up a timestamped filename with some other information (hopefully
619 * unique)
620 *
621 * @param resultSet
622 * @param columnIndex
623 * @return @throws SQLException
624 */
625 private String getTempFileFilename(ResultSet resultSet, int columnIndex) throws SQLException
626 {
627 String typeName = resultSet.getMetaData().getColumnTypeName(columnIndex + 1);
628 int rowIndex = resultSet.getRow();
629 return "EXP_" + typeName + "_ROW" + rowIndex + "_COL" + columnIndex + 1 + "_" + (new Date()).getTime() + ".tmp";
630 }
631
632 /***
633 * This method converts objects of many different types into a T
634 * @param object
635 * @return
636 * @throws ParseException
637 * @throws UnconvertableDataTypeException
638 */
639 private Timestamp convertToTimestamp(Object object) throws ParseException, UnconvertableDataTypeException
640 {
641 if (object == null)
642 return null;
643
644 if (object instanceof Timestamp)
645 return (Timestamp) object;
646 else if (object instanceof java.sql.Date)
647 return new Timestamp(((java.sql.Date) object).getTime());
648 else if (object instanceof java.util.Date)
649 return new Timestamp(((Date) object).getTime());
650 else if (object instanceof String)
651 {
652 return new Timestamp(format.parse((String)object).getTime());
653 }
654 else
655 {
656 throw new UnconvertableDataTypeException("Cannot convert objects of type " + object.getClass().getName() + " into a java.sql.Date.");
657 }
658 }
659
660 }