refactor: Merge REPLACE into UPSERT

fixes #1706
JSQLParser · wumpz · Jan 20, 2023 · Jan 11, 2023 · Jan 11, 2023 · Jan 11, 2023
commit dcd39e60c1856680af17b4ccb48bfa5a281a6134
diff --git a/src/main/java/net/sf/jsqlparser/parser/feature/Feature.java b/src/main/java/net/sf/jsqlparser/parser/feature/Feature.java
@@ -21,8 +21,8 @@
 import net.sf.jsqlparser.statement.DeclareStatement;
 import net.sf.jsqlparser.statement.DescribeStatement;
 import net.sf.jsqlparser.statement.ExplainStatement;
-import net.sf.jsqlparser.statement.SetStatement;
 import net.sf.jsqlparser.statement.ResetStatement;
+import net.sf.jsqlparser.statement.SetStatement;
 import net.sf.jsqlparser.statement.ShowColumnsStatement;
 import net.sf.jsqlparser.statement.ShowStatement;
 import net.sf.jsqlparser.statement.UseStatement;
@@ -447,6 +447,7 @@ public enum Feature {
      *
      * @see Replace
      */
+    @Deprecated
     replace,
     /**
      * SQL "DROP" statement is allowed

diff --git a/src/main/java/net/sf/jsqlparser/statement/replace/Replace.java b/src/main/java/net/sf/jsqlparser/statement/replace/Replace.java
@@ -9,179 +9,75 @@
  */
 package net.sf.jsqlparser.statement.replace;
 
+import net.sf.jsqlparser.expression.Expression;
+import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
+import net.sf.jsqlparser.statement.upsert.Upsert;
+
 import java.util.ArrayList;
 import java.util.Collection;
 import java.util.Collections;
 import java.util.List;
 import java.util.Optional;
-import net.sf.jsqlparser.expression.Expression;
-import net.sf.jsqlparser.expression.operators.relational.ItemsList;
-import net.sf.jsqlparser.schema.Column;
-import net.sf.jsqlparser.schema.Table;
-import net.sf.jsqlparser.statement.Statement;
-import net.sf.jsqlparser.statement.StatementVisitor;
-import net.sf.jsqlparser.statement.select.PlainSelect;
-
-public class Replace implements Statement {
-
-    private Table table;
-    private List<Column> columns;
-    private ItemsList itemsList;
-    private List<Expression> expressions;
-    private boolean useValues = true;
-    private boolean useIntoTables = false;
-
-    @Override
-    public void accept(StatementVisitor statementVisitor) {
-        statementVisitor.visit(this);
-    }
 
-    public Table getTable() {
-        return table;
-    }
+/**
+ * Not Standard compliant REPLACE Statement
+ * @deprecated
+ * This class has been merged into the UPSERT statement and should not longer been used.
+ * <p> Use {@link Upsert} instead.
+ *
+ */
 
-    public void setTable(Table name) {
-        table = name;
-    }
+@Deprecated
+public class Replace extends Upsert {
 
+    @Deprecated
     public boolean isUseIntoTables() {
-        return useIntoTables;
+        return super.isUsingInto();
     }
 
+    @Deprecated
     public void setUseIntoTables(boolean useIntoTables) {
-        this.useIntoTables = useIntoTables;
-    }
-
-    public List<Column> getColumns() {
-        return columns;
-    }
-
-    public ItemsList getItemsList() {
-        return itemsList;
-    }
-
-    public void setColumns(List<Column> list) {
-        columns = list;
-    }
-
-    public void setItemsList(ItemsList list) {
-        itemsList = list;
+        super.setUsingInto( useIntoTables );
     }
 
+    @Deprecated
     /**
      * A list of {@link net.sf.jsqlparser.expression.Expression}s (from a "REPLACE mytab SET
      * col1=exp1, col2=exp2"). <br>
      * it is null in case of a "REPLACE mytab (col1, col2) [...]"
      */
     public List<Expression> getExpressions() {
-        return expressions;
+        return super.getSetExpressions();
     }
 
+    @Deprecated
     public void setExpressions(List<Expression> list) {
-        expressions = list;
-    }
-
-    public boolean isUseValues() {
-        return useValues;
-    }
-
-    public void setUseValues(boolean useValues) {
-        this.useValues = useValues;
-    }
-
-    @Override
-    public String toString() {
-        StringBuilder sql = new StringBuilder();
-        sql.append("REPLACE ");
-        if (isUseIntoTables()) {
-            sql.append("INTO ");
-        }
-        sql.append(table);
-
-        if (expressions != null && columns != null) {
-            // the SET col1=exp1, col2=exp2 case
-            sql.append(" SET ");
-            // each element from expressions match up with a column from columns.
-            for (int i = 0, s = columns.size(); i < s; i++) {
-                sql.append(columns.get(i)).append("=").append(expressions.get(i));
-                sql.append( i < s - 1 
-                                      ? ", " 
-                                      : "" );
-            }
-        } else if (columns != null) {
-            // the REPLACE mytab (col1, col2) [...] case
-            sql.append(" ").append(PlainSelect.getStringList(columns, true, true));
-        }
-
-        if (itemsList != null) {
-            // REPLACE mytab SELECT * FROM mytab2
-            // or VALUES ('as', ?, 565)
-
-            if (useValues) {
-                sql.append(" VALUES");
-            }
-
-            sql.append(" ").append(itemsList);
-        }
-
-        return sql.toString();
-    }
-
-    public Replace withUseValues(boolean useValues) {
-        this.setUseValues(useValues);
-        return this;
+        super.setItemsList( new ExpressionList(list) );
     }
 
+    @Deprecated
     public Replace withUseIntoTables(boolean useIntoTables) {
-        this.setUseIntoTables(useIntoTables);
-        return this;
-    }
-
-    public Replace withTable(Table table) {
-        this.setTable(table);
-        return this;
-    }
-
-    public Replace withColumns(List<Column> columns) {
-        this.setColumns(columns);
-        return this;
-    }
-
-    public Replace withItemsList(ItemsList itemsList) {
-        this.setItemsList(itemsList);
+        super.setUsingInto(useIntoTables);
         return this;
     }
 
+    @Deprecated
     public Replace withExpressions(List<Expression> expressions) {
-        this.setExpressions(expressions);
+        super.setItemsList( new ExpressionList(expressions) );
         return this;
     }
 
-    public Replace addColumns(Column... columns) {
-        List<Column> collection = Optional.ofNullable(getColumns()).orElseGet(ArrayList::new);
-        Collections.addAll(collection, columns);
-        return this.withColumns(collection);
-    }
-
-    public Replace addColumns(Collection<? extends Column> columns) {
-        List<Column> collection = Optional.ofNullable(getColumns()).orElseGet(ArrayList::new);
-        collection.addAll(columns);
-        return this.withColumns(collection);
-    }
-
+    @Deprecated
     public Replace addExpressions(Expression... expressions) {
-        List<Expression> collection = Optional.ofNullable(getExpressions()).orElseGet(ArrayList::new);
+        List<Expression> collection = Optional.ofNullable( super.getSetExpressions() ).orElseGet(ArrayList::new);
         Collections.addAll(collection, expressions);
         return this.withExpressions(collection);
     }
 
+    @Deprecated
     public Replace addExpressions(Collection<? extends Expression> expressions) {
-        List<Expression> collection = Optional.ofNullable(getExpressions()).orElseGet(ArrayList::new);
+        List<Expression> collection = Optional.ofNullable( super.getSetExpressions() ).orElseGet(ArrayList::new);
         collection.addAll(expressions);
         return this.withExpressions(collection);
     }
-
-    public <E extends ItemsList> E getItemsList(Class<E> type) {
-        return type.cast(getItemsList());
-    }
 }
diff --git a/src/main/java/net/sf/jsqlparser/statement/upsert/Upsert.java b/src/main/java/net/sf/jsqlparser/statement/upsert/Upsert.java
@@ -9,12 +9,8 @@
  */
 package net.sf.jsqlparser.statement.upsert;
 
-import java.util.ArrayList;
-import java.util.Collection;
-import java.util.Collections;
-import java.util.List;
-import java.util.Optional;
 import net.sf.jsqlparser.expression.Expression;
+import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
 import net.sf.jsqlparser.expression.operators.relational.ItemsList;
 import net.sf.jsqlparser.schema.Column;
 import net.sf.jsqlparser.schema.Table;
@@ -23,6 +19,12 @@
 import net.sf.jsqlparser.statement.select.PlainSelect;
 import net.sf.jsqlparser.statement.select.Select;
 
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.Collections;
+import java.util.List;
+import java.util.Optional;
+
 public class Upsert implements Statement {
 
     private Table table;
@@ -35,10 +37,40 @@ public class Upsert implements Statement {
     private List<Column> duplicateUpdateColumns;
     private List<Expression> duplicateUpdateExpressionList;
 
+    private UpsertType upsertType = UpsertType.UPSERT;
+
+    private boolean isUsingInto;
+
     @Override
     public void accept(StatementVisitor statementVisitor) {
         statementVisitor.visit(this); 
     }
+
+    public UpsertType getUpsertType() {
+        return upsertType;
+    }
+
+    public void setUpsertType(UpsertType upsertType) {
+        this.upsertType=upsertType;
+    }
+
+    public Upsert withUpsertType(UpsertType upsertType) {
+        setUpsertType(upsertType);
+        return this;
+    }
+
+    public boolean isUsingInto() {
+        return isUsingInto;
+    }
+
+    public void setUsingInto(boolean useInto) {
+        this.isUsingInto = useInto;
+    }
+
+    public Upsert withUsingInto(boolean useInto) {
+        setUsingInto(useInto);
+        return this;
+    }
 
     public void setTable(Table name) {
         table = name;
@@ -63,6 +95,15 @@ public void setItemsList(ItemsList list) {
     public ItemsList getItemsList() {
         return itemsList;
     }
+
+    public List<Expression> getSetExpressions() {
+        List<Expression> expressions = null;
+        if (itemsList instanceof ExpressionList) {
+            ExpressionList expressionList = (ExpressionList) itemsList;
+            expressions= expressionList.getExpressions();
+        }
+        return  expressions;
+    }
 
     public void setUseValues(boolean useValues) {
         this.useValues = useValues;
@@ -116,27 +157,67 @@ public List<Expression> getDuplicateUpdateExpressionList() {
     @SuppressWarnings({"PMD.CyclomaticComplexity"})
     public String toString() {
         StringBuilder sb = new StringBuilder();
-
-        sb.append("UPSERT INTO ");
-        sb.append(table).append(" ");
-        if (columns != null) {
-            sb.append(PlainSelect.getStringList(columns, true, true)).append(" ");
-        }
-        if (useValues) {
-            sb.append("VALUES ");
+
+        switch (upsertType) {
+            case UPSERT:
+                sb.append("UPSERT ");
+                break;
+            case REPLACE:
+            case REPLACE_SET:
+                sb.append("REPLACE ");
+                break;
+            case INSERT_OR_ABORT:
+                sb.append("INSERT OR ABORT ");
+                break;
+            case INSERT_OR_FAIL:
+                sb.append("INSERT OR FAIL ");
+                break;
+            case INSERT_OR_IGNORE:
+                sb.append("INSERT OR IGNORE ");
+                break;
+            case INSERT_OR_REPLACE:
+                sb.append("INSERT OR REPLACE ");
+                break;
+            case INSERT_OR_ROLLBACK:
+                sb.append("INSERT OR ROLLBACK ");
+                break;
         }
 
-        if (itemsList != null) {
-            sb.append(itemsList);
+        if (isUsingInto) {
+            sb.append("INTO ");
+        }
+        sb.append(table).append(" ");
+
+        if (upsertType==UpsertType.REPLACE_SET) {
+            sb.append("SET ");
+            // each element from expressions match up with a column from columns.
+            List<Expression> expressions = getSetExpressions();
+            for (int i = 0, s = columns.size(); i < s; i++) {
+                sb.append(columns.get(i)).append("=").append(expressions.get(i));
+                sb.append( i < s - 1
+                            ? ", "
+                            : "" );
+            }
         } else {
-            if (useSelectBrackets) {
-                sb.append("(");
+            if (columns != null) {
+                sb.append(PlainSelect.getStringList(columns, true, true)).append(" ");
             }
-            if (select != null) {
-                sb.append(select);
+            if (useValues) {
+                sb.append("VALUES ");
             }
-            if (useSelectBrackets) {
-                sb.append(")");
+
+            if (itemsList != null) {
+                sb.append(itemsList);
+            } else {
+                if (useSelectBrackets) {
+                    sb.append("(");
+                }
+                if (select != null) {
+                    sb.append(select);
+                }
+                if (useSelectBrackets) {
+                    sb.append(")");
+                }
             }
         }