KickJava   Java API By Example, From Geeks To Geeks.

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


1 /*
2  * Copyright (c) 2005 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: ListQueryPreprocessor.java,v 1.6 2007/02/20 04:15:23 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 org.opensubsystems.core.error.OSSException;
25 import org.opensubsystems.core.util.GlobalConstants;
26 import org.opensubsystems.patterns.listdata.data.ListOptions;
27
28 /**
29  * Utility class to share code which helps in preprocessing list queries
30  *
31  * @version $Id: ListQueryPreprocessor.java,v 1.6 2007/02/20 04:15:23 bastafidli Exp $
32  * @author Miro Halas
33  * @code.reviewer Miro Halas
34  * @code.reviewed 1.3 2006/03/13 16:58:13 bastafidli
35  */
36 public final class ListQueryPreprocessor
37 {
38    // Constructors /////////////////////////////////////////////////////////////
39
40    /**
41     * Private constructor since this class cannot be instantiated
42     */
43    private ListQueryPreprocessor(
44    )
45    {
46       // Do nothing
47 }
48    
49    // Public methods ///////////////////////////////////////////////////////////
50
51    /**
52     * Preprocess the query to correctly handle situation when we request to sort
53     * the data using the creation date in case the database doesn't provide
54     * very good precision for dates. In this case the result set will be sorted
55     * by both, creation date and id of the object.
56     *
57     * @param inputQuery - query to preprocess
58     * @param options - list options specifig what to rieve
59     * @param schema - schema requesting this preprocessing
60     * @return String - corrected query
61     * @throws OSSException - an error has occured
62     */
63    // TODO: Test: Write test for this method so that queries returned from the
64 // databas return expected results especially because we have hardcoded
65 // CREATION_DATE column bellow
66 public static String JavaDoc preprocessSelectQueryForCreationDate(
67       String JavaDoc inputQuery,
68       ListOptions options,
69       ListDatabaseSchema schema
70    ) throws OSSException
71    {
72       // If there are data ordered by CREATION_DATE, we have to add next sorting
73 // key (ID - if it is not integrated already here in the order by clause).
74 // It is because HsqlDB timestamp has not much decimal numbers to be used
75 // for correct ordering if there are created lots of data within the very
76 // short time.
77
78       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
79       StringBuffer JavaDoc bufferTemp = new StringBuffer JavaDoc();
80       StringBuffer JavaDoc bufferTemp1 = new StringBuffer JavaDoc();
81
82       // We need to search as case sensitive. But carefull, we cannot change
83 // the case of the original query since some databases are case sensitive
84 // when using columns names, etc. So we will convert original query to
85 // lower case and we will provide search on this new query.
86 String JavaDoc strQueryLowerCase = inputQuery.toLowerCase();
87       String JavaDoc strTableName = "";
88       String JavaDoc strColumnName = "";
89       String JavaDoc strAllColumnNames = "";
90       String JavaDoc strTemp = "";
91       boolean isAsc = true;
92
93       // First find out if there is used ORDER BY clause and in that case if
94 // there will be used just one column for sorting
95 int iSortColumnStart = strQueryLowerCase.indexOf(" order by ");
96       int iSortColumnEnd = strQueryLowerCase.lastIndexOf(" asc") + 1;
97       int iSortColumnEndDesc = strQueryLowerCase.lastIndexOf(" desc") + 1;
98       int iInnerSortColumnEnd = 0;
99       int iActualPositionStart = 0;
100       int iActualPositionEnd = 0;
101
102       // Set end position for ORDER BY clause
103 if (iSortColumnEndDesc > iSortColumnEnd)
104       {
105          iSortColumnEnd = iSortColumnEndDesc;
106          isAsc = false;
107       }
108       
109       // check if there is used ORDER BY - ASC/DESC clause within the SQL command
110 if (iSortColumnStart != -1)
111       {
112          if (GlobalConstants.ERROR_CHECKING)
113          {
114             // We need to check against 0 and not -1 since we did + 1 above
115 assert iSortColumnEnd != 0
116                    : "There should be set up value for ASC/DESC position.";
117          }
118          
119          // At this point we know that there is used ORDER BY - ASC/DESC clause
120 // and we will parse substring from this clause. If there will be used
121 // only one column for ordering and this column is CREATION_DATE, we
122 // can add next sorting column ID.
123 strColumnName = inputQuery.substring(iSortColumnStart
124                                               + " order by ".length(),
125                                               iSortColumnEnd);
126
127          // Check if there is used CREATION_DATE sorting column within the ORDER
128 // BY clause
129 if (strColumnName.toUpperCase().indexOf("CREATION_DATE") != -1)
130          {
131             // find out if there is just one column used in the sort
132 // (there will be no ',' separator)
133 if (strColumnName.indexOf(",") == -1)
134             {
135                // At this point we know that there is used only one column.
136 // Column can be defined as TABLE.COLUMN_NAME, so we need to
137 // parse table name and also column name separately.
138 if (strColumnName.indexOf(".") != -1)
139                {
140                   strTableName = strColumnName.substring(
141                                     0, strColumnName.indexOf("."));
142
143                   buffer.append(inputQuery);
144                   buffer.append(", ");
145                   buffer.append(strTableName);
146                   buffer.append(".ID ");
147                   if (isAsc)
148                   {
149                      // If there is used ASC sorting for CREATION_DATE column,
150 // additional ID sorting column will be also ordered ASC.
151 buffer.append("asc");
152                   }
153                   else
154                   {
155                      // If there is used DESC sorting for CREATION_DATE column,
156 // additional ID sorting column will be also ordered DESC.
157 // MHALAS: We cannot order creation date asc and sorting
158 // column desc since if the dates are not equal the items
159 // would be sorted in ascending order which is not what we
160 // want
161 buffer.append("desc");
162                   }
163                }
164                else
165                {
166                   // If there is not specified column within the ORDER-ASC/DESC
167 // clause as TABLE.COLUMN_NAME but only as COLUMN_NAME, then
168 // we can add ID sorting column only if the original column is
169 // CREATION_DATE. If ordering is used ASC, ID column will use
170 // also ASC. If ordering is desc then ID column will use
171 // also DESC (see comments above for desc)
172
173                   buffer.append(inputQuery);
174                   buffer.append(", ID ");
175                   if (isAsc)
176                   {
177                      buffer.append("asc");
178                   }
179                   else
180                   {
181                      buffer.append("desc");
182                   }
183                }
184             }
185             else
186             {
187                // In this point we known there are more columns specified within
188 // the ORDER BY-ASC/DESC clause.
189 buffer.append(inputQuery.substring(
190                                 0, iSortColumnStart + " order by ".length()));
191                strAllColumnNames = inputQuery.substring(
192                                       iSortColumnStart + " order by ".length(),
193                                       inputQuery.length());
194                while (strAllColumnNames.indexOf(",", iActualPositionStart) != -1
195                       || iActualPositionStart < strAllColumnNames.length())
196                {
197                   // Proces through all columns untill CREATION_DATE column is
198 // found
199 iActualPositionEnd = strAllColumnNames.indexOf(
200                                           ",", iActualPositionStart);
201                   if (iActualPositionEnd == -1)
202                   {
203                      // There will be processed the last column in the ORDER BY
204 // clause
205 iActualPositionEnd = strAllColumnNames.length();
206                   }
207                   strTemp = strAllColumnNames.substring(iActualPositionStart,
208                                                         iActualPositionEnd);
209                   
210                   if (iActualPositionStart > 0)
211                   {
212                      buffer.append(",");
213                   }
214                   buffer.append(strTemp);
215                   // Column can be defined as TABLE.COLUMN_NAME, so we need to
216 // parse table name and also column name separately.
217 if (strTemp.indexOf(".") != -1)
218                   {
219                      // There is used TABLE.COLUMN
220 strTableName = strTemp.substring(0, strTemp.indexOf("."));
221                      // get position of the local ASC/DESC occurence
222 iInnerSortColumnEnd = strTemp.toLowerCase().lastIndexOf(
223                                                                     " asc");
224                      isAsc = true;
225                      if (iInnerSortColumnEnd == -1)
226                      {
227                         iInnerSortColumnEnd = strTemp.toLowerCase().lastIndexOf(
228                                                                        " desc");
229                         if (iInnerSortColumnEnd != -1)
230                         {
231                            isAsc = false;
232                         }
233                         else
234                         {
235                            // in this case there is not present local ASC/DESC
236 iInnerSortColumnEnd = strTemp.length();
237                         }
238                      }
239                      strColumnName = strTemp.substring(strTemp.indexOf(".") + 1,
240                                                        iInnerSortColumnEnd);
241                      // Check if the column name equals to CREATION_DATE column.
242 // If yes, search for occurence of the TABLE.ID column.
243 if (strColumnName.equalsIgnoreCase("CREATION_DATE"))
244                      {
245                         // Construct temp buffer with TABLE.ID and space after that
246 bufferTemp.delete(0, bufferTemp.length());
247                         bufferTemp.append(strTableName.toUpperCase());
248                         bufferTemp.append(".ID ");
249                         // Construct temp buffer with TABLE.ID and comma after that
250 bufferTemp1.delete(0, bufferTemp1.length());
251                         bufferTemp1.append(strTableName.toUpperCase());
252                         bufferTemp1.append(".ID,");
253                         if ((strAllColumnNames.toUpperCase().indexOf(
254                                bufferTemp.toString()) == -1)
255                             && (strAllColumnNames.toUpperCase().indexOf(
256                                   bufferTemp1.toString()) == -1))
257                         {
258                            // At this point we know there is not TABLE.ID
259 // occurence and we can add TABLE.ID after
260 // TABLE.CREATION_DATE column
261
262                            buffer.append(", ");
263                            buffer.append(strTableName);
264                            buffer.append(".ID ");
265                            if (isAsc)
266                            {
267                               buffer.append("asc");
268                            }
269                            else
270                            {
271                               buffer.append("desc");
272                            }
273                         }
274                      }
275                   }
276                   else
277                   {
278                      // There is used only COLUMN name (without TABLE
279 // specification) get position of the local ASC/DESC
280 // occurence
281 iInnerSortColumnEnd = strTemp.toLowerCase().lastIndexOf(
282                                                                     " asc");
283                      isAsc = true;
284                      if (iInnerSortColumnEnd == -1)
285                      {
286                         iInnerSortColumnEnd = strTemp.toLowerCase().lastIndexOf(
287                                                                        " desc");
288                         if (iInnerSortColumnEnd != -1)
289                         {
290                            isAsc = false;
291                         }
292                         else
293                         {
294                            // in this case there is not present local ASC/DESC
295 iInnerSortColumnEnd = strTemp.length();
296                         }
297                      }
298                      strColumnName = strTemp.substring(0, iInnerSortColumnEnd);
299                      // Check if the column name equals to CREATION_DATE column.
300 // If yes, search for occurence of the TABLE.ID column.
301 if (strColumnName.trim().equalsIgnoreCase("CREATION_DATE"))
302                      {
303                         if (strAllColumnNames.toUpperCase().indexOf(" ID ") == -1
304                             && strAllColumnNames.toUpperCase().indexOf(" ID,") == -1)
305                         {
306                            // At this point we know there is not ID occurence
307 // and we can add ID after CREATION_DATE column
308
309                            buffer.append(", ID ");
310                            if (isAsc)
311                            {
312                               buffer.append("asc");
313                            }
314                            else
315                            {
316                               buffer.append("desc");
317                            }
318                         }
319                      }
320                   }
321                   iActualPositionStart = iActualPositionEnd + 1;
322                }
323             }
324          }
325          else
326          {
327             buffer.append(inputQuery);
328          }
329       }
330
331       return buffer.toString();
332    }
333 }
334
Popular Tags