|
1
30 package com.genimen.djeneric.repository.rdbms;
31
32 import java.io.ByteArrayInputStream  ;
33 import java.io.ByteArrayOutputStream ;
34 import java.io.IOException ;
35 import java.io.InputStream ;
36 import java.io.UnsupportedEncodingException ;
37 import java.math.BigDecimal ;
38 import java.sql.Connection ;
39 import java.sql.PreparedStatement ;
40 import java.sql.ResultSet ;
41 import java.sql.ResultSetMetaData ;
42 import java.sql.SQLException ;
43 import java.sql.Time ;
44 import java.sql.Timestamp ;
45 import java.util.ArrayList ;
46 import java.util.HashMap ;
47 import java.util.Iterator ;
48
49 import com.genimen.djeneric.language.Messages;
50 import com.genimen.djeneric.util.DjLogger;
51 import com.genimen.djeneric.util.DjStringReplacer;
52
53 public class SqlStatement
54 {
55 protected Connection _con;
56 protected PreparedStatement _stmt;
57 protected String _originalStmt;
58 protected String _sql;
59 protected String _parsedSql;
60 protected ArrayList _params = new ArrayList (10);
61 protected ArrayList _singleParams = new ArrayList (10);
62 protected ResultSetMetaData _metaData = null;
63 protected ResultSet _resultSet = null;
64 protected HashMap _parameterValues = new HashMap ();
65
66 protected int _timeOutTime = 0;
67 private boolean _commitAllowed = true;
68 private boolean _debug = false;
69
70 public SqlStatement(Connection p_con, String stmt) throws SQLException
71 {
72 _con = p_con;
73 setSql(stmt);
74 _originalStmt = stmt;
75 }
76
77 public SqlStatement(Connection p_con, String stmt, String originalStmt) throws SQLException
78 {
79 _con = p_con;
80 setSql(stmt);
81 _originalStmt = originalStmt;
82 }
83
84 public String getInternalSql()
85 {
86 return _sql;
87 }
88
89 public String getExternalSql()
90 {
91 return _originalStmt;
92 }
93
94 final static String seps = ",<>%* ()-+=[]{};:'\"/|\t\r\n";
95
96 private void setSql(String p_newSql) throws SQLException
97 {
98 _sql = p_newSql;
99
100 _parsedSql = p_newSql;
101
102 String work = p_newSql + " ";
103
104 char[] ca = work.toCharArray();
105 char[] finalStmt = p_newSql.toCharArray();
106
107 int offset = 1;
108 int len = work.length();
109 boolean inText = false;
110 boolean inAlias = false;
111 int inc;
112
113
117 while (offset < len)
118 {
119 inc = 1;
120 if (ca[offset] == '\'')
121 {
122 if (!inText && !inAlias) inText = true;
123 else if (inText)
124 {
125 if (ca[offset + 1] != '\'')
126 {
127 ca[offset] = ' ';
128 inText = false;
129 }
130 else inc = 2;
131 }
133 }
134 else if ((ca[offset] == '"') && !inText)
135 {
136 inAlias = !inAlias;
137 ca[offset] = ' ';
138 }
139
140 if (inText || inAlias)
141 {
142 ca[offset] = ' ';
143 if (inc != 1) ca[offset + 1] = ' ';
144 }
145 if (!inText && !inAlias)
146 {
147 offset = work.indexOf('\'', offset + inc);
148 if (offset == -1) offset = len;
149 }
150 else offset += inc;
151 }
152 work = new String (ca);
153
154 _params.clear();
155
156 int start = 1;
157 do
158 {
159 offset = work.indexOf(':', start);
160 if (offset > 0)
161 {
162 finalStmt[offset] = '?';
163
164 start = offset + 1;
165 int end = start;
166
167 while ((end < len) && (seps.indexOf(work.charAt(end)) == -1))
168 end++;
169 for (int i = start; i < end; i++)
170 {
171 finalStmt[i] = ' ';
172 }
173 String parName = work.substring(start, end).toUpperCase();
174
175 _params.add(parName);
176 boolean dontAdd = false;
177 for (int x = 0; x < _singleParams.size(); x++)
178 {
179 if (((String ) (_singleParams.get(x))).equalsIgnoreCase(parName))
180 {
181 dontAdd = true;
182 break;
183 }
184 }
185 if (!dontAdd) _singleParams.add(parName);
186 }
187 }
188 while (offset >= 0);
189
190 _parsedSql = new String (finalStmt);
191
192 if (_parsedSql.endsWith(";"))
193 {
194 _parsedSql = _parsedSql.substring(0, _parsedSql.length() - 1);
195 }
196 try
197 {
198 if (_stmt != null)
199 {
200 try
201 {
202 _stmt.close();
203 }
204 catch (SQLException sss)
205 {
206 DjLogger.log(sss);
207 }
208 }
209 _stmt = _con.prepareStatement(_parsedSql);
210 }
211 catch (SQLException s1qle)
212 {
213 System.err.println(Messages.getString("SqlStatement.errorInStatement", _parsedSql));
214 throw s1qle;
215 }
216
217 _metaData = null;
218 _resultSet = null;
219 }
220
221 public void setCommitAllowed(boolean b)
222 {
223 _commitAllowed = b;
224 }
225
226 public boolean isCommitAllowed()
227 {
228 return _commitAllowed;
229 }
230
231 public String fancyStmt()
232 {
233 return fancyStmt(_sql);
234 }
235
236 public String fancyStmt(String stmt)
237 {
238 if (stmt == null) return (Messages.getString("SqlStatement.NULL_statement"));
239
240 DjStringReplacer sr = new DjStringReplacer(stmt.trim());
241 sr.replace("\r\n", " ");
242 sr.replace("\n", " ");
243 sr.replaceUsingSeps("select", "\nselect");
244 sr.replaceUsingSeps("from", "\nfrom");
245 sr.replaceUsingSeps("where", "\nwhere");
246 sr.replaceUsingSeps("and", "\nand");
247 sr.replaceUsingSeps("or", "\nor");
248 sr.replaceUsingSeps("union", "\nunion");
249 sr.replaceUsingSeps("minus", "\nminus");
250 sr.replaceUsingSeps("order", "\norder");
251 sr.replace(", ", ",\n ");
252
253 return (sr.getString().trim());
254 }
255
256 public int executeUpdate() throws SQLException
257 {
258 if (_debug) DjLogger.log("Execute:\n" + _originalStmt);
259
260 String test = _sql;
262 if (test.length() > 20)
263 {
264 test = _sql.substring(0, 20).trim().toLowerCase();
265 }
266
267 if (test.startsWith("commit"))
268 {
269 if (!isCommitAllowed()) throw new SQLException (Messages.getString("global.CommitNotAllowed"));
270 _con.commit();
271 return 0;
272 }
273 if (test.startsWith("rollback"))
274 {
275 _con.rollback();
276 return 0;
277 }
278
279 try
280 {
281 int result = _stmt.executeUpdate();
282 return (result);
283 }
284 catch (SQLException x)
285 {
286 if ("02000".equals(x.getSQLState())) return 0;
290
291 String msg = x.getMessage().trim()
292 + Messages.getString("SqlStatement.StatementDump", fancyStmt(_sql), _originalStmt);
293
294 throw new SQLException (msg, x.getSQLState(), x.getErrorCode());
295 }
296 finally
297 {
298 _stmt.close();
299 }
300 }
301
302 public ResultSet executeQuery() throws SQLException
303 {
304 if (_debug) DjLogger.log(Messages.getString("SqlStatement.Execute", _originalStmt));
305
306 try
307 {
308 _resultSet = _stmt.executeQuery();
309 return (_resultSet);
310 }
311 catch (SQLException x)
312 {
313 if (_debug) DjLogger.log(x);
314
315 String msg = x.getMessage().trim()
316 + Messages.getString("SqlStatement.StatementDump", fancyStmt(_sql), _originalStmt);
317 throw new SQLException (msg, x.getSQLState(), x.getErrorCode());
318 }
319 }
320
321 public void close() throws SQLException
322 {
323 _stmt.close();
324 }
325
326 public void setAsciiStream(String paramName, InputStream ips, int len) throws SQLException
329 {
330 storeParameterValue(paramName, ips);
331 int i = 1;
332 boolean doneOne = false;
333 Iterator it = _params.iterator();
334 while (it.hasNext())
335 {
336 if (it.next().equals(paramName.toUpperCase()))
337 {
338 _stmt.setAsciiStream(i, ips, len);
339 doneOne = true;
340 }
341 i++;
342 }
343 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
344 }
345
346 public void setBigDecimal(String paramName, BigDecimal b) throws SQLException
347 {
348 storeParameterValue(paramName, b);
349 if (_debug) DjLogger.log(paramName + "=" + b);
350 int i = 1;
351 boolean doneOne = false;
352 Iterator it = _params.iterator();
353 while (it.hasNext())
354 {
355 if (it.next().equals(paramName.toUpperCase()))
356 {
357 _stmt.setBigDecimal(i, b);
358 doneOne = true;
359 }
360 i++;
361 }
362 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
363 }
364
365 public void setBinaryStream(String paramName, InputStream ips, int s) throws SQLException
366 {
367 storeParameterValue(paramName, ips);
368 int i = 1;
369 boolean doneOne = false;
370 Iterator it = _params.iterator();
371 while (it.hasNext())
372 {
373 if (it.next().equals(paramName.toUpperCase()))
374 {
375 _stmt.setBinaryStream(i, ips, s);
376 doneOne = true;
377 }
378 i++;
379 }
380 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
381 }
382
383 public void setBoolean(String paramName, boolean b) throws SQLException
384 {
385 storeParameterValue(paramName, new Boolean (b));
386 if (_debug) DjLogger.log(paramName + "=" + b);
387 int i = 1;
388 boolean doneOne = false;
389 Iterator it = _params.iterator();
390 while (it.hasNext())
391 {
392 if (it.next().equals(paramName.toUpperCase()))
393 {
394 _stmt.setBoolean(i, b);
395 doneOne = true;
396 }
397 i++;
398 }
399 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
400 }
401
402 public void setByte(String paramName, byte b) throws SQLException
403 {
404 storeParameterValue(paramName, new Byte (b));
405 if (_debug) DjLogger.log(paramName + "=" + b);
406 int i = 1;
407 boolean doneOne = false;
408 Iterator it = _params.iterator();
409 while (it.hasNext())
410 {
411 if (it.next().equals(paramName.toUpperCase()))
412 {
413 _stmt.setByte(i, b);
414 doneOne = true;
415 }
416 i++;
417 }
418 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
419 }
420
421 public void setBytes(String paramName, byte[] b) throws SQLException
422 {
423 storeParameterValue(paramName, b);
424 if (_debug) DjLogger.log(paramName + "=" + b);
425 int i = 1;
426 boolean doneOne = false;
427 Iterator it = _params.iterator();
428 while (it.hasNext())
429 {
430 if (it.next().equals(paramName.toUpperCase()))
431 {
432 _stmt.setBytes(i, b);
433 doneOne = true;
434 }
435 i++;
436 }
437 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
438 }
439
440 public void setDate(String paramName, java.util.Date d) throws SQLException
441 {
442 setDate(paramName, new java.sql.Date (d.getTime()));
443 }
444
445 public void setDate(String paramName, java.sql.Date d) throws SQLException
446 {
447 storeParameterValue(paramName, d);
448 if (_debug)
449 {
450 java.text.SimpleDateFormat sf = new java.text.SimpleDateFormat ("dd-MM-yyyy HH:mm:ss");
451 DjLogger.log(paramName + "=" + sf.format(d));
452 }
453
454 int i = 1;
455 boolean doneOne = false;
456 Iterator it = _params.iterator();
457 while (it.hasNext())
458 {
459 if (it.next().equals(paramName.toUpperCase()))
460 {
461 _stmt.setDate(i, d);
462 doneOne = true;
463 }
464 i++;
465 }
466 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
467 }
468
469 public void setDouble(String paramName, double d) throws SQLException
470 {
471 storeParameterValue(paramName, new Double (d));
472 if (_debug) DjLogger.log(paramName + "=" + d);
473 int i = 1;
474 boolean doneOne = false;
475 Iterator it = _params.iterator();
476 while (it.hasNext())
477 {
478 if (it.next().equals(paramName.toUpperCase()))
479 {
480 _stmt.setDouble(i, d);
481 doneOne = true;
482 }
483 i++;
484 }
485 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
486 }
487
488 public void setFloat(String paramName, float f) throws SQLException
489 {
490 storeParameterValue(paramName, new Float (f));
491 if (_debug) DjLogger.log(paramName + "=" + f);
492 int i = 1;
493 boolean doneOne = false;
494 Iterator it = _params.iterator();
495 while (it.hasNext())
496 {
497 if (it.next().equals(paramName.toUpperCase()))
498 {
499 _stmt.setFloat(i, f);
500 doneOne = true;
501 }
502 i++;
503 }
504 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
505 }
506
507 public void setInt(String paramName, int n) throws SQLException
508 {
509 storeParameterValue(paramName, new Integer (n));
510 if (_debug) DjLogger.log(paramName + "=" + n);
511 int i = 1;
512 boolean doneOne = false;
513 Iterator it = _params.iterator();
514 while (it.hasNext())
515 {
516 if (it.next().equals(paramName.toUpperCase()))
517 {
518 _stmt.setInt(i, n);
519 doneOne = true;
520 }
521 i++;
522 }
523 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
524 }
525
526 public void setLong(String paramName, long l) throws SQLException
527 {
528 storeParameterValue(paramName, new Long (l));
529 if (_debug) DjLogger.log(paramName + "=" + l);
530 int i = 1;
531 boolean doneOne = false;
532 Iterator it = _params.iterator();
533 while (it.hasNext())
534 {
535 if (it.next().equals(paramName.toUpperCase()))
536 {
537 _stmt.setLong(i, l);
538 doneOne = true;
539 }
540 i++;
541 }
542 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
543 }
544
545 public void setNull(String paramName, int sqlType) throws SQLException
546 {
547 _parameterValues.remove(paramName);
548 if (_debug) DjLogger.log(paramName + "= NULL");
549 int i = 1;
550 boolean doneOne = false;
551 Iterator it = _params.iterator();
552 while (it.hasNext())
553 {
554 if (it.next().equals(paramName.toUpperCase()))
555 {
556 _stmt.setNull(i, sqlType);
557 doneOne = true;
558 }
559 i++;
560 }
561 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
562 }
563
564 public void setObject(String paramName, Object o) throws SQLException
565 {
566 storeParameterValue(paramName, o);
567 if (_debug) DjLogger.log(paramName + "=(Object)" + o);
568 int i = 1;
569 boolean doneOne = false;
570 Iterator it = _params.iterator();
571 while (it.hasNext())
572 {
573 if (it.next().equals(paramName.toUpperCase()))
574 {
575 _stmt.setObject(i, o);
576 doneOne = true;
577 }
578 i++;
579 }
580 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
581 }
582
583 public void setObject(String paramName, Object o, int targetSQLType) throws SQLException
584 {
585 storeParameterValue(paramName, o);
586 if (_debug) DjLogger.log(paramName + "=(Object)" + o);
587 int i = 1;
588 boolean doneOne = false;
589 Iterator it = _params.iterator();
590 while (it.hasNext())
591 {
592 if (it.next().equals(paramName.toUpperCase()))
593 {
594 _stmt.setObject(i, o, targetSQLType);
595 doneOne = true;
596 }
597 i++;
598 }
599 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
600 }
601
602 public void setObject(String paramName, Object o, int targetSQLType, int scale) throws SQLException
603 {
604 storeParameterValue(paramName, o);
605 if (_debug) DjLogger.log(paramName + "=(Object)" + o);
606 int i = 1;
607 boolean doneOne = false;
608 Iterator it = _params.iterator();
609 while (it.hasNext())
610 {
611 if (it.next().equals(paramName.toUpperCase()))
612 {
613 _stmt.setObject(i, o, targetSQLType, scale);
614 doneOne = true;
615 }
616 i++;
617 }
618 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
619 }
620
621 public void setShort(String paramName, short s) throws SQLException
622 {
623 storeParameterValue(paramName, new Short (s));
624 if (_debug) DjLogger.log(paramName + "=" + s);
625 int i = 1;
626 boolean doneOne = false;
627 Iterator it = _params.iterator();
628 while (it.hasNext())
629 {
630 if (it.next().equals(paramName.toUpperCase()))
631 {
632 _stmt.setShort(i, s);
633 doneOne = true;
634 }
635 i++;
636 }
637 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
638 }
639
640 public void setString(String paramName, String s) throws SQLException
641 {
642 storeParameterValue(paramName, s);
643 if (_debug) DjLogger.log(paramName + "=" + s);
644 int i = 1;
645 boolean doneOne = false;
646 Iterator it = _params.iterator();
647 while (it.hasNext())
648 {
649 if (it.next().equals(paramName.toUpperCase()))
650 {
651 if (s == null) _stmt.setNull(i, java.sql.Types.VARCHAR);
652 else _stmt.setString(i, s);
653 doneOne = true;
654 }
655 i++;
656 }
657 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
658 }
659
660 public void setBlob(String paramName, String s, String ecodingMethod) throws SQLException
661 {
662 storeParameterValue(paramName, s);
663 try
664 {
665 setBlob(paramName, s.getBytes(ecodingMethod));
666 }
667 catch (UnsupportedEncodingException uee)
668 {
669 DjLogger.log(uee);
670 setBlob(paramName, s.getBytes());
671 }
672 }
673
674 public void setBlob(String paramName, byte[] bytes) throws SQLException
675 {
676 storeParameterValue(paramName, bytes);
677 int i = 1;
678 boolean doneOne = false;
679 Iterator it = _params.iterator();
680 while (it.hasNext())
681 {
682 if (it.next().equals(paramName.toUpperCase()))
683 {
684 if (bytes == null) _stmt.setNull(i, java.sql.Types.BLOB);
685 else
686 {
687 ByteArrayInputStream bais = new ByteArrayInputStream (bytes);
688 _stmt.setBinaryStream(i, bais, bytes.length);
689 }
690 doneOne = true;
691 }
692 i++;
693 }
694 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
695 }
696
697 public void setTime(String paramName, Time t) throws SQLException
698 {
699 storeParameterValue(paramName, t);
700 if (_debug) DjLogger.log(paramName + "=" + t);
701 int i = 1;
702 boolean doneOne = false;
703 Iterator it = _params.iterator();
704 while (it.hasNext())
705 {
706 if (it.next().equals(paramName.toUpperCase()))
707 {
708 _stmt.setTime(i, t);
709 doneOne = true;
710 }
711 i++;
712 }
713 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
714 }
715
716 public void setTimestamp(String paramName, Timestamp t) throws SQLException
717 {
718 storeParameterValue(paramName, t);
719 if (_debug) DjLogger.log(paramName + "=" + t);
720 int i = 1;
721 boolean doneOne = false;
722 Iterator it = _params.iterator();
723 while (it.hasNext())
724 {
725 if (it.next().equals(paramName.toUpperCase()))
726 {
727 _stmt.setTimestamp(i, t);
728 doneOne = true;
729 }
730 i++;
731 }
732 if (!doneOne) throw new SQLException (Messages.getString("SqlStatement.ParameterNotFound", paramName, fancyStmt()));
733 }
734
735 private void storeParameterValue(String paramName, Object value)
736 {
737 _parameterValues.put(paramName.toUpperCase(), value);
738 }
739
740 public java.sql.PreparedStatement getStmt()
741 {
742 return _stmt;
743 }
744
745 public int getParamCount()
746 {
747 return (_singleParams.size());
748 }
749
750 public String getParameter(int zeroBaseIndex)
751 {
752 return ((String ) (_singleParams.get(zeroBaseIndex)));
753 }
754
755 public boolean hasParameter(String paramName)
756 {
757 return _singleParams.contains(paramName.toUpperCase());
758 }
759
760 protected void checkMetaData() throws SQLException
761 {
762 if (_resultSet == null) throw new SQLException (Messages.getString("SqlStatement.NoMetaData"));
763 if (_metaData == null)
764 {
765 _metaData = _resultSet.getMetaData();
766 }
767 }
768
769 public int getPropertyIndex(String columnName) throws SQLException
770 {
771 checkMetaData();
772 int colCount = _metaData.getColumnCount();
773
774 for (int i = 1; i <= colCount; i++)
775 {
776 if (_metaData.getColumnName(i).equalsIgnoreCase(columnName))
777 {
778 return (i);
779 }
780 }
781 throw new SQLException (Messages.getString("SqlStatement.ColumnNoInSet", columnName));
782 }
783
784 public int getPropertyCount() throws SQLException
785 {
786 checkMetaData();
787 return (_metaData.getColumnCount());
788 }
789
790 public String getPropertyName(int oneBasedPropertyIndex) throws SQLException
791 {
792 checkMetaData();
793 return (_metaData.getColumnName(oneBasedPropertyIndex));
794 }
795
796 public static byte[] getBlob(ResultSet rs, String columnName) throws SQLException , IOException
797 {
798 ByteArrayOutputStream bos = new ByteArrayOutputStream ();
799
800 byte[] buff = new byte[4096];
801
802 InputStream bis = rs.getBinaryStream(columnName);
803 if (bis == null) return null;
804 for (;;)
805 {
806 int size = bis.read(buff);
807 if (size == -1) break;
808 bos.write(buff, 0, size);
809 }
810 return bos.toByteArray();
811 }
812
813 public String toString()
814 {
815 StringBuffer sb = new StringBuffer (500);
816 sb.append(fancyStmt());
817 Iterator it = _params.iterator();
818 sb.append("\n");
819 int i = 0;
820 while (it.hasNext())
821 {
822 String paramName = it.next().toString();
823 sb.append(":");
824 sb.append(paramName.toLowerCase());
825 sb.append("=");
826 sb.append(getParamValue(paramName));
827 sb.append("\n");
828 i++;
829 }
830 return sb.toString();
831 }
832
833 public Object getParamValue(String paramName)
834 {
835 return _parameterValues.get(paramName);
836 }
837 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.