Java API By Example, From Geeks To Geeks.

1 package org.apache.ojb.broker.accesslayer.sql; 2 3 /* Copyright 2002-2005 The Apache Software Foundation 4  * 5  * Licensed under the Apache License, Version 2.0 (the "License"); 6  * you may not use this file except in compliance with the License. 7  * You may obtain a copy of the License at 8  * 9  * http://www.apache.org/licenses/LICENSE-2.0 10  * 11  * Unless required by applicable law or agreed to in writing, software 12  * distributed under the License is distributed on an "AS IS" BASIS, 13  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 14  * See the License for the specific language governing permissions and 15  * limitations under the License. 16  */ 17 18 import java.util.ArrayList ; 19 import java.util.Collection ; 20 import java.util.Enumeration ; 21 import java.util.HashMap ; 22 import java.util.Iterator ; 23 import java.util.List ; 24 import java.util.Map ; 25 26 import org.apache.ojb.broker.PersistenceBrokerSQLException; 27 import org.apache.ojb.broker.accesslayer.JoinSyntaxTypes; 28 import org.apache.ojb.broker.metadata.ClassDescriptor; 29 import org.apache.ojb.broker.metadata.CollectionDescriptor; 30 import org.apache.ojb.broker.metadata.DescriptorRepository; 31 import org.apache.ojb.broker.metadata.FieldDescriptor; 32 import org.apache.ojb.broker.metadata.FieldHelper; 33 import org.apache.ojb.broker.metadata.ObjectReferenceDescriptor; 34 import org.apache.ojb.broker.metadata.SuperReferenceDescriptor; 35 import org.apache.ojb.broker.platforms.Platform; 36 import org.apache.ojb.broker.query.BetweenCriteria; 37 import org.apache.ojb.broker.query.Criteria; 38 import org.apache.ojb.broker.query.ExistsCriteria; 39 import org.apache.ojb.broker.query.FieldCriteria; 40 import org.apache.ojb.broker.query.InCriteria; 41 import org.apache.ojb.broker.query.LikeCriteria; 42 import org.apache.ojb.broker.query.MtoNQuery; 43 import org.apache.ojb.broker.query.NullCriteria; 44 import org.apache.ojb.broker.query.Query; 45 import org.apache.ojb.broker.query.QueryByCriteria; 46 import org.apache.ojb.broker.query.QueryBySQL; 47 import org.apache.ojb.broker.query.SelectionCriteria; 48 import org.apache.ojb.broker.query.SqlCriteria; 49 import org.apache.ojb.broker.query.UserAlias; 50 import org.apache.ojb.broker.util.SqlHelper; 51 import org.apache.ojb.broker.util.SqlHelper.PathInfo; 52 import org.apache.ojb.broker.util.logging.Logger; 53 import org.apache.ojb.broker.util.logging.LoggerFactory; 54 55 /** 56  * Model a Statement based on Query. 57  * 58  * @author <a HREF="mailto:jbraeuchi@gmx.ch">Jakob Braeuchi</a> 59  * @version $Id: SqlQueryStatement.java,v 1.75.2.23 2005/12/22 18:25:51 brj Exp $ 60  */ 61 public abstract class SqlQueryStatement implements SqlStatement, JoinSyntaxTypes 62 { 63     private static final String ALIAS_SEPARATOR = "."; 64     private static final String M_N_ALIAS = "M_N"; 65     private String sql; 66      67     private SqlQueryStatement m_parentStatement; 68     /** the logger */ 69     private Logger m_logger; 70     /** the target table of the query */ 71     private TableAlias m_root; 72     /** the search table of the query */ 73     private TableAlias m_search; 74     /** the query */ 75     private QueryByCriteria m_query; 76     /** the mapping of paths to TableAliases. the key is built using the path and the path class hints. */ 77     private HashMap m_pathToAlias = new HashMap (); 78     /** the mapping of ClassDescriptor to TableAliases */ 79     private HashMap m_cldToAlias = new HashMap (); 80     /** maps trees of joins to criteria */ 81     private HashMap m_joinTreeToCriteria = new HashMap (); 82 83     private Platform m_platform; 84     private ClassDescriptor m_baseCld; 85     private ClassDescriptor m_searchCld; 86 87     private int m_aliasCount = 0; 88     protected HashMap m_attrToFld = new HashMap (); //attribute -> FieldDescriptor 89 90     /** 91      * Constructor for SqlCriteriaStatement. 92      * 93      * @param pf the Platform 94      * @param cld the ClassDescriptor 95      * @param query the Query 96      * @param logger the Logger 97      */ 98     public SqlQueryStatement(Platform pf, ClassDescriptor cld, Query query, Logger logger) 99     { 100         this(null, pf, cld, query, logger); 101     } 102 103     /** 104      * Constructor for SqlCriteriaStatement. 105      * 106      * @param parent the Parent Query 107      * @param pf the Platform 108      * @param cld the ClassDescriptor 109      * @param query the Query 110      * @param logger the Logger 111      */ 112     public SqlQueryStatement(SqlQueryStatement parent, Platform pf, ClassDescriptor cld, Query query, Logger logger) 113     { 114         m_logger = logger != null ? logger : LoggerFactory.getLogger(SqlQueryStatement.class); 115         m_parentStatement = parent; 116         m_query = (QueryByCriteria) query; 117         m_platform = pf; 118         m_searchCld = cld; 119 120         if ((m_query == null) || (m_query.getBaseClass() == m_query.getSearchClass())) 121         { 122             m_baseCld = m_searchCld; 123         } 124         else 125         { 126             m_baseCld = cld.getRepository().getDescriptorFor(query.getBaseClass()); 127         } 128 129         m_root = createTableAlias(m_baseCld, null, ""); 130          131         // BRJ: create a special alias for the indirection table 132 if (m_query instanceof MtoNQuery) 133         { 134             MtoNQuery mnQuery = (MtoNQuery)m_query; 135             TableAlias mnAlias = new TableAlias(mnQuery.getIndirectionTable(), M_N_ALIAS); 136             setTableAliasForPath(mnQuery.getIndirectionTable(), null, mnAlias); 137         } 138 139         if (m_searchCld == m_baseCld) 140         { 141             m_search = m_root; 142         } 143         else 144         { 145             m_search = getTableAlias(m_query.getObjectProjectionAttribute(), false, null, null, m_query.getPathClasses()); 146         } 147 148         // Walk the super reference-descriptor 149 buildSuperJoinTree(m_root, m_baseCld, "" ,false); 150 151         buildMultiJoinTree(m_root, m_baseCld, "", true); 152 153         // In some cases it is necessary to split the query criteria 154 // and then to generate UNION of several SELECTs 155 // We build the joinTreeToCriteria mapping, 156 if (query != null) 157         { 158             splitCriteria(); 159         } 160     } 161 162     protected ClassDescriptor getBaseClassDescriptor() 163     { 164         return m_baseCld; 165     } 166 167     protected ClassDescriptor getSearchClassDescriptor() 168     { 169         return m_searchCld; 170     } 171 172     /** 173      * Return the TableAlias and the PathInfo for an Attribute name<br> 174      * field names in functions (ie: sum(name) ) are tried to resolve ie: name 175      * from FIELDDESCRIPTOR , UPPER(name_test) from Criteria<br> 176      * also resolve pathExpression adress.city or owner.konti.saldo 177      * @param attr 178      * @param useOuterJoins 179      * @param aUserAlias 180      * @param pathClasses 181      * @return ColumnInfo 182      */ 183     protected AttributeInfo getAttributeInfo(String attr, boolean useOuterJoins, UserAlias aUserAlias, Map pathClasses) 184     { 185         AttributeInfo result = new AttributeInfo(); 186         TableAlias tableAlias; 187         SqlHelper.PathInfo pathInfo = SqlHelper.splitPath(attr); 188         String colName = pathInfo.column; 189         int sp; 190 191         // BRJ: 192 // check if we refer to an attribute in the parent query 193 // this prefix is temporary ! 194 if (colName.startsWith(Criteria.PARENT_QUERY_PREFIX) && m_parentStatement != null) 195         { 196             String [] fieldNameRef = {colName.substring(Criteria.PARENT_QUERY_PREFIX.length())}; 197             return m_parentStatement.getAttributeInfo(fieldNameRef[0], useOuterJoins, aUserAlias, pathClasses); 198         } 199 200         sp = colName.lastIndexOf("."); 201         if (sp == -1) 202         { 203             tableAlias = getRoot(); 204         } 205         else 206         { 207             String pathName = colName.substring(0, sp); 208             String [] fieldNameRef = {colName.substring(sp + 1)}; 209 210             tableAlias = getTableAlias(pathName, useOuterJoins, aUserAlias, fieldNameRef, pathClasses); 211             /** 212              * if we have not found an alias by the pathName or 213              * aliasName (if given), try again because pathName 214              * may be an aliasname. it can be only and only if it is not 215              * a path, which means there may be no path separators (,) 216              * in the pathName. 217              */ 218             if ((tableAlias == null) && (colName.lastIndexOf(".") == -1)) 219             { 220                 /** 221                  * pathName might be an alias, so check this first 222                  */ 223                 tableAlias = getTableAlias(pathName, useOuterJoins, new UserAlias(pathName, pathName, pathName), null, pathClasses); 224             } 225 226             if (tableAlias != null) 227             { 228                 // correct column name to match the alias 229 // productGroup.groupName -> groupName 230 pathInfo.column = fieldNameRef[0]; 231             } 232         } 233 234         result.tableAlias = tableAlias; 235         result.pathInfo = pathInfo; 236         return result; 237     } 238 239     /** 240      * Answer the column name for alias and path info<br> 241      * if translate try to convert attribute name into column name otherwise use attribute name<br> 242      * if a FieldDescriptor is found for the attribute name the column name is taken from 243      * there prefixed with the alias (firstname -> A0.F_NAME). 244      */ 245     protected String getColName(TableAlias aTableAlias, PathInfo aPathInfo, boolean translate) 246     { 247         String result = null; 248 249         // no translation required, use attribute name 250 if (!translate) 251         { 252             return aPathInfo.column; 253         } 254 255         // BRJ: special alias for the indirection table has no ClassDescriptor 256 if (aTableAlias.cld == null && M_N_ALIAS.equals(aTableAlias.alias)) 257         { 258             return getIndirectionTableColName(aTableAlias, aPathInfo.path); 259         } 260 261         // translate attribute name into column name 262 FieldDescriptor fld = getFieldDescriptor(aTableAlias, aPathInfo); 263 264         if (fld != null) 265         { 266             m_attrToFld.put(aPathInfo.path, fld); 267 268             // added to suport the super reference descriptor 269 if (!fld.getClassDescriptor().getFullTableName().equals(aTableAlias.table) && aTableAlias.hasJoins()) 270             { 271                 Iterator itr = aTableAlias.joins.iterator(); 272                 while (itr.hasNext()) 273                 { 274                     Join join = (Join) itr.next(); 275                     if (join.right.table.equals(fld.getClassDescriptor().getFullTableName())) 276                     { 277                         result = join.right.alias + "." + fld.getColumnName(); 278                         break; 279                     } 280                 } 281 282                 if (result == null) 283                 { 284                     result = aPathInfo.column; 285                 } 286             } 287             else 288             { 289                 result = aTableAlias.alias + "." + fld.getColumnName(); 290             } 291         } 292         else if ("*".equals(aPathInfo.column)) 293         { 294             result = aPathInfo.column; 295         } 296         else 297         { 298             // throw new IllegalArgumentException("No Field found for : " + aPathInfo.column); 299 result = aPathInfo.column; 300         } 301 302         return result; 303     } 304 305     /** 306      * Add the Column to the StringBuffer <br> 307      * 308      * @param aTableAlias 309      * @param aPathInfo 310      * @param translate flag to indicate translation of pathInfo 311      * @param buf 312      * @return true if appended 313      */ 314     protected boolean appendColName(TableAlias aTableAlias, PathInfo aPathInfo, boolean translate, StringBuffer buf) 315     { 316         String prefix = aPathInfo.prefix; 317         String suffix = aPathInfo.suffix; 318         String colName = getColName(aTableAlias, aPathInfo, translate); 319 320         if (prefix != null) // rebuild function contains ( 321 { 322             buf.append(prefix); 323         } 324 325         buf.append(colName); 326 327         if (suffix != null) // rebuild function 328 { 329             buf.append(suffix); 330         } 331 332         return true; 333     } 334 335     /** 336      * Get the FieldDescriptor for the PathInfo 337      * 338      * @param aTableAlias 339      * @param aPathInfo 340      * @return FieldDescriptor 341      */ 342     protected FieldDescriptor getFieldDescriptor(TableAlias aTableAlias, PathInfo aPathInfo) 343     { 344         FieldDescriptor fld = null; 345         String colName = aPathInfo.column; 346 347         if (aTableAlias != null) 348         { 349             fld = aTableAlias.cld.getFieldDescriptorByName(colName); 350             if (fld == null) 351             { 352                 ObjectReferenceDescriptor ord = aTableAlias.cld.getObjectReferenceDescriptorByName(colName); 353                 if (ord != null) 354                 { 355                     fld = getFldFromReference(aTableAlias, ord); 356                 } 357                 else 358                 { 359                     fld = getFldFromJoin(aTableAlias, colName); 360                 } 361             } 362         } 363 364         return fld; 365     } 366 367     /** 368      * Get FieldDescriptor from joined superclass. 369      */ 370     private FieldDescriptor getFldFromJoin(TableAlias aTableAlias, String aColName) 371     { 372         FieldDescriptor fld = null; 373 374         // Search Join Structure for attribute 375 if (aTableAlias.joins != null) 376         { 377             Iterator itr = aTableAlias.joins.iterator(); 378             while (itr.hasNext()) 379             { 380                 Join join = (Join) itr.next(); 381                 ClassDescriptor cld = join.right.cld; 382 383                 if (cld != null) 384                 { 385                     fld = cld.getFieldDescriptorByName(aColName); 386                     if (fld != null) 387                     { 388                         break; 389                     } 390 391                 } 392             } 393         } 394         return fld; 395     } 396 397     /** 398      * Get FieldDescriptor from Reference 399      */ 400     private FieldDescriptor getFldFromReference(TableAlias aTableAlias, ObjectReferenceDescriptor anOrd) 401     { 402         FieldDescriptor fld = null; 403 404         if (aTableAlias == getRoot()) 405         { 406             // no path expression 407 FieldDescriptor[] fk = anOrd.getForeignKeyFieldDescriptors(aTableAlias.cld); 408             if (fk.length > 0) 409             { 410                 fld = fk[0]; 411             } 412         } 413         else 414         { 415             // attribute with path expression 416 /** 417              * MBAIRD 418              * potentially people are referring to objects, not to the object's primary key, 419              * and then we need to take the primary key attribute of the referenced object 420              * to help them out. 421              */ 422             ClassDescriptor cld = aTableAlias.cld.getRepository().getDescriptorFor(anOrd.getItemClass()); 423             if (cld != null) 424             { 425                 fld = aTableAlias.cld.getFieldDescriptorByName(cld.getPkFields()[0].getPersistentField().getName()); 426             } 427         } 428 429         return fld; 430     } 431 432     /** 433      * Append the appropriate ColumnName to the buffer<br> 434      * if a FIELDDESCRIPTOR is found for the Criteria the colName is taken from 435      * there otherwise its taken from Criteria. <br> 436      * field names in functions (ie: sum(name) ) are tried to resolve 437      * ie: name from FIELDDESCRIPTOR , UPPER(name_test) from Criteria<br> 438      * also resolve pathExpression adress.city or owner.konti.saldo 439      */ 440     protected boolean appendColName(String attr, boolean useOuterJoins, UserAlias aUserAlias, StringBuffer buf) 441     { 442         AttributeInfo attrInfo = getAttributeInfo(attr, useOuterJoins, aUserAlias, getQuery().getPathClasses()); 443         TableAlias tableAlias = attrInfo.tableAlias; 444 445         return appendColName(tableAlias, attrInfo.pathInfo, (tableAlias != null), buf); 446     } 447 448     /** 449      * Append the appropriate ColumnName to the buffer<br> 450      * if a FIELDDESCRIPTOR is found for the Criteria the colName is taken from 451      * there otherwise its taken from Criteria. <br> 452      * field names in functions (ie: sum(name) ) are tried to resolve 453      * ie: name from FIELDDESCRIPTOR , UPPER(name_test) from Criteria<br> 454      * also resolve pathExpression adress.city or owner.konti.saldo 455      */ 456     protected boolean appendColName(String attr, String attrAlias, boolean useOuterJoins, UserAlias aUserAlias, 457             StringBuffer buf) 458     { 459         AttributeInfo attrInfo = getAttributeInfo(attr, useOuterJoins, aUserAlias, getQuery().getPathClasses()); 460         TableAlias tableAlias = attrInfo.tableAlias; 461         PathInfo pi = attrInfo.pathInfo; 462 463         if (pi.suffix != null) 464         { 465             pi.suffix = pi.suffix + " as " + attrAlias; 466         } 467         else 468         { 469             pi.suffix = " as " + attrAlias; 470         } 471 472         return appendColName(tableAlias, pi, true, buf); 473     } 474 475     /** 476      * Builds the Join for columns if they are not found among the existingColumns. 477      * @param columns the list of columns represented by Criteria.Field to ensure 478      * @param existingColumns the list of column names (String) that are already appended 479      */ 480     protected void ensureColumns(List columns, List existingColumns) 481     { 482         if (columns == null || columns.isEmpty()) 483         { 484             return; 485         } 486          487         Iterator iter = columns.iterator(); 488 489         while (iter.hasNext()) 490         { 491             FieldHelper cf = (FieldHelper) iter.next(); 492             if (!existingColumns.contains(cf.name)) 493             { 494                 getAttributeInfo(cf.name, false, null, getQuery().getPathClasses()); 495             } 496         } 497     } 498 499     /** 500      * Builds the Join for columns if they are not found among the existingColumns. 501      * These <b>columns are added to the statement</b> using a column-alias "ojb_col_x", 502      * x being the number of existing columns 503      * @param columns the list of columns represented by Criteria.Field to ensure 504      * @param existingColumns the list of column names (String) that are already appended 505      * @param buf the statement 506      * @return List of existingColumns including ojb_col_x 507      */ 508     protected List ensureColumns(List columns, List existingColumns, StringBuffer buf) 509     { 510         if (columns == null || columns.isEmpty()) 511         { 512             return existingColumns; 513         } 514 515         Iterator iter = columns.iterator(); 516         int ojb_col = existingColumns.size() + 1; 517 518         while (iter.hasNext()) 519         { 520             FieldHelper cf = (FieldHelper) iter.next(); 521             if (!existingColumns.contains(cf.name)) 522             { 523                 existingColumns.add(cf.name); 524                  525                 buf.append(","); 526                 appendColName(cf.name, "ojb_col_" + ojb_col, false, null, buf); 527                 ojb_col++; 528             } 529         } 530          531         return existingColumns; 532     } 533 534 535     /** 536      * appends a WHERE-clause to the Statement 537      * @param where 538      * @param crit 539      * @param stmt 540      */ 541     protected void appendWhereClause(StringBuffer where, Criteria crit, StringBuffer stmt) 542     { 543         if (where.length() == 0) 544         { 545             where = null; 546         } 547 548         if (where != null || (crit != null && !crit.isEmpty())) 549         { 550             stmt.append(" WHERE "); 551             appendClause(where, crit, stmt); 552         } 553     } 554 555     /** 556      * appends a HAVING-clause to the Statement 557      * @param having 558      * @param crit 559      * @param stmt 560      */ 561     protected void appendHavingClause(StringBuffer having, Criteria crit, StringBuffer stmt) 562     { 563         if (having.length() == 0) 564         { 565             having = null; 566         } 567 568         if (having != null || crit != null) 569         { 570             stmt.append(" HAVING "); 571             appendClause(having, crit, stmt); 572         } 573     } 574 575     /** 576      * appends a WHERE/HAVING-clause to the Statement 577      * @param clause 578      * @param crit 579      * @param stmt 580      */ 581     protected void appendClause(StringBuffer clause, Criteria crit, StringBuffer stmt) 582     { 583         /** 584          * MBAIRD 585          * when generating the "WHERE/HAVING" clause we need to append the criteria for multi-mapped 586          * tables. We only need to do this for the root classdescriptor and not for joined tables 587          * because we assume you cannot make a relation of the wrong type upon insertion. Of course, 588          * you COULD mess the data up manually and this would cause a problem. 589          */ 590 591         if (clause != null) 592         { 593             stmt.append(clause.toString()); 594         } 595         if (crit != null) 596         { 597             if (clause == null) 598             { 599                 stmt.append(asSQLStatement(crit)); 600             } 601             else 602             { 603                 stmt.append(" AND ("); 604                 stmt.append(asSQLStatement(crit)); 605                 stmt.append(")"); 606             } 607 608         } 609     } 610 611     /** 612      * Create SQL-String based on Criteria 613      */ 614     private String asSQLStatement(Criteria crit) 615     { 616         Enumeration e = crit.getElements(); 617         StringBuffer statement = new StringBuffer (); 618 619         while (e.hasMoreElements()) 620         { 621             Object o = e.nextElement(); 622             if (o instanceof Criteria) 623             { 624                 Criteria pc = (Criteria) o; 625                  626                 if (pc.isEmpty()) 627                 { 628                     continue; //skip empty criteria 629 } 630                  631                 String addAtStart = ""; 632                 String addAtEnd = ""; 633 634                 // need to add parenthesises? 635 if (pc.isEmbraced()) 636                 { 637                     addAtStart = " ("; 638                     addAtEnd = ")"; 639                 } 640 641                 switch (pc.getType()) 642                 { 643                     case (Criteria.OR) : 644                         { 645                             if (statement.length() > 0) 646                             { 647                                 statement.append(" OR "); 648                             } 649                             statement.append(addAtStart); 650                             statement.append(asSQLStatement(pc)); 651                             statement.append(addAtEnd); 652                             break; 653                         } 654                     case (Criteria.AND) : 655                         { 656                             if (statement.length() > 0) 657                             { 658                                 statement.insert(0, "( "); 659                                 statement.append(") AND "); 660                             } 661                             statement.append(addAtStart); 662                             statement.append(asSQLStatement(pc)); 663                             statement.append(addAtEnd); 664                             break; 665                         } 666                 } 667             } 668             else 669             { 670                 SelectionCriteria c = (SelectionCriteria) o; 671                 if (statement.length() > 0) 672                 { 673                     statement.insert(0, "("); 674                     statement.append(") AND "); 675                 } 676                 appendSQLClause(c, statement); 677             } 678         } // while 679 680         // BRJ : negative Criteria surrounded by NOT (...) 681 if (crit.isNegative()) 682         { 683             statement.insert(0, " NOT ("); 684             statement.append(")"); 685         } 686          687         return (statement.length() == 0 ? null : statement.toString()); 688     } 689 690     /** 691      * Answer the SQL-Clause for a BetweenCriteria 692      * 693      * @param alias 694      * @param pathInfo 695      * @param c BetweenCriteria 696      * @param buf 697      */ 698     private void appendBetweenCriteria(TableAlias alias, PathInfo pathInfo, BetweenCriteria c, StringBuffer buf) 699     { 700         appendColName(alias, pathInfo, c.isTranslateAttribute(), buf); 701         buf.append(c.getClause()); 702         appendParameter(c.getValue(), buf); 703         buf.append(" AND "); 704         appendParameter(c.getValue2(), buf); 705     } 706 707     /** 708      * Answer the SQL-Clause for an ExistsCriteria 709      * @param c ExistsCriteria 710      */ 711     private void appendExistsCriteria(ExistsCriteria c, StringBuffer buf) 712     { 713         Query subQuery = (Query) c.getValue(); 714 715         buf.append(c.getClause()); 716         appendSubQuery(subQuery, buf); 717     } 718 719     /** 720      * Answer the SQL-Clause for a FieldCriteria<br> 721      * The value of the FieldCriteria will be translated 722      * 723      * @param alias 724      * @param pathInfo 725      * @param c ColumnCriteria 726      * @param buf 727      */ 728     private void appendFieldCriteria(TableAlias alias, PathInfo pathInfo, FieldCriteria c, StringBuffer buf) 729     { 730         appendColName(alias, pathInfo, c.isTranslateAttribute(), buf); 731         buf.append(c.getClause()); 732 733         if (c.isTranslateField()) 734         { 735             appendColName((String ) c.getValue(), false, c.getUserAlias(), buf); 736         } 737         else 738         { 739             buf.append(c.getValue()); 740         } 741     } 742      743     /** 744      * Get the column name from the indirection table. 745      * @param mnAlias 746      * @param path 747      */ 748     private String getIndirectionTableColName(TableAlias mnAlias, String path) 749     { 750         int dotIdx = path.lastIndexOf("."); 751         String column = path.substring(dotIdx); 752         return mnAlias.alias + column; 753     } 754 755     /** 756      * Answer the SQL-Clause for an InCriteria 757      * 758      * @param alias 759      * @param pathInfo 760      * @param c InCriteria 761      * @param buf 762      */ 763     private void appendInCriteria(TableAlias alias, PathInfo pathInfo, InCriteria c, StringBuffer buf) 764     { 765         appendColName(alias, pathInfo, c.isTranslateAttribute(), buf); 766         buf.append(c.getClause()); 767 768         if (c.getValue() instanceof Collection ) 769         { 770             Object [] values = ((Collection ) c.getValue()).toArray(); 771             int size = ((Collection ) c.getValue()).size(); 772 773             buf.append("("); 774             if (size > 0) 775             { 776                 for (int i = 0; i < size - 1; i++) 777                 { 778                     appendParameter(values[i], buf); 779                     buf.append(","); 780                 } 781                 appendParameter(values[size - 1], buf); 782             } 783             buf.append(")"); 784         } 785         else 786         { 787             appendParameter(c.getValue(), buf); 788         } 789     } 790 791     /** 792      * Answer the SQL-Clause for a NullCriteria 793      * 794      * @param alias 795      * @param pathInfo 796      * @param c NullCriteria 797      * @param buf 798      */ 799     private void appendNullCriteria(TableAlias alias, PathInfo pathInfo, NullCriteria c, StringBuffer buf) 800     { 801         appendColName(alias, pathInfo, c.isTranslateAttribute(), buf); 802         buf.append(c.getClause()); 803     } 804 805     /** 806      * Answer the SQL-Clause for a SqlCriteria 807      * 808      */ 809     private void appendSQLCriteria(SqlCriteria c, StringBuffer buf) 810     { 811         buf.append(c.getClause()); 812     } 813 814     /** 815      * Answer the SQL-Clause for a SelectionCriteria 816      * 817      * @param c 818      * @param buf 819      */ 820     private void appendSelectionCriteria(TableAlias alias, PathInfo pathInfo, SelectionCriteria c, StringBuffer buf) 821     { 822         appendColName(alias, pathInfo, c.isTranslateAttribute(), buf); 823         buf.append(c.getClause()); 824         appendParameter(c.getValue(), buf); 825     } 826 827     /** 828      * Answer the SQL-Clause for a LikeCriteria 829      * 830      * @param c 831      * @param buf 832      */ 833     private void appendLikeCriteria(TableAlias alias, PathInfo pathInfo, LikeCriteria c, StringBuffer buf) 834     { 835         appendColName(alias, pathInfo, c.isTranslateAttribute(), buf); 836         buf.append(c.getClause()); 837         appendParameter(c.getValue(), buf); 838 839         buf.append(m_platform.getEscapeClause(c)); 840     } 841 842     /** 843      * Answer the SQL-Clause for a SelectionCriteria 844      * 845      * @param alias 846      * @param pathInfo 847      * @param c SelectionCriteria 848      * @param buf 849      */ 850     protected void appendCriteria(TableAlias alias, PathInfo pathInfo, SelectionCriteria c, StringBuffer buf) 851     { 852         if (c instanceof FieldCriteria) 853         { 854             appendFieldCriteria(alias, pathInfo, (FieldCriteria) c, buf); 855         } 856         else if (c instanceof NullCriteria) 857         { 858             appendNullCriteria(alias, pathInfo, (NullCriteria) c, buf); 859         } 860         else if (c instanceof BetweenCriteria) 861         { 862             appendBetweenCriteria(alias, pathInfo, (BetweenCriteria) c, buf); 863         } 864         else if (c instanceof InCriteria) 865         { 866             appendInCriteria(alias, pathInfo, (InCriteria) c, buf); 867         } 868         else if (c instanceof SqlCriteria) 869         { 870             appendSQLCriteria((SqlCriteria) c, buf); 871         } 872         else if (c instanceof ExistsCriteria) 873         { 874             appendExistsCriteria((ExistsCriteria) c, buf); 875         } 876         else if (c instanceof LikeCriteria) 877         { 878             appendLikeCriteria(alias, pathInfo, (LikeCriteria) c, buf); 879         } 880         else 881         { 882             appendSelectionCriteria(alias, pathInfo, c, buf); 883         } 884     } 885 886     /** 887      * Answer the SQL-Clause for a SelectionCriteria 888      * If the Criteria references a class with extents an OR-Clause is 889      * added for each extent 890      * @param c SelectionCriteria 891      */ 892     protected void appendSQLClause(SelectionCriteria c, StringBuffer buf) 893     { 894         // BRJ : handle SqlCriteria 895 if (c instanceof SqlCriteria) 896         { 897             buf.append(c.getAttribute()); 898             return; 899         } 900          901         // BRJ : criteria attribute is a query 902 if (c.getAttribute() instanceof Query) 903         { 904             Query q = (Query) c.getAttribute(); 905             buf.append("("); 906             buf.append(getSubQuerySQL(q)); 907             buf.append(")"); 908             buf.append(c.getClause()); 909             appendParameter(c.getValue(), buf); 910             return; 911         } 912 913         AttributeInfo attrInfo = getAttributeInfo((String ) c.getAttribute(), false, c.getUserAlias(), c.getPathClasses()); 914         TableAlias alias = attrInfo.tableAlias; 915 916         if (alias != null) 917         { 918             boolean hasExtents = alias.hasExtents(); 919 920             if (hasExtents) 921             { 922                 // BRJ : surround with braces if alias has extents 923 buf.append("("); 924                 appendCriteria(alias, attrInfo.pathInfo, c, buf); 925 926                 c.setNumberOfExtentsToBind(alias.extents.size()); 927                 Iterator iter = alias.iterateExtents(); 928                 while (iter.hasNext()) 929                 { 930                     TableAlias tableAlias = (TableAlias) iter.next(); 931                     buf.append(" OR "); 932                     appendCriteria(tableAlias, attrInfo.pathInfo, c, buf); 933                 } 934                 buf.append(")"); 935             } 936             else 937             { 938                 // no extents 939 appendCriteria(alias, attrInfo.pathInfo, c, buf); 940             } 941         } 942         else 943         { 944             // alias null 945 appendCriteria(alias, attrInfo.pathInfo, c, buf); 946         } 947 948     } 949 950     /** 951      * Append the Parameter 952      * Add the place holder ? or the SubQuery 953      * @param value the value of the criteria 954      */ 955     private void appendParameter(Object value, StringBuffer buf) 956     { 957         if (value instanceof Query) 958         { 959             appendSubQuery((Query) value, buf); 960         } 961         else 962         { 963             buf.append("?"); 964         } 965     } 966 967     /** 968      * Append a SubQuery the SQL-Clause 969      * @param subQuery the subQuery value of SelectionCriteria 970      */ 971     private void appendSubQuery(Query subQuery, StringBuffer buf) 972     { 973         buf.append(" ("); 974         buf.append(getSubQuerySQL(subQuery)); 975         buf.append(") "); 976     } 977 978     /** 979      * Convert subQuery to SQL 980      * @param subQuery the subQuery value of SelectionCriteria 981      */ 982     private String getSubQuerySQL(Query subQuery) 983     { 984         ClassDescriptor cld = getRoot().cld.getRepository().getDescriptorFor(subQuery.getSearchClass()); 985         String sql; 986 987         if (subQuery instanceof QueryBySQL) 988         { 989             sql = ((QueryBySQL) subQuery).getSql(); 990         } 991         else 992         { 993             sql = new SqlSelectStatement(this, m_platform, cld, subQuery, m_logger).getStatement(); 994         } 995 996         return sql; 997     } 998 999     /** 1000     * Get TableAlias by the path from the target table of the query. 1001     * @param aPath the path from the target table of the query to this TableAlias. 1002     * @param useOuterJoins use outer join to join this table with the previous 1003     * table in the path. 1004     * @param aUserAlias if specified, overrides alias in crit 1005     * @param fieldRef String[1] contains the field name. 1006     * In the case of related table's primary key the "related.pk" attribute 1007     * must not add new join, but use the value of foreign key 1008     * @param pathClasses the hints 1009     */ 1010    private TableAlias getTableAlias(String aPath, boolean useOuterJoins, UserAlias aUserAlias, String [] fieldRef, Map pathClasses) 1011    { 1012        TableAlias curr, prev, indirect; 1013        String attr, attrPath = null; 1014        ObjectReferenceDescriptor ord; 1015        CollectionDescriptor cod; 1016        ClassDescriptor cld; 1017        Object [] prevKeys; 1018        Object [] keys; 1019        ArrayList descriptors; 1020        boolean outer = useOuterJoins; 1021        int pathLength; 1022        List hintClasses = null; 1023        String pathAlias = aUserAlias == null ? null : aUserAlias.getAlias(aPath); 1024         1025        if (pathClasses != null) 1026        { 1027            hintClasses = (List ) pathClasses.get(aPath); 1028        } 1029         1030        curr = getTableAliasForPath(aPath, pathAlias, hintClasses); 1031        if (curr != null) 1032        { 1033            return curr; 1034        } 1035 1036        descriptors = getRoot().cld.getAttributeDescriptorsForPath(aPath, pathClasses); 1037        prev = getRoot(); 1038 1039        if (descriptors == null || descriptors.size() == 0) 1040        { 1041            if (prev.hasJoins()) 1042            { 1043                for (Iterator itr = prev.iterateJoins(); itr.hasNext();) 1044                { 1045                    prev = ((Join) itr.next()).left; 1046                    descriptors = prev.cld.getAttributeDescriptorsForPath(aPath, pathClasses); 1047                    if (descriptors.size() > 0) 1048                    { 1049                        break; 1050                    } 1051                } 1052            } 1053        } 1054 1055        pathLength = descriptors.size(); 1056        for (int i = 0; i < pathLength; i++) 1057        { 1058            if (!(descriptors.get(i) instanceof ObjectReferenceDescriptor)) 1059            { 1060                // only use Collection- and ObjectReferenceDescriptor 1061 continue; 1062            } 1063 1064            ord = (ObjectReferenceDescriptor) descriptors.get(i); 1065            attr = ord.getAttributeName(); 1066            if (attrPath == null) 1067            { 1068                attrPath = attr; 1069            } 1070            else 1071            { 1072                attrPath = attrPath + "." + attr; 1073            } 1074 1075            // use clas hints for path 1076 if (pathClasses != null) 1077            { 1078                hintClasses = (List ) pathClasses.get(attrPath); 1079            } 1080 1081            // look for outer join hint 1082 outer = outer || getQuery().isPathOuterJoin(attrPath); 1083 1084            // look for 1:n or m:n 1085 if (ord instanceof CollectionDescriptor) 1086            { 1087                cod = (CollectionDescriptor) ord; 1088                cld = getItemClassDescriptor(cod, hintClasses); 1089 1090                if (!cod.isMtoNRelation()) 1091                { 1092                    prevKeys = prev.cld.getPkFields(); 1093                    keys = cod.getForeignKeyFieldDescriptors(cld); 1094                } 1095                else 1096                { 1097                    String mnAttrPath = attrPath + "*"; 1098                    String mnUserAlias = (aUserAlias == null ? null : aUserAlias + "*"); 1099                    indirect = getTableAliasForPath(mnAttrPath, mnUserAlias, null); 1100                    if (indirect == null) 1101                    { 1102                        indirect = createTableAlias(cod.getIndirectionTable(), mnAttrPath, mnUserAlias); 1103 1104                        // we need two Joins for m:n 1105 // 1.) prev class to indirectionTable 1106 prevKeys = prev.cld.getPkFields(); 1107                        keys = cod.getFksToThisClass(); 1108                        addJoin(prev, prevKeys, indirect, keys, outer, attr + "*"); 1109                    } 1110                    // 2.) indirectionTable to the current Class 1111 prev = indirect; 1112                    prevKeys = cod.getFksToItemClass(); 1113                    keys = cld.getPkFields(); 1114                } 1115            } 1116            else 1117            { 1118                // must be n:1 or 1:1 1119 cld = getItemClassDescriptor(ord, hintClasses); 1120 1121                // BRJ : if ord is taken from 'super' we have to change prev accordingly 1122 if (!prev.cld.equals(ord.getClassDescriptor())) 1123                { 1124                    TableAlias ordAlias = getTableAliasForClassDescriptor(ord.getClassDescriptor()); 1125                    Join join = prev.getJoin(ordAlias); 1126                    if (join != null) 1127                    { 1128                        join.isOuter = join.isOuter || outer; 1129                    } 1130                    prev = ordAlias; 1131                } 1132 1133                prevKeys = ord.getForeignKeyFieldDescriptors(prev.cld); 1134                keys = cld.getPkFields(); 1135 1136                // [olegnitz] 1137 // a special case: the last element of the path is 1138 // reference and the field is one of PK fields => 1139 // use the correspondent foreign key field, don't add the join 1140 if ((fieldRef != null) && (i == (pathLength - 1))) 1141                { 1142                    FieldDescriptor[] pk = cld.getPkFields(); 1143 1144                    for (int j = 0; j < pk.length; j++) 1145                    { 1146                        if (pk[j].getAttributeName().equals(fieldRef[0])) 1147                        { 1148                            fieldRef[0] = ((FieldDescriptor) prevKeys[j]).getAttributeName(); 1149                            return prev; 1150                        } 1151                    } 1152                } 1153            } 1154 1155            pathAlias = aUserAlias == null ? null : aUserAlias.getAlias(attrPath); 1156            curr = getTableAliasForPath(attrPath, pathAlias, hintClasses); 1157 1158            if (curr == null) 1159            { 1160                curr = createTableAlias(cld, attrPath, pathAlias, hintClasses); 1161 1162                outer = outer || (curr.cld == prev.cld) || curr.hasExtents() || useOuterJoins; 1163                addJoin(prev, prevKeys, curr, keys, outer, attr); 1164 1165                buildSuperJoinTree(curr, cld, aPath, outer); 1166            } 1167 1168            prev = curr; 1169        } 1170 1171        m_logger.debug("Result of getTableAlias(): " + curr); 1172        return curr; 1173    } 1174 1175    /** 1176     * add a join between two aliases 1177     * 1178     * TODO BRJ : This needs refactoring, it looks kind of weird 1179     * 1180     * no extents 1181     * A1 -> A2 1182     * 1183     * extents on the right 1184     * A1 -> A2 1185     * A1 -> A2E0 1186     * 1187     * extents on the left : copy alias on right, extents point to copies 1188     * A1 -> A2 1189     * A1E0 -> A2C0 1190     * 1191     * extents on the left and right 1192     * A1 -> A2 1193     * A1 -> A2E0 1194     * A1E0 -> A2C0 1195     * A1E0 -> A2E0C0 1196     * 1197     * @param left 1198     * @param leftKeys 1199     * @param right 1200     * @param rightKeys 1201     * @param outer 1202     * @param name 1203     */ 1204    private void addJoin(TableAlias left, Object [] leftKeys, TableAlias right, Object [] rightKeys, boolean outer, 1205            String name) 1206    { 1207        TableAlias extAlias, rightCopy; 1208 1209        left.addJoin(new Join(left, leftKeys, right, rightKeys, outer, name)); 1210 1211        // build join between left and extents of right 1212 if (right.hasExtents()) 1213        { 1214            for (int i = 0; i < right.extents.size(); i++) 1215            { 1216                extAlias = (TableAlias) right.extents.get(i); 1217                FieldDescriptor[] extKeys = getExtentFieldDescriptors(extAlias, (FieldDescriptor[]) rightKeys); 1218 1219                left.addJoin(new Join(left, leftKeys, extAlias, extKeys, true, name)); 1220            } 1221        } 1222 1223        // we need to copy the alias on the right for each extent on the left 1224 if (left.hasExtents()) 1225        { 1226            for (int i = 0; i < left.extents.size(); i++) 1227            { 1228                extAlias = (TableAlias) left.extents.get(i); 1229                FieldDescriptor[] extKeys = getExtentFieldDescriptors(extAlias, (FieldDescriptor[]) leftKeys); 1230                rightCopy = right.copy("C" + i); 1231 1232                // copies are treated like normal extents 1233 right.extents.add(rightCopy); 1234                right.extents.addAll(rightCopy.extents); 1235 1236                addJoin(extAlias, extKeys, rightCopy, rightKeys, true, name); 1237            } 1238        } 1239    } 1240 1241    /** 1242     * Get the FieldDescriptors of the extent based on the FieldDescriptors of the parent. 1243     */ 1244    private FieldDescriptor[] getExtentFieldDescriptors(TableAlias extAlias, FieldDescriptor[] fds) 1245    { 1246        FieldDescriptor[] result = new FieldDescriptor[fds.length]; 1247 1248        for (int i = 0; i < fds.length; i++) 1249        { 1250            result[i] = extAlias.cld.getFieldDescriptorByName(fds[i].getAttributeName()); 1251        } 1252 1253        return result; 1254    } 1255 1256    private char getAliasChar() 1257    { 1258        char result = 'A'; 1259 1260        if (m_parentStatement != null) 1261        { 1262            result = (char) (m_parentStatement.getAliasChar() + 1); 1263        } 1264 1265        return result; 1266    } 1267 1268    /** 1269     * Create a TableAlias for path or userAlias 1270     * @param aCld 1271     * @param aPath 1272     * @param aUserAlias 1273     * @param hints a List os Class objects to be used as hints for path expressions 1274     * @return TableAlias 1275     * 1276     */ 1277    private TableAlias createTableAlias(ClassDescriptor aCld, String aPath, String aUserAlias, List hints) 1278    { 1279        if (aUserAlias == null) 1280        { 1281            return createTableAlias(aCld, hints, aPath); 1282        } 1283        else 1284        { 1285            return createTableAlias(aCld, hints, aUserAlias + ALIAS_SEPARATOR + aPath); 1286        } 1287    } 1288 1289    /** 1290     * Create new TableAlias for path 1291     * @param cld the class descriptor for the TableAlias 1292     * @param path the path from the target table of the query to this TableAlias. 1293     * @param hints a List of Class objects to be used on path expressions 1294     */ 1295    private TableAlias createTableAlias(ClassDescriptor cld, List hints, String path) 1296    { 1297        TableAlias alias; 1298        boolean lookForExtents = false; 1299 1300        if (!cld.getExtentClasses().isEmpty() && path.length() > 0) 1301        { 1302            lookForExtents = true; 1303        } 1304 1305        String aliasName = String.valueOf(getAliasChar()) + m_aliasCount++; // m_pathToAlias.size(); 1306 alias = new TableAlias(cld, aliasName, lookForExtents, hints); 1307 1308        setTableAliasForPath(path, hints, alias); 1309        return alias; 1310    } 1311 1312    /** 1313     * Create a TableAlias for path or userAlias 1314     * @param aTable 1315     * @param aPath 1316     * @param aUserAlias 1317     * @return TableAlias 1318     */ 1319    private TableAlias createTableAlias(String aTable, String aPath, String aUserAlias) 1320    { 1321        if (aUserAlias == null) 1322        { 1323            return createTableAlias(aTable, aPath); 1324        } 1325        else 1326        { 1327            return createTableAlias(aTable, aUserAlias + ALIAS_SEPARATOR + aPath); 1328        } 1329    } 1330 1331    /** 1332     * Create new TableAlias for path 1333     * @param table the table name 1334     * @param path the path from the target table of the query to this TableAlias. 1335     */ 1336    private TableAlias createTableAlias(String table, String path) 1337    { 1338        TableAlias alias; 1339 1340        if (table == null) 1341        { 1342            getLogger().warn("Creating TableAlias without table for path: " + path); 1343        } 1344 1345        String aliasName = String.valueOf(getAliasChar()) + m_aliasCount++; // + m_pathToAlias.size(); 1346 alias = new TableAlias(table, aliasName); 1347        setTableAliasForPath(path, null, alias); 1348        m_logger.debug("createTableAlias2: path: " + path + " tableAlias: " + alias); 1349 1350        return alias; 1351    } 1352 1353    /** 1354     * Answer the TableAlias for aPath 1355     * @param aPath 1356     * @param hintClasses 1357     * @return TableAlias, null if none 1358     */ 1359    private TableAlias getTableAliasForPath(String aPath, List hintClasses) 1360    { 1361        return (TableAlias) m_pathToAlias.get(buildAliasKey(aPath, hintClasses)); 1362    } 1363 1364    /** 1365     * Set the TableAlias for aPath 1366     * @param aPath 1367     * @param hintClasses 1368     * @param TableAlias 1369     */ 1370    private void setTableAliasForPath(String aPath, List hintClasses, TableAlias anAlias) 1371    { 1372        m_pathToAlias.put(buildAliasKey(aPath, hintClasses), anAlias); 1373    } 1374     1375    /** 1376     * Build the key for the TableAlias based on the path and the hints 1377     * @param aPath 1378     * @param hintClasses 1379     * @return the key for the TableAlias 1380     */ 1381    private String buildAliasKey(String aPath, List hintClasses) 1382    { 1383        if (hintClasses == null || hintClasses.isEmpty()) 1384        { 1385            return aPath; 1386        } 1387         1388        StringBuffer buf = new StringBuffer (aPath); 1389        for (Iterator iter = hintClasses.iterator(); iter.hasNext();) 1390        { 1391            Class hint = (Class ) iter.next(); 1392            buf.append(" "); 1393            buf.append(hint.getName()); 1394        } 1395        return buf.toString(); 1396    } 1397 1398    /** 1399     * Answer the TableAlias for ClassDescriptor. 1400     */ 1401    protected TableAlias getTableAliasForClassDescriptor(ClassDescriptor aCld) 1402    { 1403        return (TableAlias) m_cldToAlias.get(aCld); 1404    } 1405 1406    /** 1407     * Set the TableAlias for ClassDescriptor 1408     */ 1409    private void setTableAliasForClassDescriptor(ClassDescriptor aCld, TableAlias anAlias) 1410    { 1411        if (m_cldToAlias.get(aCld) == null) 1412        { 1413            m_cldToAlias.put(aCld, anAlias); 1414        } 1415    } 1416 1417    /** 1418     * Answer the TableAlias for aPath or aUserAlias 1419     * @param aPath 1420     * @param aUserAlias 1421     * @param hintClasses 1422     * @return TableAlias, null if none 1423     */ 1424    private TableAlias getTableAliasForPath(String aPath, String aUserAlias, List hintClasses) 1425    { 1426        if (aUserAlias == null) 1427        { 1428            return getTableAliasForPath(aPath, hintClasses); 1429        } 1430        else 1431        { 1432            return getTableAliasForPath(aUserAlias + ALIAS_SEPARATOR + aPath, hintClasses); 1433        } 1434    } 1435 1436    /** 1437     * Answer the ClassDescriptor for itemClass for an ObjectReferenceDescriptor 1438     * check optional hint. The returned Class is to highest superclass contained in the hint list. 1439     * TODO: add super ClassDescriptor 1440     */ 1441    private ClassDescriptor getItemClassDescriptor(ObjectReferenceDescriptor ord, List hintClasses) 1442    { 1443        DescriptorRepository repo = ord.getClassDescriptor().getRepository(); 1444 1445        if (hintClasses == null || hintClasses.isEmpty()) 1446        { 1447            return repo.getDescriptorFor(ord.getItemClass()); 1448        } 1449         1450        Class resultClass = (Class ) hintClasses.get(0); 1451         1452        for (Iterator iter = hintClasses.iterator(); iter.hasNext();) 1453        { 1454            Class clazz = (Class ) iter.next(); 1455            Class superClazz = clazz.getSuperclass(); 1456 1457            if (superClazz != null && resultClass.equals(superClazz.getSuperclass())) 1458            { 1459                continue; // skip if we already have a super superclass 1460 } 1461            1462            if (hintClasses.contains(superClazz)) 1463            { 1464                resultClass = superClazz; // use superclass if it's in the hints 1465 } 1466        } 1467 1468        return repo.getDescriptorFor(resultClass); 1469    } 1470 1471    /** 1472     * Appends the ORDER BY clause for the Query. 1473     * <br> 1474     * If the orderByField is found in the list of selected fields it's index is added. 1475     * Otherwise it's name is added. 1476     * @param orderByFields 1477     * @param selectedFields the names of the fields in the SELECT clause 1478     * @param buf 1479     */ 1480    protected void appendOrderByClause(List orderByFields, List selectedFields, StringBuffer buf) 1481    { 1482 1483        if (orderByFields == null || orderByFields.size() == 0) 1484        { 1485            return; 1486        } 1487         1488        buf.append(" ORDER BY "); 1489        for (int i = 0; i < orderByFields.size(); i++) 1490        { 1491            FieldHelper cf = (FieldHelper) orderByFields.get(i); 1492            int colNumber = selectedFields.indexOf(cf.name); 1493             1494            if (i > 0) 1495            { 1496                buf.append(","); 1497            } 1498             1499            if (colNumber >= 0) 1500            { 1501                buf.append(colNumber + 1); 1502            } 1503            else 1504            { 1505                appendColName(cf.name, false, null, buf); 1506            } 1507             1508            if (!cf.isAscending) 1509            { 1510                buf.append(" DESC"); 1511            } 1512        } 1513    } 1514 1515    /** 1516     * Appends the GROUP BY clause for the Query 1517     * @param groupByFields 1518     * @param buf 1519     */ 1520    protected void appendGroupByClause(List groupByFields, StringBuffer buf) 1521    { 1522        if (groupByFields == null || groupByFields.size() == 0) 1523        { 1524            return; 1525        } 1526 1527        buf.append(" GROUP BY "); 1528        for (int i = 0; i < groupByFields.size(); i++) 1529        { 1530            FieldHelper cf = (FieldHelper) groupByFields.get(i); 1531  1532            if (i > 0) 1533            { 1534                buf.append(","); 1535            } 1536 1537            appendColName(cf.name, false, null, buf); 1538        } 1539    } 1540 1541    /** 1542     * Appends to the statement table and all tables joined to it. 1543     * @param alias the table alias 1544     * @param where append conditions for WHERE clause here 1545     */ 1546    protected void appendTableWithJoins(TableAlias alias, StringBuffer where, StringBuffer buf) 1547    { 1548        int stmtFromPos = 0; 1549        byte joinSyntax = getJoinSyntaxType(); 1550 1551        if (joinSyntax == SQL92_JOIN_SYNTAX) 1552        { 1553            stmtFromPos = buf.length(); // store position of join (by: Terry Dexter) 1554 } 1555 1556        if (alias == getRoot()) 1557        { 1558            // BRJ: also add indirection table to FROM-clause for MtoNQuery 1559 if (getQuery() instanceof MtoNQuery) 1560            { 1561                MtoNQuery mnQuery = (MtoNQuery)m_query; 1562                buf.append(getTableAliasForPath(mnQuery.getIndirectionTable(), null).getTableAndAlias()); 1563                buf.append(", "); 1564            } 1565            buf.append(alias.getTableAndAlias()); 1566        } 1567        else if (joinSyntax != SQL92_NOPAREN_JOIN_SYNTAX) 1568        { 1569            buf.append(alias.getTableAndAlias()); 1570        } 1571 1572        if (!alias.hasJoins()) 1573        { 1574            return; 1575        } 1576 1577        for (Iterator it = alias.iterateJoins(); it.hasNext();) 1578        { 1579            Join join = (Join) it.next(); 1580 1581            if (joinSyntax == SQL92_JOIN_SYNTAX) 1582            { 1583                appendJoinSQL92(join, where, buf); 1584                if (it.hasNext()) 1585                { 1586                    buf.insert(stmtFromPos, "("); 1587                    buf.append(")"); 1588                } 1589            } 1590            else if (joinSyntax == SQL92_NOPAREN_JOIN_SYNTAX) 1591            { 1592                appendJoinSQL92NoParen(join, where, buf); 1593            } 1594            else 1595            { 1596                appendJoin(where, buf, join); 1597            } 1598 1599        } 1600    } 1601 1602    /** 1603     * Append Join for non SQL92 Syntax 1604     */ 1605    private void appendJoin(StringBuffer where, StringBuffer buf, Join join) 1606    { 1607        buf.append(","); 1608        appendTableWithJoins(join.right, where, buf); 1609        if (where.length() > 0) 1610        { 1611            where.append(" AND "); 1612        } 1613        join.appendJoinEqualities(where); 1614    } 1615 1616    /** 1617     * Append Join for SQL92 Syntax 1618     */ 1619    private void appendJoinSQL92(Join join, StringBuffer where, StringBuffer buf) 1620    { 1621        if (join.isOuter) 1622        { 1623            buf.append(" LEFT OUTER JOIN "); 1624        } 1625        else 1626        { 1627            buf.append(" INNER JOIN "); 1628        } 1629        if (join.right.hasJoins()) 1630        { 1631            buf.append("("); 1632            appendTableWithJoins(join.right, where, buf); 1633            buf.append(")"); 1634        } 1635        else 1636        { 1637            appendTableWithJoins(join.right, where, buf); 1638        } 1639        buf.append(" ON "); 1640        join.appendJoinEqualities(buf); 1641    } 1642 1643    /** 1644     * Append Join for SQL92 Syntax without parentheses 1645     */ 1646    private void appendJoinSQL92NoParen(Join join, StringBuffer where, StringBuffer buf) 1647    { 1648        if (join.isOuter) 1649        { 1650            buf.append(" LEFT OUTER JOIN "); 1651        } 1652        else 1653        { 1654            buf.append(" INNER JOIN "); 1655        } 1656 1657        buf.append(join.right.getTableAndAlias()); 1658        buf.append(" ON "); 1659        join.appendJoinEqualities(buf); 1660 1661        appendTableWithJoins(join.right, where, buf); 1662    } 1663 1664    /** 1665     * Build the tree of joins for the given criteria 1666     */ 1667    private void buildJoinTree(Criteria crit) 1668    { 1669        Enumeration e = crit.getElements(); 1670 1671        while (e.hasMoreElements()) 1672        { 1673            Object o = e.nextElement(); 1674            if (o instanceof Criteria) 1675            { 1676                buildJoinTree((Criteria) o); 1677            } 1678            else 1679            { 1680                SelectionCriteria c = (SelectionCriteria) o; 1681                 1682                // BRJ skip SqlCriteria 1683 if (c instanceof SqlCriteria) 1684                { 1685                    continue; 1686                } 1687                 1688                // BRJ: Outer join for OR 1689 boolean useOuterJoin = (crit.getType() == Criteria.OR); 1690 1691                // BRJ: do not build join tree for subQuery attribute 1692 if (c.getAttribute() != null && c.getAttribute() instanceof String ) 1693                { 1694                    //buildJoinTreeForColumn((String) c.getAttribute(), useOuterJoin, c.getAlias(), c.getPathClasses()); 1695 buildJoinTreeForColumn((String ) c.getAttribute(), useOuterJoin, c.getUserAlias(), c.getPathClasses()); 1696                } 1697                if (c instanceof FieldCriteria) 1698                { 1699                    FieldCriteria cc = (FieldCriteria) c; 1700                    buildJoinTreeForColumn((String ) cc.getValue(), useOuterJoin, c.getUserAlias(), c.getPathClasses()); 1701                } 1702            } 1703        } 1704    } 1705 1706    /** 1707     * build the Join-Information for name 1708     * functions and the last segment are removed 1709     * ie: avg(accounts.amount) -> accounts 1710     */ 1711    private void buildJoinTreeForColumn(String aColName, boolean useOuterJoin, UserAlias aUserAlias, Map pathClasses) 1712    { 1713        String pathName = SqlHelper.cleanPath(aColName); 1714        int sepPos = pathName.lastIndexOf("."); 1715 1716        if (sepPos >= 0) 1717        { 1718            getTableAlias(pathName.substring(0, sepPos), useOuterJoin, aUserAlias, 1719                          new String []{pathName.substring(sepPos + 1)}, pathClasses); 1720        } 1721    } 1722 1723    /** 1724     * build the Join-Information if a super reference exists 1725     * 1726     * @param left 1727     * @param cld 1728     * @param name 1729     */ 1730    protected void buildSuperJoinTree(TableAlias left, ClassDescriptor cld, String name, boolean useOuterJoin) 1731    { 1732        ClassDescriptor superCld = cld.getSuperClassDescriptor(); 1733        if (superCld != null) 1734        { 1735            SuperReferenceDescriptor superRef = cld.getSuperReference(); 1736            FieldDescriptor[] leftFields = superRef.getForeignKeyFieldDescriptors(cld); 1737            TableAlias base_alias = getTableAliasForPath(name, null, null); 1738            String aliasName = String.valueOf(getAliasChar()) + m_aliasCount++; 1739            TableAlias right = new TableAlias(superCld, aliasName, useOuterJoin, null); 1740 1741            Join join1to1 = new Join(left, leftFields, right, superCld.getPkFields(), useOuterJoin, "superClass"); 1742            base_alias.addJoin(join1to1); 1743 1744            buildSuperJoinTree(right, superCld, name, useOuterJoin); 1745        } 1746    } 1747 1748    /** 1749     * build the Join-Information for Subclasses having a super reference to this class 1750     * 1751     * @param left 1752     * @param cld 1753     * @param name 1754     */ 1755    private void buildMultiJoinTree(TableAlias left, ClassDescriptor cld, String name, boolean useOuterJoin) 1756    { 1757        DescriptorRepository repository = cld.getRepository(); 1758        Class [] multiJoinedClasses = repository.getSubClassesMultipleJoinedTables(cld, false); 1759 1760        for (int i = 0; i < multiJoinedClasses.length; i++) 1761        { 1762            ClassDescriptor subCld = repository.getDescriptorFor(multiJoinedClasses[i]); 1763            SuperReferenceDescriptor srd = subCld.getSuperReference(); 1764            if (srd != null) 1765            { 1766                FieldDescriptor[] leftFields = subCld.getPkFields(); 1767                FieldDescriptor[] rightFields = srd.getForeignKeyFieldDescriptors(subCld); 1768                TableAlias base_alias = getTableAliasForPath(name, null, null); 1769 1770                String aliasName = String.valueOf(getAliasChar()) + m_aliasCount++; 1771                TableAlias right = new TableAlias(subCld, aliasName, false, null); 1772 1773                Join join1to1 = new Join(left, leftFields, right, rightFields, useOuterJoin, "subClass"); 1774                base_alias.addJoin(join1to1); 1775 1776                buildMultiJoinTree(right, subCld, name, useOuterJoin); 1777            } 1778        } 1779    } 1780 1781    /** 1782     * First reduce the Criteria to the normal disjunctive form, then 1783     * calculate the necessary tree of joined tables for each item, then group 1784     * items with the same tree of joined tables. 1785     */ 1786    protected void splitCriteria() 1787    { 1788        Criteria whereCrit = getQuery().getCriteria(); 1789        Criteria havingCrit = getQuery().getHavingCriteria(); 1790 1791        if (whereCrit == null || whereCrit.isEmpty()) 1792        { 1793            getJoinTreeToCriteria().put(getRoot(), null); 1794        } 1795        else 1796        { 1797            // TODO: parameters list shold be modified when the form is reduced to DNF. 1798 getJoinTreeToCriteria().put(getRoot(), whereCrit); 1799            buildJoinTree(whereCrit); 1800        } 1801 1802        if (havingCrit != null && !havingCrit.isEmpty()) 1803        { 1804            buildJoinTree(havingCrit); 1805        } 1806 1807    } 1808  1809     1810    /** 1811     * Gets the query. 1812     * @return Returns a Query 1813     */ 1814    protected QueryByCriteria getQuery() 1815    { 1816        return m_query; 1817    } 1818 1819    /** 1820     * Gets the root. 1821     * @return Returns a TableAlias 1822     */ 1823    protected TableAlias getRoot() 1824    { 1825        return m_root; 1826    } 1827 1828    /** 1829     * Sets the root. 1830     * @param root The root to set 1831     */ 1832    protected void setRoot(TableAlias root) 1833    { 1834        this.m_root = root; 1835    } 1836 1837    /** 1838     * Gets the search table of this query. 1839     * @return Returns a TableAlias 1840     */ 1841    protected TableAlias getSearchTable() 1842    { 1843        return m_search; 1844    } 1845 1846    /** 1847     * Gets the joinTreeToCriteria. 1848     * @return Returns a HashMap 1849     */ 1850    protected HashMap getJoinTreeToCriteria() 1851    { 1852        return m_joinTreeToCriteria; 1853    } 1854 1855    /** 1856     * Returns the joinSyntaxType. 1857     * @return byte 1858     */ 1859    protected byte getJoinSyntaxType() 1860    { 1861        return m_platform.getJoinSyntaxType(); 1862    } 1863 1864    /** 1865     * Returns the logger. 1866     * @return Logger 1867     */ 1868    protected Logger getLogger() 1869    { 1870        return m_logger; 1871    } 1872     1873    public String getStatement() 1874    { 1875        if(sql == null) 1876        { 1877            sql = buildStatement(); 1878        } 1879        return sql; 1880    } 1881 1882    /** 1883     * Build the SQL String. 1884     * @return SQL String 1885     */ 1886    protected abstract String buildStatement(); 1887     1888     1889    //----------------------------------------------------------------- 1890 // ------------------- Inner classes ------------------------------ 1891 //----------------------------------------------------------------- 1892 1893    /** 1894     * This class is a helper to return TableAlias and PathInfo 1895     */ 1896    static final class AttributeInfo 1897    { 1898        TableAlias tableAlias; 1899        PathInfo pathInfo; 1900    } 1901 1902    /** 1903     * This class represents one table (possibly with alias) in the SQL query 1904     */ 1905    final class TableAlias 1906    { 1907        Logger logger = LoggerFactory.getLogger(TableAlias.class); 1908        ClassDescriptor cld; // Is null for indirection table of M:N relation 1909 String table; 1910        final String alias; 1911        List extents = new ArrayList (); 1912        List hints = new ArrayList (); 1913        List joins; 1914 1915        TableAlias(String aTable, String anAlias) 1916        { 1917            this.cld = null; 1918            this.table = aTable; 1919            this.alias = anAlias; 1920        } 1921 1922        TableAlias(ClassDescriptor aCld, String anAlias) 1923        { 1924            this(aCld, anAlias, false, null); 1925        } 1926 1927        TableAlias(ClassDescriptor aCld, String anAlias, boolean lookForExtents, List hints) 1928        { 1929            this.cld = aCld; 1930            this.table = aCld.getFullTableName(); 1931            this.alias = anAlias; 1932            boolean useHintsOnExtents = false; 1933 1934            // BRJ: store alias map of in enclosing class 1935 setTableAliasForClassDescriptor(aCld, this); 1936             1937            //LEANDRO: use hints 1938 if (hints != null && hints.size() > 0) 1939            { 1940                useHintsOnExtents = true; 1941            } 1942 1943            logger.debug("TableAlias(): using hints ? " + useHintsOnExtents); 1944 1945            // BRJ : build alias for extents, only one per Table 1946 if (lookForExtents) 1947            { 1948                ClassDescriptor[] extCLDs = (ClassDescriptor[]) aCld.getRepository().getAllConcreteSubclassDescriptors( 1949                        aCld).toArray(new ClassDescriptor[0]); 1950 1951                ClassDescriptor extCd; 1952                Class extClass; 1953                String extTable; 1954                Map extMap = new HashMap (); // only one Alias per Table 1955 int firstNonAbstractExtentIndex = 0; 1956 1957                for (int i = 0; i < extCLDs.length; i++) 1958                { 1959                    extCd = extCLDs[i]; 1960                    extClass = extCd.getClassOfObject(); 1961                    if (useHintsOnExtents && (!hints.contains(extClass))) 1962                    { 1963                        //LEANDRO: don't include this class 1964 logger.debug("Skipping class [" + extClass + "] from extents List"); 1965                        firstNonAbstractExtentIndex++; 1966                        continue; 1967                    } 1968                    extTable = extCd.getFullTableName(); 1969 1970                    // BRJ : Use the first non abstract extent 1971 // if the main cld is abstract 1972 //logger.debug("cld abstract["+aCld.isAbstract()+"] i["+i+"] index ["+firtsNonAbstractExtentIndex+"]"); 1973 if (aCld.isAbstract() && i == firstNonAbstractExtentIndex) 1974                    { 1975                        this.cld = extCd; 1976                        this.table = extTable; 1977                    } 1978                    else 1979                    { 1980                        // Add a new extent entry only if the table of the extent 1981 // does not match the table of the 'base' class. 1982 if (extMap.get(extTable) == null && !extTable.equals(table)) 1983                        { 1984                            extMap.put(extTable, new TableAlias(extCd, anAlias + "E" + i, false, hints)); 1985                        } 1986                    } 1987                } 1988                extents.addAll(extMap.values()); 1989            } 1990 1991            if (cld == null) 1992            { 1993                throw new PersistenceBrokerSQLException("Table is NULL for alias: " + alias); 1994            } 1995        } 1996 1997        ClassDescriptor getClassDescriptor() 1998        { 1999            return cld; 2000        } 2001 2002        String getTableAndAlias() 2003        { 2004            return table + " " + alias; 2005        } 2006 2007        boolean hasExtents() 2008        { 2009            return (!extents.isEmpty()); 2010        } 2011 2012        Iterator iterateExtents() 2013        { 2014            return extents.iterator(); 2015        } 2016 2017        /** 2018         * Copy the Alias and all it's extents adding a Postfix 2019         * Joins are not copied. 2020         */ 2021        TableAlias copy(String aPostfix) 2022        { 2023            TableAlias result, temp; 2024            Iterator iter = iterateExtents(); 2025 2026            if (cld == null) 2027            { 2028                result = new TableAlias(table, alias + aPostfix); 2029            } 2030            else 2031            { 2032                result = new TableAlias(cld, alias + aPostfix); 2033            } 2034 2035            while (iter.hasNext()) 2036            { 2037                temp = (TableAlias) iter.next(); 2038                result.extents.add(temp.copy(aPostfix)); 2039            } 2040 2041            return result; 2042        } 2043 2044        void addJoin(Join join) 2045        { 2046            if (joins == null) 2047            { 2048                joins = new ArrayList (); 2049            } 2050            joins.add(join); 2051        } 2052 2053        Iterator iterateJoins() 2054        { 2055            return joins.iterator(); 2056        } 2057 2058        boolean hasJoins() 2059        { 2060            return (joins != null); 2061        } 2062 2063        /** 2064         * Get the Join ponting to anAlias. 2065         */ 2066        Join getJoin(TableAlias anAlias) 2067        { 2068            Join result = null; 2069 2070            if (joins != null) 2071            { 2072                Iterator iter = joins.iterator(); 2073                while (iter.hasNext()) 2074                { 2075                    Join join = (Join) iter.next(); 2076                    if (join.right.equals(anAlias)) 2077                    { 2078                        result = join; 2079                        break; 2080                    } 2081                } 2082            } 2083            return result; 2084        } 2085         2086        public String toString() 2087        { 2088            StringBuffer sb = new StringBuffer (1024); 2089            boolean first = true; 2090 2091            sb.append(getTableAndAlias()); 2092            if (joins != null) 2093            { 2094                sb.append(" ["); 2095                for (Iterator it = joins.iterator(); it.hasNext();) 2096                { 2097                    Join join = (Join) it.next(); 2098 2099                    if (first) 2100                    { 2101                        first = false; 2102                    } 2103                    else 2104                    { 2105                        sb.append(", "); 2106                    } 2107                    sb.append("-("); 2108                    sb.append(join.name); 2109                    sb.append(")->"); 2110                    sb.append(join.right); 2111                } 2112                sb.append("]"); 2113            } 2114            return sb.toString(); 2115        } 2116 2117        public boolean equals(Object obj) 2118        { 2119            TableAlias t = (TableAlias) obj; 2120 2121            return table.equals(t.table); // BRJ: check table only 2122 } 2123 2124        public int hashCode() 2125        { 2126            return table.hashCode(); 2127        } 2128 2129    } 2130 2131    /** 2132     * This class represents join between two TableAliases 2133     */ 2134    final class Join 2135    { 2136        final TableAlias left; 2137        final String [] leftKeys; 2138        final TableAlias right; 2139        final String [] rightKeys; 2140        boolean isOuter; 2141        /** This is the name of the field corresponding to this join */ 2142        final String name; 2143 2144        /** 2145         * leftKeys and rightKeys should be either FieldDescriptor[] or String[] 2146         */ 2147        Join(TableAlias left, Object [] leftKeys, TableAlias right, Object [] rightKeys, boolean isOuter, String name) 2148        { 2149            this.left = left; 2150            this.leftKeys = getColumns(leftKeys); 2151            this.right = right; 2152            this.rightKeys = getColumns(rightKeys); 2153            this.isOuter = isOuter; 2154            this.name = name; 2155        } 2156 2157        private String [] getColumns(Object [] keys) 2158        { 2159            String [] columns = new String [keys.length]; 2160 2161            if (keys instanceof FieldDescriptor[]) 2162            { 2163                FieldDescriptor[] kd = (FieldDescriptor[]) keys; 2164                for (int i = 0; i < columns.length; i++) 2165                { 2166                    columns[i] = kd[i].getColumnName(); 2167                } 2168            } 2169            else 2170            { 2171                for (int i = 0; i < columns.length; i++) 2172                { 2173                    columns[i] = keys[i].toString(); 2174                } 2175            } 2176            return columns; 2177        } 2178 2179        void appendJoinEqualities(StringBuffer buf) 2180        { 2181            byte joinSyntax = getJoinSyntaxType(); 2182 2183            for (int i = 0; i < leftKeys.length; i++) 2184            { 2185                if (i > 0) 2186                { 2187                    buf.append(" AND "); 2188                } 2189                buf.append(left.alias); 2190                buf.append("."); 2191                buf.append(leftKeys[i]); 2192 2193                if (isOuter && joinSyntax == SYBASE_JOIN_SYNTAX) 2194                { 2195                    buf.append("*="); 2196                } 2197                else 2198                { 2199                    buf.append("="); 2200                } 2201 2202                buf.append(right.alias); 2203                buf.append("."); 2204                buf.append(rightKeys[i]); 2205 2206                if (isOuter && joinSyntax == ORACLE_JOIN_SYNTAX) 2207                { 2208                    buf.append("(+)"); 2209                } 2210            } 2211        } 2212 2213        public boolean equals(Object obj) 2214        { 2215            Join j = (Join) obj; 2216            return name.equals(j.name) && (isOuter == j.isOuter) && right.equals(j.right); 2217        } 2218 2219        public int hashCode() 2220        { 2221            return name.hashCode(); 2222        } 2223 2224        public String toString() 2225        { 2226            return left.alias + " -> " + right.alias; 2227        } 2228    } 2229} 2230

A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame Popular Tags