MSSQLListDatabaseUtils


1   /*
2    * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3    * 
4    * Project: OpenSubsystems
5    * 
6    * $Id: MSSQLListDatabaseUtils.java,v 1.16 2007/01/07 06:14:22 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.util.List  ;
25  
26  import org.opensubsystems.core.error.OSSException;
27  import org.opensubsystems.core.persist.db.DatabaseImpl;
28  import org.opensubsystems.patterns.listdata.data.ListOptions;
29  
30  /**
31   * This class is used for common list retrieval and manipulation routines 
32   * specific for MS SQL Server 2000 and 2005. 
33   * 
34   * @version $Id: MSSQLListDatabaseUtils.java,v 1.16 2007/01/07 06:14:22 bastafidli Exp $ 
35   * @author Julo Legeny
36   * @code.reviewer Miro Halas
37   * @code.reviewed 1.6 2004/12/18 06:18:34 bastafidli
38   */
39  public class MSSQLListDatabaseUtils extends ListDatabaseUtils
40  {
41     // Constructors /////////////////////////////////////////////////////////////
42  
43     /**
44      * Constructor
45      */
46     public MSSQLListDatabaseUtils()
47     {
48        super();
49     }
50     
51     // Public methods ///////////////////////////////////////////////////////////
52     
53     /**
54      * {@inheritDoc}
55      */
56     public List   getObjectList(
57        ListOptions         options,
58        String                query,
59        ListDatabaseFactory factory,
60        ListDatabaseSchema  schema
61     ) throws OSSException
62     {
63        // We call here super because all possible implementation
64        // bellow have worse performance
65        return super.getObjectList(options, query, factory, schema);
66  
67        /*
68        // ===============================================================================
69        // 1. This implementation belongs to the 2nd implementation of the method
70        //    preprocessSelectQuery 
71        // =============================================================================== 
72  
73        List              lstObjects      = null;
74        Connection        cntConnection   = null;
75        ResultSet         rsQueryResults  = null;
76        PreparedStatement pstmQuery       = null;
77        MyTimer           timer           = new MyTimer();
78        CallableStatement selectStatement = null;
79        try
80        {
81           // Try to load information from the database
82           cntConnection = DatabaseConnectionFactoryImpl.getInstance().requestConnection();
83        
84           int iCount = 0;
85  
86           if ((!DatabaseImpl.getInstance().hasAbsolutePositioningSupport())
87               || (DatabaseImpl.getInstance().preferCountToLast()))
88           {
89              // --------------------------------------------------------------
90              // 1. If the count() is preferred to last() to determine how
91              // many results are there available or if the last() is not supported
92              // then execute count separately
93              // --------------------------------------------------------------
94              
95              // get number of all items retrieved by count(*)
96              timer.reset();
97              iCount = getSelectCount(cntConnection, query);
98              s_logger.info("Duration for executing count statement for page " + 
99                            options.getActualPage() + " (at position " + 
100                           options.getBeginPosition() + ") = " + timer.toString());
101    
102             // set option positioning, at this position we know the real number of all records
103             setOptionPositioning(iCount, options);
104             
105             // allocate object list
106             lstObjects = allocateObjectList(options, iCount);
107    
108             // default implementation doesn't do anything
109             // if there is supported LIMIT, process changes on original query and use them
110             query = preprocessSelectQuery(query, options, schema);
111 
112             selectStatement = cntConnection.prepareCall("EXEC get_list ?");
113             selectStatement.setString(1, query);
114             timer.reset();
115             rsQueryResults = selectStatement.executeQuery();;
116             s_logger.info("Duration for executing final query = " + timer.toString());
117 
118             if (lstObjects != null)
119             {
120                // If query with list range support will be used then the result
121                // is the first record in the result set
122                // timer.reset();
123                if (DatabaseImpl.getInstance().hasSelectListRangeSupport())
124                {
125                   rsQueryResults.next();
126                }
127                else
128                {
129                   rsQueryResults.absolute(options.getBeginPosition());                  
130                }
131                // s_logger.info("Duration for absolute/first() for load data = " 
132                //               + timer.toString());
133             }
134          }
135          else
136          {      
137             // --------------------------------------------------------------
138             // 2. If the last() is preferred to count() so determine how many
139             // items are available by executing the query and checking how 
140             // many items it returned
141             // --------------------------------------------------------------
142    
143             // s_logger.info("Preparing query " + query);
144             // timer.reset();
145             pstmQuery = cntConnection.prepareStatement(
146                query,
147                DatabaseImpl.getInstance().getSelectListResultSetType(), 
148                DatabaseImpl.getInstance().getSelectListResultSetConcurrency()
149             );
150             if (schema.isInDomain())
151             {
152                pstmQuery.setInt(1, options.getDomainId());
153             }
154             // s_logger.info("Duration for prepare statement = " + timer.toString());
155 
156 
157             // timer.reset();
158             //rsQueryResults = pstmQuery.executeQuery();
159             // s_logger.info("Duration for executing query = " + timer.toString());
160    
161             // We should use last() to determine total number of items
162             // timer.reset();
163             iCount = getTotalRecords(rsQueryResults, options);
164             // s_logger.info("Duration for last() = " + timer.toString());
165 
166             // set option positioning, at this position we know the real number of all records
167             setOptionPositioning(iCount, options);
168             
169             // allocate object list
170             lstObjects = allocateObjectList(options, iCount);
171 
172             if (lstObjects != null)
173             {
174                // timer.reset();
175                rsQueryResults.absolute(options.getBeginPosition());                  
176                // s_logger.info("Duration for absolute() for load data = " + timer.toString());
177             }
178          }
179 
180          // =====================================================================
181          
182          // At this point we have already constructed and executed query, 
183          // allocated object list and set up options. Now load particular 
184          // data from the result set.
185          if (lstObjects != null)
186          {                     
187             if (GlobalConstants.ERROR_CHECKING)
188             {        
189                assert iCount > 0 
190                       : "List should be null for empty result";
191             }
192             
193             int   iHelpCounter = 0; 
194             // Cache this so we don't have to call it always inside of loop
195             int[] columnCodes = options.getRetrieveColumnCodes();
196 
197             // not to call rsQueryResults.next() for first loop
198             // timer.reset();
199             while ((iHelpCounter == 0 || rsQueryResults.next())
200                && ((iHelpCounter < options.getPageSize()) 
201                || (options.getPageSize() == ListOptions.PAGE_SIZE_ALL)))
202             {
203                iHelpCounter++;
204                lstObjects.add(
205                factory.load(rsQueryResults, columnCodes, 1));
206             };
207             // s_logger.info("Duration for load data = " + timer.toString());
208          }
209 
210          // =====================================================================
211 
212          // Call method that check if can be processed list keepeng selected and
213          // if yes, do it.
214          lstObjects = getObjectListKeepSelected (factory, cntConnection, query, 
215                                                  options, lstObjects, schema);
216       }
217       catch (SQLException sqleExc)
218       {
219          throw new OSSDatabaseAccessException(
220                       "Failed to retrieve specified list of data objects" +
221                       " from the database.", sqleExc);
222       }
223       finally
224       {
225          DatabaseUtils.closeStatement(selectStatement);
226          DatabaseUtils.closeResultSetAndStatement(rsQueryResults, pstmQuery);
227          DatabaseConnectionFactoryImpl.getInstance().returnConnection(cntConnection);
228       }
229    
230       return lstObjects; 
231       */
232       
233       /*
234       // ===============================================================================
235       // 1. This implementation belongs to the 3rd implementation of the method
236       //    preprocessSelectQuery 
237       // =============================================================================== 
238 
239       List              lstObjects      = null;
240       Connection        cntConnection   = null;
241       ResultSet         rsQueryResults  = null;
242       PreparedStatement pstmQuery       = null;
243       MyTimer           timer           = new MyTimer();
244       try
245       {
246          // Try to load information from the database
247          cntConnection = DatabaseConnectionFactoryImpl.getInstance().requestConnection();
248       
249          int iCount = 0;
250 
251          if ((!DatabaseImpl.getInstance().hasAbsolutePositioningSupport())
252              || (DatabaseImpl.getInstance().preferCountToLast()))
253          {
254             // --------------------------------------------------------------
255             // 1. If the count() is preferred to last() to determine how
256             // many results are there available or if the last() is not supported
257             // then execute count separately
258             // --------------------------------------------------------------
259             
260             // get number of all items retrieved by count(*)
261             timer.reset();
262             iCount = getSelectCount(cntConnection, query, schema);
263             s_logger.info("Duration for executing count statement for page " + 
264                           options.getActualPage() + " (at position " + 
265                           options.getBeginPosition() + ") = " + timer.toString());
266    
267             // set option positioning, at this position we know the real number of all records
268             setOptionPositioning(iCount, options);
269             
270             // allocate object list
271             lstObjects = allocateObjectList(options, iCount);
272    
273             // default implementation doesn't do anything
274             // if there is supported LIMIT, process changes on original query and use them
275             query = preprocessSelectQuery(query, options, schema);
276 
277             pstmQuery = cntConnection.prepareStatement(
278                query,
279                DatabaseImpl.getInstance().getSelectListResultSetType(), 
280                DatabaseImpl.getInstance().getSelectListResultSetConcurrency()
281             );
282             if (schema.isInDomain())
283             {
284                pstmQuery.setInt(1, options.getDomainId());
285             }
286             timer.reset();
287             rsQueryResults = pstmQuery.executeQuery();;
288             s_logger.info("Duration for executing final query = " + timer.toString());
289 
290             if (lstObjects != null)
291             {
292                // If query with list range support will be used then the result
293                // is the first record in the result set
294                // timer.reset();
295                rsQueryResults.absolute(options.getBeginPosition());                  
296                // s_logger.info("Duration for absolute/first() for load data = " 
297                //               + timer.toString());
298             }
299          }
300          else
301          {      
302             // --------------------------------------------------------------
303             // 2. If the last() is preferred to count() so determine how many
304             // items are available by executing the query and checking how 
305             // many items it returned
306             // --------------------------------------------------------------
307    
308             // s_logger.info("Preparing query " + query);
309             // timer.reset();
310             pstmQuery = cntConnection.prepareStatement(
311                query,
312                DatabaseImpl.getInstance().getSelectListResultSetType(), 
313                DatabaseImpl.getInstance().getSelectListResultSetConcurrency()
314             );
315             if (schema.isInDomain())
316             {
317                pstmQuery.setInt(1, options.getDomainId());
318             }
319             // s_logger.info("Duration for prepare statement = " + timer.toString());
320 
321             // timer.reset();
322             //rsQueryResults = pstmQuery.executeQuery();
323             // s_logger.info("Duration for executing query = " + timer.toString());
324    
325             // We should use last() to determine total number of items
326             // timer.reset();
327             iCount = getTotalRecords(rsQueryResults, options);
328             // s_logger.info("Duration for last() = " + timer.toString());
329 
330             // set option positioning, at this position we know the real number of all records
331             setOptionPositioning(iCount, options);
332             
333             // allocate object list
334             lstObjects = allocateObjectList(options, iCount);
335 
336             if (lstObjects != null)
337             {
338                // timer.reset();
339                rsQueryResults.absolute(options.getBeginPosition());                  
340                // s_logger.info("Duration for absolute() for load data = " + timer.toString());
341             }
342          }
343 
344          // =====================================================================
345          
346          // At this point we have already constructed and executed query, 
347          // allocated object list and set up options. Now load particular 
348          // data from the result set.
349          if (lstObjects != null)
350          {                     
351             if (GlobalConstants.ERROR_CHECKING)
352             {        
353                assert iCount > 0 
354                       : "List should be null for empty result";
355             }
356             
357             int   iHelpCounter = 0; 
358             // Cache this so we don't have to call it always inside of loop
359             int[] columnCodes = options.getRetrieveColumnCodes();
360 
361             // not to call rsQueryResults.next() for first loop
362             // timer.reset();
363             while ((iHelpCounter == 0 || rsQueryResults.next())
364                && ((iHelpCounter < options.getPageSize()) 
365                || (options.getPageSize() == ListOptions.PAGE_SIZE_ALL)))
366             {
367                iHelpCounter++;
368                lstObjects.add(
369                factory.load(rsQueryResults, columnCodes, 1));
370             };
371             // s_logger.info("Duration for load data = " + timer.toString());
372          }
373 
374          // =====================================================================
375 
376          // Call method that check if can be processed list keepeng selected and
377          // if yes, do it.
378          lstObjects = getObjectListKeepSelected (factory, cntConnection, query,
379                                                  options, lstObjects, schema);
380       }
381       catch (SQLException sqleExc)
382       {
383          throw new OSSDatabaseAccessException(
384                       "Failed to retrieve specified list of data objects" +
385                       " from the database.", sqleExc);
386       }
387       finally
388       {
389          DatabaseUtils.closeResultSetAndStatement(rsQueryResults, pstmQuery);
390          DatabaseConnectionFactoryImpl.getInstance().returnConnection(cntConnection);
391       }
392    
393       return lstObjects; 
394       */
395    }
396 
397    /**
398     * {@inheritDoc}
399     */
400    protected String   preprocessSelectQuery(
401       String               inputQuery,
402       ListOptions        options,
403       ListDatabaseSchema schema
404    ) throws OSSException
405    {
406      // if there is not supported ROWNUM, process changes on original query and 
407       // use them
408      if (DatabaseImpl.getInstance().hasSelectListRangeSupport())
409      {
410          // Here we call super because all implementation bellow
411          // have worse performance than select + last() + absolute()
412          return super.preprocessSelectQuery(inputQuery, options, schema);
413 
414          /*
415          // This code is commented because it the performance is worse that using 
416          // select + last() + absolute()
417    
418          // ====================================================================
419          // 1. Following constructed SQL command has worse performance that using 
420          // original - not preprocessed query.
421          // ==================================================================== 
422  
423          // For MS SQL Server 2000 there will be used TOP clause and more subqueries
424          // Final query will be have following structure:
425          // SELECT * FROM  
426          //    (SELECT TOP <page_size> * FROM 
427          //        (SELECT TOP <end_index> * FROM <table> WHERE <column_1> NOT IN(<value>) 
428          //            ORDER BY <column_1> ASC/DESC
429          //         ) AS YYY ORDER BY <column_1> DESC/ASC - this order must be opposite to others
430          //     ) AS ZZZ ORDER BY  <column_1> ASC/DESC
431 
432          StringBuffer buffer               = new StringBuffer();
433          StringBuffer bufferOrderedColumns = new StringBuffer();
434          StringBuffer bufferHelp           = new StringBuffer();
435          
436          boolean isUsedAsc     = true;
437          boolean isUsedOrderBy = false;
438          
439          // We need to search as case sensitive. But carefull, we cannot change the case
440          // of the original query since some databases are case sensitive when
441          // using columns names, etc. So we will convert original query to lower case and
442          // we will provide search on this new query.
443          String strQueryLowerCase = inputQuery.toLowerCase();
444 
445          // first find out if there is used ORDER BY clause and if yes, 
446          // remember start index and end index
447          int iSortColumnStart  = strQueryLowerCase.indexOf(" order by ") + " order by ".length();
448          int iSortColumnEnd    = strQueryLowerCase.indexOf(" asc");
449          
450          // get position after SELECT clause
451          int iStartAfterSelect = strQueryLowerCase.indexOf("select ") + "select ".length();
452 
453          if (iSortColumnStart != -1)
454          {
455             isUsedOrderBy = true;
456             // set end position for ORDER BY clause
457             if (iSortColumnEnd == -1)
458             {
459                iSortColumnEnd = strQueryLowerCase.indexOf(" desc");
460                isUsedAsc = false;
461             }
462    
463             if (GlobalConstants.ERROR_CHECKING)
464             {
465                assert iSortColumnEnd != -1
466                       : "There should be set up value for ASC/DESC position."; 
467             }
468 
469             // Store all ordered columns to the special string buffer. There shouldn't be
470             // columns defined as TABLE_NAME.COLUMN_NAME but just as COLUMN_NAME because
471             // outer select doesn't known the table specification in it's ORDER BY clause.
472             bufferOrderedColumns.append(inputQuery.substring(iSortColumnStart, iSortColumnEnd));
473          }
474 
475          // At this point we have all ordered columns in the special string. Now we will
476          // parse all particular columns and if there is specified also table name, it will
477          // be removed.
478          int iIndex1Start    = 0;
479          int iIndex2Start    = 0;
480          int iActualPosition = 0;
481          while (bufferOrderedColumns.indexOf(".") != -1)
482          {
483             bufferHelp.delete(0, bufferHelp.length());
484 
485             // find out if there are more columns that have to be ordered (separated by ',')
486             iIndex1Start = bufferOrderedColumns.indexOf(",", iActualPosition);
487             if (iIndex1Start != -1)
488             {
489                // there were found more columns that will be ordered
490                bufferHelp.append(bufferOrderedColumns.substring(iActualPosition, iIndex1Start));
491             }
492             else
493             {
494                // there were not found more columns that will be ordered
495                bufferHelp.append(bufferOrderedColumns);
496             }
497 
498             // find out if there is table name specified for column (separated by '.')
499             iIndex2Start = bufferHelp.indexOf(".");
500             if (iIndex2Start != -1)
501             {
502                // there was found also table name for specified column - remove it
503                bufferHelp.delete(0, iIndex2Start + 1);
504             }
505 
506             // At this point we know particular column without table name specification.
507             // We change bufferOrderedColumns now.
508             if (iIndex1Start == -1)
509             {
510                iIndex1Start = bufferOrderedColumns.length();
511             }
512             bufferOrderedColumns.replace(iActualPosition, iIndex1Start, bufferHelp.toString());
513 
514             // set actual position of the processed string
515             iActualPosition = bufferOrderedColumns.indexOf(",", iActualPosition) + 1;
516          }
517 
518          // First add start construction that will be added at the begin of sql command
519          buffer.append("select * from (select top ");
520          buffer.append(options.getPageSize());
521          buffer.append(" * from (select top ");
522          buffer.append(options.getEndPosition());
523          buffer.append(" ");
524 
525          // Add inner query without first SELECT clause.         
526          buffer.append(inputQuery.substring(iStartAfterSelect));
527 
528          // Finally add end construction for sql sommand.
529          buffer.append(") as SUBSELECT_1 ");
530          if (isUsedOrderBy)
531          {
532             buffer.append("order by ");
533             buffer.append(bufferOrderedColumns);
534             // this order must be opposite to others
535             buffer.append(isUsedAsc ? " desc " : " asc "); 
536          }
537          buffer.append(") as SUBSELECT_2 ");
538          if (isUsedOrderBy)
539          {
540             buffer.append("order by ");
541             buffer.append(bufferOrderedColumns);
542             // this order must be opposite to others
543             buffer.append(isUsedAsc ? " asc " : " desc "); 
544          }
545          return buffer.toString();
546          */
547 
548          /*
549          // *********************************************************************************
550         
551          // This code is also commented because it the performance is worse that using 
552          // select + last() + absolute()
553 
554          // ===============================================================================
555          // 2. Construct SQL command that will insert selected data into the temporary table
556          //    and final results will be retrieved from this temporary table. There is used
557          //    stored procedure that execute this.
558          // =============================================================================== 
559 
560          // For MS SQL Server 2000 there will be used stored procedure for retrieving particular 
561          // limited data into the list. There will be called query that will select particular 
562          // data and insert them into the temporary table. Final required data will be selected 
563          // from the temporary table by rownumber.
564 
565          // Stored procedure has following structure:
566          //    CREATE PROCEDURE get_list  @start_position INTEGER,
567          //                               @end_position INTEGER,
568          //                               @command VARCHAR
569          //    AS
570          //       EXEC (@command)
571          //       select * from #tempA where ROWNUM between @start_position and @end_position
572 
573          // Parameters @start_position/@end_position are EndPosition/StartPosition 
574          // from the options and defines retirieved rows limitation. Also @end_position
575          // defines number of records that will be inserted into the temporary table by using
576          // clause SELECT TOP @end_position.
577          // Parameter @command is the particular preprocessed query that will be executed by 
578          // stored procedure.
579          // Stored procedure call:  exec get_list ?, ?, ?
580 
581          // There have to be converted all IDENTITY columns, because while constructing temporary
582          // table, there is created IDENTITY column for 'rownum'. There is not allowed to have
583          // more identity columns within the one table. We are using IDENTITY for all ID columns.
584          // We just convert them into the INTEGER.
585          // Form of the final query, that will be constructed:
586          //    select top <end_position> <table>.<column1>, 
587          //                              <table>.<column2>,
588          //                              IDENTITY (int) as ROWNUM
589          //    into #tempA
590          //    where <column1> = <value> and ID not in(<value>) 
591          //    order by <table>.<column> asc/desc
592 
593          int iFromStart      = 0;
594          int iActualPosition = -1;
595          int iIDCount        = 1;
596 
597          StringBuffer buffer                 = new StringBuffer();
598          StringBuffer bufferRetrievedColumns = new StringBuffer();
599          StringBuffer strAllColumnNames      = new StringBuffer();
600 
601          // We need to search as case sensitive. But carefull, we cannot change the case
602          // of the original query since some databases are case sensitive when
603          // using columns names, etc. So we will convert original query to lower case and
604          // we will provide search on this new query.
605          String strQueryLowerCase = inputQuery.toLowerCase();
606 
607          iFromStart = strQueryLowerCase.indexOf(" from ");
608          String strColumnName = "";
609          String strTemp = "";
610          // Initialize string that will contain all retrieved columns.
611          strAllColumnNames.append(inputQuery.substring(
612                                      strQueryLowerCase.indexOf("select ") + "select ".length(), 
613                                      iFromStart));
614          
615          // If there will be retrieved column with name ID, we have to convert it because
616          // this is IDENTITY column and there is not allowed to have 2 identity columns within
617          // one table.
618          while (strAllColumnNames.indexOf(",") != -1)
619          {
620             // There are more columns that will be retrieved. Find out if there will be
621             // retrieved some column with name ID. If YES, convert it to INTEGER.
622             iActualPosition = strAllColumnNames.indexOf(",");
623             strTemp = strAllColumnNames.substring(0, iActualPosition);
624             // Column can be defined as TABLE.COLUMN_NAME, so we need to parse table name 
625             // and also column name separately.
626             if (strTemp.indexOf(".") != -1)
627             {
628                // there is used TABLE.COLUMN
629                strColumnName = strTemp.substring(strTemp.indexOf(".") + 1, strTemp.length());
630             }
631             else
632             {
633                // there is used only COLUMN name (without TABLE specification)
634                strColumnName = strTemp;
635             }
636 
637             if (strColumnName.equalsIgnoreCase("ID"))
638             {
639                // If the column name = ID, add CONVERT clause to the SQL command
640                bufferRetrievedColumns.append("convert(INTEGER, ");
641                bufferRetrievedColumns.append(strTemp);
642                bufferRetrievedColumns.append(") as ID");
643                bufferRetrievedColumns.append(iIDCount++);
644             }
645             else
646             {
647                bufferRetrievedColumns.append(strTemp);
648             }
649             bufferRetrievedColumns.append(", ");
650             
651             // delete used columns from the all columns
652             strAllColumnNames.delete(0, iActualPosition + 1);
653          }
654 
655          // Column can be defined as TABLE.COLUMN_NAME, so we need to parse table name 
656          // and also column name separately.
657          if (strAllColumnNames.indexOf(".") != -1)
658          {
659             // there is used TABLE.COLUMN
660             strColumnName = strAllColumnNames.substring(strAllColumnNames.indexOf(".") + 1, 
661                                                         strAllColumnNames.length());
662          }
663          else
664          {
665             // there is used only COLUMN name (without TABLE specification)
666             strColumnName = strAllColumnNames.toString();
667          }
668 
669          if (strColumnName.equalsIgnoreCase("ID"))
670          {
671             // If the column name = ID, add CONVERT clause to the SQL command
672             bufferRetrievedColumns.append("convert(INTEGER, ");
673             bufferRetrievedColumns.append(strTemp);
674             bufferRetrievedColumns.append(") as ID");
675             bufferRetrievedColumns.append(iIDCount++);
676          }
677          else
678          {
679             bufferRetrievedColumns.append(strAllColumnNames);
680          }
681 
682          
683          buffer.append("select top ");
684          buffer.append(options.getEndPosition());
685          buffer.append(" ");
686          buffer.append(bufferRetrievedColumns);
687          buffer.append(", IDENTITY (int) as ROWNUM into #tempA ");
688          buffer.append(inputQuery.substring(iFromStart, inputQuery.length()));
689 
690          buffer.append(" select * from #tempA where ROWNUM between ");
691          buffer.append(options.getBeginPosition());
692          buffer.append(" and ");
693          buffer.append(options.getEndPosition());
694          buffer.append(" order by ROWNUM ");
695 
696          return buffer.toString();
697          */
698 
699          /*
700          // This code is also commented because it the performance is worse that using 
701          // select + last() + absolute()
702 
703          // =============================================================================
704          // 3. Used stored procedure and constructed siple select limited with ROWCOUNT.
705          //    Particular limited records will be retrieved by last() or absolute()
706          // =============================================================================
707          StringBuffer buffer = new StringBuffer();
708 
709          // We need to search as case sensitive. But carefull, we cannot change the case
710          // of the original query since some databases are case sensitive when
711          // using columns names, etc. So we will convert original query to lower case and
712          // we will provide search on this new query.
713          String strQueryLowerCase = inputQuery.toLowerCase();
714 
715          int iFromStart = strQueryLowerCase.indexOf(" select ") + "select ".length();
716 
717          buffer.append(inputQuery);
718          buffer.insert(iFromStart, " top " + options.getEndPosition() + " ");
719 
720          return buffer.toString();
721          */
722       }
723       else
724       {
725          // In case the ROWNUMBER is not supported or we do not want to use it, 
726          // just call super
727          return super.preprocessSelectQuery(inputQuery, options, schema);
728       }
729    }
730 }
731
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags