1 /*
2 * Licensed to the Apache Software Foundation (ASF) under one
3 * or more contributor license agreements. See the NOTICE file
4 * distributed with this work for additional information
5 * regarding copyright ownership. The ASF licenses this file
6 * to you under the Apache License, Version 2.0 (the
7 * "License"); you may not use this file except in compliance
8 * with the License. You may obtain a copy of the License at
9 *
10 * http://www.apache.org/licenses/LICENSE-2.0
11 *
12 * Unless required by applicable law or agreed to in writing,
13 * software distributed under the License is distributed on an
14 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
15 * KIND, either express or implied. See the License for the
16 * specific language governing permissions and limitations
17 * under the License.
18 */
19 package org.apache.openjpa.jdbc.sql;
20
21 import java.io.Serializable;
22 import java.sql.CallableStatement;
23 import java.sql.Connection;
24 import java.sql.PreparedStatement;
25 import java.sql.ResultSet;
26 import java.sql.SQLException;
27 import java.util.ArrayList;
28 import java.util.Collections;
29 import java.util.Iterator;
30 import java.util.List;
31
32 import org.apache.commons.lang.ObjectUtils;
33 import org.apache.openjpa.jdbc.kernel.JDBCFetchConfiguration;
34 import org.apache.openjpa.jdbc.kernel.exps.Val;
35 import org.apache.openjpa.jdbc.schema.Column;
36 import org.apache.openjpa.jdbc.schema.Sequence;
37 import org.apache.openjpa.jdbc.schema.Table;
38 import serp.util.Numbers;
39
40 /**
41 * Buffer for SQL statements that can be used to create
42 * java.sql.PreparedStatements.
43 *
44 * @author Marc Prud'hommeaux
45 * @author Abe White
46 * @since 0.2.4
47 */
48 public final class SQLBuffer
49 implements Serializable, Cloneable {
50
51 private static final String PARAMETER_TOKEN = "?";
52
53 private final DBDictionary _dict;
54 private final StringBuffer _sql = new StringBuffer();
55 private List _subsels = null;
56 private List _params = null;
57 private List _cols = null;
58
59 /**
60 * Default constructor.
61 */
62 public SQLBuffer(DBDictionary dict) {
63 _dict = dict;
64 }
65
66 /**
67 * Copy constructor.
68 */
69 public SQLBuffer(SQLBuffer buf) {
70 _dict = buf._dict;
71 append(buf);
72 }
73
74 /**
75 * Perform a shallow clone of this SQL Buffer.
76 */
77 public Object clone() {
78 return new SQLBuffer(this);
79 }
80
81 /**
82 * Return true if the buffer is emtpy.
83 */
84 public boolean isEmpty() {
85 return _sql.length() == 0;
86 }
87
88 /**
89 * Append all SQL and parameters of the given buffer.
90 */
91 public SQLBuffer append(SQLBuffer buf) {
92 append(buf, _sql.length(), (_params == null) ? 0 : _params.size(),
93 true);
94 return this;
95 }
96
97 /**
98 * Append all SQL and parameters of the given buffer at the given positions.
99 */
100 private void append(SQLBuffer buf, int sqlIndex, int paramIndex,
101 boolean subsels) {
102 if (subsels) {
103 // only allow appending of buffers with subselects, not insertion
104 if (_subsels != null && !_subsels.isEmpty()
105 && sqlIndex != _sql.length())
106 throw new IllegalStateException();
107 if (buf._subsels != null && !buf._subsels.isEmpty()) {
108 if (sqlIndex != _sql.length())
109 throw new IllegalStateException();
110 if (_subsels == null)
111 _subsels = new ArrayList(buf._subsels.size());
112 for (int i = 0; i < buf._subsels.size(); i++)
113 _subsels.add(((Subselect) buf._subsels.get(i)).
114 clone(sqlIndex, paramIndex));
115 }
116 }
117
118 if (sqlIndex == _sql.length())
119 _sql.append(buf._sql.toString());
120 else
121 _sql.insert(sqlIndex, buf._sql.toString());
122
123 if (buf._params != null) {
124 if (_params == null)
125 _params = new ArrayList();
126 if (_cols == null && buf._cols != null) {
127 _cols = new ArrayList();
128 while (_cols.size() < _params.size())
129 _cols.add(null);
130 }
131
132 if (paramIndex == _params.size()) {
133 _params.addAll(buf._params);
134 if (buf._cols != null)
135 _cols.addAll(buf._cols);
136 else if (_cols != null)
137 while (_cols.size() < _params.size())
138 _cols.add(null);
139 } else {
140 _params.addAll(paramIndex, buf._params);
141 if (buf._cols != null)
142 _cols.addAll(paramIndex, buf._cols);
143 else if (_cols != null)
144 while (_cols.size() < _params.size())
145 _cols.add(paramIndex, null);
146 }
147 }
148 }
149
150 public SQLBuffer append(Table table) {
151 _sql.append(_dict.getFullName(table, false));
152 return this;
153 }
154
155 public SQLBuffer append(Sequence seq) {
156 _sql.append(_dict.getFullName(seq));
157 return this;
158 }
159
160 public SQLBuffer append(Column col) {
161 _sql.append(col.getName());
162 return this;
163 }
164
165 public SQLBuffer append(String s) {
166 _sql.append(s);
167 return this;
168 }
169
170 /**
171 * Append a subselect. This delays resolution of the select SQL.
172 */
173 public SQLBuffer append(Select sel, JDBCFetchConfiguration fetch) {
174 return append(sel, fetch, false);
175 }
176
177 /**
178 * Append a subselect count. This delays resolution of the select SQL.
179 */
180 public SQLBuffer appendCount(Select sel, JDBCFetchConfiguration fetch) {
181 return append(sel, fetch, true);
182 }
183
184 /**
185 * Append a subselect. This delays resolution of the select SQL.
186 */
187 private SQLBuffer append(Select sel, JDBCFetchConfiguration fetch,
188 boolean count) {
189 _sql.append("(");
190 Subselect sub = new Subselect();
191 sub.select = sel;
192 sub.fetch = fetch;
193 sub.count = count;
194 sub.sqlIndex = _sql.length();
195 sub.paramIndex = (_params == null) ? 0 : _params.size();
196 _sql.append(")");
197
198 if (_subsels == null)
199 _subsels = new ArrayList(2);
200 _subsels.add(sub);
201 return this;
202 }
203
204 /**
205 * Replace a subselect.
206 */
207 public boolean replace(Select old, Select sel) {
208 if (_subsels == null)
209 return false;
210 Subselect sub;
211 for (int i = 0; i < _subsels.size(); i++) {
212 sub = (Subselect) _subsels.get(i);
213 if (sub.select == old) {
214 sub.select = sel;
215 return true;
216 }
217 }
218 return false;
219 }
220
221 /**
222 * Append a parameter value.
223 */
224 public SQLBuffer appendValue(Object o) {
225 return appendValue(o, null);
226 }
227
228 /**
229 * Append a parameter value for a specific column.
230 */
231 public SQLBuffer appendValue(Object o, Column col) {
232 if (o == null)
233 _sql.append("NULL");
234 else if (o instanceof Raw)
235 _sql.append(o.toString());
236 else {
237 _sql.append(PARAMETER_TOKEN);
238
239 // initialize param and col lists; we hold off on col list until
240 // we get the first non-null col
241 if (_params == null)
242 _params = new ArrayList();
243 if (col != null && _cols == null) {
244 _cols = new ArrayList();
245 while (_cols.size() < _params.size())
246 _cols.add(null);
247 }
248
249 _params.add(o);
250 if (_cols != null)
251 _cols.add(col);
252 }
253 return this;
254 }
255
256 /**
257 * Append a parameter value.
258 */
259 public SQLBuffer appendValue(boolean b) {
260 return appendValue(b, null);
261 }
262
263 /**
264 * Append a parameter value.
265 */
266 public SQLBuffer appendValue(boolean b, Column col) {
267 return appendValue((b) ? Boolean.TRUE : Boolean.FALSE, col);
268 }
269
270 /**
271 * Append a parameter value.
272 */
273 public SQLBuffer appendValue(byte b) {
274 return appendValue(b, null);
275 }
276
277 /**
278 * Append a parameter value.
279 */
280 public SQLBuffer appendValue(byte b, Column col) {
281 return appendValue(new Byte(b), col);
282 }
283
284 /**
285 * Append a parameter value.
286 */
287 public SQLBuffer appendValue(char c) {
288 return appendValue(c, null);
289 }
290
291 /**
292 * Append a parameter value.
293 */
294 public SQLBuffer appendValue(char c, Column col) {
295 return appendValue(new Character(c), col);
296 }
297
298 /**
299 * Append a parameter value.
300 */
301 public SQLBuffer appendValue(double d) {
302 return appendValue(d, null);
303 }
304
305 /**
306 * Append a parameter value.
307 */
308 public SQLBuffer appendValue(double d, Column col) {
309 return appendValue(new Double(d), col);
310 }
311
312 /**
313 * Append a parameter value.
314 */
315 public SQLBuffer appendValue(float f) {
316 return appendValue(f, null);
317 }
318
319 /**
320 * Append a parameter value.
321 */
322 public SQLBuffer appendValue(float f, Column col) {
323 return appendValue(new Float(f), col);
324 }
325
326 /**
327 * Append a parameter value.
328 */
329 public SQLBuffer appendValue(int i) {
330 return appendValue(i, null);
331 }
332
333 /**
334 * Append a parameter value.
335 */
336 public SQLBuffer appendValue(int i, Column col) {
337 return appendValue(Numbers.valueOf(i), col);
338 }
339
340 /**
341 * Append a parameter value.
342 */
343 public SQLBuffer appendValue(long l) {
344 return appendValue(l, null);
345 }
346
347 /**
348 * Append a parameter value.
349 */
350 public SQLBuffer appendValue(long l, Column col) {
351 return appendValue(Numbers.valueOf(l), col);
352 }
353
354 /**
355 * Append a parameter value.
356 */
357 public SQLBuffer appendValue(short s) {
358 return appendValue(s, null);
359 }
360
361 /**
362 * Append a parameter value.
363 */
364 public SQLBuffer appendValue(short s, Column col) {
365 return appendValue(new Short(s), col);
366 }
367
368 /**
369 * Return the list of parameter values.
370 */
371 public List getParameters() {
372 return (_params == null) ? Collections.EMPTY_LIST : _params;
373 }
374
375 /**
376 * Return the SQL for this buffer.
377 */
378 public String getSQL() {
379 return getSQL(false);
380 }
381
382 /**
383 * Returns the SQL for this buffer.
384 *
385 * @param replaceParams if true, then replace parameters with the
386 * actual parameter values
387 */
388 public String getSQL(boolean replaceParams) {
389 resolveSubselects();
390 String sql = _sql.toString();
391 if (!replaceParams || _params == null || _params.isEmpty())
392 return sql;
393
394 StringBuffer buf = new StringBuffer();
395 Iterator pi = _params.iterator();
396 for (int i = 0; i < sql.length(); i++) {
397 if (sql.charAt(i) != '?') {
398 buf.append(sql.charAt(i));
399 continue;
400 }
401
402 Object param = pi.hasNext() ? pi.next() : null;
403 if (param == null)
404 buf.append("NULL");
405 else if (param instanceof Number || param instanceof Boolean)
406 buf.append(param);
407 else if (param instanceof String || param instanceof Character)
408 buf.append("'").append(param).append("'");
409 else
410 buf.append("?");
411 }
412 return buf.toString();
413 }
414
415 /**
416 * Resolve our delayed subselects.
417 */
418 private void resolveSubselects() {
419 if (_subsels == null || _subsels.isEmpty())
420 return;
421
422 // add subsels backwards so that the stored insertion points of
423 // later subsels remain valid
424 Subselect sub;
425 SQLBuffer buf;
426 for (int i = _subsels.size() - 1; i >= 0; i--) {
427 sub = (Subselect) _subsels.get(i);
428 if (sub.count)
429 buf = sub.select.toSelectCount();
430 else
431 buf = sub.select.toSelect(false, sub.fetch);
432 buf.resolveSubselects();
433 append(buf, sub.sqlIndex, sub.paramIndex, false);
434 }
435 _subsels.clear();
436 }
437
438 /**
439 * Create and populate the parameters of a prepared statement using
440 * the SQL in this buffer.
441 */
442 public PreparedStatement prepareStatement(Connection conn)
443 throws SQLException {
444 return prepareStatement(conn, ResultSet.TYPE_FORWARD_ONLY,
445 ResultSet.CONCUR_READ_ONLY);
446 }
447
448 /**
449 * Create and populate the parameters of a prepared statement using
450 * the SQL in this buffer.
451 */
452 public PreparedStatement prepareStatement(Connection conn, int rsType,
453 int rsConcur)
454 throws SQLException {
455 return prepareStatement(conn, null, rsType, rsConcur);
456 }
457
458 /**
459 * Create and populate the parameters of a prepred statement using the
460 * SQL in this buffer and the given fetch configuration.
461 */
462 public PreparedStatement prepareStatement(Connection conn,
463 JDBCFetchConfiguration fetch, int rsType, int rsConcur)
464 throws SQLException {
465 if (rsType == -1 && fetch == null)
466 rsType = ResultSet.TYPE_FORWARD_ONLY;
467 else if (rsType == -1)
468 rsType = fetch.getResultSetType();
469 if (rsConcur == -1)
470 rsConcur = ResultSet.CONCUR_READ_ONLY;
471
472 PreparedStatement stmnt;
473 if (rsType == ResultSet.TYPE_FORWARD_ONLY
474 && rsConcur == ResultSet.CONCUR_READ_ONLY)
475 stmnt = conn.prepareStatement(getSQL());
476 else
477 stmnt = conn.prepareStatement(getSQL(), rsType, rsConcur);
478 try {
479 setParameters(stmnt);
480 if (fetch != null) {
481 if (fetch.getFetchBatchSize() > 0)
482 stmnt.setFetchSize(fetch.getFetchBatchSize());
483 if (rsType != ResultSet.TYPE_FORWARD_ONLY
484 && fetch.getFetchDirection() != ResultSet.FETCH_FORWARD)
485 stmnt.setFetchDirection(fetch.getFetchDirection());
486 }
487 return stmnt;
488 } catch (SQLException se) {
489 try {
490 stmnt.close();
491 } catch (SQLException se2) {
492 }
493 throw se;
494 }
495 }
496
497 /**
498 * Create and populate the parameters of a prepared statement using
499 * the SQL in this buffer.
500 */
501 public CallableStatement prepareCall(Connection conn)
502 throws SQLException {
503 return prepareCall(conn, ResultSet.TYPE_FORWARD_ONLY,
504 ResultSet.CONCUR_READ_ONLY);
505 }
506
507 /**
508 * Create and populate the parameters of a prepared statement using
509 * the SQL in this buffer.
510 */
511 public CallableStatement prepareCall(Connection conn, int rsType,
512 int rsConcur)
513 throws SQLException {
514 return prepareCall(conn, null, rsType, rsConcur);
515 }
516
517 /**
518 * Create and populate the parameters of a prepred statement using the
519 * SQL in this buffer and the given fetch configuration.
520 */
521 public CallableStatement prepareCall(Connection conn,
522 JDBCFetchConfiguration fetch, int rsType, int rsConcur)
523 throws SQLException {
524 if (rsType == -1 && fetch == null)
525 rsType = ResultSet.TYPE_FORWARD_ONLY;
526 else if (rsType == -1)
527 rsType = fetch.getResultSetType();
528 if (rsConcur == -1)
529 rsConcur = ResultSet.CONCUR_READ_ONLY;
530
531 CallableStatement stmnt;
532 if (rsType == ResultSet.TYPE_FORWARD_ONLY
533 && rsConcur == ResultSet.CONCUR_READ_ONLY)
534 stmnt = conn.prepareCall(getSQL());
535 else
536 stmnt = conn.prepareCall(getSQL(), rsType, rsConcur);
537 try {
538 setParameters(stmnt);
539 if (fetch != null) {
540 if (fetch.getFetchBatchSize() > 0)
541 stmnt.setFetchSize(fetch.getFetchBatchSize());
542 if (rsType != ResultSet.TYPE_FORWARD_ONLY
543 && fetch.getFetchDirection() != ResultSet.FETCH_FORWARD)
544 stmnt.setFetchDirection(fetch.getFetchDirection());
545 }
546 return stmnt;
547 } catch (SQLException se) {
548 try {
549 stmnt.close();
550 } catch (SQLException se2) {
551 }
552 throw se;
553 }
554 }
555
556 /**
557 * Populate the parameters of an existing PreparedStatement
558 * with values from this buffer.
559 */
560 public void setParameters(PreparedStatement ps)
561 throws SQLException {
562 if (_params == null)
563 return;
564
565 Column col;
566 for (int i = 0; i < _params.size(); i++) {
567 col = (_cols == null) ? null : (Column) _cols.get(i);
568 _dict.setUnknown(ps, i + 1, _params.get(i), col);
569 }
570 }
571
572 public int hashCode() {
573 int hash = _sql.hashCode();
574 return (_params == null) ? hash : hash ^ _params.hashCode();
575 }
576
577 /**
578 * Compare internal SQL without resolving subselects or stringifying
579 * parameters.
580 */
581 public boolean sqlEquals(String sql) {
582 return _sql.toString().equals(sql);
583 }
584
585 public boolean equals(Object other) {
586 if (other == this)
587 return true;
588 if (!(other instanceof SQLBuffer))
589 return false;
590
591 SQLBuffer buf = (SQLBuffer) other;
592 return _sql.equals(buf._sql)
593 && ObjectUtils.equals(_params, buf._params);
594 }
595
596 /**
597 * Replace SQL '?' with CAST string if required by DB platform
598 * @param oper
599 * @param val
600 */
601 public void addCastForParam(String oper, Val val) {
602 if (_sql.charAt(_sql.length() - 1) == '?') {
603 String castString = _dict.addCastAsType(oper, val);
604 if (castString != null)
605 _sql.replace(_sql.length() - 1, _sql.length(), castString);
606 }
607 }
608
609 /**
610 * Replace current buffer string with the new string
611 *
612 * @param start replace start position
613 * @param end replace end position
614 * @param newString
615 */
616 public void replaceSqlString(int start, int end, String newString) {
617 _sql.replace(start, end, newString);
618 }
619
620 /**
621 * Represents a subselect.
622 */
623 private static class Subselect {
624
625 public Select select;
626 public JDBCFetchConfiguration fetch;
627 public boolean count;
628 public int sqlIndex;
629 public int paramIndex;
630
631 public Subselect clone(int sqlIndex, int paramIndex) {
632 if (sqlIndex == 0 && paramIndex == 0)
633 return this;
634
635 Subselect sub = new Subselect();
636 sub.select = select;
637 sub.fetch = fetch;
638 sub.count = count;
639 sub.sqlIndex = this.sqlIndex + sqlIndex;
640 sub.paramIndex = this.paramIndex + paramIndex;
641 return sub;
642 }
643 }
644
645 public void setParameters(List params) {
646 _params = params;
647 }
648
649 public List getColumns() {
650 return _cols;
651 }
652 }
Save This Page