001: package org.apache.torque.adapter;
002:
003: /*
004: * Licensed to the Apache Software Foundation (ASF) under one
005: * or more contributor license agreements. See the NOTICE file
006: * distributed with this work for additional information
007: * regarding copyright ownership. The ASF licenses this file
008: * to you under the Apache License, Version 2.0 (the
009: * "License"); you may not use this file except in compliance
010: * with the License. You may obtain a copy of the License at
011: *
012: * http://www.apache.org/licenses/LICENSE-2.0
013: *
014: * Unless required by applicable law or agreed to in writing,
015: * software distributed under the License is distributed on an
016: * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
017: * KIND, either express or implied. See the License for the
018: * specific language governing permissions and limitations
019: * under the License.
020: */
021:
022: import java.sql.Connection;
023: import java.sql.SQLException;
024: import java.sql.Statement;
025: import java.text.SimpleDateFormat;
026: import java.util.Date;
027: import java.util.HashSet;
028: import java.util.ListIterator;
029: import java.util.Set;
030:
031: import org.apache.torque.util.Query;
032: import org.apache.torque.util.UniqueList;
033:
034: /**
035: * This code should be used for an Oracle database pool.
036: *
037: * @author <a href="mailto:jon@clearink.com">Jon S. Stevens</a>
038: * @author <a href="mailto:bmclaugh@algx.net">Brett McLaughlin</a>
039: * @author <a href="mailto:bschneider@vecna.com">Bill Schneider</a>
040: * @author <a href="mailto:dlr@finemaltcoding.com">Daniel Rall</a>
041: * @version $Id: DBOracle.java 476550 2006-11-18 16:08:37Z tfischer $
042: */
043: public class DBOracle extends AbstractDBAdapter {
044: /**
045: * Serial version
046: */
047: private static final long serialVersionUID = 8966976210230241194L;
048:
049: /** date format used in getDateString() */
050: private static final String DATE_FORMAT = "dd-MM-yyyy HH:mm:ss";
051:
052: /**
053: * Empty constructor.
054: */
055: protected DBOracle() {
056: }
057:
058: /**
059: * This method is used to ignore case.
060: *
061: * @param in The string to transform to upper case.
062: * @return The upper case string.
063: */
064: public String toUpperCase(String in) {
065: return new StringBuffer("UPPER(").append(in).append(")")
066: .toString();
067: }
068:
069: /**
070: * This method is used to ignore case.
071: *
072: * @param in The string whose case to ignore.
073: * @return The string in a case that can be ignored.
074: */
075: public String ignoreCase(String in) {
076: return new StringBuffer("UPPER(").append(in).append(")")
077: .toString();
078: }
079:
080: /**
081: * This method is used to format any date string.
082: *
083: * @param date the Date to format
084: * @return The date formatted String for Oracle.
085: */
086: public String getDateString(Date date) {
087: return "TO_DATE('"
088: + new SimpleDateFormat(DATE_FORMAT).format(date)
089: + "', 'DD-MM-YYYY HH24:MI:SS')";
090: }
091:
092: /**
093: * @see org.apache.torque.adapter.DB#getIDMethodType()
094: */
095: public String getIDMethodType() {
096: return SEQUENCE;
097: }
098:
099: /**
100: * Returns the next key from a sequence. Uses the following
101: * implementation:
102: *
103: * <blockquote><code><pre>
104: * select sequenceName.nextval from dual
105: * </pre></code></blockquote>
106: *
107: * @param sequenceName The name of the sequence (should be of type
108: * <code>String</code>).
109: * @return SQL to retreive the next database key.
110: * @see org.apache.torque.adapter.DB#getIDMethodSQL(Object)
111: */
112: public String getIDMethodSQL(Object sequenceName) {
113: return ("select " + sequenceName + ".nextval from dual");
114: }
115:
116: /**
117: * Locks the specified table.
118: *
119: * @param con The JDBC connection to use.
120: * @param table The name of the table to lock.
121: * @exception SQLException No Statement could be created or executed.
122: */
123: public void lockTable(Connection con, String table)
124: throws SQLException {
125: Statement statement = con.createStatement();
126:
127: StringBuffer stmt = new StringBuffer();
128: stmt.append("SELECT next_id FROM ").append(table).append(
129: " FOR UPDATE");
130:
131: statement.executeQuery(stmt.toString());
132: }
133:
134: /**
135: * Unlocks the specified table.
136: *
137: * @param con The JDBC connection to use.
138: * @param table The name of the table to unlock.
139: * @exception SQLException No Statement could be created or executed.
140: */
141: public void unlockTable(Connection con, String table)
142: throws SQLException {
143: // Tables in Oracle are unlocked when a commit is issued. The
144: // user may have issued a commit but do it here to be sure.
145: con.commit();
146: }
147:
148: /**
149: * This method is used to check whether the database supports
150: * limiting the size of the resultset.
151: *
152: * @return LIMIT_STYLE_ORACLE.
153: * @deprecated This should not be exposed to the outside
154: */
155: public int getLimitStyle() {
156: return DB.LIMIT_STYLE_ORACLE;
157: }
158:
159: /**
160: * Return true for Oracle
161: * @see org.apache.torque.adapter.AbstractDBAdapter#supportsNativeLimit()
162: */
163: public boolean supportsNativeLimit() {
164: return true;
165: }
166:
167: /**
168: * Return true for Oracle
169: * @see org.apache.torque.adapter.AbstractDBAdapter#supportsNativeOffset()
170: */
171: public boolean supportsNativeOffset() {
172: return true;
173: }
174:
175: /**
176: * Build Oracle-style query with limit or offset.
177: * If the original SQL is in variable: query then the requlting
178: * SQL looks like this:
179: * <pre>
180: * SELECT B.* FROM (
181: * SELECT A.*, rownum as TORQUE$ROWNUM FROM (
182: * query
183: * ) A
184: * ) B WHERE B.TORQUE$ROWNUM > offset AND B.TORQUE$ROWNUM
185: * <= offset + limit
186: * </pre>
187: *
188: * @param query The query to modify
189: * @param offset the offset Value
190: * @param limit the limit Value
191: */
192: public void generateLimits(Query query, int offset, int limit) {
193: StringBuffer preLimit = new StringBuffer().append(
194: "SELECT B.* FROM ( ").append(
195: "SELECT A.*, rownum AS TORQUE$ROWNUM FROM ( ");
196:
197: StringBuffer postLimit = new StringBuffer().append(" ) A ")
198: .append(" ) B WHERE ");
199:
200: if (offset > 0) {
201: postLimit.append(" B.TORQUE$ROWNUM > ").append(offset);
202:
203: if (limit >= 0) {
204: postLimit.append(" AND B.TORQUE$ROWNUM <= ").append(
205: offset + limit);
206: }
207: } else {
208: postLimit.append(" B.TORQUE$ROWNUM <= ").append(limit);
209: }
210:
211: query.setPreLimit(preLimit.toString());
212: query.setPostLimit(postLimit.toString());
213: query.setLimit(null);
214:
215: // the query must not contain same column names or aliases.
216: // Find double column names and aliases and create unique aliases
217: // TODO: does not work for functions yet
218: UniqueList selectColumns = query.getSelectClause();
219: int replacementSuffix = 0;
220: Set columnNames = new HashSet();
221: // first pass: only remember aliased columns
222: // No replacements need to take place because double aliases
223: // are not allowed anyway
224: // So alias names will be retained
225: for (ListIterator columnIt = selectColumns.listIterator(); columnIt
226: .hasNext();) {
227: String selectColumn = (String) columnIt.next();
228:
229: // check for sql function
230: if ((selectColumn.indexOf('(') != -1)
231: || (selectColumn.indexOf(')') != -1)) {
232: // Sql function. Disregard.
233: continue;
234: }
235:
236: // check if alias name exists
237: int spacePos = selectColumn.lastIndexOf(' ');
238: if (spacePos == -1) {
239: // no alias, disregard for now
240: continue;
241: }
242:
243: String aliasName = selectColumn.substring(spacePos + 1);
244: columnNames.add(aliasName);
245: }
246:
247: // second pass. Regard ordinary columns only
248: for (ListIterator columnIt = selectColumns.listIterator(); columnIt
249: .hasNext();) {
250: String selectColumn = (String) columnIt.next();
251:
252: // check for sql function
253: if ((selectColumn.indexOf('(') != -1)
254: || (selectColumn.indexOf(')') != -1)) {
255: // Sql function. Disregard.
256: continue;
257: }
258:
259: {
260: int spacePos = selectColumn.lastIndexOf(' ');
261: if (spacePos != -1) {
262: // alias, already processed in first pass
263: continue;
264: }
265: }
266: // split into column name and tableName
267: String column;
268: {
269: int dotPos = selectColumn.lastIndexOf('.');
270: if (dotPos != -1) {
271: column = selectColumn.substring(dotPos + 1);
272: } else {
273: column = selectColumn;
274: }
275: }
276: if (columnNames.contains(column)) {
277: // column needs to be aliased
278: // get replacement name
279: String aliasName;
280: do {
281: aliasName = "a" + replacementSuffix;
282: ++replacementSuffix;
283: } while (columnNames.contains(aliasName));
284:
285: selectColumn = selectColumn + " " + aliasName;
286: columnIt.set(selectColumn);
287: columnNames.add(aliasName);
288: } else {
289: columnNames.add(column);
290: }
291: }
292: }
293:
294: /**
295: * This method is for the SqlExpression.quoteAndEscape rules. The rule is,
296: * any string in a SqlExpression with a BACKSLASH will either be changed to
297: * "\\" or left as "\". SapDB does not need the escape character.
298: *
299: * @return false.
300: */
301: public boolean escapeText() {
302: return false;
303: }
304:
305: /**
306: * Whether an escape clause in like should be used.
307: * Example : select * from AUTHOR where AUTHOR.NAME like '\_%' ESCAPE '\';
308: *
309: * Oracle needs this, so this implementation always returns
310: * <code>true</code>.
311: *
312: * @return whether the escape clause should be appended or not.
313: */
314: public boolean useEscapeClauseForLike() {
315: return true;
316: }
317: }
|