Skip to content

Commit dcd39e6

Browse files
manticore-projectsmanticore-projects
committed
refactor: Merge REPLACE into UPSERT
fixes #1706
1 parent f922e0d commit dcd39e6

14 files changed

Lines changed: 321 additions & 309 deletions

File tree

‎src/main/java/net/sf/jsqlparser/parser/feature/Feature.java‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,8 +21,8 @@
2121
import net.sf.jsqlparser.statement.DeclareStatement;
2222
import net.sf.jsqlparser.statement.DescribeStatement;
2323
import net.sf.jsqlparser.statement.ExplainStatement;
24-
import net.sf.jsqlparser.statement.SetStatement;
2524
import net.sf.jsqlparser.statement.ResetStatement;
25+
import net.sf.jsqlparser.statement.SetStatement;
2626
import net.sf.jsqlparser.statement.ShowColumnsStatement;
2727
import net.sf.jsqlparser.statement.ShowStatement;
2828
import net.sf.jsqlparser.statement.UseStatement;
@@ -447,6 +447,7 @@ public enum Feature {
447447
*
448448
* @see Replace
449449
*/
450+
@Deprecated
450451
replace,
451452
/**
452453
* SQL "DROP" statement is allowed

‎src/main/java/net/sf/jsqlparser/statement/replace/Replace.java‎

Lines changed: 29 additions & 133 deletions
Original file line numberDiff line numberDiff line change
@@ -9,179 +9,75 @@
99
*/
1010
package net.sf.jsqlparser.statement.replace;
1111

12+
import net.sf.jsqlparser.expression.Expression;
13+
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
14+
import net.sf.jsqlparser.statement.upsert.Upsert;
15+
1216
import java.util.ArrayList;
1317
import java.util.Collection;
1418
import java.util.Collections;
1519
import java.util.List;
1620
import java.util.Optional;
17-
import net.sf.jsqlparser.expression.Expression;
18-
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
19-
import net.sf.jsqlparser.schema.Column;
20-
import net.sf.jsqlparser.schema.Table;
21-
import net.sf.jsqlparser.statement.Statement;
22-
import net.sf.jsqlparser.statement.StatementVisitor;
23-
import net.sf.jsqlparser.statement.select.PlainSelect;
24-
25-
public class Replace implements Statement {
26-
27-
private Table table;
28-
private List<Column> columns;
29-
private ItemsList itemsList;
30-
private List<Expression> expressions;
31-
private boolean useValues = true;
32-
private boolean useIntoTables = false;
33-
34-
@Override
35-
public void accept(StatementVisitor statementVisitor) {
36-
statementVisitor.visit(this);
37-
}
3821

39-
public Table getTable() {
40-
return table;
41-
}
22+
/**
23+
* Not Standard compliant REPLACE Statement
24+
* @deprecated
25+
* This class has been merged into the UPSERT statement and should not longer been used.
26+
* <p> Use {@link Upsert} instead.
27+
*
28+
*/
4229

43-
public void setTable(Table name) {
44-
table = name;
45-
}
30+
@Deprecated
31+
public class Replace extends Upsert {
4632

33+
@Deprecated
4734
public boolean isUseIntoTables() {
48-
return useIntoTables;
35+
return super.isUsingInto();
4936
}
5037

38+
@Deprecated
5139
public void setUseIntoTables(boolean useIntoTables) {
52-
this.useIntoTables = useIntoTables;
53-
}
54-
55-
public List<Column> getColumns() {
56-
return columns;
57-
}
58-
59-
public ItemsList getItemsList() {
60-
return itemsList;
61-
}
62-
63-
public void setColumns(List<Column> list) {
64-
columns = list;
65-
}
66-
67-
public void setItemsList(ItemsList list) {
68-
itemsList = list;
40+
super.setUsingInto( useIntoTables );
6941
}
7042

43+
@Deprecated
7144
/**
7245
* A list of {@link net.sf.jsqlparser.expression.Expression}s (from a "REPLACE mytab SET
7346
* col1=exp1, col2=exp2"). <br>
7447
* it is null in case of a "REPLACE mytab (col1, col2) [...]"
7548
*/
7649
public List<Expression> getExpressions() {
77-
return expressions;
50+
return super.getSetExpressions();
7851
}
7952

53+
@Deprecated
8054
public void setExpressions(List<Expression> list) {
81-
expressions = list;
82-
}
83-
84-
public boolean isUseValues() {
85-
return useValues;
86-
}
87-
88-
public void setUseValues(boolean useValues) {
89-
this.useValues = useValues;
90-
}
91-
92-
@Override
93-
public String toString() {
94-
StringBuilder sql = new StringBuilder();
95-
sql.append("REPLACE ");
96-
if (isUseIntoTables()) {
97-
sql.append("INTO ");
98-
}
99-
sql.append(table);
100-
101-
if (expressions != null && columns != null) {
102-
// the SET col1=exp1, col2=exp2 case
103-
sql.append(" SET ");
104-
// each element from expressions match up with a column from columns.
105-
for (int i = 0, s = columns.size(); i < s; i++) {
106-
sql.append(columns.get(i)).append("=").append(expressions.get(i));
107-
sql.append( i < s - 1
108-
? ", "
109-
: "" );
110-
}
111-
} else if (columns != null) {
112-
// the REPLACE mytab (col1, col2) [...] case
113-
sql.append(" ").append(PlainSelect.getStringList(columns, true, true));
114-
}
115-
116-
if (itemsList != null) {
117-
// REPLACE mytab SELECT * FROM mytab2
118-
// or VALUES ('as', ?, 565)
119-
120-
if (useValues) {
121-
sql.append(" VALUES");
122-
}
123-
124-
sql.append(" ").append(itemsList);
125-
}
126-
127-
return sql.toString();
128-
}
129-
130-
public Replace withUseValues(boolean useValues) {
131-
this.setUseValues(useValues);
132-
return this;
55+
super.setItemsList( new ExpressionList(list) );
13356
}
13457

58+
@Deprecated
13559
public Replace withUseIntoTables(boolean useIntoTables) {
136-
this.setUseIntoTables(useIntoTables);
137-
return this;
138-
}
139-
140-
public Replace withTable(Table table) {
141-
this.setTable(table);
142-
return this;
143-
}
144-
145-
public Replace withColumns(List<Column> columns) {
146-
this.setColumns(columns);
147-
return this;
148-
}
149-
150-
public Replace withItemsList(ItemsList itemsList) {
151-
this.setItemsList(itemsList);
60+
super.setUsingInto(useIntoTables);
15261
return this;
15362
}
15463

64+
@Deprecated
15565
public Replace withExpressions(List<Expression> expressions) {
156-
this.setExpressions(expressions);
66+
super.setItemsList( new ExpressionList(expressions) );
15767
return this;
15868
}
15969

160-
public Replace addColumns(Column... columns) {
161-
List<Column> collection = Optional.ofNullable(getColumns()).orElseGet(ArrayList::new);
162-
Collections.addAll(collection, columns);
163-
return this.withColumns(collection);
164-
}
165-
166-
public Replace addColumns(Collection<? extends Column> columns) {
167-
List<Column> collection = Optional.ofNullable(getColumns()).orElseGet(ArrayList::new);
168-
collection.addAll(columns);
169-
return this.withColumns(collection);
170-
}
171-
70+
@Deprecated
17271
public Replace addExpressions(Expression... expressions) {
173-
List<Expression> collection = Optional.ofNullable(getExpressions()).orElseGet(ArrayList::new);
72+
List<Expression> collection = Optional.ofNullable( super.getSetExpressions() ).orElseGet(ArrayList::new);
17473
Collections.addAll(collection, expressions);
17574
return this.withExpressions(collection);
17675
}
17776

77+
@Deprecated
17878
public Replace addExpressions(Collection<? extends Expression> expressions) {
179-
List<Expression> collection = Optional.ofNullable(getExpressions()).orElseGet(ArrayList::new);
79+
List<Expression> collection = Optional.ofNullable( super.getSetExpressions() ).orElseGet(ArrayList::new);
18080
collection.addAll(expressions);
18181
return this.withExpressions(collection);
18282
}
183-
184-
public <E extends ItemsList> E getItemsList(Class<E> type) {
185-
return type.cast(getItemsList());
186-
}
18783
}

‎src/main/java/net/sf/jsqlparser/statement/upsert/Upsert.java‎

Lines changed: 102 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -9,12 +9,8 @@
99
*/
1010
package net.sf.jsqlparser.statement.upsert;
1111

12-
import java.util.ArrayList;
13-
import java.util.Collection;
14-
import java.util.Collections;
15-
import java.util.List;
16-
import java.util.Optional;
1712
import net.sf.jsqlparser.expression.Expression;
13+
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
1814
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
1915
import net.sf.jsqlparser.schema.Column;
2016
import net.sf.jsqlparser.schema.Table;
@@ -23,6 +19,12 @@
2319
import net.sf.jsqlparser.statement.select.PlainSelect;
2420
import net.sf.jsqlparser.statement.select.Select;
2521

22+
import java.util.ArrayList;
23+
import java.util.Collection;
24+
import java.util.Collections;
25+
import java.util.List;
26+
import java.util.Optional;
27+
2628
public class Upsert implements Statement {
2729

2830
private Table table;
@@ -35,10 +37,40 @@ public class Upsert implements Statement {
3537
private List<Column> duplicateUpdateColumns;
3638
private List<Expression> duplicateUpdateExpressionList;
3739

40+
private UpsertType upsertType = UpsertType.UPSERT;
41+
42+
private boolean isUsingInto;
43+
3844
@Override
3945
public void accept(StatementVisitor statementVisitor) {
4046
statementVisitor.visit(this);
4147
}
48+
49+
public UpsertType getUpsertType() {
50+
return upsertType;
51+
}
52+
53+
public void setUpsertType(UpsertType upsertType) {
54+
this.upsertType=upsertType;
55+
}
56+
57+
public Upsert withUpsertType(UpsertType upsertType) {
58+
setUpsertType(upsertType);
59+
return this;
60+
}
61+
62+
public boolean isUsingInto() {
63+
return isUsingInto;
64+
}
65+
66+
public void setUsingInto(boolean useInto) {
67+
this.isUsingInto = useInto;
68+
}
69+
70+
public Upsert withUsingInto(boolean useInto) {
71+
setUsingInto(useInto);
72+
return this;
73+
}
4274

4375
public void setTable(Table name) {
4476
table = name;
@@ -63,6 +95,15 @@ public void setItemsList(ItemsList list) {
6395
public ItemsList getItemsList() {
6496
return itemsList;
6597
}
98+
99+
public List<Expression> getSetExpressions() {
100+
List<Expression> expressions = null;
101+
if (itemsList instanceof ExpressionList) {
102+
ExpressionList expressionList = (ExpressionList) itemsList;
103+
expressions= expressionList.getExpressions();
104+
}
105+
return expressions;
106+
}
66107

67108
public void setUseValues(boolean useValues) {
68109
this.useValues = useValues;
@@ -116,27 +157,67 @@ public List<Expression> getDuplicateUpdateExpressionList() {
116157
@SuppressWarnings({"PMD.CyclomaticComplexity"})
117158
public String toString() {
118159
StringBuilder sb = new StringBuilder();
119-
120-
sb.append("UPSERT INTO ");
121-
sb.append(table).append(" ");
122-
if (columns != null) {
123-
sb.append(PlainSelect.getStringList(columns, true, true)).append(" ");
124-
}
125-
if (useValues) {
126-
sb.append("VALUES ");
160+
161+
switch (upsertType) {
162+
case UPSERT:
163+
sb.append("UPSERT ");
164+
break;
165+
case REPLACE:
166+
case REPLACE_SET:
167+
sb.append("REPLACE ");
168+
break;
169+
case INSERT_OR_ABORT:
170+
sb.append("INSERT OR ABORT ");
171+
break;
172+
case INSERT_OR_FAIL:
173+
sb.append("INSERT OR FAIL ");
174+
break;
175+
case INSERT_OR_IGNORE:
176+
sb.append("INSERT OR IGNORE ");
177+
break;
178+
case INSERT_OR_REPLACE:
179+
sb.append("INSERT OR REPLACE ");
180+
break;
181+
case INSERT_OR_ROLLBACK:
182+
sb.append("INSERT OR ROLLBACK ");
183+
break;
127184
}
128185

129-
if (itemsList != null) {
130-
sb.append(itemsList);
186+
if (isUsingInto) {
187+
sb.append("INTO ");
188+
}
189+
sb.append(table).append(" ");
190+
191+
if (upsertType==UpsertType.REPLACE_SET) {
192+
sb.append("SET ");
193+
// each element from expressions match up with a column from columns.
194+
List<Expression> expressions = getSetExpressions();
195+
for (int i = 0, s = columns.size(); i < s; i++) {
196+
sb.append(columns.get(i)).append("=").append(expressions.get(i));
197+
sb.append( i < s - 1
198+
? ", "
199+
: "" );
200+
}
131201
} else {
132-
if (useSelectBrackets) {
133-
sb.append("(");
202+
if (columns != null) {
203+
sb.append(PlainSelect.getStringList(columns, true, true)).append(" ");
134204
}
135-
if (select != null) {
136-
sb.append(select);
205+
if (useValues) {
206+
sb.append("VALUES ");
137207
}
138-
if (useSelectBrackets) {
139-
sb.append(")");
208+
209+
if (itemsList != null) {
210+
sb.append(itemsList);
211+
} else {
212+
if (useSelectBrackets) {
213+
sb.append("(");
214+
}
215+
if (select != null) {
216+
sb.append(select);
217+
}
218+
if (useSelectBrackets) {
219+
sb.append(")");
220+
}
140221
}
141222
}
142223

0 commit comments

Comments
 (0)