-
-
Notifications
You must be signed in to change notification settings - Fork 1.4k
Closed
@zhangconan
Description
Always check against the Latest SNAPSHOT of JSQLParser and the Syntax Diagram
Failing SQL Feature:
- not support on duplicate key update nothing / where
SQL Example:
- Simplified Insert Example, focusing on the failing feature
INSERT INTO users (id, name) VALUES (1, 'Alice') ON DUPLICATE KEY UPDATE nothing
Software Information:
- JSqlParser 5.3
- Database opengauss
Tips:
I hope to support nothing and where syntax。Now I made some modifications to support the nothing and where syntax。
New Add InsertDuplicateAction class,the content is as follows:
public class InsertDuplicateAction implements Serializable { ConflictActionType conflictActionType; Expression whereExpression; private List<UpdateSet> updateSets; public InsertDuplicateAction(ConflictActionType conflictActionType) { this.conflictActionType = Objects.requireNonNull(conflictActionType, "The Conflict Action Type is mandatory and must not be Null."); } public List<UpdateSet> getUpdateSets() { return updateSets; } public void setUpdateSets(List<UpdateSet> updateSets) { this.updateSets = updateSets; } public InsertDuplicateAction withUpdateSets(List<UpdateSet> updateSets) { this.setUpdateSets(updateSets); return this; } public ConflictActionType getConflictActionType() { return conflictActionType; } public void setConflictActionType(ConflictActionType conflictActionType) { this.conflictActionType = Objects.requireNonNull(conflictActionType, "The Conflict Action Type is mandatory and must not be Null."); } public InsertDuplicateAction withConflictActionType(ConflictActionType conflictActionType) { setConflictActionType(conflictActionType); return this; } public InsertDuplicateAction addUpdateSet(Column column, Expression expression) { return this.addUpdateSet(new UpdateSet()); } public InsertDuplicateAction addUpdateSet(UpdateSet updateSet) { if (updateSets == null) { updateSets = new ArrayList<>(); } this.updateSets.add(updateSet); return this; } public InsertDuplicateAction withUpdateSets(Collection<UpdateSet> updateSets) { this.setUpdateSets(new ArrayList<>(updateSets)); return this; } public Expression getWhereExpression() { return whereExpression; } public void setWhereExpression(Expression whereExpression) { this.whereExpression = whereExpression; } public InsertDuplicateAction withWhereExpression(Expression whereExpression) { setWhereExpression(whereExpression); return this; } @SuppressWarnings("PMD.SwitchStmtsShouldHaveDefault") public StringBuilder appendTo(StringBuilder builder) { switch (conflictActionType) { case DO_NOTHING: builder.append(" DO NOTHING"); break; default: UpdateSet.appendUpdateSetsTo(builder, updateSets); if (whereExpression != null) { builder.append(" WHERE ").append(whereExpression); } break; } return builder; } @Override public String toString() { return appendTo(new StringBuilder()).toString(); } }
Modify Insert class,add InsertDuplicateAction field related content。
private InsertDuplicateAction duplicateAction; public List<UpdateSet> getDuplicateUpdateSets() { if (duplicateAction != null) { return duplicateAction.getUpdateSets(); } return duplicateUpdateSets; } public Insert withDuplicateUpdateSets(List<UpdateSet> duplicateUpdateSets) { if (duplicateAction != null) { duplicateAction.setConflictActionType(ConflictActionType.DO_UPDATE); duplicateAction.setUpdateSets(duplicateUpdateSets); } else { duplicateAction = new InsertDuplicateAction(ConflictActionType.DO_UPDATE); duplicateAction.setUpdateSets(duplicateUpdateSets); } return this; } @Override @SuppressWarnings({"PMD.CyclomaticComplexity", "PMD.NPathComplexity"}) public String toString() { StringBuilder sql = new StringBuilder(); if (withItemsList != null && !withItemsList.isEmpty()) { sql.append("WITH "); for (Iterator<WithItem<?>> iter = withItemsList.iterator(); iter.hasNext(); ) { WithItem<?> withItem = iter.next(); sql.append(withItem); if (iter.hasNext()) { sql.append(","); } sql.append(" "); } } sql.append("INSERT "); if (oracleHint != null) { sql.append(oracleHint).append(" "); } if (modifierPriority != null) { sql.append(modifierPriority.name()).append(" "); } if (modifierIgnore) { sql.append("IGNORE "); } if (overwrite) { sql.append("OVERWRITE "); } else { sql.append("INTO "); } if (tableKeyword) { sql.append("TABLE "); } sql.append(table).append(" "); if (onlyDefaultValues) { sql.append("DEFAULT VALUES"); } if (columns != null) { sql.append("("); for (int i = 0; i < columns.size(); i++) { if (i > 0) { sql.append(", "); } // only plain names, but not fully qualified names allowed sql.append(columns.get(i).getColumnName()); } sql.append(") "); } if (overriding) { sql.append("OVERRIDING SYSTEM VALUE "); } if (partitions != null) { sql.append(" PARTITION ("); Partition.appendPartitionsTo(sql, partitions); sql.append(") "); } if (outputClause != null) { sql.append(outputClause); } if (select != null) { sql.append(select); } if (setUpdateSets != null && !setUpdateSets.isEmpty()) { sql.append("SET "); sql = UpdateSet.appendUpdateSetsTo(sql, setUpdateSets); } if (duplicateAction != null) { sql.append(" ON DUPLICATE KEY UPDATE "); duplicateAction.appendTo(sql); } if (conflictAction != null) { sql.append(" ON CONFLICT"); if (conflictTarget != null) { conflictTarget.appendTo(sql); } conflictAction.appendTo(sql); } if (returningClause != null) { returningClause.appendTo(sql); } return sql.toString(); } public InsertDuplicateAction getDuplicateAction() { return duplicateAction; } public void setDuplicateAction(InsertDuplicateAction duplicateAction) { this.duplicateAction = duplicateAction; }
And modify JSqlParserCC.jjt, content is as follows:
Insert Insert():
{
....
InsertDuplicateAction duplicateAction = null;
}
{
[ LOOKAHEAD(2) <K_ON> <K_DUPLICATE> <K_KEY> <K_UPDATE>
duplicateAction = InsertDuplicateAction() { insert.setDuplicateAction(duplicateAction); }
]
}
InsertDuplicateAction InsertDuplicateAction():
{
InsertDuplicateAction duplicateAction;
Expression whereExpression = null;
List<UpdateSet> updateSets;
}
{
(
LOOKAHEAD(2) (
<K_DO> <K_NOTHING> { duplicateAction = new InsertDuplicateAction( ConflictActionType.DO_NOTHING ); }
)
|
(
{ duplicateAction = new InsertDuplicateAction( ConflictActionType.DO_UPDATE ); }
updateSets = UpdateSets() { duplicateAction.setUpdateSets(updateSets); }
[ whereExpression = WhereClause() ]
)
)
{ return duplicateAction
.withWhereExpression(whereExpression); }
}
Metadata
Metadata
Assignees
Labels
No labels