Skip to content

Commit 4fb1cfb

Browse files
manticore-projectsmanticore-projects
committed
refactor: RETURNING clause
- supports Oracle's `RETURN ... INTO ...` - fixes #1780 - fixes #686 - Special Oracle tests `insert11.sql` and `insert12.sql`
1 parent daa56ed commit 4fb1cfb

23 files changed

Lines changed: 277 additions & 132 deletions

File tree

‎src/main/java/net/sf/jsqlparser/expression/RangeExpression.java‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,12 @@
1+
/*-
2+
* #%L
3+
* JSQLParser library
4+
* %%
5+
* Copyright (C) 2004 - 2023 JSQLParser
6+
* %%
7+
* Dual licensed under GNU LGPL 2.1 or Apache License 2.0
8+
* #L%
9+
*/
110
package net.sf.jsqlparser.expression;
211

312
import net.sf.jsqlparser.parser.ASTNodeAccessImpl;

‎src/main/java/net/sf/jsqlparser/expression/operators/relational/ExpressionList.java‎

Lines changed: 12 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,6 @@
1111

1212
import net.sf.jsqlparser.expression.Expression;
1313
import net.sf.jsqlparser.expression.ExpressionVisitor;
14-
import net.sf.jsqlparser.statement.select.PlainSelect;
1514
import net.sf.jsqlparser.parser.SimpleNode;
1615

1716
import java.io.Serializable;
@@ -65,7 +64,7 @@ public ExpressionList addExpressions(T... expressions) {
6564
return this;
6665
}
6766

