Save This Page
1 /*
2 * JBoss, Home of Professional Open Source
3 * Copyright 2005, JBoss Inc., and individual contributors as indicated
4 * by the @authors tag. See the copyright.txt in the distribution for a
5 * full listing of individual contributors.
6 *
7 * This is free software; you can redistribute it and/or modify it
8 * under the terms of the GNU Lesser General Public License as
9 * published by the Free Software Foundation; either version 2.1 of
10 * the License, or (at your option) any later version.
11 *
12 * This software is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15 * Lesser General Public License for more details.
16 *
17 * You should have received a copy of the GNU Lesser General Public
18 * License along with this software; if not, write to the Free
19 * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
20 * 02110-1301 USA, or see the FSF site: http://www.fsf.org.
21 */
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 /**
43 * SQLUtil helps with building sql statements.
44 *
45 * @author <a href="mailto:dain@daingroup.com">Dain Sundstrom</a>
46 * @author <a href="mailto:alex@jboss.org">Alex Loubyansky</a>
47 * @author <a href="joachim@cabsoft.be">Joachim Van der Auwera</a>
48 * @version $Revision: 67395 $
49 */
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 CONCAT = "CONCAT";
81 public static final String SUBSTRING = "SUBSTRING";
82 public static final String LCASE = "LCASE";
83 public static final String UCASE = "UCASE";
84 public static final String LENGTH = "LENGTH";
85 public static final String LOCATE = "LOCATE";
86 public static final String ABS = "ABS";
87 public static final String MOD = "MOD";
88 public static final String SQRT = "SQRT";
89 public static final String COUNT = "COUNT";
90 public static final String MAX = "MAX";
91 public static final String MIN = "MIN";
92 public static final String AVG = "AVG";
93 public static final String SUM = "SUM";
94 public static final String ASC = " ASC";
95 public static final String DESC = " DESC";
96 public static final String OFFSET = " OFFSET ";
97 public static final String LIMIT = " LIMIT ";
98 public static final String UPDATE = "UPDATE ";
99 public static final String SET = " SET ";
100 public static final String TYPE = " TYPE ";
101 private static final String DOT = ".";
102
103 private static final String EQ_QUESTMARK = "=?";
104
105 private static final Vector rwords = new Vector();
106
107 public static String getTableNameWithoutSchema(String tableName)
108 {
109 final int dot = tableName.indexOf('.');
110 if(dot != -1)
111 {
112 char firstChar = tableName.charAt(0);
113 tableName = tableName.substring(dot + 1);
114 if(firstChar == '"' || firstChar == '\'')
115 {
116 tableName = firstChar + tableName;
117 }
118 }
119 return tableName;
120 }
121
122 public static String getSchema(String tableName)
123 {
124 String schema = null;
125 final int dot = tableName.indexOf('.');
126 if(dot != -1)
127 {
128 char firstChar = tableName.charAt(0);
129 final boolean quoted = firstChar == '"' || firstChar == '\'';
130 schema = tableName.substring(quoted ? 1 : 0, dot);
131 }
132 return schema;
133 }
134
135 public static String fixTableName(String tableName, DataSource dataSource)
136 throws DeploymentException
137 {
138 // don't fix the quited table name
139 char firstChar = tableName.charAt(0);
140 if(firstChar == '"' || firstChar == '\'')
141 {
142 return tableName;
143 }
144
145 // Separate schema name and table name
146 String strSchema = "";
147 int iIndex;
148 if((iIndex = tableName.indexOf('.')) != -1)
149 {
150 strSchema = tableName.substring(0, iIndex);
151 tableName = tableName.substring(iIndex + 1);
152 }
153
154 // check for SQL reserved word and escape it with prepending a "X"
155 // IMHO one should reject reserved words and throw a
156 // DeploymentException - pilhuhn
157 if(rwords != null)
158 {
159 for(int i = 0; i < rwords.size(); i++)
160 {
161 if(((String)rwords.elementAt(i)).equalsIgnoreCase(tableName))
162 {
163 tableName = "X" + tableName;
164 break;
165 }
166 }
167 }
168
169 Connection con = null;
170 try
171 {
172 con = dataSource.getConnection();
173 DatabaseMetaData dmd = con.getMetaData();
174
175 // fix length
176 int maxLength = dmd.getMaxTableNameLength();
177 if(maxLength > 0 && tableName.length() > maxLength)
178 {
179 CRC32 crc = new CRC32();
180 crc.update(tableName.getBytes());
181 String nameCRC = Long.toString(crc.getValue(), 36);
182
183 tableName = tableName.substring(
184 0,
185 maxLength - nameCRC.length() - 2);
186 tableName += "_" + nameCRC;
187 }
188
189 // fix case
190 if(dmd.storesLowerCaseIdentifiers())
191 {
192 tableName = tableName.toLowerCase();
193 }
194 else if(dmd.storesUpperCaseIdentifiers())
195 {
196 tableName = tableName.toUpperCase();
197 }
198 // now put the schema name back on the table name
199 if(strSchema.length() > 0)
200 {
201 tableName = strSchema + "." + tableName;
202 }
203 return tableName;
204 }
205 catch(SQLException e)
206 {
207 // This should not happen. A J2EE compatiable JDBC driver is
208 // required fully support metadata.
209 throw new DeploymentException("Error while fixing table name", e);
210 }
211 finally
212 {
213 JDBCUtil.safeClose(con);
214 }
215 }
216
217 public static void addToRwords(String word)
218 {
219 if(!rwords.contains(word))
220 rwords.add(word);
221 }
222
223
224 public static String fixConstraintName(String name, DataSource dataSource)
225 throws DeploymentException
226 {
227 return fixTableName(name, dataSource).replace('.', '_');
228 }
229
230 // =======================================================================
231 // Create Table Columns Clause
232 // columnName0 sqlType0
233 // [, columnName1 sqlType0
234 // [, columnName2 sqlType0 [...]]]
235 // =======================================================================
236 public static String getCreateTableColumnsClause(JDBCFieldBridge[] fields)
237 {
238 StringBuffer buf = new StringBuffer(100);
239 boolean comma = false;
240 for(int i = 0; i < fields.length; ++i)
241 {
242 JDBCType type = getJDBCType(fields[i]);
243 if(type != null)
244 {
245 if(comma)
246 buf.append(COMMA);
247 else
248 comma = true;
249 buf.append(getCreateTableColumnsClause(type));
250 }
251 }
252 return buf.toString();
253 }
254
255 /**
256 * Returns columnName0 sqlType0
257 * [, columnName1 sqlType0
258 * [, columnName2 sqlType0 [...]]]
259 */
260 public static String getCreateTableColumnsClause(JDBCType type)
261 {
262 String[] columnNames = type.getColumnNames();
263 String[] sqlTypes = type.getSQLTypes();
264 boolean[] notNull = type.getNotNull();
265
266 StringBuffer buf = new StringBuffer();
267 for(int i = 0; i < columnNames.length; i++)
268 {
269 if(i != 0)
270 buf.append(COMMA);
271 buf.append(columnNames[i]).append(' ').append(sqlTypes[i]);
272 if(notNull[i])
273 buf.append(NOT).append(NULL);
274 }
275 return buf.toString();
276 }
277
278 // =======================================================================
279 // Column Names Clause
280 // columnName0 [, columnName1 [AND columnName2 [...]]]
281 // =======================================================================
282
283 /**
284 * Returns columnName0 [, columnName1 [AND columnName2 [...]]]
285 */
286 public static StringBuffer getColumnNamesClause(JDBCFieldBridge[] fields, StringBuffer sb)
287 {
288 return getColumnNamesClause(fields, "", sb);
289 }
290
291 /**
292 * Returns columnName0 [, columnName1 [AND columnName2 [...]]]
293 */
294 public static StringBuffer getColumnNamesClause(JDBCFieldBridge[] fields,
295 String identifier,
296 StringBuffer buf)
297 {
298 boolean comma = false;
299 for(int i = 0; i < fields.length; ++i)
300 {
301 JDBCType type = getJDBCType(fields[i]);
302 if(type != null)
303 {
304 if(comma)
305 buf.append(COMMA);
306 else
307 comma = true;
308 getColumnNamesClause(type, identifier, buf);
309 }
310 }
311 return buf;
312 }
313
314 /**
315 * Returns columnName0 [, columnName1 [AND columnName2 [...]]]
316 */
317 public static StringBuffer getSearchableColumnNamesClause(JDBCFieldBridge[] fields,
318 String identifier,
319 StringBuffer buf)
320 {
321 boolean comma = false;
322 for(int i = 0; i < fields.length; ++i)
323 {
324 JDBCType type = getJDBCType(fields[i]);
325 if(type != null && type.isSearchable())
326 {
327 if(comma)
328 buf.append(COMMA);
329 else
330 comma = true;
331 getColumnNamesClause(type, identifier, buf);
332 }
333 }
334 return buf;
335 }
336
337 /**
338 * Returns columnName0 [, columnName1 [AND columnName2 [...]]]
339 */
340 public static StringBuffer getColumnNamesClause(JDBCEntityBridge.FieldIterator loadIter, StringBuffer sb)
341 {
342 if(loadIter.hasNext())
343 getColumnNamesClause(loadIter.next(), sb);
344 while(loadIter.hasNext())
345 {
346 sb.append(COMMA);
347 getColumnNamesClause(loadIter.next(), sb);
348 }
349 return sb;
350 }
351
352 /**
353 * Returns columnName0 [, columnName1 [, columnName2 [...]]]
354 */
355 public static StringBuffer getColumnNamesClause(JDBCFieldBridge field, StringBuffer sb)
356 {
357 return getColumnNamesClause(field.getJDBCType(), sb);
358 }
359
360 /**
361 * Returns identifier.columnName0
362 * [, identifier.columnName1
363 * [, identifier.columnName2 [...]]]
364 */
365 public static StringBuffer getColumnNamesClause(JDBCFieldBridge field, String identifier, StringBuffer sb)
366 {
367 return getColumnNamesClause(field.getJDBCType(), identifier, sb);
368 }
369
370 /**
371 * Returns identifier.columnName0
372 * [, identifier.columnName1
373 * [, identifier.columnName2 [...]]]
374 */
375 private static StringBuffer getColumnNamesClause(JDBCType type, String identifier, StringBuffer buf)
376 {
377 String[] columnNames = type.getColumnNames();
378 boolean hasIdentifier = identifier.length() > 0;
379 if(hasIdentifier)
380 buf.append(identifier).append(DOT);
381 buf.append(columnNames[0]);
382 int i = 1;
383 while(i < columnNames.length)
384 {
385 buf.append(COMMA);
386 if(hasIdentifier)
387 buf.append(identifier).append(DOT);
388 buf.append(columnNames[i++]);
389 }
390 return buf;
391 }
392
393 /**
394 * Returns ', columnName0 [, columnName1 [AND columnName2 [...]]]'
395 */
396 public static StringBuffer appendColumnNamesClause(JDBCAbstractEntityBridge entity, String eagerLoadGroup, StringBuffer sb)
397 {
398 return appendColumnNamesClause(entity, eagerLoadGroup, "", sb);
399 }
400
401 /**
402 * Returns ', columnName0 [, columnName1 [AND columnName2 [...]]]'
403 */
404 public static StringBuffer appendColumnNamesClause(JDBCAbstractEntityBridge entity,
405 String eagerLoadGroup,
406 String alias,
407 StringBuffer sb)
408 {
409 return appendColumnNamesClause(entity.getTableFields(), entity.getLoadGroupMask(eagerLoadGroup), alias, sb);
410 }
411
412 /**
413 * Returns ', columnName0 [, columnName1 [AND columnName2 [...]]]'
414 */
415 public static StringBuffer appendColumnNamesClause(JDBCFieldBridge[] fields,
416 boolean[] mask,
417 String identifier,
418 StringBuffer buf)
419 {
420 for(int i = 0; i < fields.length; ++i)
421 {
422 if(mask[i])
423 {
424 JDBCType type = getJDBCType(fields[i]);
425 if(type != null)
426 {
427 buf.append(COMMA);
428 getColumnNamesClause(type, identifier, buf);
429 }
430 }
431 }
432 return buf;
433 }
434
435 public static StringBuffer appendSearchableColumnNamesClause(JDBCFieldBridge[] fields,
436 boolean[] mask,
437 String identifier,
438 StringBuffer buf)
439 {
440 for(int i = 0; i < fields.length; ++i)
441 {
442 if(mask[i])
443 {
444 JDBCType type = getJDBCType(fields[i]);
445 if(type != null && type.isSearchable())
446 {
447 buf.append(COMMA);
448 getColumnNamesClause(type, identifier, buf);
449 }
450 }
451 }
452 return buf;
453 }
454
455 /**
456 * Returns ', columnName0 [, columnName1 [AND columnName2 [...]]]'
457 */
458 public static StringBuffer appendColumnNamesClause(JDBCFieldBridge[] fields,
459 String identifier,
460 StringBuffer buf)
461 {
462 for(int i = 0; i < fields.length; ++i)
463 {
464 JDBCType type = getJDBCType(fields[i]);
465 if(type != null)
466 {
467 buf.append(COMMA);
468 getColumnNamesClause(type, identifier, buf);
469 }
470 }
471 return buf;
472 }
473
474 /**
475 * Returns identifier.columnName0
476 * [, identifier.columnName1
477 * [, identifier.columnName2 [...]]]
478 */
479 private static StringBuffer getColumnNamesClause(JDBCType type, StringBuffer buf)
480 {
481 String[] columnNames = type.getColumnNames();
482 buf.append(columnNames[0]);
483 int i = 1;
484 while(i < columnNames.length)
485 {
486 buf.append(COMMA).append(columnNames[i++]);
487 }
488 return buf;
489 }
490
491 // =======================================================================
492 // Set Clause
493 // columnName0=? [, columnName1=? [, columnName2=? [...]]]
494 // =======================================================================
495
496 /**
497 * Returns columnName0=? [, columnName1=? [, columnName2=? [...]]]
498 */
499 public static StringBuffer getSetClause(JDBCEntityBridge.FieldIterator fieldsIter,
500 StringBuffer buf)
501 {
502 JDBCType type = getJDBCType(fieldsIter.next());
503 getSetClause(type, buf);
504 while(fieldsIter.hasNext())
505 {
506 type = getJDBCType(fieldsIter.next());
507 buf.append(COMMA);
508 getSetClause(type, buf);
509 }
510 return buf;
511 }
512
513 /**
514 * Returns columnName0=? [, columnName1=? [, columnName2=? [...]]]
515 */
516 private static StringBuffer getSetClause(JDBCType type, StringBuffer buf)
517 {
518 String[] columnNames = type.getColumnNames();
519 buf.append(columnNames[0]).append(EQ_QUESTMARK);
520 int i = 1;
521 while(i < columnNames.length)
522 {
523 buf.append(COMMA).append(columnNames[i++]).append(EQ_QUESTMARK);
524 }
525 return buf;
526 }
527
528 // =======================================================================
529 // Values Clause
530 // ? [, ? [, ? [...]]]
531 // =======================================================================
532
533 /**
534 * Returns ? [, ? [, ? [...]]]
535 */
536 public static StringBuffer getValuesClause(JDBCFieldBridge[] fields, StringBuffer buf)
537 {
538 boolean comma = false;
539 for(int i = 0; i < fields.length; ++i)
540 {
541 JDBCType type = getJDBCType(fields[i]);
542 if(type != null)
543 {
544 if(comma)
545 buf.append(COMMA);
546 else
547 comma = true;
548 getValuesClause(type, buf);
549 }
550 }
551 return buf;
552 }
553
554 /**
555 * Returns ? [, ? [, ? [...]]]
556 */
557 private static StringBuffer getValuesClause(JDBCType type, StringBuffer buf)
558 {
559 int columnCount = type.getColumnNames().length;
560 buf.append('?');
561 int i = 1;
562 while(i++ < columnCount)
563 buf.append(COMMA).append('?');
564 return buf;
565 }
566
567 // =======================================================================
568 // Where Clause
569 // columnName0=? [AND columnName1=? [AND columnName2=? [...]]]
570 // =======================================================================
571
572 /**
573 * Returns columnName0=? [AND columnName1=? [AND columnName2=? [...]]]
574 */
575 public static StringBuffer getWhereClause(JDBCFieldBridge[] fields, StringBuffer buf)
576 {
577 return getWhereClause(fields, "", buf);
578 }
579
580 /**
581 * Returns identifier.columnName0=?
582 * [AND identifier.columnName1=?
583 * [AND identifier.columnName2=? [...]]]
584 */
585 public static StringBuffer getWhereClause(JDBCFieldBridge[] fields, String identifier, StringBuffer buf)
586 {
587 boolean and = false;
588 for(int i = 0; i < fields.length; ++i)
589 {
590 JDBCType type = getJDBCType(fields[i]);
591 if(type != null)
592 {
593 if(and)
594 buf.append(AND);
595 else
596 and = true;
597 getWhereClause(type, identifier, buf);
598 }
599 }
600 return buf;
601 }
602
603 /**
604 * Returns columnName0=? [AND columnName1=? [AND columnName2=? [...]]]
605 */
606 public static StringBuffer getWhereClause(JDBCFieldBridge[] fields,
607 long mask,
608 StringBuffer buf)
609 {
610 return getWhereClause(fields, mask, "", buf);
611 }
612
613 /**
614 * Returns columnName0=? [AND columnName1=? [AND columnName2=? [...]]]
615 */
616 private static StringBuffer getWhereClause(JDBCFieldBridge[] fields,
617 long mask,
618 String identifier,
619 StringBuffer buf)
620 {
621 boolean and = false;
622 long fieldMask = 1;
623 for(int i = 0; i < fields.length; ++i)
624 {
625 if((fieldMask & mask) > 0)
626 {
627 JDBCType type = getJDBCType(fields[i]);
628 if(type != null)
629 {
630 if(and)
631 buf.append(AND);
632 else
633 and = true;
634 getWhereClause(type, identifier, buf);
635 }
636 }
637 fieldMask <<= 1;
638 }
639 return buf;
640 }
641
642 /**
643 * Returns columnName0=? [AND columnName1=? [AND columnName2=? [...]]]
644 */
645 public static StringBuffer getWhereClause(JDBCFieldBridge field, StringBuffer buf)
646 {
647 return getWhereClause(field.getJDBCType(), "", buf);
648 }
649
650 /**
651 * Returns identifier.columnName0=?
652 * [AND identifier.columnName1=?
653 * [AND identifier.columnName2=? [...]]]
654 */
655 public static StringBuffer getWhereClause(JDBCType type, String identifier, StringBuffer buf)
656 {
657 if(identifier.length() > 0)
658 {
659 identifier += '.';
660 }
661
662 String[] columnNames = type.getColumnNames();
663 buf.append(identifier).append(columnNames[0]).append(EQ_QUESTMARK);
664 int i = 1;
665 while(i < columnNames.length)
666 {
667 buf.append(AND).append(identifier).append(columnNames[i++]).append(EQ_QUESTMARK);
668 }
669 return buf;
670 }
671
672 /**
673 * Returns identifier.columnName0{comparison}?
674 * [AND identifier.columnName1{comparison}?
675 * [AND identifier.columnName2{comparison}? [...]]]
676 */
677 public static StringBuffer getWhereClause(JDBCType type, String identifier, String comparison, StringBuffer buf)
678 {
679 if(identifier.length() > 0)
680 {
681 identifier += '.';
682 }
683
684 String[] columnNames = type.getColumnNames();
685 buf.append(identifier).append(columnNames[0]).append(comparison).append('?');
686 int i = 1;
687 while(i < columnNames.length)
688 {
689 buf.append(AND).append(identifier).append(columnNames[i++]).append(comparison).append('?');
690 }
691 return buf;
692 }
693
694
695 // =======================================================================
696 // Is [Not] Null Clause
697 // columnName0 IS [NOT] NULL [AND columnName1 IS [NOT] NULL [...]]
698 // =======================================================================
699
700 /**
701 * Returns identifier.columnName0 IS [NOT] NULL
702 * [AND identifier.columnName1 IS [NOT] NULL
703 * [AND identifier.columnName2 IS [NOT] NULL [...]]]
704 */
705 public static StringBuffer getIsNullClause(boolean not,
706 JDBCFieldBridge[] fields,
707 String identifier,
708 StringBuffer buf)
709 {
710 boolean and = false;
711 for(int i = 0; i < fields.length; ++i)
712 {
713 JDBCType type = getJDBCType(fields[i]);
714 if(type != null)
715 {
716 if(and)
717 buf.append(AND);
718 else
719 and = true;
720 getIsNullClause(not, type, identifier, buf);
721 }
722 }
723 return buf;
724 }
725
726 /**
727 * Returns identifier.columnName0 IS [NOT] NULL
728 * [AND identifier.columnName1 IS [NOT] NULL
729 * [AND identifier.columnName2 IS [NOT] NULL [...]]]
730 */
731 public static StringBuffer getIsNullClause(boolean not,
732 JDBCFieldBridge field,
733 String identifier,
734 StringBuffer buf)
735 {
736 return getIsNullClause(not, field.getJDBCType(), identifier, buf);
737 }
738
739 /**
740 * Returns identifier.columnName0 IS [NOT] NULL
741 * [AND identifier.columnName1 IS [NOT] NULL
742 * [AND identifier.columnName2 IS [NOT] NULL [...]]]
743 */
744 private static StringBuffer getIsNullClause(boolean not,
745 JDBCType type,
746 String identifier,
747 StringBuffer buf)
748 {
749 if(identifier.length() > 0)
750 {
751 identifier += '.';
752 }
753
754 String[] columnNames = type.getColumnNames();
755
756 buf.append(identifier).append(columnNames[0]).append(IS);
757 (not ? buf.append(NOT) : buf).append(NULL);
758 int i = 1;
759 while(i < columnNames.length)
760 {
761 buf.append(AND).append(identifier).append(columnNames[i++]).append(IS);
762 (not ? buf.append(NOT) : buf).append(NULL);
763 }
764 return buf;
765 }
766
767 // =======================================================================
768 // Join Clause
769 // parent.pkColumnName0=child.fkColumnName0
770 // [AND parent.pkColumnName1=child.fkColumnName1
771 // [AND parent.pkColumnName2=child.fkColumnName2 [...]]]
772 // =======================================================================
773
774 public static StringBuffer getJoinClause(JDBCAbstractCMRFieldBridge cmrField,
775 String parentAlias,
776 String childAlias,
777 StringBuffer buf)
778 {
779 JDBCAbstractEntityBridge parentEntity = cmrField.getEntity();
780 JDBCAbstractEntityBridge childEntity = (JDBCAbstractEntityBridge)cmrField.getRelatedEntity();
781
782 JDBCFieldBridge parentField;
783 JDBCFieldBridge childField;
784
785 if(cmrField.hasForeignKey())
786 {
787 // parent has the foreign keys
788 JDBCFieldBridge[] parentFkFields = cmrField.getForeignKeyFields();
789 int i = 0;
790 while(i < parentFkFields.length)
791 {
792 parentField = parentFkFields[i++];
793 childField = (JDBCFieldBridge)childEntity.getFieldByName(parentField.getFieldName());
794 getJoinClause(parentField, parentAlias, childField, childAlias, buf);
795 if(i < parentFkFields.length)
796 buf.append(AND);
797 }
798 }
799 else
800 {
801 // child has the foreign keys
802 JDBCFieldBridge[] childFkFields = cmrField.getRelatedCMRField().getForeignKeyFields();
803 int i = 0;
804 while(i < childFkFields.length)
805 {
806 childField = childFkFields[i++];
807 parentField = (JDBCFieldBridge)parentEntity.getFieldByName(childField.getFieldName());
808
809 // add the sql
810 getJoinClause(parentField, parentAlias, childField, childAlias, buf);
811 if(i < childFkFields.length)
812 {
813 buf.append(AND);
814 }
815 }
816 }
817 return buf;
818 }
819
820 public static StringBuffer getRelationTableJoinClause(JDBCAbstractCMRFieldBridge cmrField,
821 String parentAlias,
822 String relationTableAlias,
823 StringBuffer buf)
824 {
825 JDBCAbstractEntityBridge parentEntity = cmrField.getEntity();
826 JDBCFieldBridge parentField;
827 JDBCFieldBridge relationField;
828
829 // parent to relation table join
830 JDBCFieldBridge[] parentFields = cmrField.getTableKeyFields();
831 int i = 0;
832 while(i < parentFields.length)
833 {
834 relationField = parentFields[i++];
835 parentField = (JDBCFieldBridge)parentEntity.getFieldByName(relationField.getFieldName());
836 getJoinClause(parentField, parentAlias, relationField, relationTableAlias, buf);
837 if(i < parentFields.length)
838 buf.append(AND);
839 }
840 return buf;
841 }
842
843 /**
844 * Returns parent.pkColumnName0=child.fkColumnName0
845 * [AND parent.pkColumnName1=child.fkColumnName1
846 * [AND parent.pkColumnName2=child.fkColumnName2 [...]]]
847 */
848 private static StringBuffer getJoinClause(JDBCFieldBridge pkField,
849 String parent,
850 JDBCFieldBridge fkField,
851 String child,
852 StringBuffer buf)
853 {
854 return getJoinClause(pkField.getJDBCType(), parent, fkField.getJDBCType(), child, buf);
855 }
856
857 public static StringBuffer getJoinClause(JDBCFieldBridge[] pkFields,
858 String parent,
859 JDBCFieldBridge[] fkFields,
860 String child,
861 StringBuffer buf)
862 {
863 if(pkFields.length != fkFields.length)
864 {
865 throw new IllegalArgumentException(
866 "Error createing theta join clause:" +
867 " pkField.size()=" + pkFields.length +
868 " fkField.size()=" + fkFields.length);
869 }
870
871 boolean and = false;
872 for(int i = 0; i < pkFields.length; ++i)
873 {
874 // these types should not be null
875 JDBCType pkType = getJDBCType(pkFields[i]);
876 JDBCType fkType = getJDBCType(fkFields[i]);
877 if(and)
878 buf.append(AND);
879 else
880 and = true;
881 getJoinClause(pkType, parent, fkType, child, buf);
882 }
883 return buf;
884 }
885
886 /**
887 * Returns parent.pkColumnName0=child.fkColumnName0
888 * [AND parent.pkColumnName1=child.fkColumnName1
889 * [AND parent.pkColumnName2=child.fkColumnName2 [...]]]
890 */
891 private static StringBuffer getJoinClause(JDBCType pkType,
892 String parent,
893 JDBCType fkType,
894 String child,
895 StringBuffer buf)
896 {
897 if(parent.length() > 0)
898 {
899 parent += '.';
900 }
901 if(child.length() > 0)
902 {
903 child += '.';
904 }
905
906 String[] pkColumnNames = pkType.getColumnNames();
907 String[] fkColumnNames = fkType.getColumnNames();
908 if(pkColumnNames.length != fkColumnNames.length)
909 {
910 throw new IllegalArgumentException("PK and FK have different number of columns");
911 }
912
913 buf.append(parent).append(pkColumnNames[0]).append('=').append(child).append(fkColumnNames[0]);
914 int i = 1;
915 while(i < pkColumnNames.length)
916 {
917 buf.append(AND)
918 .append(parent)
919 .append(pkColumnNames[i])
920 .append('=')
921 .append(child)
922 .append(fkColumnNames[i++]);
923 }
924 return buf;
925 }
926
927 // =======================================================================
928 // Self Compare Where Clause
929 // fromIdentifier.pkColumnName0=toIdentifier.fkColumnName0
930 // [AND fromIdentifier.pkColumnName1=toIdentifier.fkColumnName1
931 // [AND fromIdentifier.pkColumnName2=toIdentifier.fkColumnName2 [...]]]
932 // =======================================================================
933
934 public static StringBuffer getSelfCompareWhereClause(JDBCFieldBridge[] fields,
935 String fromIdentifier,
936 String toIdentifier,
937 StringBuffer buf)
938 {
939 boolean and = false;
940 for(int i = 0; i < fields.length; ++i)
941 {
942 JDBCType type = getJDBCType(fields[i]);
943 if(type != null)
944 {
945 if(and)
946 buf.append(AND);
947 else
948 and = true;
949 getSelfCompareWhereClause(type, fromIdentifier, toIdentifier, buf);
950 }
951 }
952 return buf;
953 }
954
955 private static StringBuffer getSelfCompareWhereClause(JDBCType type,
956 String fromIdentifier,
957 String toIdentifier,
958 StringBuffer buf)
959 {
960 if(fromIdentifier.length() > 0)
961 fromIdentifier += '.';
962 if(toIdentifier.length() > 0)
963 toIdentifier += '.';
964
965 String[] columnNames = type.getColumnNames();
966
967 buf.append(fromIdentifier)
968 .append(columnNames[0])
969 .append('=')
970 .append(toIdentifier)
971 .append(columnNames[0]);
972 int i = 1;
973 while(i < columnNames.length)
974 {
975 buf.append(AND)
976 .append(fromIdentifier)
977 .append(columnNames[i])
978 .append('=')
979 .append(toIdentifier)
980 .append(columnNames[i++]);
981 }
982 return buf;
983 }
984
985 public static StringBuffer getSelfCompareWhereClause(JDBCFieldBridge fromField,
986 JDBCFieldBridge toField,
987 String fromIdentifier,
988 String toIdentifier,
989 String comparison,
990 StringBuffer buf)
991 {
992 return getSelfCompareWhereClause(
993 fromField.getJDBCType(), toField.getJDBCType(), fromIdentifier, toIdentifier, comparison, buf
994 );
995 }
996
997 private static StringBuffer getSelfCompareWhereClause(JDBCType fromType,
998 JDBCType toType,
999 String fromIdentifier,
1000 String toIdentifier,
1001 String comparison,
1002 StringBuffer buf)
1003 {
1004 if(fromIdentifier.length() > 0)
1005 fromIdentifier += '.';
1006 if(toIdentifier.length() > 0)
1007 toIdentifier += '.';
1008
1009 String[] fromColumnNames = fromType.getColumnNames();
1010 String[] toColumnNames = toType.getColumnNames();
1011
1012 buf.append(fromIdentifier)
1013 .append(fromColumnNames[0])
1014 .append(comparison)
1015 .append(toIdentifier)
1016 .append(toColumnNames[0]);
1017 int i = 1;
1018 while(i < fromColumnNames.length)
1019 {
1020 buf.append(AND)
1021 .append(fromIdentifier)
1022 .append(fromColumnNames[i])
1023 .append(comparison)
1024 .append(toIdentifier)
1025 .append(toColumnNames[i++]);
1026 }
1027 return buf;
1028 }
1029
1030 public static boolean tableExists(String tableName, DataSource dataSource)
1031 throws DeploymentException
1032 {
1033 Connection con = null;
1034 ResultSet rs = null;
1035 try
1036 {
1037 con = dataSource.getConnection();
1038
1039 // (a j2ee spec compatible jdbc driver has to fully
1040 // implement the DatabaseMetaData)
1041 DatabaseMetaData dmd = con.getMetaData();
1042 String catalog = con.getCatalog();
1043 String schema = null;
1044 String quote = dmd.getIdentifierQuoteString();
1045 if(tableName.startsWith(quote))
1046 {
1047 if(tableName.endsWith(quote) == false)
1048 {
1049 throw new DeploymentException("Mismatched quote in table name: " + tableName);
1050 }
1051 int quoteLength = quote.length();
1052 tableName = tableName.substring(quoteLength, tableName.length() - quoteLength);
1053 if(dmd.storesLowerCaseQuotedIdentifiers())
1054 tableName = tableName.toLowerCase();
1055 else if(dmd.storesUpperCaseQuotedIdentifiers())
1056 tableName = tableName.toUpperCase();
1057 }
1058 else
1059 {
1060 if(dmd.storesLowerCaseIdentifiers())
1061 tableName = tableName.toLowerCase();
1062 else if(dmd.storesUpperCaseIdentifiers())
1063 tableName = tableName.toUpperCase();
1064 }
1065
1066 // Patch #927759: Split tablename into "schema" and "table" separated by '.'
1067 int dotIndex;
1068 if ((dotIndex = tableName.indexOf('.')) != -1)
1069 {
1070 // Yank out schema name ...
1071 schema = tableName.substring(0, dotIndex);
1072 tableName = tableName.substring(dotIndex + 1);
1073 }
1074
1075 rs = dmd.getTables(catalog, schema, tableName, null);
1076 return rs.next();
1077 }
1078 catch(SQLException e)
1079 {
1080 // This should not happen. A J2EE compatiable JDBC driver is
1081 // required fully support metadata.
1082 throw new DeploymentException("Error while checking if table aleady exists "+tableName, e);
1083 }
1084 finally
1085 {
1086 JDBCUtil.safeClose(rs);
1087 JDBCUtil.safeClose(con);
1088 }
1089 }
1090
1091 public static OldColumns getOldColumns(String tableName, DataSource dataSource)
1092 throws DeploymentException
1093 {
1094 Connection con = null;
1095 ResultSet rs = null;
1096 ArrayList columnNames = new ArrayList();
1097 ArrayList typeNames = new ArrayList();
1098 ArrayList columnSizes = new ArrayList();
1099 try
1100 {
1101 con = dataSource.getConnection();
1102
1103 // (a j2ee spec compatible jdbc driver has to fully
1104 // implement the DatabaseMetaData)
1105 DatabaseMetaData dmd = con.getMetaData();
1106 String catalog = con.getCatalog();
1107 String schema = null;
1108 String quote = dmd.getIdentifierQuoteString();
1109 if (tableName.startsWith(quote))
1110 {
1111 if (tableName.endsWith(quote) == false)
1112 {
1113 throw new DeploymentException("Mismatched quote in table name: " + tableName);
1114 }
1115 int quoteLength = quote.length();
1116 tableName = tableName.substring(quoteLength, tableName.length() - quoteLength);
1117 if (dmd.storesLowerCaseQuotedIdentifiers())
1118 tableName = tableName.toLowerCase();
1119 else if (dmd.storesUpperCaseQuotedIdentifiers())
1120 tableName = tableName.toUpperCase();
1121 }
1122 else
1123 {
1124 if (dmd.storesLowerCaseIdentifiers())
1125 tableName = tableName.toLowerCase();
1126 else if (dmd.storesUpperCaseIdentifiers())
1127 tableName = tableName.toUpperCase();
1128 }
1129
1130 // Patch #927759: Split tablename into "schema" and "table" separated by '.'
1131 int dotIndex;
1132 if ((dotIndex = tableName.indexOf('.')) != -1)
1133 {
1134 // Yank out schema name ...
1135 schema = tableName.substring(0, dotIndex);
1136 tableName = tableName.substring(dotIndex + 1);
1137 }
1138
1139 rs = dmd.getColumns(catalog, schema, tableName, null);
1140 while (rs.next())
1141 {
1142 String columnName = rs.getString("COLUMN_NAME");
1143 columnNames.add(columnName == null ? null : columnName.toUpperCase());
1144 typeNames.add(rs.getString("TYPE_NAME"));
1145 columnSizes.add(new Integer(rs.getInt("COLUMN_SIZE")));
1146 }
1147 return new OldColumns(columnNames, typeNames, columnSizes);
1148
1149 }
1150 catch (SQLException e)
1151 {
1152 // This should not happen. A J2EE compatiable JDBC driver is
1153 // required fully support metadata.
1154 throw new DeploymentException("Error while geting column names", e);
1155 }
1156 finally
1157 {
1158 JDBCUtil.safeClose(rs);
1159 JDBCUtil.safeClose(con);
1160 }
1161 }
1162
1163 public static OldIndexes getOldIndexes(String tableName, DataSource dataSource)
1164 throws DeploymentException
1165 {
1166 tableName = unquote(tableName, dataSource);
1167
1168 Connection con = null;
1169 ResultSet rs = null;
1170 ArrayList indexNames = new ArrayList();
1171 ArrayList columnNames = new ArrayList();
1172 ArrayList ascDesc = new ArrayList();
1173 try
1174 {
1175 con = dataSource.getConnection();
1176
1177 // (a j2ee spec compatible jdbc driver has to fully
1178 // implement the DatabaseMetaData)
1179 DatabaseMetaData dmd = con.getMetaData();
1180 String catalog = con.getCatalog();
1181 String schema = null;
1182 if (dmd.storesLowerCaseIdentifiers())
1183 tableName = tableName.toLowerCase();
1184 else if (dmd.storesUpperCaseIdentifiers())
1185 tableName = tableName.toUpperCase();
1186
1187 // Patch #927759: Split tablename into "schema" and "table" separated by '.'
1188 int dotIndex;
1189 if ((dotIndex = tableName.indexOf('.')) != -1)
1190 {
1191 // Yank out schema name ...
1192 schema = tableName.substring(0, dotIndex);
1193 tableName = tableName.substring(dotIndex + 1);
1194 }
1195
1196 rs = dmd.getIndexInfo(catalog, schema, tableName, false, false);
1197 while (rs.next())
1198 {
1199 indexNames.add(rs.getString("INDEX_NAME"));
1200 columnNames.add(rs.getString("COLUMN_NAME"));
1201 ascDesc.add(rs.getString("ASC_OR_DESC"));
1202 }
1203 return new OldIndexes(indexNames, columnNames, ascDesc);
1204
1205 }
1206 catch (SQLException e)
1207 {
1208 // This should not happen. A J2EE compatiable JDBC driver is
1209 // required fully support metadata.
1210 throw new DeploymentException("Error while geting column names", e);
1211 }
1212 finally
1213 {
1214 JDBCUtil.safeClose(rs);
1215 JDBCUtil.safeClose(con);
1216 }
1217 }
1218
1219 public static String unquote(String tableName, DataSource ds)
1220 throws DeploymentException
1221 {
1222 Connection con = null;
1223 try
1224 {
1225 con = ds.getConnection();
1226 String quote = con.getMetaData().getIdentifierQuoteString();
1227 if (tableName.startsWith(quote))
1228 {
1229 if (tableName.endsWith(quote) == false)
1230 {
1231 throw new DeploymentException("Mismatched quote in table name: " + tableName);
1232 }
1233 int quoteLength = quote.length();
1234 tableName = tableName.substring(quoteLength, tableName.length() - quoteLength);
1235 }
1236 }
1237 catch(SQLException e)
1238 {
1239 throw new DeploymentException("Failed to get datasource connection");
1240 }
1241 finally
1242 {
1243 JDBCUtil.safeClose(con);
1244 }
1245 return tableName;
1246 }
1247
1248 private static JDBCType getJDBCType(JDBCFieldBridge field)
1249 {
1250 JDBCType type = field.getJDBCType();
1251 if(type != null && type.getColumnNames().length > 0)
1252 {
1253 return type;
1254 }
1255 return null;
1256 }
1257
1258 public static void dropTable(DataSource dataSource,
1259 String tableName)
1260 throws DeploymentException
1261 {
1262 Logger log = Logger.getLogger("CLEANER");
1263 String sql = "DROP TABLE " + tableName;
1264 try
1265 {
1266 Connection con = null;
1267 Statement statement = null;
1268 try
1269 {
1270 // execute sql
1271 con = dataSource.getConnection();
1272 statement = con.createStatement();
1273 statement.executeUpdate(sql);
1274 }
1275 finally
1276 {
1277 // make sure to close the connection and statement before
1278 // comitting the transaction or XA will break
1279 JDBCUtil.safeClose(statement);
1280 JDBCUtil.safeClose(con);
1281 }
1282 } catch (Exception e) {
1283 throw new DeploymentException("Error while droping table "+tableName, e);
1284 }
1285 log.info("Dropped table "+tableName+" succesfuly");
1286 }
1287
1288 /**
1289 * utility class to store the information returned by getOldColumns()
1290 */
1291 public static class OldColumns
1292 {
1293 private ArrayList columnNames;
1294 private ArrayList typeNames;
1295 private ArrayList columnSizes;
1296
1297 private OldColumns(ArrayList cn, ArrayList tn, ArrayList cs)
1298 {
1299 columnNames = cn;
1300 typeNames = tn;
1301 columnSizes = cs;
1302 }
1303
1304 public ArrayList getColumnNames()
1305 {
1306 return columnNames;
1307 }
1308
1309 public ArrayList getTypeNames()
1310 {
1311 return typeNames;
1312 }
1313
1314 public ArrayList getColumnSizes()
1315 {
1316 return columnSizes;
1317 }
1318 }
1319
1320 /**
1321 * utility class to store the information returned by getOldColumns()
1322 */
1323 public static class OldIndexes
1324 {
1325 private ArrayList indexNames;
1326 private ArrayList columnNames;
1327 private ArrayList columnAscDesc;
1328
1329 private OldIndexes(ArrayList in, ArrayList cn, ArrayList ad)
1330 {
1331 indexNames = in;
1332 columnNames = cn;
1333 columnAscDesc = ad;
1334 }
1335
1336 public ArrayList getColumnNames()
1337 {
1338 return columnNames;
1339 }
1340
1341 public ArrayList getIndexNames()
1342 {
1343 return indexNames;
1344 }
1345
1346 public ArrayList getColumnAscDesc()
1347 {
1348 return columnAscDesc;
1349 }
1350 }
1351
1352 }