KickJava   Java API By Example, From Geeks To Geeks.

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


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenSubsystems
5  *
6  * $Id: OracleListDatabaseUtils.java,v 1.14 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.patterns.listdata.data.ListOptions;
27
28
29 /**
30  * This class is used for common list retrieval and manipulation routines
31  * specific for Oracle 9i.
32  *
33  * @version $Id: OracleListDatabaseUtils.java,v 1.14 2007/01/07 06:14:23 bastafidli Exp $
34  * @author Julo Legeny
35  * @code.reviewer Miro Halas
36  * @code.reviewed 1.11 2004/12/22 09:24:36 jlegeny
37  */
38 public class OracleListDatabaseUtils extends ListDatabaseUtils
39 {
40    // Constructors /////////////////////////////////////////////////////////////
41
42    /**
43     * Constructor
44     */
45    public OracleListDatabaseUtils()
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 there is not supported ROWNUM, process changes on original query and use them
62 if (DatabaseImpl.getInstance().hasSelectListRangeSupport())
63      {
64          // For Oracle 9i we will be use specific ROWNUM variable in the
65 // inner query is used to limit number of retrieved rows using the
66 // outside query. Final query will be have following structure:
67 // SELECT a1, a2 FROM
68 // (
69 // SELECT a1, a2, rownum RN FROM
70 // (
71 // SELECT <column_1> a1, <column_2> a2 FROM <table>
72 // WHERE <column_1> NOT IN(<value>) AND <column_3> = <value> ORDER BY <column_2>
73 // ) WHERE rownum < <end_index + 1>
74 // ) WHERE RN > <start_index + 1>
75 // rownum must be added as the last column to the selected columns
76
77          StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
78          StringBuffer JavaDoc bufferAllColumns = new StringBuffer JavaDoc();
79          StringBuffer JavaDoc bufferAllAliases = new StringBuffer JavaDoc();
80          StringBuffer JavaDoc bufferAlias = new StringBuffer JavaDoc();
81
82          // We need to search as case sensitive. But carefull, we cannot change the case
83 // of the original query since some databases are case sensitive when
84 // using columns names, etc. So we will convert original query to lower case and
85 // we will provide search on this new query.
86 String JavaDoc strQueryLowerCase = inputQuery.toLowerCase();
87
88          int iStartFromPosition = strQueryLowerCase.indexOf(" from ");
89          int iAliasIndex = 1;
90          int iActualPosition = 0;
91          
92          // Get all columns that have to be retrieved to the special string buffer.
93 // To all these columns will be added alias that will be used within the outer select.
94 bufferAllColumns.append(inputQuery.substring("select".length(), iStartFromPosition));
95          while (bufferAllColumns.indexOf(",", iActualPosition) != -1)
96          {
97             iActualPosition = bufferAllColumns.indexOf(",", iActualPosition);
98             // construct alias name
99 bufferAlias.append(" a");
100             bufferAlias.append(iAliasIndex++);
101             // add alias for column name
102 bufferAllColumns.insert(iActualPosition, bufferAlias.toString());
103             // count actual position
104 iActualPosition += bufferAlias.length() + 1;
105             // add alias to the all aliases separated by ',' to be used for outer select
106 bufferAllAliases.append(bufferAlias);
107             bufferAllAliases.append(", ");
108             bufferAlias.delete(0, bufferAlias.length());
109          }
110          // add alias for last column
111 bufferAlias.append(" a");
112          bufferAlias.append(iAliasIndex);
113
114          bufferAllColumns.append(bufferAlias);
115          // add alias to the all aliases separated by ',' to be used for outer select
116 bufferAllAliases.append(bufferAlias);
117          
118          // First add outer select
119 buffer.append("select ");
120          buffer.append(bufferAllAliases);
121          // Add start construction with SELECT and column aliases + ROWNUM specification
122 buffer.append(" from (select ");
123          buffer.append(bufferAllAliases);
124          buffer.append(", rownum RN from (select");
125          
126          // Add all columns included theyr aliases to the inner query string until FROM clause
127 buffer.append(bufferAllColumns);
128          
129          // Add inner query string from FROM clause
130 buffer.append(inputQuery.substring(iStartFromPosition));
131
132          // Add end construction for inner SELECT and ROWNUM specification
133 buffer.append(") where rownum < ");
134          buffer.append(options.getEndPosition() + 1);
135          // Finally end construction for outer SELECT and ROWNUM specification
136 buffer.append(") where RN > ");
137          buffer.append(options.getBeginPosition() - 1);
138
139          return buffer.toString();
140       }
141       else
142       {
143          // In case the ROWNUM is not supported or we do not want to use it,
144 // just call super
145 return super.preprocessSelectQuery(inputQuery, options, schema);
146       }
147    }
148 }
149
Popular Tags