Examples with Upsert net.sf.jsqlparser.statement.upsert.Upsert used on opensource projects

Example 1 with Upsert

use of net.sf.jsqlparser.statement.upsert.Upsert in project JSqlParser by JSQLParser.

the class UpsertTest method testUpsertDuplicate.

@Test
public void testUpsertDuplicate() throws JSQLParserException {
    String statement = "UPSERT INTO TEST (ID, COUNTER) VALUES (123, 0) ON DUPLICATE KEY UPDATE COUNTER = COUNTER + 1";
    Upsert upsert = (Upsert) parserManager.parse(new StringReader(statement));
    assertEquals("TEST", upsert.getTable().getName());
    assertEquals(2, upsert.getColumns().size());
    assertTrue(upsert.isUseValues());
    assertEquals("ID", ((Column) upsert.getColumns().get(0)).getColumnName());
    assertEquals("COUNTER", ((Column) upsert.getColumns().get(1)).getColumnName());
    assertEquals(2, ((ExpressionList) upsert.getItemsList()).getExpressions().size());
    assertEquals(123, ((LongValue) ((ExpressionList) upsert.getItemsList()).getExpressions().get(0)).getValue());
    assertEquals(0, ((LongValue) ((ExpressionList) upsert.getItemsList()).getExpressions().get(1)).getValue());
    assertEquals(1, upsert.getDuplicateUpdateColumns().size());
    assertEquals("COUNTER", ((Column) upsert.getDuplicateUpdateColumns().get(0)).getColumnName());
    assertEquals(1, upsert.getDuplicateUpdateExpressionList().size());
    assertEquals("COUNTER + 1", upsert.getDuplicateUpdateExpressionList().get(0).toString());
    assertFalse(upsert.isUseSelectBrackets());
    assertTrue(upsert.isUseDuplicate());
    assertEquals(statement, "" + upsert);
}

Example 2 with Upsert

use of net.sf.jsqlparser.statement.upsert.Upsert in project JSqlParser by JSQLParser.

the class UpsertTest method testUpsertN.

@Test
public void testUpsertN() throws JSQLParserException {
    String statement = "UPSERT INTO TEST VALUES ('foo', 'bar', 3)";
    Upsert upsert = (Upsert) parserManager.parse(new StringReader(statement));
    assertEquals("TEST", upsert.getTable().getName());
    assertEquals(3, ((ExpressionList) upsert.getItemsList()).getExpressions().size());
    assertTrue(upsert.isUseValues());
    assertEquals("foo", ((StringValue) ((ExpressionList) upsert.getItemsList()).getExpressions().get(0)).getValue());
    assertEquals("bar", ((StringValue) ((ExpressionList) upsert.getItemsList()).getExpressions().get(1)).getValue());
    assertEquals(3, ((LongValue) ((ExpressionList) upsert.getItemsList()).getExpressions().get(2)).getValue());
    assertFalse(upsert.isUseSelectBrackets());
    assertFalse(upsert.isUseDuplicate());
    assertEquals(statement, "" + upsert);
}

Example 3 with Upsert

use of net.sf.jsqlparser.statement.upsert.Upsert in project JSqlParser by JSQLParser.

the class UpsertTest method testUpsertSelect.

@Test
public void testUpsertSelect() throws JSQLParserException {
    String statement = "UPSERT INTO test.targetTable (col1, col2) SELECT * FROM test.sourceTable";
    Upsert upsert = (Upsert) parserManager.parse(new StringReader(statement));
    assertEquals("test.targetTable", upsert.getTable().getFullyQualifiedName());
    assertEquals(2, upsert.getColumns().size());
    assertFalse(upsert.isUseValues());
    assertEquals("col1", ((Column) upsert.getColumns().get(0)).getColumnName());
    assertEquals("col2", ((Column) upsert.getColumns().get(1)).getColumnName());
    assertNull(upsert.getItemsList());
    assertNotNull(upsert.getSelect());
    assertEquals("test.sourceTable", ((Table) ((PlainSelect) upsert.getSelect().getSelectBody()).getFromItem()).getFullyQualifiedName());
    assertFalse(upsert.isUseDuplicate());
    assertEquals(statement, "" + upsert);
}

Example 4 with Upsert

use of net.sf.jsqlparser.statement.upsert.Upsert in project JSqlParser by JSQLParser.

the class TablesNamesFinderTest method testUpsertSelect.

@Test
public void testUpsertSelect() throws Exception {
    String sql = "UPSERT INTO mytable (mycolumn) SELECT mycolumn FROM mytable2";
    net.sf.jsqlparser.statement.Statement statement = pm.parse(new StringReader(sql));
    Upsert insertStatement = (Upsert) statement;
    TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
    List<String> tableList = tablesNamesFinder.getTableList(insertStatement);
    assertEquals(2, tableList.size());
    assertTrue(tableList.contains("mytable"));
    assertTrue(tableList.contains("mytable2"));
}

Example 5 with Upsert

use of net.sf.jsqlparser.statement.upsert.Upsert in project JSqlParser by JSQLParser.

the class StatementDeParserTest method shouldUseProvidedDeparsersWhenDeParsingUpsertWithExpressionList.

@Test
@SuppressWarnings("PMD.JUnitTestsShouldIncludeAssert")
public void shouldUseProvidedDeparsersWhenDeParsingUpsertWithExpressionList() throws JSQLParserException {
    Upsert upsert = new Upsert();
    Table table = new Table();
    List<Column> duplicateUpdateColumns = new ArrayList<Column>();
    List<Expression> duplicateUpdateExpressionList = new ArrayList<Expression>();
    Column duplicateUpdateColumn1 = new Column();
    Column duplicateUpdateColumn2 = new Column();
    Expression duplicateUpdateExpression1 = mock(Expression.class);
    Expression duplicateUpdateExpression2 = mock(Expression.class);
    Select select = new Select();
    List<WithItem> withItemsList = new ArrayList<WithItem>();
    WithItem withItem1 = spy(new WithItem());
    WithItem withItem2 = spy(new WithItem());
    SelectBody withItem1SelectBody = mock(SelectBody.class);
    SelectBody withItem2SelectBody = mock(SelectBody.class);
    SelectBody selectBody = mock(SelectBody.class);
    upsert.setSelect(select);
    upsert.setTable(table);
    upsert.setUseDuplicate(true);
    upsert.setDuplicateUpdateColumns(duplicateUpdateColumns);
    upsert.setDuplicateUpdateExpressionList(duplicateUpdateExpressionList);
    duplicateUpdateColumns.add(duplicateUpdateColumn1);
    duplicateUpdateColumns.add(duplicateUpdateColumn2);
    duplicateUpdateExpressionList.add(duplicateUpdateExpression1);
    duplicateUpdateExpressionList.add(duplicateUpdateExpression2);
    upsert.setDuplicateUpdateExpressionList(duplicateUpdateExpressionList);
    select.setWithItemsList(withItemsList);
    select.setSelectBody(selectBody);
    withItemsList.add(withItem1);
    withItemsList.add(withItem2);
    withItem1.setSelectBody(withItem1SelectBody);
    withItem2.setSelectBody(withItem2SelectBody);
    statementDeParser.visit(upsert);
    then(withItem1).should().accept(selectDeParser);
    then(withItem2).should().accept(selectDeParser);
    then(selectBody).should().accept(selectDeParser);
    then(duplicateUpdateExpression1).should().accept(expressionDeParser);
    then(duplicateUpdateExpression1).should().accept(expressionDeParser);
}