|
1
19 package org.openharmonise.commons.dsi.impl;
20
21 import java.sql.*;
22 import java.sql.Date  ;
23 import java.text.*;
24 import java.util.*;
25 import java.util.logging.*;
26 import java.util.logging.Level ;
27
28 import org.openharmonise.commons.dsi.*;
29 import org.openharmonise.commons.dsi.ddl.*;
30 import org.openharmonise.commons.dsi.dml.*;
31 import org.openharmonise.commons.dsi.dml.functions.*;
32
33
34
40 public class DataStoreInterfaceFirebird extends AbstractDataStoreInterface {
41
42
45 private static Map keywords = null;
46
47
50 private static List joinTables = new Vector();
51 private static final String TYPE_NTEXT = "BLOB SUB_TYPE TEXT CHARACTER SET UNICODE_FSS";
52 private static final String TYPE_NVARCHAR_255 = "NCHAR VARYING (255)";
53 private static final String TYPE_INT = "INT";
54 private static final String KEYWORD_PRIMARY_KEY = "PRIMARY KEY";
55 private static final String KEYWORD_UNIQUE = "UNIQUE";
56 private static final String KEYWORD_DEFAULT = "DEFAULT";
57 private static final String KEYWORD_NOT_NULL = "NOT NULL";
58 private static final String KEYWORD_NULL = "NULL";
59 private static final String KEYWORD_FOREIGN_KEY = "FOREIGN KEY";
60 private static final String KEYWORD_REFERENCES = "REFERENCES";
61
62
65 private static final Logger m_logger = Logger.getLogger(DataStoreInterfaceFirebird.class.getName());
66
67 static {
69 DB_DATEFORMAT = "yyyy-MM-dd HH:mm:ss";
70 keywords = new Hashtable();
71 keywords.put("password", "\"password\"");
73 keywords.put("page", "\"page\"");
74 keywords.put("value", "\"value\"");
75 keywords.put("output_type", "\"output_type\"");
76 keywords.put("type", "\"type\"");
77 keywords.put("event", "\"event\"");
78 keywords.put("timestamp", "\"timestamp\"");
79 keywords.put("action", "\"action\"");
80 }
81
82
85 public DataStoreInterfaceFirebird() {;
86 }
87
88
97 public DataStoreInterfaceFirebird(
98 String sJDBCDriver,
99 String sDBurl,
100 String sDBUsr,
101 String sDBPwd) {
102 super(sJDBCDriver, sDBurl, sDBUsr, sDBPwd);
103 }
104
105
112 public DataStoreInterfaceFirebird(int nConnectionType) throws DataStoreException {
113 super(nConnectionType);
114 }
115
116
119 public int getSequenceNextValue(String sSeqName)
120 throws DataStoreException, SQLException {
121 Connection conn = null;
122 ResultSet rs = null;
123 Statement stmt = null;
124 String sSql = null;
125 int nSeq = -1;
126
127 conn = getConnection();
128
129 stmt = conn.createStatement();
130
131 sSql = "UPDATE oh_seq SET id = id +1 WHERE seq_name='" + sSeqName + "'";
132 stmt.executeUpdate(sSql);
133
134 sSql = "SELECT id from oh_seq WHERE seq_name='" + sSeqName + "'";
135 rs = stmt.executeQuery(sSql);
136
137 if (rs.next()) {
138 nSeq = rs.getInt(1);
139 } else {
140 throw new DataStoreException("Sequence [" + sSeqName + "] not found.");
141 }
142
143 if (rs != null) {
144 rs.close();
145 }
146
147 if (stmt != null) {
148 stmt.close();
149 }
150
151 if (isPooledConnection() && (conn != null)) {
152 this.closeConnection(conn);
153 }
154
155 return nSeq;
156 }
157
158
161 public void insertClob(
162 String sTable,
163 String sColumn,
164 String sClob,
165 String sCondition)
166 throws DataStoreException {
167 Connection conn = null;
168 Statement stmt = null;
169 ResultSet rs = null;
170
171 if ((sCondition == null) || (sCondition.length() == 0)) {
172 throw new DataStoreException("Missing CLOB condition");
173 }
174
175 StringBuffer sSql = new StringBuffer ();
176
177 try {
178 conn = getConnection();
179 stmt = conn.createStatement();
180
181 sSql.append("update ");
182
183 if (keywords.containsKey(sTable) == true) {
184 sSql.append(keywords.get(sTable));
185 } else {
186 sSql.append(sTable);
187 }
188
189 sSql.append(" set ");
190
191 if (keywords.containsKey(sColumn) == true) {
192 sSql.append(keywords.get(sColumn));
193
194 } else {
195 sSql.append(sColumn);
196 }
197
198 sSql.append(" = '");
199 sSql.append(addEscapeChars(sClob));
200 sSql.append("' where ");
201 sSql.append(sCondition);
202
203 stmt.execute(sSql.toString());
204
205 if (rs != null) {
206 rs.close();
207 }
208
209 if (stmt != null) {
210 stmt.close();
211 }
212
213 if (isPooledConnection() && (conn != null)) {
214 this.closeConnection(conn);
215 }
216 } catch (SQLException e) {
217 throw new DataStoreException("SQLException: " + e.getMessage());
218 }
219 }
220
221
224 public void updateClob(
225 String sTable,
226 String sColumn,
227 String sClob,
228 String sCondition)
229 throws DataStoreException {
230 Connection conn = null;
231 Statement stmt = null;
232 ResultSet rs = null;
233
234 if ((sCondition == null) || (sCondition.length() == 0)) {
235 throw new DataStoreException("Missing CLOB condition");
236 }
237
238 StringBuffer sSql = new StringBuffer ();
239
240 try {
241 conn = getConnection();
242 stmt = conn.createStatement();
243
244 sSql.append("update ");
245
246 if (keywords.containsKey(sTable) == true) {
247 sSql.append(keywords.get(sTable));
248 } else {
249 sSql.append(sTable);
250 }
251
252 sSql.append(" set ");
253
254 if (keywords.containsKey(sColumn) == true) {
255 sSql.append(keywords.get(sColumn));
256
257 } else {
258 sSql.append(sColumn);
259 }
260
261 sSql.append(" = '");
262 sSql.append(addEscapeChars(sClob));
263 sSql.append("' where ");
264 sSql.append(sCondition);
265
266 stmt.execute(sSql.toString());
267
268 if (rs != null) {
269 rs.close();
270 }
271
272 if (stmt != null) {
273 stmt.close();
274 }
275
276 if (isPooledConnection() && (conn != null)) {
277 this.closeConnection(conn);
278 }
279 } catch (SQLException e) {
280 throw new DataStoreException("SQLException: " + e.getMessage());
281 }
282 }
283
284
287 public String getClob(String sTable, String sColumn, String sCondition)
288 throws DataStoreException {
289 Connection conn = null;
290 Statement stmt = null;
291 ResultSet rs = null;
292 String sReturn = null;
293
294 if ((sCondition == null) || (sCondition.length() == 0)) {
295 throw new DataStoreException("Missing CLOB condition");
296 }
297
298 StringBuffer sSql = new StringBuffer ();
299
300 try {
301 conn = getConnection();
302
303 stmt = conn.createStatement();
304
305 sSql.append("select ");
306
307 if (keywords.containsKey(sColumn) == true) {
308 sSql.append(keywords.get(sColumn));
309
310 } else {
311 sSql.append(sColumn);
312 }
313
314 sSql.append(" from ");
315
316 if (keywords.containsKey(sTable) == true) {
317 sSql.append(keywords.get(sTable));
318 } else {
319 sSql.append(sTable);
320 }
321
322 sSql.append(" where ");
323 sSql.append(sCondition);
324
325 try {
326 rs = stmt.executeQuery(sSql.toString());
327 } catch (SQLException e) {
328 throw new DataStoreException(
329 "Error Executing query:" + sSql.toString(),
330 e);
331 }
332
333 if (rs.next()) {
334 sReturn = rs.getString(1);
335 }
336
337 if (rs != null) {
338 rs.close();
339 }
340
341 if (stmt != null) {
342 stmt.close();
343 }
344
345 if (isPooledConnection() && (conn != null)) {
346 this.closeConnection(conn);
347 }
348 } catch (SQLException e) {
349 throw new DataStoreException("SQLException: ", e);
350 }
351
352 return sReturn;
353 }
354
355
358 protected String addEscapeChars(String sOldString) {
359 int marker = -1;
360 int lastmarker = 0;
361 int quotemarker = -1;
362
363 if (sOldString == null) {
364 return "";
365 }
366
367 StringBuffer sBuf = new StringBuffer ();
368
369 quotemarker = sOldString.indexOf("'");
370
371 if (quotemarker >= 0) {
372 marker = quotemarker;
373 }
374
375 if (marker < 0) {
376 return sOldString;
377 } else {
378 while (marker >= 0) {
379 sBuf.append(sOldString.substring(lastmarker, marker));
381 sBuf.append("'");
382
383 quotemarker = -1;
385 lastmarker = marker;
386
387 quotemarker = sOldString.indexOf("'", marker + 1);
388
389 if (quotemarker >= 0) {
390 marker = quotemarker;
391 } else {
392 marker = -1;
393 }
394 }
395
396 sBuf.append(sOldString.substring(lastmarker));
397
398 return (sBuf.toString());
399 }
400 }
401
402
405 protected String getFunction(Function func) throws DataStoreException {
406 String sFunc = null;
407
408 if (func instanceof Substring) {
409 sFunc = getSubstring((Substring) func);
410
411 } else if (func instanceof Length) {
412 sFunc = getLength((Length) func);
413
414 } else if (func instanceof ToDate) {
415 sFunc = getToDate((ToDate) func);
416
417 } else {
418 throw new DataStoreException(
419 "Function not supported - " + func.getClass().getName());
420 }
421
422 return sFunc;
423 }
424
425
426
434 private String getSubstring(Substring substr) throws DataStoreException {
435
436 StringBuffer strbuf = new StringBuffer ();
437
438 strbuf.append("SUBSTR('").append(substr.getString()).append("',");
439
440 Object objStart = substr.getStart();
441
442 if (objStart instanceof Integer ) {
443 strbuf.append(((Integer ) objStart).toString());
444 } else if (objStart instanceof String ) {
445 strbuf.append((String ) objStart);
446 } else if (objStart instanceof Function) {
447 strbuf.append(getFunction((Function) objStart));
448 }
449
450 strbuf.append(",");
451
452 Object objEnd = substr.getFinish();
453
454 if (objEnd instanceof Integer ) {
455 strbuf.append(((Integer ) objEnd).toString());
456 } else if (objEnd instanceof String ) {
457 strbuf.append((String ) objEnd);
458 } else if (objEnd instanceof Function) {
459 strbuf.append(getFunction((Function) objEnd));
460 }
461
462 strbuf.append(")");
463
464 return strbuf.toString();
465 }
466
467
475 private String getLength(Length func) throws DataStoreException {
476 StringBuffer strbuf = new StringBuffer ();
477
478 strbuf.append("STRLEN(");
479
480 Object lenObj = func.getLengthObject();
481
482 if (lenObj instanceof String ) {
483 strbuf.append(lenObj);
484 } else if (lenObj instanceof ColumnRef) {
485 strbuf.append(((ColumnRef) lenObj).getFullRef());
486 }
487
488 strbuf.append(")");
489
490 return strbuf.toString();
491 }
492
493
494
501 private String getToDate(ToDate date) {
502 StringBuffer strbuf = new StringBuffer ();
503
504 strbuf.append("CONVERT(datetime,");
505
506 Object objVal = date.getValue();
507
508 if (objVal instanceof ColumnRef) {
509 strbuf.append(((ColumnRef) objVal).getFullRef());
510 } else if (objVal instanceof String ) {
511 strbuf.append("'").append(objVal).append("'");
512 }
513
514 strbuf.append(", 120 )");
515 return strbuf.toString();
516 }
517
518
521 protected String getDateAsSQL(String date) {
522 StringBuffer sSql = new StringBuffer ();
525
526 sSql.append("'").append(date).append("'");
527
528 return sSql.toString();
529 }
530
531
534 public String getDateDataType() {
535 return "TIMESTAMP";
536 }
537
538
541 public String getCLOBDataType() {
542 return "BLOB SUB_TYPE TEXT CHARACTER SET UNICODE_FSS";
543 }
544
545
548 public String getBooleanDataType() {
549 return "BOOLEAN";
550 }
551
552
555 public List getTableList() throws DataStoreException {
556 Vector tables = new Vector();
557 Connection con = getConnection();
558 try {
559 DatabaseMetaData dbMetaData = con.getMetaData();
561 ResultSet rs =
563 dbMetaData.getTables(null, null, "%", new String [] { "TABLE" });
564
565 while (rs.next()) {
566 tables.add(rs.getString("TABLE_NAME").trim().toLowerCase());
567 }
568
569 rs.close();
570 } catch (SQLException e) {
571 throw new DataStoreException(e.getLocalizedMessage(),e);
572 }
573
574 return tables;
575 }
576
577
580 public List getSequenceList() throws DataStoreException {
581 Vector seqs = new Vector();
582 ResultSet rs = null;
583
584 try {
585 rs = executeQuery("select seq_name from oh_seq order by seq_name");
586
587 while (rs.next()) {
588 seqs.add(rs.getString(1).trim());
589 }
590
591 rs.close();
592 } catch (SQLException e) {
593 throw new DataStoreException(e.getLocalizedMessage(),e);
594 }
595
596 return seqs;
597 }
598
599
605 public String getOuterJoinCondition(
606 ColumnRef ref1,
607 ColumnRef ref2,
608 AbstractDMLStatement DML) {
609 StringBuffer sSql = new StringBuffer ();
610
611 String sTable1 = ref1.getTable(); String sTable2 = ref2.getTable();
613 String sColumn1 = ref1.getColumn();
614 String sColumn2 = ref2.getColumn();
615
616 sSql.append(" right outer join ");
617
618
619 String sRealTable = null;
620
621 try {
622 if (joinTables.contains(sTable1) == true) {
623 if (DML.isAlias(sTable2) == true) {
624 sRealTable = DML.getTableName(sTable2);
625
626 if (keywords.containsKey(sRealTable) == true) {
627 sSql.append(keywords.get(sRealTable));
628 } else {
629 sSql.append(sRealTable);
630 }
631
632 sSql.append(" ");
633 }
634
635 if (keywords.containsKey(sTable2) == true) {
636 sSql.append(keywords.get(sTable2));
637 } else {
638 sSql.append(sTable2);
639 }
640 } else if (joinTables.contains(sTable2) == true) {
641 if (DML.isAlias(sTable1) == true) {
642 sRealTable = DML.getTableName(sTable1);
643
644 if (keywords.containsKey(sRealTable) == true) {
645 sSql.append(keywords.get(sRealTable));
646 } else {
647 sSql.append(sRealTable);
648 }
649
650 sSql.append(" ");
651 }
652 if (keywords.containsKey(sTable1) == true) {
653 sSql.append(keywords.get(sTable1));
654 } else {
655 sSql.append(sTable1);
656 }
657 }
658 } catch (DataStoreException e) {
659 m_logger.log(Level.WARNING, e.getLocalizedMessage(), e);
660 }
661
662 sSql.append(" on (");
663
664 if (keywords.containsKey(sTable1) == true) {
665 sSql.append(keywords.get(sTable1));
666 } else {
667 sSql.append(sTable1);
668 }
669
670 sSql.append(".");
671
672 if (keywords.containsKey(sColumn1) == true) {
673 sSql.append(keywords.get(sColumn1));
674 } else {
675 sSql.append(sColumn1);
676 }
677
678 sSql.append("=");
679
680 if (keywords.containsKey(sTable2) == true) {
681 sSql.append(keywords.get(sTable2));
682 } else {
683 sSql.append(sTable2);
684 }
685
686 sSql.append(".");
687
688 if (keywords.containsKey(sColumn2) == true) {
689 sSql.append(keywords.get(sColumn2));
690 } else {
691 sSql.append(sColumn2);
692 }
693
694 sSql.append(") ");
695
696 return sSql.toString();
697 }
698
699
706 public String getInnerJoinCondition(ColumnRef ref1, ColumnRef ref2) {
707 StringBuffer sSql = new StringBuffer ();
708
709 String sTable1 = ref1.getTable(); String sTable2 = ref2.getTable();
711 String sColumn1 = ref1.getColumn();
712 String sColumn2 = ref2.getColumn();
713
714 if (keywords.containsKey(sTable1) == true) {
715 sSql.append(keywords.get(sTable1));
716 } else {
717 sSql.append(sTable1);
718 }
719
720 sSql.append(".");
721
722 if (keywords.containsKey(sColumn1) == true) {
723 sSql.append(keywords.get(sColumn1));
724 } else {
725 sSql.append(sColumn1);
726 }
727
728 sSql.append("=");
729
730 if (keywords.containsKey(sTable2) == true) {
731 sSql.append(keywords.get(sTable2));
732 } else {
733 sSql.append(sTable2);
734 }
735
736 sSql.append(".");
737
738 if (keywords.containsKey(sColumn2) == true) {
739 sSql.append(keywords.get(sColumn2));
740 } else {
741 sSql.append(sColumn2);
742 }
743 return sSql.toString();
744 }
745
746
747
750 public String getInsertStatement(InsertStatement insert)
751 throws DataStoreException {
752 boolean bflag = false;
753 Vector saValues = new Vector(16);
754 Vector ColRefs = new Vector(16);
755
756 Map ValuePairs = insert.getColumnValuePairs();
757
758 Set set = ValuePairs.keySet();
759
760 Iterator iter = set.iterator();
761
762 while (iter.hasNext()) {
763 ColumnRef colref = (ColumnRef) iter.next();
764 ColRefs.add(colref);
765
766 saValues.add(ValuePairs.get(colref));
767 }
768
769 StringBuffer sSql = new StringBuffer ();
770
771 sSql.append("insert into ");
772 String sTableName = ((ColumnRef) ColRefs.get(0)).getTable();
773
774 if (keywords.get(sTableName) != null) {
776 sSql.append(keywords.get(sTableName));
777 } else {
778 sSql.append(sTableName);
779 }
780
781 if (insert.isColumnValuesBySelect()) {
782 sSql.append(" ");
783 sSql.append(getSelectStatement(insert.getColumnValuesSelect()));
784 } else {
785 sSql.append(" (");
786 String sColumnName = null;
787
788 for (int i = 0; i < ColRefs.size(); i++) {
789 if (bflag) {
790 sSql.append(",");
791 }
792 sColumnName = ((ColumnRef) ColRefs.get(i)).getColumn();
793
794 if (keywords.get(sColumnName) != null) {
796 sSql.append(keywords.get(sColumnName));
797
798 } else {
799 sSql.append(sColumnName);
800 }
801
802 bflag = true;
803 }
804
805 sSql.append(") values (");
806
807 for (int i = 0; i < saValues.size(); i++) {
808 if (i > 0) {
809 sSql.append(",");
810 }
811 try {
812
813 if (saValues
814 .get(i)
815 .getClass()
816 .getName()
817 .equalsIgnoreCase("java.lang.String")) {
818 sSql.append("'");
819 sSql.append(addEscapeChars((String ) saValues.get(i)));
820 sSql.append("'");
821 } else if (
822 saValues.get(i).getClass().getName().equalsIgnoreCase(
823 "java.lang.Integer")) {
824 sSql.append((Integer ) saValues.get(i));
825 } else if (saValues.get(i) instanceof java.util.Date ) {
826 SimpleDateFormat date_formatter =
827 new SimpleDateFormat(DB_DATEFORMAT);
828 String sDate =
829 date_formatter.format((java.util.Date ) saValues.get(i));
830 sSql.append(getDateAsSQL(sDate));
831 } else if (
832 saValues.get(i).getClass().getName().equalsIgnoreCase(
833 SelectStatement.class.getName())) {
834 sSql.append(
835 getSelectStatement((SelectStatement) saValues.get(i)));
836 } else {
837 throw new DataStoreException(
838 "Error creating Insert statement: " + sSql.toString());
839 }
840 } catch (NullPointerException e) {
841 throw new DataStoreException(
842 "Null value in insert for "
843 + ((ColumnRef) ColRefs.get(i)).getColumn());
844 }
845 }
846
847 sSql.append(")");
848 }
849
850 return (sSql.toString());
851 }
852
853
854
857 public String getUpdateStatement(UpdateStatement update)
858 throws DataStoreException {
859 boolean bflag = false;
860 Vector saValues = new Vector(16);
861 Vector ColRefs = new Vector(16);
862
863 Map ValuePairs = update.getColumnValuePairs();
864
865 Set set = ValuePairs.keySet();
866
867 Iterator iter = set.iterator();
868
869 while (iter.hasNext()) {
870 ColumnRef colref = (ColumnRef) iter.next();
871 ColRefs.add(colref);
872
873 saValues.add(ValuePairs.get(colref));
874 }
875
876 StringBuffer sSql = new StringBuffer ();
877
878 sSql.append("update ");
879 String sTableName = ((ColumnRef) ColRefs.get(0)).getTable();
880
881 if (keywords.get(sTableName) != null) {
883 sSql.append(keywords.get(sTableName));
884 } else {
885 sSql.append(sTableName);
886 }
887
888 sSql.append(" set ");
889 String sColumnName = null;
890
891 for (int i = 0; i < ColRefs.size(); i++) {
892 if (bflag) {
893 sSql.append(",");
894 }
895
896 if (keywords.get(sTableName) != null) {
898 sSql.append(keywords.get(sTableName));
899 } else {
900 sSql.append(sTableName);
901 }
902
903 sSql.append(".");
904
905 sColumnName = ((ColumnRef) ColRefs.get(i)).getColumn();
906
907 if (keywords.get(sColumnName) != null) {
909 sSql.append(keywords.get(sColumnName));
910 } else {
911 sSql.append(sColumnName);
912 }
913
914 sSql.append("=");
915
916 if (saValues.get(i) == null) {
917 sSql.append("null");
918 } else if (
919 saValues.get(i).getClass().getName().equalsIgnoreCase(
920 "java.lang.String")) {
921 sSql.append("'");
922 sSql.append(addEscapeChars((String ) saValues.get(i)));
923 sSql.append("'");
924 } else if (
925 saValues.get(i).getClass().getName().equalsIgnoreCase(
926 "java.lang.Integer")) {
927 sSql.append(((Integer ) saValues.get(i)).toString());
928 } else if (
929 saValues.get(i).getClass().getName().equalsIgnoreCase(
930 "java.util.Date")
931 || saValues.get(i).getClass().getName().equalsIgnoreCase(
932 "java.sql.Date")) {
933 SimpleDateFormat date_formatter = new SimpleDateFormat(DB_DATEFORMAT);
934 String sDate =
935 date_formatter.format((java.util.Date ) saValues.get(i));
936 sSql.append(getDateAsSQL(sDate));
937 } else {
938 throw new DataStoreException(
939 "Error creating Update statement: " + sSql.toString());
940 }
941
942 bflag = true;
943 }
944
945 sSql.append(" where ");
946
947 sSql.append(generateWhereClause(update));
948
949 return (sSql.toString());
950 }
951
952
953
956 public String getDeleteStatement(DeleteStatement delete)
957 throws DataStoreException {
958 String sTable = delete.getTable();
959
960 if (!delete.hasWhereClause()) {
961 throw new DataStoreException("Delete statements without Where clauses are not allowed.");
962 }
963
964 WhereConditionGroup where = delete.getWhereConditions();
965
966 StringBuffer sSql = new StringBuffer ();
967
968 sSql.append("delete from ");
969
970 if (keywords.get(sTable) != null) {
971 sSql.append(keywords.get(sTable));
972 } else {
973 sSql.append(sTable);
974 }
975
976 sSql.append(" where ");
977 sSql.append(generateWhereClause(delete));
978
979 return (sSql.toString());
980 }
981
982
983
986 public String getSelectStatement(SelectStatement select)
987 throws DataStoreException {
988 List SelectCols = select.getSelectColumns();
989 List maxcols = select.getSelectMaxColumns();
990 JoinConditions outerJoins = null;
991 JoinConditions innerJoins = null;
992 WhereConditionGroup where = select.getWhereConditions();
993
994 joinTables.clear();
996 Set colSet = select.getOrderByColumns();
997
998 StringBuffer sSql = new StringBuffer ();
999
1000 sSql.append("select ");
1001
1002 if (select.isLimit()) {
1003 sSql.append("first ");
1004 sSql.append(select.getLimit());
1005 sSql.append(" ");
1006 }
1007
1008 if (select.isDistinct()) {
1009 sSql.append("distinct ");
1010 }
1011
1012 if ((SelectCols == null) || (SelectCols.size() == 0)) {
1013 sSql.append("*");
1014 } else {
1015 boolean bMax = false;
1016
1017 for (int i = 0; i < SelectCols.size(); i++) {
1018 if ((maxcols != null) && maxcols.contains(new Integer (i))) {
1019 bMax = true;
1020 }
1021
1022 if (i > 0) {
1023 sSql.append(",");
1024 }
1025
1026 if (SelectCols
1027 .get(i)
1028 .getClass()
1029 .getName()
1030 .equalsIgnoreCase("java.lang.Integer")) {
1031 sSql.append((Integer ) SelectCols.get(i));
1032 } else {
1033 if (bMax) {
1034 sSql.append("max(");
1035 }
1036
1037 ColumnRef colRef = (ColumnRef) SelectCols.get(i);
1038
1040 String sTable = colRef.getTable(); String sColumn = colRef.getColumn();
1043 if (keywords.containsKey(sTable) == true) {
1044 sSql.append(keywords.get(sTable));
1045 } else {
1046 sSql.append(sTable);
1047 }
1048
1049 sSql.append(".");
1050
1051 if (keywords.containsKey(sColumn) == true) {
1052 sSql.append(keywords.get(sColumn));
1053 } else {
1054 sSql.append(sColumn);
1055 }
1056
1057 if (bMax) {
1058 sSql.append(")");
1059 }
1060 }
1061 }
1062
1063 if (colSet.isEmpty() == false) {
1064 Iterator iter = colSet.iterator();
1065 int i=0;
1066 while (iter.hasNext()) {
1067 ColumnRef ordercol = (ColumnRef) iter.next();
1068 sSql.append(",");
1069 sSql.append(getOrderByRef(ordercol));
1070 sSql.append(" AS upperCol").append(i++);
1071 }
1072
1073 }
1074 }
1075
1076 sSql.append(" from ");
1077
1078 Vector forTables = new Vector();
1079 String sTable = null;
1080
1081 if (select.hasJoinConditions()) {
1082 outerJoins = select.getJoinConditions().getOuterJoins();
1083 innerJoins = select.getJoinConditions().getInnerJoins();
1084 if (select.hasWhereConditions()) {
1087 List wvec = where.getTableList();
1088 for (int i = 0; i < wvec.size(); i++) {
1089 sTable = (String ) wvec.get(i);
1090 if (forTables.contains(sTable) == false) {
1091 forTables.add(wvec.get(i));
1092 }
1093 }
1094 }
1095
1096 List jvec = innerJoins.getTableList(); for (int i = 0; i < jvec.size(); i++) {
1100 sTable = (String ) jvec.get(i);
1101 if (forTables.contains(sTable) == false) {
1102 forTables.add(sTable);
1103 }
1104 }
1105
1106 for (int i = 0; i < outerJoins.size(); i++) {
1109 String sTable1 = outerJoins.getLeftTableName(i);
1110 String sTable2 = outerJoins.getRightTableName(i);
1111
1112 if (forTables.contains(sTable1) == true) {
1113 if (forTables.contains(sTable2) == true) {
1114 forTables.remove(sTable2);
1115 }
1116 } else if (
1117 forTables.contains(sTable1) == false
1118 && forTables.contains(sTable2) == false) {
1119 forTables.add(sTable1);
1120 }
1121 }
1122
1123 joinTables.addAll(forTables); } else if (SelectCols.size() > 0) {
1125 forTables = new Vector(1);
1126 String sTempTable = ((ColumnRef) SelectCols.get(0)).getTable();
1127 forTables.add(sTempTable);
1128 } else {
1129 forTables = new Vector(1);
1130 if (select.hasWhereConditions()) {
1131 String sTempTable = null;
1132
1133 List wvec = where.getTableList();
1134 sTempTable = (String ) wvec.get(0);
1135
1136 forTables.add(sTempTable);
1137 }
1138 }
1139
1140 sSql.append(generateFromClause(select, forTables));
1142
1143 if (select.hasJoinConditions() == true) {
1144 for (int i = 0; i < outerJoins.size(); i++) {
1145 sSql.append(" ");
1146 sSql.append(
1149 getOuterJoinCondition(
1150 outerJoins.getLeftColumnRef(i),
1151 outerJoins.getRightColumnRef(i),
1152 select));
1153 }
1154 }
1155
1156 if (select.hasWhereClause()) {
1157 sSql.append(" where ");
1158
1159 if ((innerJoins != null) && (innerJoins.size() > 0)) {
1160 for (int i = 0; i < innerJoins.size(); i++) {
1162 sSql.append(" ");
1163
1164 if (i > 0) {
1165 sSql.append("and ");
1166 }
1167
1168 sSql.append(
1171 getInnerJoinCondition(
1172 innerJoins.getLeftColumnRef(i),
1173 innerJoins.getRightColumnRef(i)));
1174 }
1175 sSql.append(" and");
1176 }
1177
1178 sSql.append(generateWhereClause(select));
1179 }
1180
1181 if (colSet.isEmpty() == false) {
1182 sSql.append(generateOrderByClause(select));
1183 }
1184
1185 return (sSql.toString());
1186 }
1187
1188
1189
1192 protected String generateOrderByClause(SelectStatement select) {
1193 StringBuffer sSql = new StringBuffer ();
1194 Set orderColSet = select.getOrderByColumns();
1195
1196 Iterator iter = orderColSet.iterator();
1197 int i=0;
1198
1199 if(iter.hasNext()) {
1200 sSql.append(" order by ");
1201
1202 while (iter.hasNext()) {
1203 ColumnRef ordercol = (ColumnRef) iter.next();
1204
1205 int nOrderColType = ordercol.getDataType();
1206
1207 if ((nOrderColType == ColumnRef.TEXT)
1208 || (nOrderColType == ColumnRef.LONG_TEXT)) {
1209 sSql.append("upperCol").append(i++);
1210 } else {
1211 sSql.append(getOrderByRef(ordercol));
1212 }
1213
1214 sSql.append(" ");
1215 sSql.append(select.getOrderByDirection(ordercol));
1216 if(iter.hasNext()) {
1217 sSql.append(",");
1218 }
1219 }
1220 }
1221
1222
1223 return (sSql.toString());
1224 }
1225
1226
1229 protected String getOrderByRef(ColumnRef ordercol) {
1230 StringBuffer sSql = new StringBuffer ();
1231 int nOrderColType = ordercol.getDataType();
1232
1233 if ((nOrderColType == ColumnRef.TEXT)
1234 || (nOrderColType == ColumnRef.LONG_TEXT)) {
1235 sSql.append("upper(");
1236 }
1237
1238 if (nOrderColType == ColumnRef.LONG_TEXT) {
1239 sSql.append("cast (");
1240 }
1241
1242 String sTable = ordercol.getTable();
1243
1244 if (ordercol.hasTableAlias() == true) {
1245 sTable = ordercol.getTableAlias();
1246 }
1247
1248 if (keywords.get(sTable) != null) {
1249 sSql.append(keywords.get(sTable));
1250 } else {
1251 sSql.append(sTable);
1252 }
1253
1254 sSql.append(".");
1255
1256 String sColumn = ordercol.getColumn();
1257
1258 if (keywords.get(sColumn) != null) {
1259 sSql.append(keywords.get(sColumn));
1260 } else {
1261 sSql.append(sColumn);
1262 }
1263
1264 if (nOrderColType == ColumnRef.LONG_TEXT) {
1265 sSql.append(" as char(80)");
1266 }
1267
1268 if (nOrderColType == ColumnRef.LONG_TEXT) {
1269 sSql.append(")");
1270 }
1271
1272 if ((nOrderColType == ColumnRef.TEXT)
1273 || (nOrderColType == ColumnRef.LONG_TEXT)) {
1274 sSql.append(")");
1275 }
1276
1277 return sSql.toString();
1278 }
1279
1280
1288 protected String generateFromClause(
1289 AbstractDMLStatement DML,
1290 Vector saTables)
1291 throws DataStoreException {
1292 StringBuffer sSql = new StringBuffer ();
1293
1294 for (int i = 0; i < saTables.size(); i++) {
1295 String sTable = (String ) saTables.get(i);
1296
1297 if (i > 0) {
1298 sSql.append(",");
1299 }
1300
1301 if (DML.isAlias(sTable) == true) {
1302 String sRealTable = DML.getTableName(sTable);
1303
1304 if (keywords.containsKey(sRealTable) == true) {
1306 sSql.append(keywords.get(sRealTable));
1307 } else {
1308 sSql.append(sRealTable);
1309 }
1310
1311 sSql.append(" ");
1312
1313 if (keywords.containsKey(sTable) == true) {
1315 sSql.append(keywords.get(sTable));
1316 } else {
1317 sSql.append(sTable);
1318 }
1319 } else { if (keywords.containsKey(sTable) == true) {
1321 sSql.append(keywords.get(sTable));
1322 } else {
1323 sSql.append(sTable);
1324 }
1325 }
1326 }
1327
1328 return (sSql.toString());
1329 }
1330
1331
1332
1335 protected String generateWhereClause(SelectStatement select)
1336 throws DataStoreException {
1337
1338 return generateWhereClause((AbstractDMLStatement) select);
1339 }
1340
1341
1342
1345 protected String generateWhereClause(AbstractDMLStatement DML)
1346 throws DataStoreException {
1347 if (!DML.hasWhereClause()) {
1348 return null;
1349 }
1350
1351 WhereConditionGroup where = DML.getWhereConditions();
1352
1353 return (generateWhereClause(where, DML));
1354 }
1355
1356
1364 private String generateWhereClause(
1365 WhereConditionGroup where,
1366 AbstractDMLStatement DML)
1367 throws DataStoreException {
1368 boolean bAnd = false;
1369
1370 StringBuffer sSql = new StringBuffer ();
1371
1372 for (int i = 0; i < where.size(); i++) {
1373 if (bAnd) {
1374 sSql.append(" ");
1375 sSql.append(where.getStringingOperator());
1376 }
1377
1378 if (where.isWhereConditionsLeaf(i) == false) {
1379 sSql.append(" (");
1380 sSql.append(
1381 generateWhereClause((WhereConditionGroup) where.getCondition(i), DML));
1382 sSql.append(")");
1383 } else {
1384 StringBuffer sCol = new StringBuffer ();
1385
1386 String sColumn = where.getColumnName(i);
1387 String sTableName = where.getTableName(i);
1388
1389 if (keywords.get(sTableName) != null) {
1390 sCol.append(keywords.get(sTableName));
1391 } else {
1392 sCol.append(sTableName);
1393 }
1394
1395 sCol.append(".");
1396
1397 if (keywords.get(sColumn) != null) {
1398 sCol.append(keywords.get(sColumn));
1399 } else {
1400 sCol.append(sColumn);
1401 }
1402
1403 String sOperator = where.getOperator(i);
1404 List Values = where.getValues(i);
1405
1406 if (!sOperator.equalsIgnoreCase("NOT IN")
1407 && !sOperator.equalsIgnoreCase("IN")
1408 && !sOperator.equalsIgnoreCase("BETWEEN")
1409 && !sOperator.equalsIgnoreCase("OR")
1410 && !sOperator.equalsIgnoreCase(
1411 "CONTAINS")
1413 ) {
1414 for (int j = 0; j < Values.size(); j++) {
1415 sSql.append(" ");
1416 sSql.append(sCol.toString());
1417
1418 if (Values.get(j) == null) {
1419 if (sOperator.equals("=") == true
1420 || sOperator.equals("is") == true) {
1421 sSql.append(" is null");
1422 } else if (
1423 sOperator.equals("!=") == true
1424 || sOperator.equals("is not") == true) {
1425 sSql.append(" is not null");
1426 }
1427 continue;
1428 }
1429
1430 if (sOperator.equalsIgnoreCase("LIKE")
1431 || sOperator.equalsIgnoreCase("STARTS_WITH")) {
1432 sSql.append(" ");
1433 sSql.append("LIKE");
1434 sSql.append(" ");
1435 } else {
1436 sSql.append(sOperator);
1437 }
1438
1439 if (Values
1440 .get(j)
1441 .getClass()
1442 .getName()
1443 .equalsIgnoreCase(SelectStatement.class.getName())) {
1444 SelectStatement query = (SelectStatement) Values.get(j);
1445 sSql.append("(");
1446 sSql.append(getSelectStatement(query));
1447 sSql.append(")");
1448 } else if (
1449 Values.get(j).getClass().getName().equalsIgnoreCase(
1450 "java.util.Date")
1451 || Values.get(j).getClass().getName().equalsIgnoreCase(
1452 "java.sql.Date")) {
1453 SimpleDateFormat date_formatter =
1454 new SimpleDateFormat(DB_DATEFORMAT);
1455 String sDate =
1456 date_formatter.format((java.util.Date ) Values.get(j));
1457 sSql.append(getDateAsSQL(sDate));
1458 } else if (Values.get(j) instanceof Function) {
1459 sSql.append(getFunction((Function) Values.get(j)));
1460 } else {
1461 if (!Values
1462 .get(j)
1463 .getClass()
1464 .getName()
1465 .equalsIgnoreCase("java.lang.Integer")) {
1466 sSql.append("'");
1467 }
1468
1469 sSql.append(addEscapeChars(Values.get(j).toString()));
1470
1471 if (!Values
1472 .get(j)
1473 .getClass()
1474 .getName()
1475 .equalsIgnoreCase("java.lang.Integer")) {
1476 if (sOperator.equals("STARTS_WITH")) {
1477 sSql.append("%");
1478 }
1479 sSql.append("'");
1480 }
1481 }
1482 }
1483 } else if (
1484 sOperator.equalsIgnoreCase("IN")
1485 || sOperator.equalsIgnoreCase("NOT IN")) {
1486 sSql.append(" ");
1487 sSql.append(sCol);
1488 sSql.append(" ");
1489 sSql.append(sOperator);
1490 sSql.append(" (");
1491
1492 for (int j = 0; j < Values.size(); j++) {
1493 if (j != 0) {
1494 sSql.append(",");
1495 }
1496
1497 if (Values
1498 .get(j)
1499 .getClass()
1500 .getName()
1501 .equalsIgnoreCase(SelectStatement.class.getName())) {
1502 SelectStatement query = (SelectStatement) Values.get(j);
1503
1504 sSql.append(getSelectStatement(query));
1505 } else if (
1506 Values.get(j).getClass().getName().equalsIgnoreCase(
1507 "java.util.Date")
1508 || Values.get(j).getClass().getName().equalsIgnoreCase(
1509 "java.sql.Date")) {
1510 SimpleDateFormat date_formatter =
1511 new SimpleDateFormat(DB_DATEFORMAT);
1512 String sDate =
1513 date_formatter.format((java.util.Date ) Values.get(j));
1514 sSql.append(getDateAsSQL(sDate));
1515 } else {
1516 if (!Values
1517 .get(j)
1518 .getClass()
1519 .getName()
1520 .equalsIgnoreCase("java.lang.Integer")) {
1521 sSql.append("'");
1522 }
1523
1524 sSql.append(addEscapeChars(Values.get(j).toString()));
1525
1526 if (!Values
1527 .get(j)
1528 .getClass()
1529 .getName()
1530 .equalsIgnoreCase("java.lang.Integer")) {
1531 sSql.append("'");
1532 }
1533 }
1534 }
1535
1536 sSql.append(")");
1537 } else if (sOperator.equalsIgnoreCase("BETWEEN")) {
1538 sSql.append(" ");
1539 sSql.append(sCol.toString());
1540 sSql.append(sOperator);
1541 sSql.append(" ");
1542
1543 if (Values
1544 .get(0)
1545 .getClass()
1546 .getName()
1547 .equalsIgnoreCase("java.util.Date")
1548 || Values.get(0).getClass().getName().equalsIgnoreCase(
1549 "java.sql.Date")) {
1550 SimpleDateFormat date_formatter =
1551 new SimpleDateFormat(DB_DATEFORMAT);
1552 String sDate =
1553 date_formatter.format((java.util.Date ) Values.get(0));
1554 sSql.append(getDateAsSQL(sDate));
1555 } else {
1556 if (!Values
1557 .get(0)
1558 .getClass()
1559 .getName()
1560 .equalsIgnoreCase("java.lang.Integer")) {
1561 sSql.append("'");
1562 }
1563
1564 sSql.append(Values.get(0).toString());
1565
1566 if (!Values
1567 .get(0)
1568 .getClass()
1569 .getName()
1570 .equalsIgnoreCase("java.lang.Integer")) {
1571 sSql.append("'");
1572 }
1573 }
1574
1575 sSql.append(" AND ");
1576
1577 if (Values
1578 .get(1)
1579 .getClass()
1580 .getName()
1581 .equalsIgnoreCase("java.util.Date")
1582 || Values.get(1).getClass().getName().equalsIgnoreCase(
1583 "java.sql.Date")) {
1584 SimpleDateFormat date_formatter =
1585 new SimpleDateFormat(DB_DATEFORMAT);
1586 String sDate =
1587 date_formatter.format((java.util.Date ) Values.get(1));
1588 sSql.append(getDateAsSQL(sDate));
1589 } else {
1590 if (!Values
1591 .get(1)
1592 .getClass()
1593 .getName()
1594 .equalsIgnoreCase("java.lang.Integer")) {
1595 sSql.append("'");
1596 }
1597
1598 sSql.append(Values.get(1).toString());
1599
1600 if (!Values
1601 .get(1)
1602 .getClass()
1603 .getName()
1604 .equalsIgnoreCase("java.lang.Integer")) {
1605 sSql.append("'");
1606 }
1607 }
1608 } else if (sOperator.equalsIgnoreCase("CONTAINS") == true) {
1609 sSql.append("(");
1610 for (int j = 0; j < Values.size(); j++) {
1611 if (j != 0) {
1612 sSql.append(" OR ");
1613 }
1614
1615 sSql.append(" (");
1616 sSql.append(sCol);
1617 sSql.append(" ");
1618 sSql.append("LIKE");
1619 sSql.append(" ");
1620
1621 if ((Values.get(j) instanceof Integer ) == false) {
1622 sSql.append("'%");
1623 }
1624
1625 sSql.append(addEscapeChars(Values.get(j).toString()));
1626
1627 sSql.append("%'");
1628
1629 sSql.append(")");
1630 }
1631 sSql.append(")");
1632 }
1633 }
1634
1635 bAnd = true;
1636 }
1637
1638 return (sSql.toString());
1639 }
1640
1641
1644 public String getJoinCondition(
1645 ColumnRef ref1,
1646 ColumnRef ref2,
1647 boolean bIsOuter) {
1648 return null;
1649 }
1650
1651
1654 public void createTable(TableDefinition tblDef) throws DataStoreException {
1655 StringBuffer str = new StringBuffer ();
1656
1657 str.append("create table ")
1658 .append(tblDef.getName());
1659 str.append(" (");
1660
1661 Iterator iter = tblDef.iterator();
1662
1663 while (iter.hasNext()) {
1664 ColumnDefinition coldef = (ColumnDefinition) iter.next();
1665
1666 str.append(coldef.getName());
1667 str.append(" ");
1668 int nDataType = coldef.getDataType();
1669
1670 if(nDataType == ColumnDefinition.NUMBER) {
1671 str.append(TYPE_INT);
1672 } else if(nDataType == ColumnDefinition.TEXT) {
1673 str.append(TYPE_NVARCHAR_255);
1674 } else if(nDataType == ColumnDefinition.LONG_TEXT) {
1675 str.append(TYPE_NTEXT);
1676 } else if(nDataType == ColumnDefinition.DATE) {
1677 str.append(getDateDataType());
1678 } else if(nDataType == ColumnDefinition.BOOLEAN) {
1679 str.append(getBooleanDataType());
1680 }
1681
1682 Object defaultVal = coldef.getDefault();
1683
1684 if(defaultVal != null) {
1685
1686 str.append(" ").append(KEYWORD_DEFAULT).append(" ");
1687
1688 if(defaultVal instanceof String && (nDataType == ColumnDefinition.TEXT
1689 || nDataType == ColumnDefinition.LONG_TEXT)) {
1690 str.append(defaultVal);
1691 } else if(defaultVal instanceof Date ) {
1692
1693 } else if(defaultVal instanceof Integer ) {
1694 str.append(((Integer )defaultVal).intValue());
1695 } else if (coldef.allowNulls() == true) {
1696 str.append(KEYWORD_NULL);
1697 }
1698 }
1699
1700 if (coldef.allowNulls() == false) {
1701 str.append(" ");
1702 str.append(KEYWORD_NOT_NULL);
1703 }
1704
1705 if(coldef.isPrimaryKey()) {
1706 str.append(" ").append(KEYWORD_PRIMARY_KEY);
1707 } else if(coldef.isUnique()) {
1708 str.append(" ").append(KEYWORD_UNIQUE);
1709 } else if(coldef.isForeignKey()) {
1710 str.append(" ").append(KEYWORD_REFERENCES)
1711 .append(" ").append(coldef.getForeignKeyReference());
1712 }
1713
1714 if(iter.hasNext()) {
1715 str.append(",");
1716 }
1717 }
1718
1719 str.append(")");
1720
1721 execute(str.toString());
1722 }
1723}
1724 |
Popular Tags |
Java API By Example, From Geeks To Geeks.