Java > Open Source Codes > org > jboss > ejb > plugins > cmp > jdbc > SQLUtil


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: 58402 $
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 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       // don't fix the quited table name
140 char firstChar = tableName.charAt(0);
141       if(firstChar == '"' || firstChar == '\'')
142       {
143          return tableName;
144       }
145
146       // Separate schema name and table name
147 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       // check for SQL reserved word and escape it with prepending a "X"
156 // IMHO one should reject reserved words and throw a
157 // DeploymentException - pilhuhn
158 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          // fix length
177 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          // fix case
191 if(dmd.storesLowerCaseIdentifiers())
192          {
193             tableName = tableName.toLowerCase();
194          }
195          else if(dmd.storesUpperCaseIdentifiers())
196          {
197             tableName = tableName.toUpperCase();
198          }
199          // now put the schema name back on the table name
200 if(strSchema.length() > 0)
201          {
202             tableName = strSchema + "." + tableName;
203          }
204          return tableName;
205       }
206       catch(SQLException e)
207       {
208          // This should not happen. A J2EE compatiable JDBC driver is
209 // required fully support metadata.
210 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    // =======================================================================
232 // Create Table Columns Clause
233 // columnName0 sqlType0
234 // [, columnName1 sqlType0
235 // [, columnName2 sqlType0 [...]]]
236 // =======================================================================
237 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    /**
257     * Returns columnName0 sqlType0
258     * [, columnName1 sqlType0
259     * [, columnName2 sqlType0 [...]]]
260     */
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    // =======================================================================
280 // Column Names Clause
281 // columnName0 [, columnName1 [AND columnName2 [...]]]
282 // =======================================================================
283
284    /**
285     * Returns columnName0 [, columnName1 [AND columnName2 [...]]]
286     */
287    public static StringBuffer getColumnNamesClause(JDBCFieldBridge[] fields, StringBuffer sb)
288    {
289       return getColumnNamesClause(fields, "", sb);
290    }
291
292    /**
293     * Returns columnName0 [, columnName1 [AND columnName2 [...]]]
294     */
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    /**
316     * Returns columnName0 [, columnName1 [AND columnName2 [...]]]
317     */
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    /**
339     * Returns columnName0 [, columnName1 [AND columnName2 [...]]]
340     */
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    /**
354     * Returns columnName0 [, columnName1 [, columnName2 [...]]]
355     */
356    public static StringBuffer getColumnNamesClause(JDBCFieldBridge field, StringBuffer sb)
357    {
358       return getColumnNamesClause(field.getJDBCType(), sb);
359    }
360
361    /**
362     * Returns identifier.columnName0
363     * [, identifier.columnName1
364     * [, identifier.columnName2 [...]]]
365     */
366    public static StringBuffer getColumnNamesClause(JDBCFieldBridge field, String identifier, StringBuffer sb)
367    {
368       return getColumnNamesClause(field.getJDBCType(), identifier, sb);
369    }
370
371    /**
372     * Returns identifier.columnName0
373     * [, identifier.columnName1
374     * [, identifier.columnName2 [...]]]
375     */
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    /**
395     * Returns ', columnName0 [, columnName1 [AND columnName2 [...]]]'
396     */
397    public static StringBuffer appendColumnNamesClause(JDBCAbstractEntityBridge entity, String eagerLoadGroup, StringBuffer sb)
398    {
399       return appendColumnNamesClause(entity, eagerLoadGroup, "", sb);
400    }
401
402    /**
403     * Returns ', columnName0 [, columnName1 [AND columnName2 [...]]]'
404     */
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    /**
414     * Returns ', columnName0 [, columnName1 [AND columnName2 [...]]]'
415     */
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    /**
437     * Returns ', columnName0 [, columnName1 [AND columnName2 [...]]]'
438     */
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    /**
456     * Returns identifier.columnName0
457     * [, identifier.columnName1
458     * [, identifier.columnName2 [...]]]
459     */
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    // =======================================================================
473 // Set Clause
474 // columnName0=? [, columnName1=? [, columnName2=? [...]]]
475 // =======================================================================
476
477    /**
478     * Returns columnName0=? [, columnName1=? [, columnName2=? [...]]]
479     */
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    /**
495     * Returns columnName0=? [, columnName1=? [, columnName2=? [...]]]
496     */
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    // =======================================================================
510 // Values Clause
511 // ? [, ? [, ? [...]]]
512 // =======================================================================
513
514    /**
515     * Returns ? [, ? [, ? [...]]]
516     */
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    /**
536     * Returns ? [, ? [, ? [...]]]
537     */
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    // =======================================================================
549 // Where Clause
550 // columnName0=? [AND columnName1=? [AND columnName2=? [...]]]
551 // =======================================================================
552
553    /**
554     * Returns columnName0=? [AND columnName1=? [AND columnName2=? [...]]]
555     */
556    public static StringBuffer getWhereClause(JDBCFieldBridge[] fields, StringBuffer buf)
557    {
558       return getWhereClause(fields, "", buf);
559    }
560
561    /**
562     * Returns identifier.columnName0=?
563     * [AND identifier.columnName1=?
564     * [AND identifier.columnName2=? [...]]]
565     */
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    /**
585     * Returns columnName0=? [AND columnName1=? [AND columnName2=? [...]]]
586     */
587    public static StringBuffer getWhereClause(JDBCFieldBridge[] fields,
588                                              long mask,
589                                              StringBuffer buf)
590    {
591       return getWhereClause(fields, mask, "", buf);
592    }
593
594    /**
595     * Returns columnName0=? [AND columnName1=? [AND columnName2=? [...]]]
596     */
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    /**
624     * Returns columnName0=? [AND columnName1=? [AND columnName2=? [...]]]
625     */
626    public static StringBuffer getWhereClause(JDBCFieldBridge field, StringBuffer buf)
627    {
628       return getWhereClause(field.getJDBCType(), "", buf);
629    }
630
631    /**
632     * Returns identifier.columnName0=?
633     * [AND identifier.columnName1=?
634     * [AND identifier.columnName2=? [...]]]
635     */
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    /**
654     * Returns identifier.columnName0{comparison}?
655     * [AND identifier.columnName1{comparison}?
656     * [AND identifier.columnName2{comparison}? [...]]]
657     */
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    // =======================================================================
677 // Is [Not] Null Clause
678 // columnName0 IS [NOT] NULL [AND columnName1 IS [NOT] NULL [...]]
679 // =======================================================================
680
681    /**
682     * Returns identifier.columnName0 IS [NOT] NULL
683     * [AND identifier.columnName1 IS [NOT] NULL
684     * [AND identifier.columnName2 IS [NOT] NULL [...]]]
685     */
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    /**
708     * Returns identifier.columnName0 IS [NOT] NULL
709     * [AND identifier.columnName1 IS [NOT] NULL
710     * [AND identifier.columnName2 IS [NOT] NULL [...]]]
711     */
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    /**
721     * Returns identifier.columnName0 IS [NOT] NULL
722     * [AND identifier.columnName1 IS [NOT] NULL