DBOracle


1   package org.apache.torque.adapter;
2   
3   /*
4    * Licensed to the Apache Software Foundation (ASF) under one
5    * or more contributor license agreements.  See the NOTICE file
6    * distributed with this work for additional information
7    * regarding copyright ownership.  The ASF licenses this file
8    * to you under the Apache License, Version 2.0 (the
9    * "License"); you may not use this file except in compliance
10   * with the License.  You may obtain a copy of the License at
11   *
12   *   http://www.apache.org/licenses/LICENSE-2.0
13   *
14   * Unless required by applicable law or agreed to in writing,
15   * software distributed under the License is distributed on an
16   * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
17   * KIND, either express or implied.  See the License for the
18   * specific language governing permissions and limitations
19   * under the License.
20   */
21  
22  import java.sql.Connection  ;
23  import java.sql.SQLException  ;
24  import java.sql.Statement  ;
25  import java.text.SimpleDateFormat  ;
26  import java.util.Date  ;
27  import java.util.HashSet  ;
28  import java.util.ListIterator  ;
29  import java.util.Set  ;
30  
31  import org.apache.torque.util.Query;
32  import org.apache.torque.util.UniqueList;
33  
34  /**
35   * This code should be used for an Oracle database pool.
36   *
37   * @author <a HREF="mailto:jon@clearink.com">Jon S. Stevens</a>
38   * @author <a HREF="mailto:bmclaugh@algx.net">Brett McLaughlin</a>
39   * @author <a HREF="mailto:bschneider@vecna.com">Bill Schneider</a>
40   * @author <a HREF="mailto:dlr@finemaltcoding.com">Daniel Rall</a>
41   * @version $Id: DBOracle.java 476550 2006-11-18 16:08:37Z tfischer $
42   */
43  public class DBOracle extends AbstractDBAdapter
44  {
45      /**
46       * Serial version
47       */
48      private static final long serialVersionUID = 8966976210230241194L;
49  
50      /** date format used in getDateString() */
51      private static final String   DATE_FORMAT = "dd-MM-yyyy HH:mm:ss";
52  
53      /**
54       * Empty constructor.
55       */
56      protected DBOracle()
57      {
58      }
59  
60      /**
61       * This method is used to ignore case.
62       *
63       * @param in The string to transform to upper case.
64       * @return The upper case string.
65       */
66      public String   toUpperCase(String   in)
67      {
68          return new StringBuffer  ("UPPER(").append(in).append(")").toString();
69      }
70  
71      /**
72       * This method is used to ignore case.
73       *
74       * @param in The string whose case to ignore.
75       * @return The string in a case that can be ignored.
76       */
77      public String   ignoreCase(String   in)
78      {
79          return new StringBuffer  ("UPPER(").append(in).append(")").toString();
80      }
81  
82      /**
83       * This method is used to format any date string.
84       *
85       * @param date the Date to format
86       * @return The date formatted String for Oracle.
87       */
88      public String   getDateString(Date   date)
89      {
90          return "TO_DATE('" + new SimpleDateFormat  (DATE_FORMAT).format(date)
91                  + "', 'DD-MM-YYYY HH24:MI:SS')";
92      }
93  
94      /**
95       * @see org.apache.torque.adapter.DB#getIDMethodType()
96       */
97      public String   getIDMethodType()
98      {
99          return SEQUENCE;
100     }
101 
102     /**
103      * Returns the next key from a sequence.  Uses the following
104      * implementation:
105      *
106      * <blockquote><code><pre>
107      * select sequenceName.nextval from dual
108      * </pre></code></blockquote>
109      *
110      * @param sequenceName The name of the sequence (should be of type
111      * <code>String</code>).
112      * @return SQL to retreive the next database key.
113      * @see org.apache.torque.adapter.DB#getIDMethodSQL(Object)
114      */
115     public String   getIDMethodSQL(Object   sequenceName)
116     {
117         return ("select " + sequenceName + ".nextval from dual");
118     }
119 
120     /**
121      * Locks the specified table.
122      *
123      * @param con The JDBC connection to use.
124      * @param table The name of the table to lock.
125      * @exception SQLException No Statement could be created or executed.
126      */
127     public void lockTable(Connection   con, String   table) throws SQLException  
128     {
129         Statement   statement = con.createStatement();
130 
131         StringBuffer   stmt = new StringBuffer  ();
132         stmt.append("SELECT next_id FROM ")
133                 .append(table)
134                 .append(" FOR UPDATE");
135 
136         statement.executeQuery(stmt.toString());
137     }
138 
139     /**
140      * Unlocks the specified table.
141      *
142      * @param con The JDBC connection to use.
143      * @param table The name of the table to unlock.
144      * @exception SQLException No Statement could be created or executed.
145      */
146     public void unlockTable(Connection   con, String   table) throws SQLException  
147     {
148         // Tables in Oracle are unlocked when a commit is issued.  The
149         // user may have issued a commit but do it here to be sure.
150         con.commit();
151     }
152 
153     /**
154      * This method is used to check whether the database supports
155      * limiting the size of the resultset.
156      *
157      * @return LIMIT_STYLE_ORACLE.
158      * @deprecated This should not be exposed to the outside
159      */
160     public int getLimitStyle()
161     {
162         return DB.LIMIT_STYLE_ORACLE;
163     }
164 
165     /**
166      * Return true for Oracle
167      * @see org.apache.torque.adapter.AbstractDBAdapter#supportsNativeLimit()
168      */
169     public boolean supportsNativeLimit()
170     {
171         return true;
172     }
173 
174     /**
175      * Return true for Oracle
176      * @see org.apache.torque.adapter.AbstractDBAdapter#supportsNativeOffset()
177      */
178     public boolean supportsNativeOffset()
179     {
180         return true;
181     }
182 
183     /**
184      * Build Oracle-style query with limit or offset.
185      * If the original SQL is in variable: query then the requlting
186      * SQL looks like this:
187      * <pre>
188      * SELECT B.* FROM (
189      *          SELECT A.*, rownum as TORQUE$ROWNUM FROM (
190      *                  query
191      *          ) A
192      *     ) B WHERE B.TORQUE$ROWNUM > offset AND B.TORQUE$ROWNUM
193      *     <= offset + limit
194      * </pre>
195      *
196      * @param query The query to modify
197      * @param offset the offset Value
198      * @param limit the limit Value
199      */
200     public void generateLimits(Query query, int offset, int limit)
201     {
202         StringBuffer   preLimit = new StringBuffer  ()
203         .append("SELECT B.* FROM ( ")
204         .append("SELECT A.*, rownum AS TORQUE$ROWNUM FROM ( ");
205 
206         StringBuffer   postLimit = new StringBuffer  ()
207                 .append(" ) A ")
208                 .append(" ) B WHERE ");
209 
210         if (offset > 0)
211         {
212             postLimit.append(" B.TORQUE$ROWNUM > ")
213                     .append(offset);
214 
215             if (limit >= 0)
216             {
217                 postLimit.append(" AND B.TORQUE$ROWNUM <= ")
218                         .append(offset + limit);
219             }
220         }
221         else
222         {
223             postLimit.append(" B.TORQUE$ROWNUM <= ")
224                     .append(limit);
225         }
226 
227         query.setPreLimit(preLimit.toString());
228         query.setPostLimit(postLimit.toString());
229         query.setLimit(null);
230 
231         // the query must not contain same column names or aliases.
232         // Find double column names and aliases and create unique aliases
233         // TODO: does not work for functions yet
234         UniqueList selectColumns = query.getSelectClause();
235         int replacementSuffix = 0;
236         Set   columnNames = new HashSet  ();
237         // first pass: only remember aliased columns
238         // No replacements need to take place because double aliases
239         // are not allowed anyway
240         // So alias names will be retained
241         for (ListIterator   columnIt = selectColumns.listIterator();
242                 columnIt.hasNext();)
243         {
244             String   selectColumn = (String  ) columnIt.next();
245 
246             // check for sql function
247             if ((selectColumn.indexOf('(') != -1)
248                 || (selectColumn.indexOf(')') != -1))
249             {
250                 // Sql function. Disregard.
251                 continue;
252             }
253 
254             // check if alias name exists
255             int spacePos = selectColumn.lastIndexOf(' ');
256             if (spacePos == -1)
257             {
258                 // no alias, disregard for now
259                 continue;
260             }
261 
262             String   aliasName = selectColumn.substring(spacePos + 1);
263             columnNames.add(aliasName);
264         }
265 
266         // second pass. Regard ordinary columns only
267         for (ListIterator   columnIt = selectColumns.listIterator();
268                 columnIt.hasNext();)
269         {
270             String   selectColumn = (String  ) columnIt.next();
271 
272             // check for sql function
273             if ((selectColumn.indexOf('(') != -1)
274                 || (selectColumn.indexOf(')') != -1))
275             {
276                 // Sql function. Disregard.
277                 continue;
278             }
279 
280             {
281                 int spacePos = selectColumn.lastIndexOf(' ');
282                 if (spacePos != -1)
283                 {
284                     // alias, already processed in first pass
285                     continue;
286                 }
287             }
288             // split into column name and tableName
289             String   column;
290             {
291                 int dotPos = selectColumn.lastIndexOf('.');
292                 if (dotPos != -1)
293                 {
294                     column = selectColumn.substring(dotPos + 1);
295                 }
296                 else
297                 {
298                     column = selectColumn;
299                 }
300             }
301             if (columnNames.contains(column))
302             {
303                 // column needs to be aliased
304                 // get replacement name
305                 String   aliasName;
306                 do
307                 {
308                     aliasName = "a" + replacementSuffix;
309                     ++replacementSuffix;
310                 }
311                 while (columnNames.contains(aliasName));
312 
313                 selectColumn = selectColumn + " " + aliasName;
314                 columnIt.set(selectColumn);
315                 columnNames.add(aliasName);
316             }
317             else
318             {
319                 columnNames.add(column);
320             }
321         }
322     }
323 
324     /**
325      * This method is for the SqlExpression.quoteAndEscape rules.  The rule is,
326      * any string in a SqlExpression with a BACKSLASH will either be changed to
327      * "\\" or left as "\".  SapDB does not need the escape character.
328      *
329      * @return false.
330      */
331     public boolean escapeText()
332     {
333         return false;
334     }
335 
336     /**
337      * Whether an escape clause in like should be used.
338      * Example : select * from AUTHOR where AUTHOR.NAME like '\_%' ESCAPE '\';
339      *
340      * Oracle needs this, so this implementation always returns
341      * <code>true</code>.
342      *
343      * @return whether the escape clause should be appended or not.
344      */
345     public boolean useEscapeClauseForLike()
346     {
347         return true;
348     }
349 }
350
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags