1 package org.apache.torque.adapter;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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
149
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
232
233
234 UniqueList selectColumns = query.getSelectClause();
235 int replacementSuffix = 0;
236 Set columnNames = new HashSet();
237
238
239
240
241 for (ListIterator columnIt = selectColumns.listIterator();
242 columnIt.hasNext();)
243 {
244 String selectColumn = (String) columnIt.next();
245
246
247 if ((selectColumn.indexOf('(') != -1)
248 || (selectColumn.indexOf(')') != -1))
249 {
250
251 continue;
252 }
253
254
255 int spacePos = selectColumn.lastIndexOf(' ');
256 if (spacePos == -1)
257 {
258
259 continue;
260 }
261
262 String aliasName = selectColumn.substring(spacePos + 1);
263 columnNames.add(aliasName);
264 }
265
266
267 for (ListIterator columnIt = selectColumns.listIterator();
268 columnIt.hasNext();)
269 {
270 String selectColumn = (String) columnIt.next();
271
272
273 if ((selectColumn.indexOf('(') != -1)
274 || (selectColumn.indexOf(')') != -1))
275 {
276
277 continue;
278 }
279
280 {
281 int spacePos = selectColumn.lastIndexOf(' ');
282 if (spacePos != -1)
283 {
284
285 continue;
286 }
287 }
288
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
304
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 }