68-
public ExpressionList addExpressions(Collection<T> expressions) {
67+
public ExpressionList<?> addExpressions(Collection<T> expressions) {
6968
addAll(expressions);
7069
return this;
7170
}
@@ -80,9 +79,19 @@ public ExpressionList withExpressions(Collection<T> expressions) {
8079
return addExpressions(expressions);
8180
}
8281

82+
public StringBuilder appendTo(StringBuilder builder) {
83+
for (int i = 0; i < size(); i++) {
84+
if (i > 0) {
85+
builder.append(", ");
86+
}
87+
builder.append(get(i));
88+
}
89+
return builder;
90+
}
91+
8392
@Override
8493
public String toString() {
85-
return PlainSelect.getStringList(this, true, false);
94+
return appendTo(new StringBuilder()).toString();
8695
}
8796

8897
@Override

‎src/main/java/net/sf/jsqlparser/expression/operators/relational/MemberOfExpression.java‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,12 @@
1+
/*-
2+
* #%L
3+
* JSQLParser library
4+
* %%
5+
* Copyright (C) 2004 - 2023 JSQLParser
6+
* %%
7+
* Dual licensed under GNU LGPL 2.1 or Apache License 2.0
8+
* #L%
9+
*/
110
package net.sf.jsqlparser.expression.operators.relational;
211

312
import net.sf.jsqlparser.expression.Expression;

‎src/main/java/net/sf/jsqlparser/statement/ReturningClause.java‎

Lines changed: 97 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,97 @@
1+
/*-
2+
* #%L
3+
* JSQLParser library
4+
* %%
5+
* Copyright (C) 2004 - 2023 JSQLParser
6+
* %%
7+
* Dual licensed under GNU LGPL 2.1 or Apache License 2.0
8+
* #L%
9+
*/
10+
package net.sf.jsqlparser.statement;
11+
12+
import net.sf.jsqlparser.statement.select.SelectItem;
13+
14+
import java.util.ArrayList;
15+
import java.util.List;
16+
17+
/**
18+
* RETURNING clause according to
19+
* {@see https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/DELETE.html#GUID-156845A5-B626-412B-9F95-8869B988ABD7
20+
* } Part of UPDATE, INSERT, DELETE statements
21+
*/
22+
23+
public class ReturningClause extends ArrayList<SelectItem<?>> {
24+
enum Keyword {
25+
RETURN, RETURNING;
26+
27+
public static Keyword from(String keyword) {
28+
return Enum.valueOf(Keyword.class, keyword.toUpperCase());
29+
}
30+
}
31+
32+
private Keyword keyword;
33+
34+
/**
35+
* List of output targets like Table or UserVariable
36+
*/
37+
private final List<Object> dataItems;
38+
39+
public ReturningClause(Keyword keyword, List<SelectItem<?>> selectItems,
40+
List<Object> dataItems) {
41+
this.keyword = keyword;
42+
this.addAll(selectItems);
43+
this.dataItems = dataItems;
44+
}
45+
46+
public ReturningClause(String keyword, List<SelectItem<?>> selectItems,
47+
List<Object> dataItems) {
48+
this(Keyword.from(keyword), selectItems, dataItems);
49+
}
50+
51+
public ReturningClause(Keyword keyword, List<SelectItem<?>> selectItems) {
52+
this(keyword, selectItems, null);
53+
}
54+
55+
public ReturningClause(String keyword, List<SelectItem<?>> selectItems) {
56+
this(Keyword.valueOf(keyword), selectItems, null);
57+
}
58+
59+
public Keyword getKeyword() {
60+
return keyword;
61+
}
62+
63+
public ReturningClause setKeyword(Keyword keyword) {
64+
this.keyword = keyword;
65+
return this;
66+
}
67+
68+
public List<?> getDataItems() {
69+
return dataItems;
70+
}
71+
72+
public StringBuilder appendTo(StringBuilder builder) {
73+
builder.append(" ").append(keyword).append(" ");
74+
for (int i = 0; i < size(); i++) {
75+
if (i > 0) {
76+
builder.append(", ");
77+
}
78+
builder.append(get(i));
79+
}
80+
81+
if (dataItems != null && dataItems.size() > 0) {
82+
builder.append(" INTO ");
83+
for (int i = 0; i < dataItems.size(); i++) {
84+
if (i > 0) {
85+
builder.append(" ,");
86+
}
87+
builder.append(dataItems.get(i));
88+
}
89+
}
90+
return builder;
91+
}
92+
93+
@Override
94+
public String toString() {
95+
return appendTo(new StringBuilder()).toString();
96+
}
97+
}

‎src/main/java/net/sf/jsqlparser/statement/delete/Delete.java‎

Lines changed: 9 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -13,13 +13,13 @@
1313
import net.sf.jsqlparser.expression.OracleHint;
1414
import net.sf.jsqlparser.schema.Table;
1515
import net.sf.jsqlparser.statement.OutputClause;
16+
import net.sf.jsqlparser.statement.ReturningClause;
1617
import net.sf.jsqlparser.statement.Statement;
1718
import net.sf.jsqlparser.statement.StatementVisitor;
1819
import net.sf.jsqlparser.statement.select.Join;
1920
import net.sf.jsqlparser.statement.select.Limit;
2021
import net.sf.jsqlparser.statement.select.OrderByElement;
2122
import net.sf.jsqlparser.statement.select.PlainSelect;
22-
import net.sf.jsqlparser.statement.select.SelectItem;
2323
import net.sf.jsqlparser.statement.select.WithItem;
2424

2525
import java.util.ArrayList;
@@ -46,7 +46,8 @@ public class Delete implements Statement {
4646
private DeleteModifierPriority modifierPriority;
4747
private boolean modifierIgnore;
4848
private boolean modifierQuick;
49-
private List<SelectItem<?>> returningExpressionList = null;
49+
50+
private ReturningClause returningClause;
5051
private OutputClause outputClause;
5152

5253
public OutputClause getOutputClause() {
@@ -57,16 +58,12 @@ public void setOutputClause(OutputClause outputClause) {
5758
this.outputClause = outputClause;
5859
}
5960

60-
public List<SelectItem<?>> getReturningExpressionList() {
61-
return returningExpressionList;
62-
}
63-
64-
public void setReturningExpressionList(List<SelectItem<?>> returningExpressionList) {
65-
this.returningExpressionList = returningExpressionList;
61+
public ReturningClause getReturningClause() {
62+
return returningClause;
6663
}
6764

68-
public Delete withReturningExpressionList(List<SelectItem<?>> returningExpressionList) {
69-
this.returningExpressionList = returningExpressionList;
65+
public Delete setReturningClause(ReturningClause returningClause) {
66+
this.returningClause = returningClause;
7067
return this;
7168
}
7269

@@ -248,9 +245,8 @@ public String toString() {
248245
b.append(limit);
249246
}
250247

251-
if (getReturningExpressionList() != null) {
252-
b.append(" RETURNING ")
253-
.append(PlainSelect.getStringList(getReturningExpressionList(), true, false));
248+
if (returningClause != null) {
249+
returningClause.appendTo(b);
254250
}
255251

256252
return b.toString();

‎src/main/java/net/sf/jsqlparser/statement/insert/Insert.java‎

Lines changed: 9 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -14,17 +14,16 @@
1414
import net.sf.jsqlparser.schema.Column;
1515
import net.sf.jsqlparser.schema.Table;
1616
import net.sf.jsqlparser.statement.OutputClause;
17+
import net.sf.jsqlparser.statement.ReturningClause;
1718
import net.sf.jsqlparser.statement.Statement;
1819
import net.sf.jsqlparser.statement.StatementVisitor;
1920
import net.sf.jsqlparser.statement.select.PlainSelect;
2021
import net.sf.jsqlparser.statement.select.Select;
21-
import net.sf.jsqlparser.statement.select.SelectItem;
2222
import net.sf.jsqlparser.statement.select.SetOperationList;
2323
import net.sf.jsqlparser.statement.select.Values;
2424
import net.sf.jsqlparser.statement.select.WithItem;
2525
import net.sf.jsqlparser.statement.update.UpdateSet;
2626

27-
import java.util.ArrayList;
2827
import java.util.Arrays;
2928
import java.util.Collection;
3029
import java.util.Iterator;
@@ -41,7 +40,7 @@ public class Insert implements Statement {
4140
private List<UpdateSet> duplicateUpdateSets = null;
4241
private InsertModifierPriority modifierPriority = null;
4342
private boolean modifierIgnore = false;
44-
private List<SelectItem<?>> returningExpressionList = null;
43+
private ReturningClause returningClause;
4544
private List<UpdateSet> setUpdateSets = null;
4645
private List<WithItem> withItemsList;
4746
private OutputClause outputClause;
@@ -108,12 +107,13 @@ public boolean isUseValues() {
108107
return select != null && select instanceof Values;
109108
}
110109

111-
public List<SelectItem<?>> getReturningExpressionList() {
112-
return returningExpressionList;
110+
public ReturningClause getReturningClause() {
111+
return returningClause;
113112
}
114113

115-
public void setReturningExpressionList(List<SelectItem<?>> returningExpressionList) {
116-
this.returningExpressionList = returningExpressionList;
114+
public Insert setReturningClause(ReturningClause returningClause) {
115+
this.returningClause = returningClause;
116+
return this;
117117
}
118118

119119
public Select getSelect() {
@@ -257,9 +257,8 @@ public String toString() {
257257
conflictAction.appendTo(sql);
258258
}
259259

260-
if (getReturningExpressionList() != null) {
261-
sql.append(" RETURNING ")
262-
.append(PlainSelect.getStringList(getReturningExpressionList(), true, false));
260+
if (returningClause != null) {
261+
returningClause.appendTo(sql);
263262
}
264263

265264
return sql.toString();
@@ -285,11 +284,6 @@ public Insert withModifierIgnore(boolean modifierIgnore) {
285284
return this;
286285
}
287286

288-
public Insert withReturningExpressionList(List<SelectItem<?>> returningExpressionList) {
289-
this.setReturningExpressionList(returningExpressionList);
290-
return this;
291-
}
292-
293287
public Insert withTable(Table table) {
294288
this.setTable(table);
295289
return this;
@@ -310,16 +304,4 @@ public Insert addColumns(Collection<Column> columns) {
310304
collection.addAll(columns);
311305
return this.withColumns(collection);
312306
}
313-
314-
public Insert addReturningExpressionList(SelectItem<?>... returningExpressions) {
315-
return this.addReturningExpressionList(Arrays.asList(returningExpressions));
316-
}
317-
318-
public Insert addReturningExpressionList(
319-
Collection<? extends SelectItem<?>> returningExpressions) {
320-
List<SelectItem<?>> collection =
321-
Optional.ofNullable(getReturningExpressionList()).orElseGet(ArrayList::new);
322-
collection.addAll(returningExpressions);
323-
return this.withReturningExpressionList(collection);
324-
}
325307
}

‎src/main/java/net/sf/jsqlparser/statement/update/Update.java‎

Lines changed: 9 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,7 @@
1414
import net.sf.jsqlparser.schema.Column;
1515
import net.sf.jsqlparser.schema.Table;
1616
import net.sf.jsqlparser.statement.OutputClause;
17+
import net.sf.jsqlparser.statement.ReturningClause;
1718
import net.sf.jsqlparser.statement.Statement;
1819
import net.sf.jsqlparser.statement.StatementVisitor;
1920
import net.sf.jsqlparser.statement.select.FromItem;
@@ -22,7 +23,6 @@
2223
import net.sf.jsqlparser.statement.select.OrderByElement;
2324
import net.sf.jsqlparser.statement.select.PlainSelect;
2425
import net.sf.jsqlparser.statement.select.Select;
25-
import net.sf.jsqlparser.statement.select.SelectItem;
2626
import net.sf.jsqlparser.statement.select.WithItem;
2727

2828
import java.util.ArrayList;
@@ -46,7 +46,7 @@ public class Update implements Statement {
4646
private OracleHint oracleHint = null;
4747
private List<OrderByElement> orderByElements;
4848
private Limit limit;
49-
private List<SelectItem<?>> returningExpressionList = null;
49+
private ReturningClause returningClause;
5050
private UpdateModifierPriority modifierPriority;
5151
private boolean modifierIgnore;
5252

@@ -245,12 +245,13 @@ public Limit getLimit() {
245245
return limit;
246246
}
247247

248-
public List<SelectItem<?>> getReturningExpressionList() {
249-
return returningExpressionList;
248+
public ReturningClause getReturningClause() {
249+
return returningClause;
250250
}
251251

252-
public void setReturningExpressionList(List<SelectItem<?>> returningExpressionList) {
253-
this.returningExpressionList = returningExpressionList;
252+
public Update setReturningClause(ReturningClause returningClause) {
253+
this.returningClause = returningClause;
254+
return this;
254255
}
255256

256257
public UpdateModifierPriority getModifierPriority() {
@@ -335,9 +336,8 @@ public String toString() {
335336
b.append(limit);
336337
}
337338

338-
if (getReturningExpressionList() != null) {
339-
b.append(" RETURNING ")
340-
.append(PlainSelect.getStringList(getReturningExpressionList(), true, false));
339+
if (returningClause != null) {
340+
returningClause.appendTo(b);
341341
}
342342

343343
return b.toString();
@@ -388,11 +388,6 @@ public Update withLimit(Limit limit) {
388388
return this;
389389
}
390390

391-
public Update withReturningExpressionList(List<SelectItem<?>> returningExpressionList) {
392-
this.setReturningExpressionList(returningExpressionList);
393-
return this;
394-
}
395-
396391
public Update withWhere(Expression where) {
397392
this.setWhere(where);
398393
return this;
@@ -478,21 +473,6 @@ public Update addOrderByElements(Collection<? extends OrderByElement> orderByEle
478473
return this.withOrderByElements(collection);
479474
}
480475

481-
public Update addReturningExpressionList(SelectItem<?>... returningExpressionList) {
482-
List<SelectItem<?>> collection =
483-
Optional.ofNullable(getReturningExpressionList()).orElseGet(ArrayList::new);
484-
Collections.addAll(collection, returningExpressionList);
485-
return this.withReturningExpressionList(collection);
486-
}
487-
488-
public Update addReturningExpressionList(
489-
Collection<? extends SelectItem<?>> returningExpressionList) {
490-
List<SelectItem<?>> collection =
491-
Optional.ofNullable(getReturningExpressionList()).orElseGet(ArrayList::new);
492-
collection.addAll(returningExpressionList);
493-
return this.withReturningExpressionList(collection);
494-
}
495-
496476
public <E extends Expression> E getWhere(Class<E> type) {
497477
return type.cast(getWhere());
498478
}

0 commit comments

Comments
 (0)