Java > Open Source Codes > org > opensubsystems > patterns > listdata > persist > db > ListDatabaseUtils


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: ListDatabaseUtils.java,v 1.55 2007/01/28 06:54:46 bastafidli Exp $
7  *
8  * This program is free software; you can redistribute it and/or modify
9  * it under the terms of the GNU General Public License as published by
10  * the Free Software Foundation; version 2 of the License.
11  *
12  * This program 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
15  * GNU General Public License for more details.
16  *
17  * You should have received a copy of the GNU General Public License
18  * along with this program; if not, write to the Free Software
19  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20  */
21
22 package org.opensubsystems.patterns.listdata.persist.db;
23
24 import java.sql.Connection ;
25 import java.sql.PreparedStatement ;
26 import java.sql.ResultSet ;
27 import java.sql.SQLException ;
28 import java.sql.Timestamp ;
29 import java.text.SimpleDateFormat ;
30 import java.util.ArrayList ;
31 import java.util.Collection ;
32 import java.util.Iterator ;
33 import java.util.List ;
34
35 import org.opensubsystems.core.data.DataConstant;
36 import org.opensubsystems.core.data.DataObject;
37 import org.opensubsystems.core.error.OSSDatabaseAccessException;
38 import org.opensubsystems.core.error.OSSException;
39 import org.opensubsystems.core.error.OSSInvalidDataException;
40 import org.opensubsystems.core.persist.db.DatabaseConnectionFactoryImpl;
41 import org.opensubsystems.core.persist.db.DatabaseDependentClassManager;
42 import org.opensubsystems.core.persist.db.DatabaseFactoryImpl;
43 import org.opensubsystems.core.persist.db.DatabaseImpl;
44 import org.opensubsystems.core.persist.db.DatabaseSchemaImpl;
45 import org.opensubsystems.core.util.CallContext;
46 import org.opensubsystems.core.util.DatabaseUtils;
47 import org.opensubsystems.core.util.DateUtils;
48 import org.opensubsystems.core.util.GlobalConstants;
49 import org.opensubsystems.core.util.StringUtils;
50 import org.opensubsystems.patterns.listdata.data.DataCondition;
51 import org.opensubsystems.patterns.listdata.data.ListOptions;
52 import org.opensubsystems.patterns.listdata.data.SimpleRule;
53
54 /**
55  * This class is used for common list retrieval and manipulation routines.
56  *
57  * @version $Id: ListDatabaseUtils.java,v 1.55 2007/01/28 06:54:46 bastafidli Exp $
58  * @author Julo Legeny
59  * @code.reviewer Miro Halas
60  * @code.reviewed 1.47 2006/03/14 23:56:56 jlegeny
61  */
62 public class ListDatabaseUtils
63 {
64    // Constants ////////////////////////////////////////////////////////////////
65
66    /**
67     * Prefix that will be used for explicitely defined index name on columns that
68     * can be used within the ORDER BY clause for retrieving data to the list.
69     */
70    public static final String LIST_INDEX_PREFIX = "LST_";
71
72    /**
73     * Array used to translate operands into SQL. The index of the operand
74     * has to match value of DataCondition.OPERAND_XXX constant.
75     */
76    private static final String [] OPERANDS = new String [] {"",
77                                                           "=",
78                                                           "in",
79                                                           "not in",
80                                                           "<>",
81                                                           ">",
82                                                           ">=",
83                                                           "<",
84                                                           "<=",
85                                                           "like",
86                                                           "like",
87                                                           "",
88                                                           "=",
89                                                           "not like",
90                                                           "not like",
91                                                           "like",
92                                                           "like",
93                                                           "not like",
94                                                           "not like",
95                                                           "like",
96                                                           "like",
97                                                           "not like",
98                                                           "not like",
99                                                           "like",
100                                                           "like",
101                                                           "not like",
102                                                           "not like",
103                                                          };
104
105    // Public methods ///////////////////////////////////////////////////////////
106
107    /**
108     * Get database dependent instance of ListDatabaseUtils.
109     *
110     * @return ListDatabaseUtils - the database dependent instance to use for this class
111     * @throws OSSException - an error has occured
112     */
113    public static ListDatabaseUtils getInstance(
114    ) throws OSSException
115    {
116       // Convenience methods to that everybody doesn't have to write this
117 return (ListDatabaseUtils)DatabaseDependentClassManager.getInstance(
118                                    ListDatabaseUtils.class);
119    }
120
121    /**
122     * Method construct name of the index used for data retrieving into the list.
123     * This index name should be common for all DB systems. But there is restriction
124     * for IBM DB2 database, the length of the index name shold be max. 18 characters.
125     * So there will be generated index name from:
126     * 1. index prefix = LST_ (4 characters)
127     * 2. Prefix BF_ from the table name will be removed first. Also all others occurences
128     * of the character '_' will be removed.
129     * max 7. characters from the table name: 3 characters from the begining of the table
130     * name and 4 characters from the end of the table name string.
131     * 3. All occurences of the character '_' will be removed.
132     * max 7. characters from the column name: 3 characters from the begining of
133     * the column name and 4 characters from the end of the column name string
134     * For example:
135     * table BF_INTERNAL_SESSION
136     * column CREATION_DATE
137     * index name = LST_INTSIONCREDATE
138     *
139     * @param strTableName - name of the table that will be useed for index name costruction
140     * @param strColumnName - name of the column that will be useed for index name costruction
141     * @return String - constructed index name
142     */
143    public String getConstructListIndexName(
144       String strTableName,
145       String strColumnName
146    )
147    {
148       if (GlobalConstants.ERROR_CHECKING)
149       {
150          // Table name has to be at least 2 characters length
151 assert strTableName.length() > 2 : "Table name is too short.";
152          assert strColumnName.length() > 1 : "Column name is too short.";
153       }
154
155       StringBuffer buffer = new StringBuffer ();
156       
157       int iTableNameLength = strTableName.length();
158       int iColumnNameLength = strColumnName.length();
159       
160       // append list prefix first
161 buffer.append(LIST_INDEX_PREFIX);
162       
163       if (strTableName.toUpperCase().startsWith(DatabaseSchemaImpl.getSchemaPrefix())
164           && strTableName.length() > 3)
165       {
166          // remove database schema prefix from the table name
167 strTableName = strTableName.substring(
168             DatabaseSchemaImpl.getSchemaPrefix().length(), iTableNameLength);
169       }
170
171       // process table name for constructing part of the index name
172 if (iTableNameLength < 8)
173       {
174          buffer.append(strTableName);
175       }
176       else
177       {
178          
179          // first remove all '_' characters
180 strTableName = strTableName.replaceAll("_", "");
181          iTableNameLength = strTableName.length();
182          // add first 3 characters
183 buffer.append(strTableName.substring(0, 3));
184          // add last 4 characters
185 buffer.append(strTableName.substring(iTableNameLength - 4, iTableNameLength));
186       }
187
188       // process column name for constructing part of the index name
189 if (iColumnNameLength < 8)
190       {
191          buffer.append(strColumnName);
192       }
193       else
194       {
195          // first remove all '_' characters
196 strColumnName = strColumnName.replaceAll("_", "");
197          iColumnNameLength = strColumnName.length();
198          // add first 3 characters
199 buffer.append(strColumnName.substring(0, 3));
200          // add last 4 characters
201 buffer.append(strColumnName.substring(iColumnNameLength - 4, iColumnNameLength));
202       }
203          
204       return buffer.toString();
205    }
206   
207    /**
208     * Get list of data objects for specified parameters
209     *
210     * @param options - list option describing the list to retrieve
211     * @param query - select SQL query
212     * @param factory - list factory for specific data object
213     * @param schema - list database schema
214     * @return List - list of data objects
215     * @throws OSSException - an error getting object list
216     */
217    public List getObjectList(
218       ListOptions options,
219       String query,
220       ListDatabaseFactory factory,
221       ListDatabaseSchema schema
222    ) throws OSSException
223    {
224       List lstObjects = null;
225       Connection cntConnection = null;
226       ResultSet rsQueryResults = null;
227       PreparedStatement pstmQuery = null;
228       
229       String strOriginalQuery = query;
230 // MyTimer timer = new MyTimer();
231
232       try
233       {
234          // Request autocommit true since we are just reading data from the database
235 cntConnection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(true);
236       
237          try
238          {
239             int iCount = 0;
240    
241             if ((!DatabaseImpl.getInstance().hasAbsolutePositioningSupport())
242                 || (DatabaseImpl.getInstance().preferCountToLast()))
243             {
244                // --------------------------------------------------------------
245 // 1. If the count() is preferred to last() to determine how
246 // many results are there available or if the last() is not supported
247 // then execute count separately
248 // --------------------------------------------------------------
249
250                // get number of all items retrieved by count(*)
251 iCount = getSelectCount(cntConnection, query, schema, options.getDomainId());
252       
253                // set option positioning, at this position we know the real number of all records
254 setOptionPositioning(iCount, options);
255                
256                // allocate object list
257 lstObjects = allocateObjectList(options, iCount);
258       
259                // Preprocess select query for creation date (used here especially for MySQL)
260 // default implementation doesn't do anything
261 query = preprocessSelectQueryForCreationDate(query, options, schema);
262                // store original query for later usage (we want to store query without
263 // containing LIMIT) - it is important to have original query without
264 // LIMIT clause for calling method getObjectListKeepSelected()
265 strOriginalQuery = query;
266    
267                // default implementation doesn't do anything
268 // if there is supported LIMIT, process changes on original query and use them
269 query = preprocessSelectQuery(query, options, schema);
270    
271                //timer.reset();
272 if (DatabaseImpl.getInstance().hasSelectListRangeSupport())
273                {
274                   pstmQuery = cntConnection.prepareStatement(query);
275                }
276                else
277                {
278                   // for later processing 'absolute' on resultset we have to
279 // call preparedStatement with 3 parameters
280 pstmQuery = cntConnection.prepareStatement(query,
281                      ((DatabaseFactoryImpl) factory).getTypeSelectListResultSet(),
282                      ((DatabaseFactoryImpl) factory).getConcurrencySelectListResultSet());
283                }
284                if (schema.isInDomain())
285                {
286                   pstmQuery.setInt(1, options.getDomainId());
287                }
288
289                // s_logger.info("Duration for prepare statement = " + timer.toString());
290
291                rsQueryResults = pstmQuery.executeQuery();
292    
293                if (lstObjects != null)
294                {
295                   // If query with list range support will be used then the result
296 // is the first record in the result set
297 // timer.reset();
298 if (DatabaseImpl.getInstance().hasSelectListRangeSupport())
299                   {
300                      rsQueryResults.next();
301                   }
302                   else
303                   {
304                      rsQueryResults.absolute(options.getBeginPosition());
305                   }
306                   // s_logger.info("Duration for absolute/first() for load data = "
307 // + timer.toString());
308 }
309             }
310             else
311             {
312                // --------------------------------------------------------------
313 // 2. If the last() is preferred to count() so determine how many
314 // items are available by executing the query and checking how
315 // many items it returned
316 // --------------------------------------------------------------
317
318                // Preprocess select query for creation date (used here especially for HsqlDB)
319 query = preprocessSelectQueryForCreationDate(query, options, schema);
320                // store original query for later usage (we want to store query without
321 // containing LIMIT) - it is important to have original query without
322 // LIMIT clause for calling method getObjectListKeepSelected()
323 strOriginalQuery = query;
324
325                // s_logger.info("Preparing query " + query);
326 // timer.reset();
327 pstmQuery = cntConnection.prepareStatement(
328                   query,
329                   DatabaseImpl.getInstance().getSelectListResultSetType(),
330                   DatabaseImpl.getInstance().getSelectListResultSetConcurrency()
331                );
332                if (schema.isInDomain())
333                {
334                   pstmQuery.setInt(1, options.getDomainId());
335                }
336                // s_logger.info("Duration for prepare statement = " + timer.toString());
337
338                // timer.reset();
339 rsQueryResults = pstmQuery.executeQuery();
340                // s_logger.info("Duration for executing query = " + timer.toString());
341
342                // We should use last() to determine total number of items
343 // timer.reset();
344 iCount = getTotalRecords(rsQueryResults, options);
345                // s_logger.info("Duration for last() = " + timer.toString());
346
347                // set option positioning, at this position we know the real number of all records
348 setOptionPositioning(iCount, options);
349                
350                // allocate object list
351 lstObjects = allocateObjectList(options, iCount);
352    
353                if (lstObjects != null)
354                {
355                   // timer.reset();
356 rsQueryResults.absolute(options.getBeginPosition());
357                   // s_logger.info("Duration for absolute() for load data = " + timer.toString());
358 }
359             }
360    
361             // =====================================================================
362
363             // At this point we have already constructed and executed query,
364 // allocated object list and set up options. Now load particular
365 // data from the result set.
366 if (lstObjects != null)
367             {
368                if (GlobalConstants.ERROR_CHECKING)
369                {
370                   assert iCount > 0
371                          : "List should be null for empty result";
372                }
373                
374                int iHelpCounter = 0;
375                // Cache this so we don't have to call it always inside of loop
376 int[] columnCodes = options.getRetrieveColumnCodes();
377    
378                // not to call rsQueryResults.next() for first loop
379 // timer.reset();
380 while ((iHelpCounter == 0 || rsQueryResults.next())
381                   && ((iHelpCounter < options.getPageSize())
382                   || (options.getPageSize() == ListOptions.PAGE_SIZE_ALL)))
383                {
384                   iHelpCounter++;
385                   lstObjects.add(factory.load(rsQueryResults, columnCodes, 1));
386                }
387                // s_logger.info("Duration for load data = " + timer.toString());
388 }
389          }
390          finally
391          {
392             DatabaseUtils.closeResultSetAndStatement(rsQueryResults, pstmQuery);
393          }
394
395          // =====================================================================
396
397          // Call method that check if can be processed list keeping selected and
398 // if yes, do it.
399 lstObjects = getObjectListKeepSelected(factory, cntConnection, strOriginalQuery,
400                                                 options, lstObjects, schema);
401       }
402       catch (SQLException sqleExc)
403       {
404          throw new OSSDatabaseAccessException(
405                       "Failed to retrieve specified list of data objects" +
406                       " from the database.", sqleExc);
407       }
408       finally
409       {
410          DatabaseConnectionFactoryImpl.getInstance().returnConnection(cntConnection);
411       }
412    
413       return lstObjects;
414    }
415
416    /**
417     * Get list of data objects keeping the selected data object.
418     *
419     * @param factory - list factory for specific data object
420     * @param cntConnection - connection
421     * @param query - select SQL query
422     * @param options - list option describing the list to retrieve
423     * @param lstObjects - list of data objects
424     * @param schema - list database schema
425     * @return List - list of data objects
426     * @throws OSSException - an error getting object list keeping selected
427     * @throws SQLException - an error getting object list keeping selected
428     */
429    protected List getObjectListKeepSelected(
430       ListDatabaseFactory factory,
431       Connection cntConnection,
432       String query,
433       ListOptions options,
434       List lstObjects,
435       ListDatabaseSchema schema
436    ) throws OSSException, SQLException
437    {
438       // If 'Keep selected' checkbox is checked, try to construct
439 // new query that will retrieve all selected rows
440 if ((options.getIsKeepSelectedChecked())
441          && (options.getSelectedItemIDs().length() > 0))
442       {
443          Iterator items = null;
444          int iActualID;
445          int iIndex = 0;
446
447          StringBuffer sbSelectedIDs = new StringBuffer ();
448          StringBuffer sbTemp = new StringBuffer ();
449          
450          // construct string of original selected item IDs
451 sbSelectedIDs.append(",");
452          sbSelectedIDs.append(options.getSelectedItemIDs().replaceAll(" ", ""));
453          sbSelectedIDs.append(",");
454          
455          items = lstObjects.iterator();
456          while (items.hasNext())
457          {
458             iActualID = ((DataObject)items.next()).getId();
459             // construct string of actual ID from already retrieved list
460 sbTemp.append(",");
461             sbTemp.append(iActualID);
462             sbTemp.append(",");
463             
464             // Check if already retrieved ID is also contained within the
465 // selected IDs. If YES, don't add this ID to the list of selected
466 // IDs (we don't want to select it duplicite).
467 iIndex = sbSelectedIDs.indexOf(sbTemp.toString());
468             if (iIndex > -1)
469             {
470                sbSelectedIDs.delete(iIndex, iIndex + sbTemp.length() - 1);
471             }
472
473             sbTemp.delete(0, sbTemp.length());
474          }
475          // remove first and last comma occurence
476 iIndex = sbSelectedIDs.indexOf(",");
477          if (iIndex != -1)
478          {
479             sbSelectedIDs.deleteCharAt(iIndex);
480          }
481          iIndex = sbSelectedIDs.lastIndexOf(",");
482          if (iIndex != -1)
483          {
484             sbSelectedIDs.deleteCharAt(iIndex);
485          }
486
487          // Add condition AND ID IN (<sbFinalSelectedIDs>) to the query. This
488 // query will retrieve previously selected items that weren't retrieved
489 // in the first select.
490 if (sbSelectedIDs.length() > 0)
491          {
492             int iIndexOrderBy = query.toLowerCase().indexOf(" order by ");
493             if (iIndexOrderBy > -1)
494             {
495                sbTemp.append(query.substring(0, iIndexOrderBy));
496                sbTemp.append(" and ID in (");
497                sbTemp.append(sbSelectedIDs);
498                sbTemp.append(") ");
499                sbTemp.append(query.substring(iIndexOrderBy, query.length()));
500             }
501             else
502             {
503                sbTemp.append(query);
504                sbTemp.append(" and ID in (");
505                sbTemp.append(sbSelectedIDs);
506                sbTemp.append(") ");
507             }
508             
509             ResultSet rsQueryResults = null;
510             PreparedStatement pstmQuery = null;
511
512             try
513             {
514                pstmQuery = cntConnection.prepareStatement(sbTemp.toString());
515                if (schema.isInDomain())
516                {
517                   pstmQuery.setInt(1, options.getDomainId());
518                }
519                rsQueryResults = pstmQuery.executeQuery();
520    
521                // At this point we have already constructed and executed query.
522 // Now load particular data from the result set.
523 // Cache this so we don't have to call it always inside of loop
524 int[] columnCodes = options.getRetrieveColumnCodes();
525                iIndex = 0;
526       
527                while (rsQueryResults.next())
528                {
529                   lstObjects.add(iIndex++, factory.load(rsQueryResults, columnCodes, 1));
530                }
531             }
532             finally
533             {
534                DatabaseUtils.closeResultSetAndStatement(rsQueryResults, pstmQuery);
535             }
536          }
537       }
538       
539       return lstObjects;
540    }
541
542    /**
543     * Generate select query to returns data based on the list option describing
544     * what data objects to load taking into account filter decribing security
545     * restrictions that prevent us from loading data that the user
546     * doesn't have rights to see and all other filtering conditions.
547     *
548     * Following portions of the list options are considered
549     *
550     * - list parent specification
551     * - list ignored list
552     * - security options
553     * - filter options
554     * - any extra conditions
555     * - ordering clause
556     *
557     * @param strTableName - name of the table from which the data should be loaded
558     * @param options - list options used to generate query to load list of data
559     * @param schema - list database schema implementation capable of providing
560     * more info about attributes included in the query
561     * @return String - select query for list options
562     * @throws OSSException - an error has occured
563     */
564    public String getSelectList(
565       String strTableName,
566       ListOptions options,
567       ListDatabaseSchema schema
568    ) throws OSSException
569    {
570       if (GlobalConstants.ERROR_CHECKING)
571       {
572          assert ((strTableName != null) && (strTableName.length() > 0))
573                 : "Table name must be specified";
574          assert options != null : "List options must be specified.";
575          assert options.getSecurityFilter() != null
576                 : "If I don't have right to view, this shouldn't be called";
577          assert options.getDefinition() != null
578                 : "We have to have filter to view something (even if we want to view all)";
579       }
580
581       // The strategy:
582 // First join all conditions so we can construct the correct FROM clause
583 // so that each table is there only once
584 // Then add individual conditions to the WHERE clause separately
585
586       List filterConditions = null;
587       List securityConditions = null;
588       List extraConditions = null;
589       // List searchConditions = null;
590 List allConditions = new ArrayList ();
591       Iterator items;
592
593       //////////////////////////////////////////////////////////////////////////
594 // We need to collect all conditions so we can generate list of tables
595 // for the FROM clause and list of join statements for these tabels
596 //////////////////////////////////////////////////////////////////////////
597
598       // Add condition in case we want to get only list for some parent object
599 // Do this first since this limits the list most
600 StringBuffer parentChildAssociationCondition = null;
601       if (options.getParentDataType() != DataConstant.NO_DATA_TYPE)
602       {
603          DataCondition parentChildAssociation = null;
604          
605          parentChildAssociationCondition = new StringBuffer ();
606          parentChildAssociation = new DataCondition(options.getParentDataType(),
607                                                     DataCondition.OPERAND_EQUALS,
608                                                     new Integer (options.getParentId()),
609                                                     DataCondition.VALUE_TYPE_ID);
610          allConditions.add(parentChildAssociation);
611          // Prepare the SQL WHERE clause for this condition
612 parseCondition(parentChildAssociationCondition, parentChildAssociation, schema);
613       }
614       
615       // Add security conditions before any other since they are the most important
616 if (options.getSecurityFilter() != null)
617       {
618          securityConditions = options.getSecurityFilter().getConditions();
619          if (securityConditions != null)
620          {
621             allConditions.addAll(securityConditions);
622          }
623       }
624       
625       // Add filter conditions form list definitions
626 filterConditions = options.getDefinition().getRule().getConditions();
627       if (filterConditions != null)
628       {
629          allConditions.addAll(filterConditions);
630       }
631       
632       // Add extra conditions
633 SimpleRule extraFilter;
634       
635       extraFilter = options.getExtraFilter();
636       if (extraFilter != null)
637       {
638          extraConditions = extraFilter.getConditions();
639          if (extraConditions != null)
640          {
641             allConditions.addAll(extraConditions);
642          }
643       }
644
645       //////////////////////////////////////////////////////////////////////////
646 // Now we have all conditions so lets generate the lists for the tables and joins
647 //////////////////////////////////////////////////////////////////////////
648
649       String [] otherFromWhere = schema.getJoinFromWhere(allConditions,
650                                                  options.getRetrieveColumnCodes());
651       
652       // Now we are done with the preparatory work and we know all the conditions
653 // and tables we are going to access. Lets construct the query.
654
655       StringBuffer buffer = new StringBuffer ();
656       
657       // This is basic select for list
658 buffer.append("select ");
659
660       if (options.isDistinct())
661       {
662          buffer.append("distinct ");
663       }
664       
665       schema.getColumns(true, options.getRetrieveColumnCodes(), null, null, buffer);
666       buffer.append(" from ");
667       // we will add specific table from method parameters
668 buffer.append(strTableName);
669       
670       // Get all join tables to satisfy filter, security or parent data
671 // and add it to the FROM caluse
672 if (otherFromWhere != null)
673       {
674          buffer.append(otherFromWhere[0]);
675       }
676
677       buffer.append(" where ");
678       // we will add specific table from method parameters
679
680       if (schema.isInDomain())
681       {
682          buffer.append(strTableName);
683          buffer.append(".DOMAIN_ID = ?");
684       }
685       // this is used only for confirmation that
686 // where clause will be not empty
687 else
688       {
689          buffer.append(strTableName);
690          buffer.append(".ID is not null");
691       }
692       
693       // Get all join statements to satisfy filter, security or parent data
694 // and add it to the WHERE caluse
695 if ((otherFromWhere != null) && (otherFromWhere[1].length() > 0))
696       {
697          buffer.append(" and ");
698          buffer.append(otherFromWhere[1]);
699       }
700       
701       //////////////////////////////////////////////////////////////////////////
702 // And now when all tables and joins are there add the individual conditions
703 //////////////////////////////////////////////////////////////////////////
704
705       if ((parentChildAssociationCondition != null)
706          && (parentChildAssociationCondition.length() > 0))
707       {
708          buffer.append(" and ");
709          buffer.append(parentChildAssociationCondition);
710       }
711
712       // add security conditions
713 StringBuffer sbSecurityBuffer = null;
714       if (securityConditions != null)
715       {
716          sbSecurityBuffer = new StringBuffer ();
717          for (items = securityConditions.iterator(); items.hasNext();)
718          {
719             parseCondition(sbSecurityBuffer, (DataCondition)items.next(), schema);
720             if (items.hasNext())
721             {
722                // The security conditions are valid all at once therefore
723 // they have to be connected using OR while filter
724 // depends on the type of the filter what they are
725 sbSecurityBuffer.append(" or ");
726             }
727          }
728          if (sbSecurityBuffer.length() > 0)
729          {
730             buffer.append(" and (");
731             buffer.append(sbSecurityBuffer);
732             buffer.append(")");
733          }
734       }
735
736       // Now parse the filter conditions and add them to the query
737 if (filterConditions != null)
738       {
739          int iFilterType;
740          
741          iFilterType = options.getDefinition().getRule().getConditionsOperand();
742          
743          buffer.append(" and (");
744          for (items = filterConditions.iterator(); items.hasNext();)
745          {
746             parseCondition(buffer, (DataCondition)items.next(), schema);
747             if (items.hasNext())
748             {
749                // The filter conditions depend on the type of filter
750 switch (iFilterType)
751                {
752                   case (SimpleRule.LOGICAL_AND) :
753                   {
754                      buffer.append(" and ");
755                      break;
756                   }
757                   case (SimpleRule.LOGICAL_OR) :
758                   {
759                      buffer.append(" or ");
760                      break;
761                   }
762                   default :
763                   {
764                      assert false : "Invalid filter type " + iFilterType;
765                   }
766                }
767             }
768          }
769          buffer.append(")");
770       }
771
772       // Now parse any extra conditions and add them to the query
773 if (extraConditions != null)
774       {
775          int iFilterType;
776          
777          iFilterType = options.getExtraLogicalOperation();
778          
779          buffer.append(" and (");
780          for (items = extraConditions.iterator(); items.hasNext();)
781          {
782             parseCondition(buffer, (DataCondition)items.next(), schema);
783             if (items.hasNext())
784             {
785                // The filter conditions depend on the type of filter
786 switch (iFilterType)
787                {
788                   case (SimpleRule.LOGICAL_AND) :
789                   {
790                      buffer.append(" and ");
791                      break;
792                   }
793                   case (SimpleRule.LOGICAL_OR) :
794                   {
795                      buffer.append(" or ");
796                      break;
797                   }
798                   default :
799                   {
800                      assert false : "Invalid filter type " + iFilterType;
801                   }
802                }
803             }
804          }
805          buffer.append(")");
806       }
807
808       // Add condition if there are any ignored items
809 if ((options.getIgnoredItemIDs() != null)
810          && (options.getIgnoredItemIDs().length() > 0))
811       {
812          buffer.append(" and ");
813          // we will add table name from method parameters
814 buffer.append(strTableName);
815          buffer.append(".ID NOT IN(");
816          buffer.append(options.getIgnoredItemIDs());
817          buffer.append(")");
818       }
819
820       // Now sort the whole list
821 int[] sortColumns;
822       
823       sortColumns = options.getOrderColumnCodes();
824       if ((sortColumns != null) && (sortColumns.length > 0))
825       {
826          buffer.append(" order by ");
827          schema.getColumns(true, sortColumns, null, options.getOrderDirections(), buffer);
828       }
829
830       return buffer.toString();
831    }
832    
833    /**
834     * Get where clause to select specified ids while taking into account specified
835     * security filter therefore retrieving only those data objects for which the
836     * access is granted. The returned clause will start with "where".
837     *
838     * @param strTableName - name of the table in database
839     * @param arrIds - array of Ids which should be selected, may be null when no
840     * special IDs should be selected
841     * @param securityFilter - rule describing what data can user access
842     * @param schema - list database schema implementation capable of providing
843     * more info about attributes included in the query
844     * @return String - where query
845     * @throws OSSException - error during parse
846     */
847    public String getWhereClause(
848       String strTableName,
849       int[] arrIds,
850       SimpleRule securityFilter,
851       ListDatabaseSchema schema
852    ) throws OSSException
853    {
854       if (GlobalConstants.ERROR_CHECKING)
855       {
856          assert securityFilter != null
857                 : "If I don't have rights for action, this shouldn't be called";
858       }
859       
860       StringBuffer buffer = new StringBuffer ();
861       
862       buffer.append(" where ");
863       
864       if (schema.isInDomain())
865       {
866          // We must use the table name since the calling function may do join
867 // as well
868 buffer.append(strTableName);
869          buffer.append(".DOMAIN_ID = ? ");
870       }
871       
872       if ((arrIds != null) && (arrIds.length > 0))
873       {
874          // We must use the table name since the calling function may do join
875 // as well
876 if (schema.isInDomain())
877          {
878             buffer.append(" and ");
879          }
880          buffer.append(strTableName);
881          buffer.append(".ID in (");
882          buffer.append(StringUtils.parseIntArrayToString(arrIds, ","));
883          buffer.append(")");
884       }
885
886       // TODO: Consider moving this section to security package to something
887 // like SecureListDatabaseUtils since as the name of the property suggests
888 // this in only for security conditions and it also locks us down to the
889 // current domain. If you do so do not forget to do similar change in all
890 // derived classes.
891
892       // All below is totally security related and only tells us what data
893 // objects use can access
894 List securityConditions = null;
895       
896       securityConditions = securityFilter.getConditions();
897       if (securityConditions != null)
898       {
899          // There are some security restrictions so create a subcondition which
900 // will say that only those IDs may be accessed that match subquery
901 // that describes the access rights
902 String [] otherFromWhere;
903          
904          // We must use the table name since the calling function may do join as well
905 buffer.append(" and ");
906          buffer.append(strTableName);
907          buffer.append(".ID in (select ");
908          buffer.append(strTableName);
909          buffer.append(".ID from ");
910          buffer.append(strTableName);
911          // Here we finish FROM clause since, the security may depend on other
912 // tables which must go to FROM claus
913 otherFromWhere = schema.getJoinFromWhere(securityConditions, null);
914          if (otherFromWhere != null)
915          {
916             buffer.append(otherFromWhere[0]);
917          }
918          // Here is FROM done and we add WHERE joining those two tables
919 buffer.append(" where ");
920          if (schema.isInDomain())
921          {
922             buffer.append(strTableName);
923             buffer.append(".DOMAIN_ID=");
924             buffer.append(CallContext.getInstance().getCurrentDomainId());
925          }
926          // this is used only for confirmation that
927 // inner select where clause will be not empty
928 else
929          {
930             buffer.append(strTableName);
931             buffer.append(".ID is not null ");
932          }
933
934          if ((otherFromWhere != null) && (otherFromWhere[1].length() > 0))
935          {
936             buffer.append(" and ");
937             buffer.append(otherFromWhere[1]);
938          }
939          buffer.append(" and (");
940          
941          // Here we construct WHERE clause for the security
942 for (int iCount = 0; iCount < securityConditions.size(); iCount++)
943          {
944             if (iCount > 0)
945             {
946                buffer.append(" or ");
947             }
948             parseCondition(buffer, (DataCondition) securityConditions.get(iCount),
949                            schema);
950          }
951          buffer.append("))");
952       }
953       
954       return buffer.toString();
955    }
956
957    /**
958     * Get where clause to select specified ids while taking into account specified
959     * security filter therefore retrieving only those data objects for which the
960     * access is granted. The returned clause will start with "where".
961     *
962     * @param strTableName - name of the table in database
963     * @param arrIds - array of Ids which should be selected, may be null when no
964     * special IDs should be selected
965     * @param arrDomainIds - array of domain Ids in which should be selected arrIDs,
966     * may be null and in that case will be used original
967     * method getWhereClause() above
968     * @param securityFilter - rule describing what data can user access
969     * @param schema - list database schema implementation capable of providing
970     * more info about attributes included in the query
971     * @return String - where query
972     * @throws OSSException - error during parse
973     */
974    public String getWhereClause(
975       String strTableName,
976       int[] arrIds,
977       int[] arrDomainIds,
978       SimpleRule securityFilter,
979       ListDatabaseSchema schema
980    ) throws OSSException
981    {
982       if (GlobalConstants.ERROR_CHECKING)
983       {
984          assert securityFilter != null
985                 : "If I don't have rights for action, this shouldn't be called";
986       }
987       
988       StringBuffer buffer = new StringBuffer ();
989       
990       if (arrDomainIds != null && arrDomainIds.length > 0)
991       {
992          String strDomainIDs;
993          
994          strDomainIDs = StringUtils.parseIntArrayToString(arrDomainIds, ",");
995          buffer.append(" where ");
996          
997          if (schema.isInDomain())
998          {
999             // We must use the table name since the calling function may do join
1000 // as well
1001 buffer.append(strTableName);
1002            buffer.append(".DOMAIN_ID IN (");
1003            buffer.append(strDomainIDs);
1004            buffer.append(") ");
1005         }
1006         
1007         if ((arrIds != null) && (arrIds.length > 0))
1008         {
1009            // We must use the table name since the calling function may do join
1010 // as well
1011 if (schema.isInDomain())
1012            {
1013               buffer.append(" and ");
1014            }
1015            buffer.append(strTableName);
1016            buffer.append(".ID in (");
1017            buffer.append(StringUtils.parseIntArrayToString(arrIds, ","));
1018            buffer.append(")");
1019         }
1020   
1021         // All below is totally security related and only tells us what data objects
1022 // use can access
1023 List securityConditions = null;
1024         
1025         securityConditions = securityFilter.getConditions();
1026         if (securityConditions != null)
1027         {
1028            // There are some security restrictions so create a subcondition which
1029 // will say that only those IDs may be accessed that match subquery
1030 // that describes the access rights
1031 String [] otherFromWhere;
1032            
1033            // We must use the table name since the calling function may do join as well
1034 buffer.append(" and ");
1035            buffer.append(strTableName);
1036            buffer.append(".ID in (select ");
1037            buffer.append(strTableName);
1038            buffer.append(".ID from ");
1039            buffer.append(strTableName);
1040            // Here we finish FROM clause since, the security may depend on other
1041 // tables which must go to FROM claus
1042 otherFromWhere = schema.getJoinFromWhere(securityConditions, null);
1043            if (otherFromWhere != null)
1044            {
1045               buffer.append(otherFromWhere[0]);
1046            }
1047            // Here is FROM done and we add WHERE joining those two tables
1048 buffer.append(" where ");
1049            if (schema.isInDomain())
1050            {
1051               buffer.append(strTableName);
1052               buffer.append(".DOMAIN_ID IN (");
1053               buffer.append(strDomainIDs);
1054               buffer.append(") ");
1055            }
1056            // this is used only for confirmation that
1057 // inner select where clause will be not empty
1058 else
1059            {
1060               buffer.append(strTableName);
1061               buffer.append(".ID is not null ");
1062            }
1063   
1064            if ((otherFromWhere != null) && (otherFromWhere[1].length() > 0))
1065            {
1066               buffer.append(" and ");
1067               buffer.append(otherFromWhere[1]);
1068            }
1069            buffer.append(" and (");
1070            
1071            // Here we construct WHERE clause for the security
1072 for (int iCount = 0; iCount < securityConditions.size(); iCount++)
1073            {
1074               if (iCount > 0)
1075               {
1076                  buffer.append(" or ");
1077               }
1078               parseCondition(buffer, (DataCondition) securityConditions.get(iCount),
1079                              schema);
1080            }
1081            buffer.append("))");
1082         }
1083      }
1084      else
1085      {
1086         // if array of domain IDs has not been specified, call original method above
1087 getWhereClause(strTableName, arrIds, securityFilter, schema);
1088      }
1089      
1090      
1091      return buffer.toString();
1092   }
1093
1094   // Helper methods ///////////////////////////////////////////////////////////
1095
1096   /**
1097    * Method that preprocess query (by adding specific SQL clauses for example
1098    * LIMIT, ...) to improve performance of selecting items from the list at
1099    * specific position. This method can be overwritten by DB specific method.
1100    *
1101    * @param inputQuery - query that will be preprocessed
1102    * @param options - initialized list options that will be used for retrieving
1103    * begin position, page size, ...
1104    * @param schema - list database schema
1105    * @return String - preprocessed query
1106    * @throws OSSException - error during preprocessing select query
1107    */
1108   protected String preprocessSelectQuery(
1109      String inputQuery,
1110      ListOptions options,
1111      ListDatabaseSchema schema
1112   ) throws OSSException
1113   {
1114      // Default implementation returns original query without preprocessing
1115 return inputQuery;
1116   }
1117
1118   /**
1119    * Method that preprocess query (by adding specific SQL clauses after
1120    * CREATION_DATE sort column) to fix ordering if DBS supports short
1121    * timestamp value. This method can be overwritten by DB specific method.
1122    *
1123    * @param inputQuery - query that will be preprocessed
1124    * @param options - initialized list options that will be used for retrieving
1125    * begin position, page size, ...
1126    * @param schema - list database schema
1127    * @return String - preprocessed query
1128    * @throws OSSException - error during preprocessing select query
1129    */
1130   protected String preprocessSelectQueryForCreationDate(
1131      String inputQuery,
1132      ListOptions options,
1133      ListDatabaseSchema schema
1134   ) throws OSSException
1135   {
1136      // Default implementation returns original query without preprocessing
1137 return inputQuery;
1138   }
1139
1140   /**
1141    * Parse DataCondition to SQL query expression. There result will be
1142    * one or several search term: COLUMN OPERATION VALUE ....
1143    *
1144    * @param strBuffer - buffer to add SQL query expression to
1145    * @param condition - condition to parse
1146    * @param schema - list database schema implementation capable of providing
1147    * more info about attributes included in the query
1148    * @throws OSSException - an error has occured
1149    */
1150   protected void parseCondition(
1151      StringBuffer strBuffer,
1152      DataCondition condition,
1153      ListDatabaseSchema schema
1154   ) throws OSSException
1155   {
1156      Object objValue;
1157               
1158      objValue = condition.getValue();
1159      if ((condition.getAttribute() != DataCondition.NO_ATTRIBUTE) || (objValue != null))
1160      {
1161         // There is some attribute and value
1162 if (condition.getOperation() == DataCondition.OPERAND_EQUALS_OR_NULL)
1163         {
1164            strBuffer.append("(");
1165            schema.getColumns(true, new int[] {condition.getAttribute()}, null, null, strBuffer);
1166            strBuffer.append(getSQLOperation(condition));
1167            strBuffer.append(convertValueToSQL(condition.getValue(), condition.getValueType()));
1168            strBuffer.append(" or ");
1169            schema.getColumns(true, new int[] {condition.getAttribute()}, null, null, strBuffer);
1170            strBuffer.append(" is null ) ");
1171         }
1172         else if (condition.getOperation() != DataCondition.OPERAND_SQL_QUERY)
1173         {
1174            boolean caseUnsensitive
1175               = condition.getOperation() == DataCondition.OPERAND_LIKE_CASEUNSENSITIVE
1176               || condition.getOperation() == DataCondition.OPERAND_NOT_LIKE_CASEUNSENSITIVE
1177               || condition.getOperation() == DataCondition.OPERAND_STARTS_CASEUNSENSITIVE
1178               || condition.getOperation() == DataCondition.OPERAND_NOT_STARTS_CASEUNSENSITIVE
1179               || condition.getOperation() == DataCondition.OPERAND_ENDS_CASEUNSENSITIVE
1180               || condition.getOperation() == DataCondition.OPERAND_NOT_ENDS_CASEUNSENSITIVE
1181               || condition.getOperation() == DataCondition.OPERAND_EQUALS_CASEUNSENSITIVE
1182               || condition.getOperation() == DataCondition.OPERAND_NOT_EQUALS_CASEUNSENSITIVE;
1183                  
1184            if (caseUnsensitive)
1185            {
1186               strBuffer.append("UPPER(");
1187            }
1188            schema.getColumns(true, new int[] {condition.getAttribute()}, null, null, strBuffer);
1189            if (caseUnsensitive)
1190            {
1191               strBuffer.append(")");
1192            }
1193
1194            strBuffer.append(" ");
1195            strBuffer.append(getSQLOperation(condition));
1196            strBuffer.append(" ");
1197         }
1198
1199         // Only add the value part if it was not added because for Equals or NULL
1200 // operation the value was already added
1201 if (condition.getOperation() != DataCondition.OPERAND_EQUALS_OR_NULL)
1202         {
1203            strBuffer.append(getSQLValue(condition));
1204         }
1205      }
1206   }
1207   
1208   /**
1209    * Get SQL operation for operation and value of given data condition.
1210    *
1211    * @param condition - data condition to process
1212    * @return String - operation string
1213    * @throws OSSInvalidDataException - error occured during parsing data
1214    */
1215   protected String getSQLOperation(
1216      DataCondition condition
1217   ) throws OSSInvalidDataException
1218   {
1219      String strReturn;
1220      int iOperation;
1221      Object objValue;
1222      
1223      iOperation = condition.getOperation();
1224      objValue = condition.getValue();
1225      if ((iOperation != DataCondition.OPERAND_IN)
1226         && (iOperation != DataCondition.OPERAND_NOT_IN)
1227         && ("null".equalsIgnoreCase(convertValueToSQL(objValue,
1228                                                       condition.getValueType()))))
1229      {
1230         switch (iOperation)
1231         {
1232            case (DataCondition.OPERAND_EQUALS) :
1233            {
1234               strReturn = "is";
1235               break;
1236            }
1237            case (DataCondition.OPERAND_NOT_EQUALS) :
1238            {
1239               strReturn = "is not";
1240               break;
1241            }
1242            default:
1243            {
1244               strReturn = OPERANDS[iOperation];
1245               break;
1246            }
1247         }
1248      }
1249      else
1250      {
1251         strReturn = OPERANDS[iOperation];
1252      }
1253      
1254      return strReturn;
1255   }
1256
1257   /**
1258    * Convert the value to coresponding SQL representation based on it's data
1259    * type.
1260    *
1261    * @param objValue - value to convert to SQL string representation.
1262    * @param iValueType - one of the DataCondition.VALUE_TYPE_XXX constants
1263    * @return String - string representing the value in valid SQL command
1264    * @throws OSSInvalidDataException - error occured during parsing data
1265    */
1266   protected String convertValueToSQL(
1267      Object objValue,
1268      int iValueType
1269   ) throws OSSInvalidDataException
1270   {
1271      StringBuffer strReturn = new StringBuffer ();
1272      int iHelp;
1273      
1274      if (objValue == null)
1275      {
1276         strReturn.append("null");
1277      }
1278      else
1279      {
1280         switch (iValueType)
1281         {
1282            case (DataCondition.VALUE_TYPE_ID) :
1283            {
1284               iHelp = ((Integer ) objValue).intValue();
1285               if (iHelp == DataObject.NEW_ID)
1286               {
1287                  strReturn.append("null");
1288               }
1289               else
1290               {
1291                  strReturn.append(Integer.toString(iHelp));
1292               }
1293               break;
1294            }
1295            case (DataCondition.VALUE_TYPE_BOOLEAN) :
1296            {
1297               String strValue = objValue.toString();
1298               if (strValue.equals("0") || strValue.equals("1"))
1299               {
1300                  strReturn.append(strValue);
1301               }
1302               else
1303               {
1304                  // We will code true as 1 and false as 0 in DB
1305 strReturn.append(((Boolean ) objValue).booleanValue() ? "1" : "0");
1306               }
1307               break;
1308            }
1309            case (DataCondition.VALUE_TYPE_INTEGER) :
1310            {
1311               strReturn.append(Integer.toString(((Integer ) objValue).intValue()));
1312               break;
1313            }
1314            case (DataCondition.VALUE_TYPE_DOUBLE) :
1315            {
1316               strReturn.append(Double.toString(((Double ) objValue).doubleValue()));
1317               break;
1318            }
1319            case (DataCondition.VALUE_TYPE_STRING) :
1320            {
1321               strReturn.append("'");
1322               strReturn.append((String ) objValue);
1323               strReturn.append("'");
1324               break;
1325            }
1326            case (DataCondition.VALUE_TYPE_TIMESTAMP) :
1327            {
1328               Timestamp tmstp = DateUtils.parseDateTime(
1329                                    objValue.toString(),
1330                                    DateUtils.DATE_TYPE_DATETIME, true);
1331               SimpleDateFormat sdfSQLFormat = new SimpleDateFormat ("''yyyy-MM-dd HH:mm:ss.SSS''");
1332               strReturn.append(sdfSQLFormat.format(tmstp));
1333               break;
1334            }
1335            default :
1336            {
1337               assert false : "Not valid value type for data condition.";
1338            }
1339         }
1340      }
1341      return strReturn.toString();
1342   }
1343   
1344   /**
1345    * Get SQL value for operation and value of given data condition..
1346    *
1347    * @param condition - data condition to process
1348    * @return String - value string
1349    * @throws OSSInvalidDataException - error occured during parsing data
1350    */
1351   protected String getSQLValue(
1352      DataCondition condition
1353   ) throws OSSInvalidDataException
1354   {
1355      String strValue;
1356      int iOperation;
1357      Object objValue;
1358      
1359      iOperation = condition.getOperation();
1360      objValue = condition.getValue();
1361      if (objValue == null)
1362      {
1363         strValue = "null";
1364      }
1365      else
1366      {
1367         // in case that operand is IN or NOT_IN we expect that
1368 // value is String or Oject[] with length > 0
1369 if ((iOperation == DataCondition.OPERAND_IN)
1370            || (iOperation == DataCondition.OPERAND_NOT_IN))
1371         {
1372            StringBuffer buffer = new StringBuffer ();
1373            String strTest;
1374            
1375            buffer.append("(");
1376            if (objValue instanceof Object [])
1377            {
1378               Object [] helpArray = (Object [])objValue;
1379               
1380               if (GlobalConstants.ERROR_CHECKING)
1381               {
1382                  assert helpArray.length > 0 : "IN operator has NO values in array";
1383               }
1384               
1385               for (int iCount = 0; iCount < helpArray.length; iCount++)
1386               {
1387                  if (iCount > 0)
1388                  {
1389                     buffer.append(",");
1390                  }
1391                  strTest = convertValueToSQL(helpArray[iCount], condition.getValueType());
1392                  buffer.append(strTest);
1393               }
1394            }
1395            else if (objValue instanceof Collection )
1396            {
1397               Collection helpCol = (Collection )objValue;
1398               boolean bFirst = true;
1399
1400               if (GlobalConstants.ERROR_CHECKING)
1401               {
1402                  assert helpCol.size() > 0 : "IN operator has NO values in collection";
1403               }
1404               
1405               for (Iterator items = helpCol.iterator(); items.hasNext();)
1406               {
1407                  if (!bFirst)
1408                  {
1409                     buffer.append(",");
1410                  }
1411                  strTest = convertValueToSQL(items.next(), condition.getValueType());
1412                  buffer.append(strTest);
1413                  bFirst = false;
1414               }
1415            }
1416            else
1417            {
1418               buffer.append(objValue.toString());
1419            }
1420            buffer.append(")");
1421            strValue = buffer.toString();
1422         }
1423         else if (iOperation == DataCondition.OPERAND_LIKE_CASEUNSENSITIVE
1424                  || iOperation == DataCondition.OPERAND_NOT_LIKE_CASEUNSENSITIVE)
1425         {
1426            StringBuffer buffer = new StringBuffer ();
1427            buffer.append("'%");
1428            buffer.append(((String ) objValue).toUpperCase());
1429            buffer.append("%'");
1430            strValue = buffer.toString();
1431         }
1432         else if (iOperation == DataCondition.OPERAND_LIKE_CASESENSITIVE
1433                  || iOperation == DataCondition.OPERAND_NOT_LIKE_CASESENSITIVE)
1434         {
1435            StringBuffer buffer = new StringBuffer ();
1436            buffer.append("'%");
1437            buffer.append((String ) objValue);
1438            buffer.append("%'");
1439            strValue = buffer.toString();
1440         }
1441         else if (iOperation == DataCondition.OPERAND_EQUALS_CASEUNSENSITIVE
1442                  || iOperation == DataCondition.OPERAND_NOT_EQUALS_CASEUNSENSITIVE)
1443         {
1444            StringBuffer buffer = new StringBuffer ();
1445            buffer.append("'");
1446            buffer.append(((String ) objValue).toUpperCase());
1447            buffer.append("'");
1448            strValue = buffer.toString();
1449         }
1450         else if (iOperation == DataCondition.OPERAND_EQUALS_CASESENSITIVE
1451                  || iOperation == DataCondition.OPERAND_NOT_EQUALS_CASESENSITIVE)
1452         {
1453            StringBuffer buffer = new StringBuffer ();
1454            buffer.append("'");
1455            buffer.append((String ) objValue);
1456            buffer.append("'");
1457            strValue = buffer.toString();
1458         }
1459         else if (iOperation == DataCondition.OPERAND_STARTS_CASESENSITIVE
1460                  || iOperation == DataCondition.OPERAND_NOT_STARTS_CASESENSITIVE)
1461         {
1462            StringBuffer buffer = new StringBuffer ();
1463            buffer.append("'");
1464            buffer.append((String ) objValue);
1465            buffer.append("%'");
1466            strValue = buffer.toString();
1467         }
1468         else if (iOperation == DataCondition.OPERAND_STARTS_CASEUNSENSITIVE
1469                  || iOperation == DataCondition.OPERAND_NOT_STARTS_CASEUNSENSITIVE)
1470         {
1471            StringBuffer buffer = new StringBuffer ();
1472            buffer.append("'");
1473            buffer.append(((String ) objValue).toUpperCase());
1474            buffer.append("%'");
1475            strValue = buffer.toString();
1476         }
1477         else if (iOperation == DataCondition.OPERAND_ENDS_CASESENSITIVE
1478                  || iOperation == DataCondition.OPERAND_NOT_ENDS_CASESENSITIVE)
1479         {
1480            StringBuffer buffer = new StringBuffer ();
1481            buffer.append("'%");
1482            buffer.append((String ) objValue);
1483            buffer.append("'");
1484            strValue = buffer.toString();
1485         }
1486         else if (iOperation == DataCondition.OPERAND_ENDS_CASEUNSENSITIVE
1487                  || iOperation == DataCondition.OPERAND_NOT_ENDS_CASEUNSENSITIVE)
1488         {
1489            StringBuffer buffer = new StringBuffer ();
1490            buffer.append("'%");
1491            buffer.append(((String ) objValue).toUpperCase());
1492            buffer.append("'");
1493            strValue = buffer.toString();
1494         }
1495         else if (iOperation == DataCondition.OPERAND_SQL_QUERY)
1496         {
1497            strValue = (String ) objValue;
1498         }
1499         else
1500         {
1501            // this should be used for self list creation for IN or NOT_IN operands
1502 strValue = convertValueToSQL(objValue, condition.getValueType());
1503         }
1504      }
1505      return strValue;
1506   }
1507
1508   /**
1509    * Method set up begin position, end position and actual list size based on
1510    * the total number of retrieved items.
1511    *
1512    * @param iCount - number of items
1513    * @param options - options to be set up
1514    * @return ListOptions - changed options
1515    */
1516   protected ListOptions setOptionPositioning(
1517      int iCount,
1518      ListOptions options
1519   )
1520   {
1521      if (iCount == 0)
1522      {
1523         options.setBeginPosition(0);
1524         options.setEndPosition(0);
1525         options.setActualPage(0);
1526      }
1527      else
1528      {
1529         if ((options.getBeginPosition() > iCount)
1530            && (options.getPageSize() != ListOptions.PAGE_SIZE_ALL))
1531         {
1532            options.setBeginPosition(
1533               (((iCount - 1) / options.getPageSize())
1534               * options.getPageSize()) + 1
1535            );
1536         }
1537         if (options.getPageSize() >= iCount)
1538         {
1539            options.setBeginPosition(1);
1540         }
1541         
1542         if ((options.getBeginPosition() + options.getPageSize() - 1)
1543             > iCount)
1544         {
1545            options.setEndPosition(iCount);
1546         }
1547         else
1548         {
1549            options.setEndPosition(options.getBeginPosition()
1550                                       + options.getPageSize() - 1);
1551         }
1552           
1553         if (options.getPageSize() == ListOptions.PAGE_SIZE_ALL)
1554         {
1555            options.setEndPosition(iCount);
1556         }
1557         else
1558         {
1559            options.setActualPage(
1560               ((options.getBeginPosition() + options.getPageSize() - 2)
1561               /
1562               options.getPageSize()) + 1
1563            );
1564         }
1565      }
1566      
1567      options.setActualListSize(iCount);
1568      
1569      return options;
1570   }
1571
1572   /**
1573    * Method for executing query for getting number of all items retrieved using
1574    * count(x).
1575    *
1576    * @param cntConnection - db connection
1577    * @param query - original query to be executed to retrieve items
1578    * @param schema - list database schema implementation capable of providing
1579    * more info about attributes included in the query
1580    * @param iDomainId - ID of the domain
1581    * @return int - total number of specified records retrieved from count(x)
1582    * @throws OSSException - an error has occured
1583    * @throws SQLException - error while executing query
1584    */
1585   protected int getSelectCount(
1586      Connection cntConnection,
1587      String query,
1588      ListDatabaseSchema schema,
1589      int iDomainId
1590   ) throws OSSException,
1591            SQLException
1592   {
1593      PreparedStatement pstmQuery = null;
1594      ResultSet rsQueryResults = null;
1595
1596      int iOutputCount = 0;
1597
1598      // We should use count(x) to determine total number of items
1599 StringBuffer buffer = new StringBuffer ();
1600      
1601      // We need to search as case sensitive. But carefull, we cannot change the case
1602 // of the original query since some databases are case sensitive when
1603 // using columns names, etc. So we will convert original query to lower case and
1604 // we will provide search on this new query.
1605 String strQueryLowerCase = query.toLowerCase();
1606
1607      int iOrderBy;
1608
1609      try
1610      {
1611         // parse query sent to this method and construct query for selecting data count
1612 // This will take the where condition and replace the columns with count
1613 iOrderBy = strQueryLowerCase.indexOf("order by");
1614         buffer.append("select ");
1615         buffer.append(DatabaseImpl.getInstance().getSQLCountFunctionCall());
1616         if (iOrderBy != -1)
1617         {
1618            buffer.append(query.substring(strQueryLowerCase.indexOf(" from"), iOrderBy));
1619         }
1620         else
1621         {
1622            buffer.append(query.substring(strQueryLowerCase.indexOf(" from")));
1623         }
1624         
1625         // first select total number of items
1626 // s_logger.info("Preparing query " + buffer.toString());
1627 // timer.reset();
1628 pstmQuery = cntConnection.prepareStatement(buffer.toString());
1629         if (schema.isInDomain())
1630         {
1631            pstmQuery.setInt(1, iDomainId);
1632         }
1633         // s_logger.info("Duration for prepare count statement = " + timer.toString());
1634
1635         // timer.reset();
1636 rsQueryResults = pstmQuery.executeQuery();
1637         // s_logger.info("Duration for executing COUNT query = " + timer.toString());
1638
1639         if (rsQueryResults.next())
1640         {
1641            iOutputCount = rsQueryResults.getInt(1);
1642         }
1643      }
1644      finally
1645      {
1646         DatabaseUtils.closeResultSetAndStatement(rsQueryResults, pstmQuery);
1647      }
1648
1649      return iOutputCount;
1650   }
1651
1652   /**
1653    * Method for allocating of the object list
1654    *
1655    * @param options - list options
1656    * @param iCount - number of all items
1657    * @return List - allocated object list, this will be null if options
1658    * were not returned of if there are no items
1659    * @throws OSSException - error occured during allocating object list
1660    */
1661   protected List allocateObjectList(
1662      ListOptions options,
1663      int iCount
1664   ) throws OSSException
1665   {
1666      List lstObjects = null;
1667      int iSelectedIDs = 0;
1668      
1669      if ((options.getIsKeepSelectedChecked())
1670         && (options.getSelectedItemIDs().length() > 0))
1671      {
1672         // Get number of actually selected items that will be used for
1673 // increasing of the allocated array. This will be provided in
1674 // case when 'Keep selected' checkbox is checked and there are
1675 // some items selected.
1676 // At this point we don't know how many items will be really
1677 // retrieved from previous page (selected items). We will use
1678 // pesimistic view and we allocate array for all selected items
1679 // thet were sent from client side.
1680 iSelectedIDs = StringUtils.parseStringToIntArray(
1681                           options.getSelectedItemIDs(), ",").length;
1682      }
1683
1684      if ((options != null) && (iCount > 0))
1685      {
1686         if (options.getPageSize() != ListOptions.PAGE_SIZE_ALL)
1687         {
1688            lstObjects = new ArrayList (options.getPageSize() + iSelectedIDs);
1689         }
1690         else
1691         {
1692            lstObjects = new ArrayList (iCount);
1693         }
1694      }
1695      
1696      return lstObjects;
1697   }
1698
1699   /**
1700    * Method for getting number of total records in result set using getRow() method.
1701    * There can be owerwritten this method by DB specific parts.
1702    *
1703    * @param rsQueryResults - result set
1704    * @param options - list options
1705    * @return int - number of records in resultset
1706    * @throws OSSException - an error has occured
1707    * @throws SQLException - error while executing query
1708    */
1709   protected int getTotalRecords(
1710      ResultSet rsQueryResults,
1711      ListOptions options
1712   ) throws OSSException,
1713            SQLException
1714   {
1715      int iOutputCount = 0;
1716
1717      // We should use last() to determine total number of items
1718 // timer.reset();
1719 // Try to eficiently allocate the memory by first figuring out
1720 // how many data objects we have
1721 if (rsQueryResults.last())
1722      {
1723         // The last row number will tell us the total element count
1724 // (since it is 1 based)
1725 iOutputCount = rsQueryResults.getRow();
1726         // s_logger.info("Duration for last() = " + timer.toString());
1727 }
1728      else
1729      {
1730         // s_logger.info("Duration for last() = " + timer.toString());
1731 }
1732
1733      return iOutputCount;
1734   }
1735}
1736

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags