KickJava   Java API By Example, From Geeks To Geeks.

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


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: DB2ListDatabaseUtils.java,v 1.8 2007/01/07 06:14: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.persist.db.DatabaseImpl;
26 import org.opensubsystems.core.util.GlobalConstants;
27 import org.opensubsystems.patterns.listdata.data.ListOptions;
28
29 /**
30  * This class is used for common list retrieval and manipulation routines
31  * specific for IBM DB2.
32  *
33  * @version $Id: DB2ListDatabaseUtils.java,v 1.8 2007/01/07 06:14:23 bastafidli Exp $
34  * @author Julo Legeny
35  * @code.reviewer Miro Halas
36  * @code.reviewed 1.5 2004/12/20 20:37:34 jlegeny
37  */
38 public class DB2ListDatabaseUtils extends ListDatabaseUtils
39 {
40    // Constructors /////////////////////////////////////////////////////////////
41
42    /**
43     * Constructor
44     */
45    public DB2ListDatabaseUtils()
46    {
47       super();
48    }
49
50    // Helper methods ///////////////////////////////////////////////////////////
51
52    /**
53     * {@inheritDoc}
54     */
55    protected String JavaDoc preprocessSelectQuery(
56       String JavaDoc inputQuery,
57       ListOptions options,
58       ListDatabaseSchema schema
59    ) throws OSSException
60    {
61       if (DatabaseImpl.getInstance().hasSelectListRangeSupport())
62       {
63          // For IBM DB2 we will be use ROWNUMBER() function in the inner query
64 // to limit number of retrieved rows using the
65 // outside query. Final query will be have following structure:
66 // SELECT * FROM
67 // (SELECT <column_1>, <column_2>, rownumber() OVER
68 // (ORDER BY <column_1>) AS rn FROM <table>
69 // ) AS tr WHERE rn BETWEEN <start_index> AND <end_index>
70 // rownum RN must be added as the last column to the selected columns
71
72          StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
73          StringBuffer JavaDoc bufferOrderedColumns = new StringBuffer JavaDoc();
74
75          // We need to search as case sensitive. But carefull, we cannot change
76 // the case of the original query since some databases are case sensitive
77 // when using columns names, etc. So we will convert original query to
78 // lower case and we will provide search on this new query.
79 String JavaDoc strQueryLowerCase = inputQuery.toLowerCase();
80
81          boolean isDesc = false;
82
83          int iStartFromPosition = strQueryLowerCase.indexOf(" from ");
84
85          // Find out if there is used ORDER BY clause and if yes, store all
86 // ordered columns in the special string buffer
87 int iSortColumnStart = strQueryLowerCase.indexOf(" order by ");
88          int iSortColumnEnd = strQueryLowerCase.indexOf(" asc");
89          if (iSortColumnStart != -1)
90          {
91             // set end position for ORDER BY clause
92 if (iSortColumnEnd == -1)
93             {
94                iSortColumnEnd = strQueryLowerCase.indexOf(" desc");
95                isDesc = true;
96             }
97    
98             if (GlobalConstants.ERROR_CHECKING)
99             {
100                assert iSortColumnEnd != -1
101                       : "There should be set up value for ASC/DESC position.";
102             }
103
104             // Store all ordered columns to the special string buffer.
105 bufferOrderedColumns.append(
106                inputQuery.substring(iSortColumnStart + " order by ".length(),
107                                     iSortColumnEnd));
108          }
109
110          // First add start construction with outer SELECT and ROWNUM specification
111 buffer.append("select * from (");
112          
113          // Add inner query string until FROM clause
114 buffer.append(inputQuery.substring(0, iStartFromPosition));
115          
116          // Add rownum RN
117 buffer.append(", rownumber() over (order by ");
118          buffer.append(bufferOrderedColumns);
119          if (isDesc)
120          {
121             buffer.append(" desc");
122          }
123          buffer.append(") as RN ");
124
125          // Add inner query string from FROM clause
126 buffer.append(inputQuery.substring(iStartFromPosition));
127
128          // Finally add end construction for outer SELECT and ROWNUMBER() specification
129 buffer.append(") as TR where RN between ");
130          buffer.append(options.getBeginPosition());
131          buffer.append(" and ");
132          buffer.append(options.getEndPosition());
133
134          return buffer.toString();
135       }
136       else
137       {
138          // In case the ROWNUMBER is not supported or we do not want to use it,
139 // just call super
140 return super.preprocessSelectQuery(inputQuery, options, schema);
141       }
142    }
143 }
144
Popular Tags