|
1
22 package org.jboss.ejb.plugins.cmp.jdbc;
23
24 import java.sql.Connection  ;
25 import java.sql.DatabaseMetaData ;
26 import javax.sql.DataSource ;
27 import java.sql.SQLException ;
28 import java.sql.Statement ;
29 import java.sql.ResultSet ;
30 import java.util.zip.CRC32 ;
31 import java.util.ArrayList ;
32
33 import org.jboss.deployment.DeploymentException;
34 import org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCEntityBridge;
35 import org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCFieldBridge;
36 import org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCAbstractEntityBridge;
37 import org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCAbstractCMRFieldBridge;
38 import org.jboss.logging.Logger;
39
40 import java.util.Vector ;
41
42
50 public final class SQLUtil
51 {
52 public static final String EMPTY_STRING = "";
53 public static final String INSERT_INTO = "INSERT INTO ";
54 public static final String VALUES = " VALUES ";
55 public static final String SELECT = "SELECT ";
56 public static final String DISTINCT = "DISTINCT ";
57 public static final String FROM = " FROM ";
58 public static final String WHERE = " WHERE ";
59 public static final String ORDERBY = " ORDER BY ";
60 public static final String DELETE_FROM = "DELETE FROM ";
61 public static final String AND = " AND ";
62 public static final String OR = " OR ";
63 public static final String NOT = " NOT ";
64 public static final String EXISTS = "EXISTS ";
65 public static final String COMMA = ", ";
66 public static final String LEFT_JOIN = " LEFT JOIN ";
67 public static final String LEFT_OUTER_JOIN = " LEFT OUTER JOIN ";
68 public static final String ON = " ON ";
69 public static final String NOT_EQUAL = "<>";
70 public static final String CREATE_TABLE = "CREATE TABLE ";
71 public static final String DROP_TABLE = "DROP TABLE ";
72 public static final String CREATE_INDEX = "CREATE INDEX ";
73 public static final String NULL = "NULL";
74 public static final String IS = " IS ";
75 public static final String IN = " IN ";
76 public static final String EMPTY = "EMPTY";
77 public static final String BETWEEN = " BETWEEN ";
78 public static final String LIKE = " LIKE ";
79 public static final String MEMBER_OF = " MEMBER OF ";
80 public static final String ESCAPE = " ESCAPE ";
81 public static final String CONCAT = "CONCAT";
82 public static final String SUBSTRING = "SUBSTRING";
83 public static final String LCASE = "LCASE";
84 public static final String UCASE = "UCASE";
85 public static final String LENGTH = "LENGTH";
86 public static final String LOCATE = "LOCATE";
87 public static final String ABS = "ABS";
88 public static final String MOD = "MOD";
89 public static final String SQRT = "SQRT";
90 public static final String COUNT = "COUNT";
91 public static final String MAX = "MAX";
92 public static final String MIN = "MIN";
93 public static final String AVG = "AVG";
94 public static final String SUM = "SUM";
95 public static final String ASC = " ASC";
96 public static final String DESC = " DESC";
97 public static final String OFFSET = " OFFSET ";
98 public static final String LIMIT = " LIMIT ";
99 public static final String UPDATE = "UPDATE ";
100 public static final String SET = " SET ";
101 public static final String TYPE = " TYPE ";
102 private static final String DOT = ".";
103
104 private static final String EQ_QUESTMARK = "=?";
105
106 private static final Vector rwords = new Vector ();
107
108 public static String getTableNameWithoutSchema(String tableName)
109 {
110 final int dot = tableName.indexOf('.');
111 if(dot != -1)
112 {
113 char firstChar = tableName.charAt(0);
114 tableName = tableName.substring(dot + 1);
115 if(firstChar == '"' || firstChar == '\'')
116 {
117 tableName = firstChar + tableName;
118 }
119 }
120 return tableName;
121 }
122
123 public static String getSchema(String tableName)
124 {
125 String schema = null;
126 final int dot = tableName.indexOf('.');
127 if(dot != -1)
128 {
129 char firstChar = tableName.charAt(0);
130 final boolean quoted = firstChar == '"' || firstChar == '\'';
131 schema = tableName.substring(quoted ? 1 : 0, dot);
132 }
133 return schema;
134 }
135
136 public static String fixTableName(String tableName, DataSource dataSource)
137 throws DeploymentException
138 {
139 char firstChar = tableName.charAt(0);
141 if(firstChar == '"' || firstChar == '\'')
142 {
143 return tableName;
144 }
145
146 String strSchema = "";
148 int iIndex;
149 if((iIndex = tableName.indexOf('.')) != -1)
150 {
151 strSchema = tableName.substring(0, iIndex);
152 tableName = tableName.substring(iIndex + 1);
153 }
154
155 if(rwords != null)
159 {
160 for(int i = 0; i < rwords.size(); i++)
161 {
162 if(((String )rwords.elementAt(i)).equalsIgnoreCase(tableName))
163 {
164 tableName = "X" + tableName;
165 break;
166 }
167 }
168 }
169
170 Connection con = null;
171 try
172 {
173 con = dataSource.getConnection();
174 DatabaseMetaData dmd = con.getMetaData();
175
176 int maxLength = dmd.getMaxTableNameLength();
178 if(maxLength > 0 && tableName.length() > maxLength)
179 {
180 CRC32 crc = new CRC32 ();
181 crc.update(tableName.getBytes());
182 String nameCRC = Long.toString(crc.getValue(), 36);
183
184 tableName = tableName.substring(
185 0,
186 maxLength - nameCRC.length() - 2);
187 tableName += "_" + nameCRC;
188 }
189
190 if(dmd.storesLowerCaseIdentifiers())
192 {
193 tableName = tableName.toLowerCase();
194 }
195 else if(dmd.storesUpperCaseIdentifiers())
196 {
197 tableName = tableName.toUpperCase();
198 }
199 if(strSchema.length() > 0)
201 {
202 tableName = strSchema + "." + tableName;
203 }
204 return tableName;
205 }
206 catch(SQLException e)
207 {
208 throw new DeploymentException("Error while fixing table name", e);
211 }
212 finally
213 {
214 JDBCUtil.safeClose(con);
215 }
216 }
217
218 public static void addToRwords(String word)
219 {
220 if(!rwords.contains(word))
221 rwords.add(word);
222 }
223
224
225 public static String fixConstraintName(String name, DataSource dataSource)
226 throws DeploymentException
227 {
228 return fixTableName(name, dataSource).replace('.', '_');
229 }
230
231 public static String getCreateTableColumnsClause(JDBCFieldBridge[] fields)
238 {
239 StringBuffer buf = new StringBuffer (100);
240 boolean comma = false;
241 for(int i = 0; i < fields.length; ++i)
242 {
243 JDBCType type = getJDBCType(fields[i]);
244 if(type != null)
245 {
246 if(comma)
247 buf.append(COMMA);
248 else
249 comma = true;
250 buf.append(getCreateTableColumnsClause(type));
251 }
252 }
253 return buf.toString();
254 }
255
256
261 public static String getCreateTableColumnsClause(JDBCType type)
262 {
263 String [] columnNames = type.getColumnNames();
264 String [] sqlTypes = type.getSQLTypes();
265 boolean[] notNull = type.getNotNull();
266
267 StringBuffer buf = new StringBuffer ();
268 for(int i = 0; i < columnNames.length; i++)
269 {
270 if(i != 0)
271 buf.append(COMMA);
272 buf.append(columnNames[i]).append(' ').append(sqlTypes[i]);
273 if(notNull[i])
274 buf.append(NOT).append(NULL);
275 }
276 return buf.toString();
277 }
278
279
284
287 public static StringBuffer getColumnNamesClause(JDBCFieldBridge[] fields, StringBuffer sb)
288 {
289 return getColumnNamesClause(fields, "", sb);
290 }
291
292
295 public static StringBuffer getColumnNamesClause(JDBCFieldBridge[] fields,
296 String identifier,
297 StringBuffer buf)
298 {
299 boolean comma = false;
300 for(int i = 0; i < fields.length; ++i)
301 {
302 JDBCType type = getJDBCType(fields[i]);
303 if(type != null)
304 {
305 if(comma)
306 buf.append(COMMA);
307 else
308 comma = true;
309 getColumnNamesClause(type, identifier, buf);
310 }
311 }
312 return buf;
313 }
314
315
318 public static StringBuffer getSearchableColumnNamesClause(JDBCFieldBridge[] fields,
319 String identifier,
320 StringBuffer buf)
321 {
322 boolean comma = false;
323 for(int i = 0; i < fields.length; ++i)
324 {
325 JDBCType type = getJDBCType(fields[i]);
326 if(type != null && type.isSearchable())
327 {
328 if(comma)
329 buf.append(COMMA);
330 else
331 comma = true;
332 getColumnNamesClause(type, identifier, buf);
333 }
334 }
335 return buf;
336 }
337
338
341 public static StringBuffer getColumnNamesClause(JDBCEntityBridge.FieldIterator loadIter, StringBuffer sb)
342 {
343 if(loadIter.hasNext())
344 getColumnNamesClause(loadIter.next(), sb);
345 while(loadIter.hasNext())
346 {
347 sb.append(COMMA);
348 getColumnNamesClause(loadIter.next(), sb);
349 }
350 return sb;
351 }
352
353
356 public static StringBuffer getColumnNamesClause(JDBCFieldBridge field, StringBuffer sb)
357 {
358 return getColumnNamesClause(field.getJDBCType(), sb);
359 }
360
361
366 public static StringBuffer getColumnNamesClause(JDBCFieldBridge field, String identifier, StringBuffer sb)
367 {
368 return getColumnNamesClause(field.getJDBCType(), identifier, sb);
369 }
370
371
376 private static StringBuffer getColumnNamesClause(JDBCType type, String identifier, StringBuffer buf)
377 {
378 String [] columnNames = type.getColumnNames();
379 boolean hasIdentifier = identifier.length() > 0;
380 if(hasIdentifier)
381 buf.append(identifier).append(DOT);
382 buf.append(columnNames[0]);
383 int i = 1;
384 while(i < columnNames.length)
385 {
386 buf.append(COMMA);
387 if(hasIdentifier)
388 buf.append(identifier).append(DOT);
389 buf.append(columnNames[i++]);
390 }
391 return buf;
392 }
393
394
397 public static StringBuffer appendColumnNamesClause(JDBCAbstractEntityBridge entity, String eagerLoadGroup, StringBuffer sb)
398 {
399 return appendColumnNamesClause(entity, eagerLoadGroup, "", sb);
400 }
401
402
405 public static StringBuffer appendColumnNamesClause(JDBCAbstractEntityBridge entity,
406 String eagerLoadGroup,
407 String alias,
408 StringBuffer sb)
409 {
410 return appendColumnNamesClause(entity.getTableFields(), entity.getLoadGroupMask(eagerLoadGroup), alias, sb);
411 }
412
413
416 public static StringBuffer appendColumnNamesClause(JDBCFieldBridge[] fields,
417 boolean[] mask,
418 String identifier,
419 StringBuffer buf)
420 {
421 for(int i = 0; i < fields.length; ++i)
422 {
423 if(mask[i])
424 {
425 JDBCType type = getJDBCType(fields[i]);
426 if(type != null)
427 {
428 buf.append(COMMA);
429 getColumnNamesClause(type, identifier, buf);
430 }
431 }
432 }
433 return buf;
434 }
435
436
439 public static StringBuffer appendColumnNamesClause(JDBCFieldBridge[] fields,
440 String identifier,
441 StringBuffer buf)
442 {
443 for(int i = 0; i < fields.length; ++i)
444 {
445 JDBCType type = getJDBCType(fields[i]);
446 if(type != null)
447 {
448 buf.append(COMMA);
449 getColumnNamesClause(type, identifier, buf);
450 }
451 }
452 return buf;
453 }
454
455
460 private static StringBuffer getColumnNamesClause(JDBCType type, StringBuffer buf)
461 {
462 String [] columnNames = type.getColumnNames();
463 buf.append(columnNames[0]);
464 int i = 1;
465 while(i < columnNames.length)
466 {
467 buf.append(COMMA).append(columnNames[i++]);
468 }
469 return buf;
470 }
471
472
477
480 public static StringBuffer getSetClause(JDBCEntityBridge.FieldIterator fieldsIter,
481 StringBuffer buf)
482 {
483 JDBCType type = getJDBCType(fieldsIter.next());
484 getSetClause(type, buf);
485 while(fieldsIter.hasNext())
486 {
487 type = getJDBCType(fieldsIter.next());
488 buf.append(COMMA);
489 getSetClause(type, buf);
490 }
491 return buf;
492 }
493
494
497 private static StringBuffer getSetClause(JDBCType type, StringBuffer buf)
498 {
499 String [] columnNames = type.getColumnNames();
500 buf.append(columnNames[0]).append(EQ_QUESTMARK);
501 int i = 1;
502 while(i < columnNames.length)
503 {
504 buf.append(COMMA).append(columnNames[i++]).append(EQ_QUESTMARK);
505 }
506 return buf;
507 }
508
509
514
517 public static StringBuffer getValuesClause(JDBCFieldBridge[] fields, StringBuffer buf)
518 {
519 boolean comma = false;
520 for(int i = 0; i < fields.length; ++i)
521 {
522 JDBCType type = getJDBCType(fields[i]);
523 if(type != null)
524 {
525 if(comma)
526 buf.append(COMMA);
527 else
528 comma = true;
529 getValuesClause(type, buf);
530 }
531 }
532 return buf;
533 }
534
535
538 private static StringBuffer getValuesClause(JDBCType type, StringBuffer buf)
539 {
540 int columnCount = type.getColumnNames().length;
541 buf.append('?');
542 int i = 1;
543 while(i++ < columnCount)
544 buf.append(COMMA).append('?');
545 return buf;
546 }
547
548
553
556 public static StringBuffer getWhereClause(JDBCFieldBridge[] fields, StringBuffer buf)
557 {
558 return getWhereClause(fields, "", buf);
559 }
560
561
566 public static StringBuffer getWhereClause(JDBCFieldBridge[] fields, String identifier, StringBuffer buf)
567 {
568 boolean and = false;
569 for(int i = 0; i < fields.length; ++i)
570 {
571 JDBCType type = getJDBCType(fields[i]);
572 if(type != null)
573 {
574 if(and)
575 buf.append(AND);
576 else
577 and = true;
578 getWhereClause(type, identifier, buf);
579 }
580 }
581 return buf;
582 }
583
584
587 public static StringBuffer getWhereClause(JDBCFieldBridge[] fields,
588 long mask,
589 StringBuffer buf)
590 {
591 return getWhereClause(fields, mask, "", buf);
592 }
593
594
597 private static StringBuffer getWhereClause(JDBCFieldBridge[] fields,
598 long mask,
599 String identifier,
600 StringBuffer buf)
601 {
602 boolean and = false;
603 long fieldMask = 1;
604 for(int i = 0; i < fields.length; ++i)
605 {
606 if((fieldMask & mask) > 0)
607 {
608 JDBCType type = getJDBCType(fields[i]);
609 if(type != null)
610 {
611 if(and)
612 buf.append(AND);
613 else
614 and = true;
615 getWhereClause(type, identifier, buf);
616 }
617 }
618 fieldMask <<= 1;
619 }
620 return buf;
621 }
622
623
626 public static StringBuffer getWhereClause(JDBCFieldBridge field, StringBuffer buf)
627 {
628 return getWhereClause(field.getJDBCType(), "", buf);
629 }
630
631
636 public static StringBuffer getWhereClause(JDBCType type, String identifier, StringBuffer buf)
637 {
638 if(identifier.length() > 0)
639 {
640 identifier += '.';
641 }
642
643 String [] columnNames = type.getColumnNames();
644 buf.append(identifier).append(columnNames[0]).append(EQ_QUESTMARK);
645 int i = 1;
646 while(i < columnNames.length)
647 {
648 buf.append(AND).append(identifier).append(columnNames[i++]).append(EQ_QUESTMARK);
649 }
650 return buf;
651 }
652
653
658 public static StringBuffer getWhereClause(JDBCType type, String identifier, String comparison, StringBuffer buf)
659 {
660 if(identifier.length() > 0)
661 {
662 identifier += '.';
663 }
664
665 String [] columnNames = type.getColumnNames();
666 buf.append(identifier).append(columnNames[0]).append(comparison).append('?');
667 int i = 1;
668 while(i < columnNames.length)
669 {
670 buf.append(AND).append(identifier).append(columnNames[i++]).append(comparison).append('?');
671 }
672 return buf;
673 }
674
675
676
681
686 public static StringBuffer getIsNullClause(boolean not,
687 JDBCFieldBridge[] fields,
688 String identifier,
689 StringBuffer buf)
690 {
691 boolean and = false;
692 for(int i = 0; i < fields.length; ++i)
693 {
694 JDBCType type = getJDBCType(fields[i]);
695 if(type != null)
696 {
697 if(and)
698 buf.append(AND);
699 else
700 and = true;
701 getIsNullClause(not, type, identifier, buf);
702 }
703 }
704 return buf;
705 }
706
707
712 public static StringBuffer getIsNullClause(boolean not,
713 JDBCFieldBridge field,
714 String identifier,
715 StringBuffer buf)
716 {
717 return getIsNullClause(not, field.getJDBCType(), identifier, buf);
718 }
719
720
725 private static StringBuffer getIsNullClause(boolean not,
726 JDBCType type,
727 String identifier,
728 StringBuffer buf)
729 {
730 if(identifier.length() > 0)
731 {
732 identifier += '.';
733 }
734
735 String [] columnNames = type.getColumnNames();
736
737 buf.append(identifier).append(columnNames[0]).append(IS);
738 (not ? buf.append(NOT) : buf).append(NULL);
739 int i = 1;
740 while(i < columnNames.length)
741 {
742 buf.append(AND).append(identifier).append(columnNames[i++]).append(IS);
743 (not ? buf.append(NOT) : buf).append(NULL);
744 }
745 return buf;
746 }
747
748
755 public static StringBuffer getJoinClause(JDBCAbstractCMRFieldBridge cmrField,
756 String parentAlias,
757 String childAlias,
758 StringBuffer buf)
759 {
760 JDBCAbstractEntityBridge parentEntity = cmrField.getEntity();
761 JDBCAbstractEntityBridge childEntity = (JDBCAbstractEntityBridge)cmrField.getRelatedEntity();
762
763 JDBCFieldBridge parentField;
764 JDBCFieldBridge childField;
765
766 if(cmrField.hasForeignKey())
767 {
768 JDBCFieldBridge[] parentFkFields = cmrField.getForeignKeyFields();
770 int i = 0;
771 while(i < parentFkFields.length)
772 {
773 parentField = parentFkFields[i++];
774 childField = (JDBCFieldBridge)childEntity.getFieldByName(parentField.getFieldName());
775 getJoinClause(parentField, parentAlias, childField, childAlias, buf);
776 if(i < parentFkFields.length)
777 buf.append(AND);
778 }
779 }
780 else
781 {
782 JDBCFieldBridge[] childFkFields = cmrField.getRelatedCMRField().getForeignKeyFields();
784 int i = 0;
785 while(i < childFkFields.length)
786 {
787 childField = childFkFields[i++];
788 parentField = (JDBCFieldBridge)parentEntity.getFieldByName(childField.getFieldName());
789
790 getJoinClause(parentField, parentAlias, childField, childAlias, buf);
792 if(i < childFkFields.length)
793 {
794 buf.append(AND);
795 }
796 }
797 }
798 return buf;
799 }
800
801 public static StringBuffer getRelationTableJoinClause(JDBCAbstractCMRFieldBridge cmrField,
802 String parentAlias,
803 String relationTableAlias,
804 StringBuffer buf)
805 {
806 JDBCAbstractEntityBridge parentEntity = cmrField.getEntity();
807 JDBCFieldBridge parentField;
808 JDBCFieldBridge relationField;
809
810 JDBCFieldBridge[] parentFields = cmrField.getTableKeyFields();
812 int i = 0;
813 while(i < parentFields.length)
814 {
815 relationField = parentFields[i++];
816 parentField = (JDBCFieldBridge)parentEntity.getFieldByName(relationField.getFieldName());
817 getJoinClause(parentField, parentAlias, relationField, relationTableAlias, buf);
818 if(i < parentFields.length)
819 buf.append(AND);
820 }
821 return buf;
822 }
823
824
829 private static StringBuffer getJoinClause(JDBCFieldBridge pkField,
830 String parent,
831 JDBCFieldBridge fkField,
832 String child,
833 StringBuffer buf)
834 {
835 return getJoinClause(pkField.getJDBCType(), parent, fkField.getJDBCType(), child, buf);
836 }
837
838 public static StringBuffer getJoinClause(JDBCFieldBridge[] pkFields,
839 String parent,
840 JDBCFieldBridge[] fkFields,
841 String child,
842 StringBuffer buf)
843 {
844 if(pkFields.length != fkFields.length)
845 {
846 throw new IllegalArgumentException (
847 "Error createing theta join clause:" +
848 " pkField.size()=" + pkFields.length +
849 " fkField.size()=" + fkFields.length);
850 }
851
852 boolean and = false;
853 for(int i = 0; i < pkFields.length; ++i)
854 {
855 JDBCType pkType = getJDBCType(pkFields[i]);
857 JDBCType fkType = getJDBCType(fkFields[i]);
858 if(and)
859 buf.append(AND);
860 else
861 and = true;
862 getJoinClause(pkType, parent, fkType, child, buf);
863 }
864 return buf;
865 }
866
867
872 private static StringBuffer getJoinClause(JDBCType pkType,
873 String parent,
874 JDBCType fkType,
875 String child,
876 StringBuffer buf)
877 {
878 if(parent.length() > 0)
879 {
880 parent += '.';
881 }
882 if(child.length() > 0)
883 {
884 child += '.';
885 }
886
887 String [] pkColumnNames = pkType.getColumnNames();
888 String [] fkColumnNames = fkType.getColumnNames();
889 if(pkColumnNames.length != fkColumnNames.length)
890 {
891 throw new IllegalArgumentException ("PK and FK have different number of columns");
892 }
893
894 buf.append(parent).append(pkColumnNames[0]).append('=').append(child).append(fkColumnNames[0]);
895 int i = 1;
896 while(i < pkColumnNames.length)
897 {
898 buf.append(AND)
899 .append(parent)
900 .append(pkColumnNames[i])
901 .append('=')
902 .append(child)
903 .append(fkColumnNames[i++]);
904 }
905 return buf;
906 }
907
908
915 public static StringBuffer getSelfCompareWhereClause(JDBCFieldBridge[] fields,
916 String fromIdentifier,
917 String toIdentifier,
918 StringBuffer buf)
919 {
920 boolean and = false;
921 for(int i = 0; i < fields.length; ++i)
922 {
923 JDBCType type = getJDBCType(fields[i]);
924 if(type != null)
925 {
926 if(and)
927 buf.append(AND);
928 else
929 and = true;
930 getSelfCompareWhereClause(type, fromIdentifier, toIdentifier, buf);
931 }
932 }
933 return buf;
934 }
935
936 private static StringBuffer getSelfCompareWhereClause(JDBCType type,
937 String fromIdentifier,
938 String toIdentifier,
939 StringBuffer buf)
940 {
941 if(fromIdentifier.length() > 0)
942 fromIdentifier += '.';
943 if(toIdentifier.length() > 0)
944 toIdentifier += '.';
945
946 String [] columnNames = type.getColumnNames();
947
948 buf.append(fromIdentifier)
949 .append(columnNames[0])
950 .append('=')
951 .append(toIdentifier)
952 .append(columnNames[0]);
953 int i = 1;
954 while(i < columnNames.length)
955 {
956 buf.append(AND)
957 .append(fromIdentifier)
958 .append(columnNames[i])
959 .append('=')
960 .append(toIdentifier)
961 .append(columnNames[i++]);
962 }
963 return buf;
964 }
965
966 public static StringBuffer getSelfCompareWhereClause(JDBCFieldBridge fromField,
967 JDBCFieldBridge toField,
968 String fromIdentifier,
969 String toIdentifier,
970 String comparison,
971 StringBuffer buf)
972 {
973 return getSelfCompareWhereClause(
974 fromField.getJDBCType(), toField.getJDBCType(), fromIdentifier, toIdentifier, comparison, buf
975 );
976 }
977
978 private static StringBuffer getSelfCompareWhereClause(JDBCType fromType,
979 JDBCType toType,
980 String fromIdentifier,
981 String toIdentifier,
982 String comparison,
983 StringBuffer buf)
984 {
985 if(fromIdentifier.length() > 0)
986 fromIdentifier += '.';
987 if(toIdentifier.length() > 0)
988 toIdentifier += '.';
989
990 String [] fromColumnNames = fromType.getColumnNames();
991 String [] toColumnNames = toType.getColumnNames();
992
993 buf.append(fromIdentifier)
994 .append(fromColumnNames[0])
995 .append(comparison)
996 .append(toIdentifier)
997 .append(toColumnNames[0]);
998 int i = 1;
999 while(i < fromColumnNames.length)
1000 {
1001 buf.append(AND)
1002 .append(fromIdentifier)
1003 .append(fromColumnNames[i])
1004 .append(comparison)
1005 .append(toIdentifier)
1006 .append(toColumnNames[i++]);
1007 }
1008 return buf;
1009 }
1010
1011 public static boolean tableExists(String tableName, DataSource dataSource)
1012 throws DeploymentException
1013 {
1014 Connection con = null;
1015 ResultSet rs = null;
1016 try
1017 {
1018 con = dataSource.getConnection();
1019
1020 DatabaseMetaData dmd = con.getMetaData();
1023 String catalog = con.getCatalog();
1024 String schema = null;
1025 String quote = dmd.getIdentifierQuoteString();
1026 if(tableName.startsWith(quote))
1027 {
1028 if(tableName.endsWith(quote) == false)
1029 {
1030 throw new DeploymentException("Mismatched quote in table name: " + tableName);
1031 }
1032 int quoteLength = quote.length();
1033 tableName = tableName.substring(quoteLength, tableName.length() - quoteLength);
1034 if(dmd.storesLowerCaseQuotedIdentifiers())
1035 tableName = tableName.toLowerCase();
1036 else if(dmd.storesUpperCaseQuotedIdentifiers())
1037 tableName = tableName.toUpperCase();
1038 }
1039 else
1040 {
1041 if(dmd.storesLowerCaseIdentifiers())
1042 tableName = tableName.toLowerCase();
1043 else if(dmd.storesUpperCaseIdentifiers())
1044 tableName = tableName.toUpperCase();
1045 }
1046
1047 int dotIndex;
1049 if ((dotIndex = tableName.indexOf('.')) != -1)
1050 {
1051 schema = tableName.substring(0, dotIndex);
1053 tableName = tableName.substring(dotIndex + 1);
1054 }
1055
1056 rs = dmd.getTables(catalog, schema, tableName, null);
1057 return rs.next();
1058 }
1059 catch(SQLException e)
1060 {
1061 throw new DeploymentException("Error while checking if table aleady exists "+tableName, e);
1064 }
1065 finally
1066 {
1067 JDBCUtil.safeClose(rs);
1068 JDBCUtil.safeClose(con);
1069 }
1070 }
1071
1072 public static OldColumns getOldColumns(String tableName, DataSource dataSource)
1073 throws DeploymentException
1074 {
1075 Connection con = null;
1076 ResultSet rs = null;
1077 ArrayList columnNames = new ArrayList ();
1078 ArrayList typeNames = new ArrayList ();
1079 ArrayList columnSizes = new ArrayList ();
1080 try
1081 {
1082 con = dataSource.getConnection();
1083
1084 DatabaseMetaData dmd = con.getMetaData();
1087 String catalog = con.getCatalog();
1088 String schema = null;
1089 String quote = dmd.getIdentifierQuoteString();
1090 if (tableName.startsWith(quote))
1091 {
1092 if (tableName.endsWith(quote) == false)
1093 {
1094 throw new DeploymentException("Mismatched quote in table name: " + tableName);
1095 }
1096 int quoteLength = quote.length();
1097 tableName = tableName.substring(quoteLength, tableName.length() - quoteLength);
1098 if (dmd.storesLowerCaseQuotedIdentifiers())
1099 tableName = tableName.toLowerCase();
1100 else if (dmd.storesUpperCaseQuotedIdentifiers())
1101 tableName = tableName.toUpperCase();
1102 }
1103 else
1104 {
1105 if (dmd.storesLowerCaseIdentifiers())
1106 tableName = tableName.toLowerCase();
1107 else if (dmd.storesUpperCaseIdentifiers())
1108 tableName = tableName.toUpperCase();
1109 }
1110
1111 int dotIndex;
1113 if ((dotIndex = tableName.indexOf('.')) != -1)
1114 {
1115 schema = tableName.substring(0, dotIndex);
1117 tableName = tableName.substring(dotIndex + 1);
1118 }
1119
1120 rs = dmd.getColumns(catalog, schema, tableName, null);
1121 while (rs.next())
1122 {
1123 String columnName = rs.getString("COLUMN_NAME");
1124 columnNames.add(columnName == null ? null : columnName.toUpperCase());
1125 typeNames.add(rs.getString("TYPE_NAME"));
1126 columnSizes.add(new Integer (rs.getInt("COLUMN_SIZE")));
1127 }
1128 return new OldColumns(columnNames, typeNames, columnSizes);
1129
1130 }
1131 catch (SQLException e)
1132 {
1133 throw new DeploymentException("Error while geting column names", e);
1136 }
1137 finally
1138 {
1139 JDBCUtil.safeClose(rs);
1140 JDBCUtil.safeClose(con);
1141 }
1142 }
1143
1144 public static OldIndexes getOldIndexes(String tableName, DataSource dataSource)
1145 throws DeploymentException
1146 {
1147 tableName = unquote(tableName, dataSource);
1148
1149 Connection con = null;
1150 ResultSet rs = null;
1151 ArrayList indexNames = new ArrayList ();
1152 ArrayList columnNames = new ArrayList ();
1153 ArrayList ascDesc = new ArrayList ();
1154 try
1155 {
1156 con = dataSource.getConnection();
1157
1158 DatabaseMetaData dmd = con.getMetaData();
1161 String catalog = con.getCatalog();
1162 String schema = null;
1163 if (dmd.storesLowerCaseIdentifiers())
1164 tableName = tableName.toLowerCase();
1165 else if (dmd.storesUpperCaseIdentifiers())
1166 tableName = tableName.toUpperCase();
1167
1168 int dotIndex;
1170 if ((dotIndex = tableName.indexOf('.')) != -1)
1171 {
1172 schema = tableName.substring(0, dotIndex);
1174 tableName = tableName.substring(dotIndex + 1);
1175 }
1176
1177 rs = dmd.getIndexInfo(catalog, schema, tableName, false, false);
1178 while (rs.next())
1179 {
1180 indexNames.add(rs.getString("INDEX_NAME"));
1181 columnNames.add(rs.getString("COLUMN_NAME"));
1182 ascDesc.add(rs.getString("ASC_OR_DESC"));
1183 }
1184 return new OldIndexes(indexNames, columnNames, ascDesc);
1185
1186 }
1187 catch (SQLException e)
1188 {
1189 throw new DeploymentException("Error while geting column names", e);
1192 }
1193 finally
1194 {
1195 JDBCUtil.safeClose(rs);
1196 JDBCUtil.safeClose(con);
1197 }
1198 }
1199
1200 public static String unquote(String tableName, DataSource ds)
1201 throws DeploymentException
1202 {
1203 Connection con = null;
1204 try
1205 {
1206 con = ds.getConnection();
1207 String quote = con.getMetaData().getIdentifierQuoteString();
1208 if (tableName.startsWith(quote))
1209 {
1210 if (tableName.endsWith(quote) == false)
1211 {
1212 throw new DeploymentException("Mismatched quote in table name: " + tableName);
1213 }
1214 int quoteLength = quote.length();
1215 tableName = tableName.substring(quoteLength, tableName.length() - quoteLength);
1216 }
1217 }
1218 catch(SQLException e)
1219 {
1220 throw new DeploymentException("Failed to get datasource connection");
1221 }
1222 finally
1223 {
1224 JDBCUtil.safeClose(con);
1225 }
1226 return tableName;
1227 }
1228
1229 private static JDBCType getJDBCType(JDBCFieldBridge field)
1230 {
1231 JDBCType type = field.getJDBCType();
1232 if(type != null && type.getColumnNames().length > 0)
1233 {
1234 return type;
1235 }
1236 return null;
1237 }
1238
1239 public static void dropTable(DataSource dataSource,
1240 String tableName)
1241 throws DeploymentException
1242 {
1243 Logger log = Logger.getLogger("CLEANER");
1244 String sql = "DROP TABLE " + tableName;
1245 try
1246 {
1247 Connection con = null;
1248 Statement statement = null;
1249 try
1250 {
1251 con = dataSource.getConnection();
1253 statement = con.createStatement();
1254 statement.executeUpdate(sql);
1255 }
1256 finally
1257 {
1258 JDBCUtil.safeClose(statement);
1261 JDBCUtil.safeClose(con);
1262 }
1263 } catch (Exception e) {
1264 throw new DeploymentException("Error while droping table "+tableName, e);
1265 }
1266 log.info("Dropped table "+tableName+" succesfuly");
1267 }
1268
1269
1272 public static class OldColumns
1273 {
1274 private ArrayList columnNames;
1275 private ArrayList typeNames;
1276 private ArrayList columnSizes;
1277
1278 private OldColumns(ArrayList cn, ArrayList tn, ArrayList cs)
1279 {
1280 columnNames = cn;
1281 typeNames = tn;
1282 columnSizes = cs;
1283 }
1284
1285 public ArrayList getColumnNames()
1286 {
1287 return columnNames;
1288 }
1289
1290 public ArrayList getTypeNames()
1291 {
1292 return typeNames;
1293 }
1294
1295 public ArrayList getColumnSizes()
1296 {
1297 return columnSizes;
1298 }
1299 }
1300
1301
1304 public static class OldIndexes
1305 {
1306 private ArrayList indexNames;
1307 private ArrayList columnNames;
1308 private ArrayList columnAscDesc;
1309
1310 private OldIndexes(ArrayList in, ArrayList cn, ArrayList ad)
1311 {
1312 indexNames = in;
1313 columnNames = cn;
1314 columnAscDesc = ad;
1315 }
1316
1317 public ArrayList getColumnNames()
1318 {
1319 return columnNames;
1320 }
1321
1322 public ArrayList getIndexNames()
1323 {
1324 return indexNames;
1325 }
1326
1327 public ArrayList getColumnAscDesc()
1328 {
1329 return columnAscDesc;
1330 }
1331 }
1332
1333}
1334 |
Popular Tags |
Java API By Example, From Geeks To Geeks.