1 /*
2 * Copyright 2003 - 2013 The eFaps Team
3 *
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
7 *
8 * http://www.apache.org/licenses/LICENSE-2.0
9 *
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
15 *
16 * Revision: $Rev$
17 * Last Changed: $Date$
18 * Last Changed By: $Author$
19 */
20
21 package org.efaps.db.wrapper;
22
23 import java.util.ArrayList;
24 import java.util.List;
25
26 import org.efaps.db.Context;
27 import org.efaps.db.search.section.AbstractQSection;
28 import org.efaps.util.EFapsException;
29
30 /**
31 * An easy wrapper for a SQL select statement.
32 *
33 * @author The eFaps Team
34 * @version $Id$
35 */
36 public class SQLSelect
37 {
38 /**
39 * Parts that will be added to the created SQL Statement.
40 */
41 private final List<SQLSelectPart> parts = new ArrayList<SQLSelectPart>();
42
43 /**
44 * Selected columns.
45 *
46 * @see #column(String)
47 */
48 private final List<Column> columns = new ArrayList<Column>();
49
50 /**
51 * Selected tables.
52 *
53 * @see #from(String)
54 */
55 private final List<FromTable> fromTables = new ArrayList<FromTable>();
56
57 /**
58 * Must the select be distinct.
59 *
60 */
61 private boolean distinct = false;
62
63 /**
64 * Appends a selected column.
65 *
66 * @param _name name of the column
67 * @return this SQL select statement
68 * @see #columns
69 */
70 public SQLSelect column(final String _name)
71 {
72 this.columns.add(new Column(null, _name));
73 return this;
74 }
75
76 /**
77 * Appends a selected column <code>_name</code> for given
78 * <code>_tableIndex</code>.
79 *
80 * @param _tableIndex index of the table
81 * @param _columnName name of the column
82 * @return this SQL select statement
83 * @see #columns
84 */
85 public SQLSelect column(final int _tableIndex,
86 final String _columnName)
87 {
88 this.columns.add(new Column(_tableIndex, _columnName));
89 return this;
90 }
91
92 /**
93 * Getter method for the instance variable {@link #columns}.
94 *
95 * @return value of instance variable {@link #columns}
96 */
97 public List<Column> getColumns()
98 {
99 return this.columns;
100 }
101
102 /**
103 * Appends a table as from selected table.
104 *
105 * @param _name name of the table
106 * @return this SQL select statement
107 * @see #fromTables
108 */
109 public SQLSelect from(final String _name)
110 {
111 this.fromTables.add(new FromTable(_name, null));
112 return this;
113 }
114
115 /**
116 * Appends a table as from selected table.
117 *
118 * @param _tableName name of the SQL table
119 * @param _tableIndex index of the table within the SQL statement
120 * @return this SQL select statement
121 * @see #fromTables
122 */
123 public SQLSelect from(final String _tableName,
124 final int _tableIndex)
125 {
126 this.fromTables.add(new FromTable(_tableName, _tableIndex));
127 return this;
128 }
129
130 /**
131 * Getter method for the instance variable {@link #fromTables}.
132 *
133 * @return value of instance variable {@link #fromTables}
134 */
135 public List<FromTable> getFromTables()
136 {
137 return this.fromTables;
138 }
139
140 /**
141 *
142 * @param _tableName name of the SQL table
143 * @param _tableIndex index of the table used within the SQL select
144 * statement
145 * @param _columnName name of the column of table <code>_tableName</code>
146 * used for "left join"
147 * @param _joinTableIndex index of the table from which is joined
148 * @param _joinColumnName name of the column of the table from which is
149 * joined
150 * @return this SQL select statement instance
151 */
152 public SQLSelect leftJoin(final String _tableName,
153 final int _tableIndex,
154 final String _columnName,
155 final int _joinTableIndex,
156 final String _joinColumnName)
157 {
158 this.fromTables.add(new FromTableLeftJoin(_tableName, _tableIndex, _columnName,
159 _joinTableIndex, _joinColumnName));
160 return this;
161 }
162
163 /**
164 *
165 * @param _tableName name of the SQL table
166 * @param _tableIndex index of the table used within the SQL select
167 * statement
168 * @param _columnNames names of the columns of table <code>_tableName</code>
169 * used for "left join"
170 * @param _joinTableIndex index of the table from which is joined
171 * @param _joinColumnNames names of the column of the table from which is
172 * joined
173 * @return this SQL select statement instance
174 */
175 public SQLSelect leftJoin(final String _tableName,
176 final int _tableIndex,
177 final String[] _columnNames,
178 final int _joinTableIndex,
179 final String[] _joinColumnNames)
180 {
181 this.fromTables.add(new FromTableLeftJoin(_tableName, _tableIndex, _columnNames,
182 _joinTableIndex, _joinColumnNames));
183 return this;
184 }
185
186 /**
187 *
188 * @param _tableName name of the SQL table
189 * @param _tableIndex index of the table used within the SQL select
190 * statement
191 * @param _columnName name of the column of table <code>_tableName</code>
192 * used for "left join"
193 * @param _joinTableIndex index of the table from which is joined
194 * @param _joinColumnName name of the column of the table from which is
195 * joined
196 * @return this SQL select statement instance
197 */
198 public SQLSelect innerJoin(final String _tableName,
199 final int _tableIndex,
200 final String _columnName,
201 final int _joinTableIndex,
202 final String _joinColumnName)
203 {
204 this.fromTables.add(new FromTableInnerJoin(_tableName, _tableIndex, _columnName,
205 _joinTableIndex, _joinColumnName));
206 return this;
207 }
208
209 /**
210 *
211 * @param _tableName name of the SQL table
212 * @param _tableIndex index of the table used within the SQL select
213 * statement
214 * @param _columnNames names of the columns of table <code>_tableName</code>
215 * used for "left join"
216 * @param _joinTableIndex index of the table from which is joined
217 * @param _joinColumnNames names of the columns of the table from which is
218 * joined
219 * @return this SQL select statement instance
220 */
221 public SQLSelect innerJoin(final String _tableName,
222 final int _tableIndex,
223 final String[] _columnNames,
224 final int _joinTableIndex,
225 final String[] _joinColumnNames)
226 {
227 this.fromTables.add(new FromTableInnerJoin(_tableName, _tableIndex, _columnNames,
228 _joinTableIndex, _joinColumnNames));
229 return this;
230 }
231
232 /**
233 * Returns the depending SQL statement.
234 *
235 * @return SQL statement
236 */
237 public String getSQL()
238 {
239 final StringBuilder cmd = new StringBuilder().append(" ")
240 .append(Context.getDbType().getSQLPart(SQLPart.SELECT)).append(" ");
241 if (this.distinct) {
242 cmd.append(Context.getDbType().getSQLPart(SQLPart.DISTINCT)).append(" ");
243 }
244 boolean first = true;
245 for (final Column column : this.columns) {
246 if (first) {
247 first = false;
248 } else {
249 cmd.append(Context.getDbType().getSQLPart(SQLPart.COMMA));
250 }
251 column.appendSQL(cmd);
252 }
253 cmd.append(" ").append(Context.getDbType().getSQLPart(SQLPart.FROM)).append(" ");
254 first = true;
255 for (final FromTable fromTable : this.fromTables) {
256 fromTable.appendSQL(first, cmd);
257 if (first) {
258 first = false;
259 }
260 }
261 cmd.append(" ");
262 for (final SQLSelectPart part : this.parts) {
263 part.appendSQL(cmd);
264 cmd.append(" ");
265 }
266 return cmd.toString();
267 }
268
269 /**
270 * Must this SQLSelect be distinct.
271 *
272 * @param _distinct distinct
273 * @return this
274 */
275 public SQLSelect distinct(final boolean _distinct)
276 {
277 this.distinct = _distinct;
278 return this;
279 }
280
281 /**
282 * @param _section Sectin o to be added
283 * @throws EFapsException on error
284 * @return this
285 */
286 public SQLSelect addSection(final AbstractQSection _section)
287 throws EFapsException
288 {
289 if (_section != null) {
290 _section.appendSQL(this);
291 }
292 return this;
293 }
294
295 /**
296 * @param _part Part to be added
297 * @return this
298 */
299 public SQLSelect addPart(final SQLPart _part)
300 {
301 this.parts.add(new SQLSelectPart(_part));
302 return this;
303 }
304
305 /**
306 * Add a column as part.
307 * @param _tableIndex index of the table
308 * @param _columnName name of the column
309 * @return this
310 */
311 public SQLSelect addColumnPart(final Integer _tableIndex,
312 final String _columnName)
313 {
314 this.parts.add(new Column(_tableIndex, _columnName));
315 return this;
316 }
317
318 /**
319 * Add a table as part.
320 * @param _tableName name of the table
321 * @param _tableIndex index of the table
322 * @return this
323 */
324 public SQLSelect addTablePart(final String _tableName,
325 final Integer _tableIndex)
326 {
327 this.parts.add(new FromTable(_tableName, _tableIndex));
328 return this;
329 }
330
331 /**
332 * @param _char val;ue to be added as nested Select part
333 * @return this
334 */
335 public SQLSelect addNestedSelectPart(final CharSequence _char)
336 {
337 this.parts.add(new NestedSelect(_char));
338 return this;
339 }
340
341 /**
342 * @param _value value to be added as part
343 * @return this
344 */
345 public SQLSelect addValuePart(final Object _value)
346 {
347 this.parts.add(new Value(_value));
348 return this;
349 }
350
351 /**
352 * @param _value add the value that must be escaped
353 * @return this
354 */
355 public SQLSelect addEscapedValuePart(final String _value)
356 {
357 this.parts.add(new EscapedValue(_value));
358 return this;
359 }
360
361 /**
362 * Add a timestamp value to the select.
363 * @param _isoDateTime String to be casted to a timestamp
364 * @return this
365 */
366 public SQLSelect addTimestampValue(final String _isoDateTime)
367 {
368 this.parts.add(new Value(Context.getDbType().getTimestampValue(_isoDateTime)));
369 return this;
370 }
371
372 /**
373 * Add a timestamp value to the select.
374 * @param _value String to be casted to a Boolean
375 * @return this
376 */
377 public SQLSelect addBooleanValue(final Boolean _value)
378 {
379 this.parts.add(new BooleanValue(_value));
380 return this;
381 }
382
383 /**
384 * @return get a new instance of this SQLSelect
385 */
386 public SQLSelect getCopy()
387 {
388 final SQLSelect select = new SQLSelect();
389 select.columns.addAll(this.columns);
390 select.parts.addAll(this.parts);
391 select.fromTables.addAll(this.fromTables);
392 select.distinct = this.distinct;
393 return select;
394 }
395
396 @Override
397 public String toString()
398 {
399 return getSQL();
400 }
401
402 /**
403 *
404 */
405 protected static class FromTable
406 extends SQLSelectPart
407 {
408
409 /** SQL name of the table. */
410 private final String tableName;
411
412 /** Index of the table within the SQL select statement. */
413 private final Integer tableIndex;
414
415 /**
416 * Default constructor.
417 *
418 * @param _tableName name of the SQL table
419 * @param _tableIndex index of the table
420 */
421 protected FromTable(final String _tableName,
422 final Integer _tableIndex)
423 {
424 this.tableName = _tableName;
425 this.tableIndex = _tableIndex;
426 }
427
428 /**
429 * Returns the related {@link #tableName SQL table name} which is
430 * represented by this class.
431 *
432 * @return name of the SQL table
433 * @see #tableName
434 */
435 public String getTableName()
436 {
437 return this.tableName;
438 }
439
440 /**
441 * Returns the related {@link #tableIndex table index} in the SQL select
442 * statement.
443 *
444 * @return table index
445 * @see #tableIndex
446 */
447 public Integer getTableIndex()
448 {
449 return this.tableIndex;
450 }
451
452 @Override
453 public void appendSQL(final StringBuilder _cmd)
454 {
455 appendSQL(true, _cmd);
456 }
457
458 /**
459 * Appends the {@link #tableName name} of this table depending on a
460 * given {@link #tableIndex index} to the SQL select statement in
461 * <code>_cmd</code>. If <code>_first</code> is <i>true</i> a comma ','
462 * is defined in the front.
463 *
464 * @param _first <i>true</i> if first statement and a comma must be
465 * prefixed; otherwise <i>false</i>
466 * @param _cmd string builder used to append SQL statement for this
467 * table
468 */
469 public void appendSQL(final boolean _first,
470 final StringBuilder _cmd)
471 {
472 if (!_first) {
473 _cmd.append(Context.getDbType().getSQLPart(SQLPart.COMMA));
474 }
475 _cmd.append(Context.getDbType().getTableQuote())
476 .append(this.tableName)
477 .append(Context.getDbType().getTableQuote());
478 if (this.tableIndex != null) {
479 _cmd.append(" T").append(this.tableIndex);
480 }
481 }
482 }
483
484 /**
485 *
486 */
487 protected static class FromTableLeftJoin
488 extends SQLSelect.FromTable
489 {
490
491 /**
492 * Name of the columns used for the "left join".
493 */
494 private final String[] columnNames;
495
496 /**
497 * Index of the table from which is joined.
498 */
499 private final int joinTableIndex;
500
501 /**
502 * Name of the columns of the table from which is joined.
503 */
504 private final String[] joinColumnNames;
505
506 /**
507 *
508 * @param _tableName name of the SQL table
509 * @param _tableIndex index of the table used within the SQL select
510 * statement
511 * @param _columnName name of the column of table
512 * <code>_tableName</code> used for "left join"
513 * @param _joinTableIndex index of the table from which is joined
514 * @param _joinColumnName name of the column of the table from which is
515 * joined
516 */
517 protected FromTableLeftJoin(final String _tableName,
518 final Integer _tableIndex,
519 final String _columnName,
520 final int _joinTableIndex,
521 final String _joinColumnName)
522 {
523 super(_tableName, _tableIndex);
524 this.columnNames = new String[] {_columnName};
525 this.joinTableIndex = _joinTableIndex;
526 this.joinColumnNames = new String[] {_joinColumnName};
527 }
528
529 /**
530 * Constructor used to join on more than one column.
531 *
532 * @param _tableName name of the SQL table
533 * @param _tableIndex index of the table used within the
534 * SQL select statement
535 * @param _columnNames names of the columns of table <code>_tableName</code>
536 * used for "left join"
537 * @param _joinTableIndex index of the table from which is joined
538 * @param _joinColumnNames names of the columns of the table from
539 * which is joined
540 */
541 private FromTableLeftJoin(final String _tableName,
542 final Integer _tableIndex,
543 final String[] _columnNames,
544 final int _joinTableIndex,
545 final String[] _joinColumnNames)
546 {
547 super(_tableName, _tableIndex);
548 this.columnNames = _columnNames;
549 this.joinTableIndex = _joinTableIndex;
550 this.joinColumnNames = _joinColumnNames;
551 }
552
553 /**
554 * Appends the SQL statement for this left join.
555 *
556 * @param _first <i>true</i> if first statement and a space must be
557 * prefixed; otherwise <i>false</i>
558 * @param _cmd string builder used to append SQL statement for this left
559 * join
560 */
561 @Override
562 public void appendSQL(final boolean _first,
563 final StringBuilder _cmd)
564 {
565 if (!_first) {
566 _cmd.append(' ');
567 }
568
569 for (int i = 0; i < this.columnNames.length; i++) {
570 if (i == 0) {
571 _cmd.append(Context.getDbType().getSQLPart(getJoin()))
572 .append(" ").append(Context.getDbType().getSQLPart(SQLPart.JOIN)).append(" ")
573 .append(Context.getDbType().getTableQuote())
574 .append(getTableName())
575 .append(Context.getDbType().getTableQuote())
576 .append(" T").append(getTableIndex()).append(" ")
577 .append(Context.getDbType().getSQLPart(SQLPart.ON));
578 } else {
579 _cmd.append(" ").append(Context.getDbType().getSQLPart(SQLPart.AND)).append(" ");
580 }
581 _cmd.append(" T").append(this.joinTableIndex).append('.')
582 .append(Context.getDbType().getColumnQuote())
583 .append(this.joinColumnNames[i])
584 .append(Context.getDbType().getColumnQuote())
585 .append(Context.getDbType().getSQLPart(SQLPart.EQUAL))
586 .append("T").append(getTableIndex()).append('.')
587 .append(Context.getDbType().getColumnQuote())
588 .append(this.columnNames[i])
589 .append(Context.getDbType().getColumnQuote());
590 }
591 }
592
593 /**
594 * @return the join for this class
595 */
596 protected SQLPart getJoin()
597 {
598 return SQLPart.LEFT;
599 }
600 }
601
602 /**
603 * Render an inner join.
604 */
605 protected static class FromTableInnerJoin
606 extends SQLSelect.FromTableLeftJoin
607 {
608 /**
609 *
610 * @param _tableName name of the SQL table
611 * @param _tableIndex index of the table used within the SQL select
612 * statement
613 * @param _columnName name of the column of table
614 * <code>_tableName</code> used for "left join"
615 * @param _joinTableIndex index of the table from which is joined
616 * @param _joinColumnName name of the column of the table from which is
617 * joined
618 */
619 protected FromTableInnerJoin(final String _tableName,
620 final Integer _tableIndex,
621 final String _columnName,
622 final int _joinTableIndex,
623 final String _joinColumnName)
624 {
625 super(_tableName, _tableIndex, _columnName, _joinTableIndex, _joinColumnName);
626 }
627
628 /**
629 *
630 * @param _tableName name of the SQL table
631 * @param _tableIndex index of the table used within the SQL select statement
632 * @param _columnNames name of the column of table <code>_tableName</code> used for "left join"
633 * @param _joinTableIndex index of the table from which is joined
634 * @param _joinColumnNames name of the column of the table from which is joined
635 */
636 private FromTableInnerJoin(final String _tableName,
637 final Integer _tableIndex,
638 final String[] _columnNames,
639 final int _joinTableIndex,
640 final String[] _joinColumnNames)
641 {
642 super(_tableName, _tableIndex, _columnNames, _joinTableIndex, _joinColumnNames);
643 }
644
645 /**
646 * {@inheritDoc}
647 */
648 @Override
649 protected SQLPart getJoin()
650 {
651 return SQLPart.INNER;
652 }
653 }
654
655 /**
656 * Nested Select.
657 */
658 protected static class NestedSelect
659 extends SQLSelectPart
660 {
661 /**
662 * Value.
663 */
664 private final CharSequence value;
665
666 /**
667 * @param _value Value
668 */
669 public NestedSelect(final CharSequence _value)
670 {
671 this.value = _value;
672 }
673
674 @Override
675 public void appendSQL(final StringBuilder _cmd)
676 {
677 _cmd.append(this.value);
678 }
679 }
680
681 /**
682 * Value .
683 */
684 protected static class Value
685 extends SQLSelectPart
686 {
687
688 /**
689 * Value.
690 */
691 private final Object value;
692
693 /**
694 * @param _value Value
695 */
696 public Value(final Object _value)
697 {
698 this.value = _value;
699 }
700
701 @Override
702 public void appendSQL(final StringBuilder _cmd)
703 {
704 _cmd.append(this.value);
705 }
706
707 @Override
708 public String toString()
709 {
710 return this.value.toString();
711 }
712 }
713
714 /**
715 * Value to be escaped.
716 */
717 protected static class EscapedValue
718 extends SQLSelectPart
719 {
720
721 /**
722 * Value.
723 */
724 private final String value;
725
726 /**
727 * @param _value Value
728 */
729 public EscapedValue(final String _value)
730 {
731 this.value = _value;
732 }
733
734 @Override
735 public void appendSQL(final StringBuilder _cmd)
736 {
737 _cmd.append(Context.getDbType().escapeForWhere(this.value));
738 }
739
740 @Override
741 public String toString()
742 {
743 return Context.getDbType().escapeForWhere(this.value);
744 }
745 }
746
747 /**
748 * Value to be escaped.
749 */
750 protected static class BooleanValue
751 extends SQLSelectPart
752 {
753
754 /**
755 * Value.
756 */
757 private final Boolean value;
758
759 /**
760 * @param _value Value
761 */
762 public BooleanValue(final Boolean _value)
763 {
764 this.value = _value;
765 }
766
767 @Override
768 public void appendSQL(final StringBuilder _cmd)
769 {
770 _cmd.append(Context.getDbType().getBooleanValue(this.value));
771 }
772
773 @Override
774 public String toString()
775 {
776 final String ret = Context.getDbType().getBooleanValue(this.value).toString();
777 return ret;
778 }
779 }
780
781 /**
782 *
783 */
784 public static class SQLSelectPart
785 {
786
787 /**
788 * Part.
789 */
790 private SQLPart sqlpart;
791
792 /**
793 * Constructor.
794 * @param _part SQLPart
795 */
796 public SQLSelectPart(final SQLPart _part)
797 {
798 this.sqlpart = _part;
799 }
800
801 /**
802 * Constructor.
803 */
804 protected SQLSelectPart()
805 {
806 }
807
808 /**
809 * @param _cmd StringBuilder to append to
810 */
811 public void appendSQL(final StringBuilder _cmd)
812 {
813 _cmd.append(Context.getDbType().getSQLPart(this.sqlpart));
814 }
815
816 @Override
817 public String toString()
818 {
819 return Context.getDbType().getSQLPart(this.sqlpart);
820 }
821 }
822
823 /**
824 * Column.
825 */
826 protected static class Column
827 extends SQLSelect.SQLSelectPart
828 {
829 /**
830 * Index of the table in the select statement where this column is
831 * defined.
832 */
833 private final Integer tableIndex;
834
835 /** SQL name of the column. */
836 private final String columnName;
837
838 /**
839 * Default constructor.
840 *
841 * @param _tableIndex related index of the table
842 * @param _columnName SQL name of the column
843 */
844 protected Column(final Integer _tableIndex,
845 final String _columnName)
846 {
847 this.tableIndex = _tableIndex;
848 this.columnName = _columnName;
849 }
850
851 /**
852 *
853 * @param _cmd string builder used to append SQL statement for this
854 * column
855 */
856 @Override
857 public void appendSQL(final StringBuilder _cmd)
858 {
859 if (this.tableIndex != null) {
860 _cmd.append("T").append(this.tableIndex).append(".");
861 }
862 _cmd.append(Context.getDbType().getColumnQuote())
863 .append(this.columnName)
864 .append(Context.getDbType().getColumnQuote());
865 }
866
867 @Override
868 public String toString()
869 {
870 final StringBuilder cmd = new StringBuilder();
871 appendSQL(cmd);
872 return cmd.toString();
873 }
874 }
875 }