Migration to 4.7

The new version of JSQLParser 4.7 is a rewrite in order to simplify accessing the SQLs Abstract Syntax Tree (AST). Quite a few redundant classes have been removed or merged.

As always, such a major improvement comes at a certain cost, which is breaking the previous API. Following the guidance below, the new API can be adopted easily although you are welcome to lodge a support request when any questions or concerns arise.

Values Clause

The ValueListExpression has been replaced by Values, which implements Select Statement and Expression.

The ValuesStatement has been replaced by Values, which implements Select Statement and Expression.

Statement

VALUES ( 1, 2, 3 )
;

SQL Text
 Statements: statement.select.Values
    ParenthesedExpressionList: (1, 2, 3)

Values values = (Values) CCJSqlParserUtil.parse(sqlStr);
assertEquals( 3, values.getExpressions().size() );

Sub-query

SELECT *
FROM ( VALUES 1, 2, 3 )
;

SQL Text
 Statements: statement.select.PlainSelect
    selectItems: statement.select.SelectItem
      AllColumns: *
    fromItem: statement.select.ParenthesedSelect
       select: statement.select.Values
          ExpressionList: 1, 2, 3

PlainSelect select = (PlainSelect)  CCJSqlParserUtil.parse(sqlStr);
ParenthesedSelect subSelect = (ParenthesedSelect) select.getFromItem();
Values values = (Values) subSelect.getSelect();
assertEquals( 3, values.getExpressions().size() );

Expression

UPDATE test
SET (   a
        , b
        , c ) = ( VALUES 1, 2, 3 )
;

SQL Text
 Statements: statement.update.Update
    Table: test
    updateSets: statement.update.UpdateSet
       ParenthesedExpressionList: (a, b, c)
       ExpressionList: (VALUES 1, 2, 3)

Update update = (Update)  CCJSqlParserUtil.parse(sqlStr);
UpdateSet updateSet = update.getUpdateSets().get(0);
ParenthesedSelect subSelect = (ParenthesedSelect) updateSet.getValues().get(0);
Values values = (Values) subSelect.getSelect();
assertEquals( 3, values.getExpressions().size() );

Clause

INSERT INTO test
VALUES ( 1, 2, 3 )
;

SQL Text
 Statements: statement.insert.Insert
    Table: test
    select: statement.select.Values
       ParenthesedExpressionList: (1, 2, 3)

Insert insert = (Insert)  CCJSqlParserUtil.parse(sqlStr);
Values values = (Values) insert.getSelect();
Assertions.assertEquals(3, values.getExpressions().size());

Expression Lists

The class ExpressionList extends List<Expression> directly and so ExpressionList.getExpressions() is obsolete.

Any instance of List<Expression> is considered an Anti Pattern and the class ExpressionList<T extends Expression> shall be used instead.

ItemsList has been removed and ExpressionList is used instead.

MultiExpressionList has been removed and ExpressionList is used instead (with ExpressionList elements).

ExpressionList

SELECT Function( a, b, c )
FROM dual
GROUP BY    a
            , b
            , c
;

SQL Text
 Statements: statement.select.PlainSelect
    selectItems: statement.select.SelectItem
      expression: expression.Function
         ExpressionList: a, b, c
    Table: dual
    groupBy: statement.select.GroupByElement
       ExpressionList: a, b, c

PlainSelect select = (PlainSelect)  CCJSqlParserUtil.parse(sqlStr);
Function function = (Function) select.getSelectItem(0).getExpression();
assertEquals(3, function.getParameters().size());

ExpressionList<?> groupByExpressions=select.getGroupBy().getGroupByExpressionList();
assertEquals(3, groupByExpressions.size());

Wrapped ExpressionList

SELECT ( ( 1, 2, 3 ), ( 4, 5, 6 ), ( 7, 8, 9 ) )
;

SQL Text
 Statements: statement.select.PlainSelect
    selectItems: statement.select.SelectItem
       ParenthesedExpressionList: ((1, 2, 3), (4, 5, 6), (7, 8, 9))

PlainSelect select = (PlainSelect)  CCJSqlParserUtil.parse(sqlStr);
ParenthesedExpressionList<?> expressionList = (ParenthesedExpressionList<?>) select.getSelectItem(0).getExpression();

ParenthesedExpressionList<?> expressionList1 = (ParenthesedExpressionList<?>) expressionList.get(0);
assertEquals(3, expressionList1.size());

Generic SelectItem

The class SelectItem<T extends Expression> is now generic and various derivatives (e.g. SelectExpressionItem, FunctionItem, ExpressionListItem) have been removed.

Select Statement

SelectBody has been removed and PlainSelect can be used directly

SubJoin has been replaced by ParenthesedFromItem (implementing a FromItem with a regular list of Join)

SubSelect has been removed and any instance of Select (PlainSelect, Values or SetOperationList) can be used instead

Select

(
    SELECT *
    FROM (  SELECT 1 )
    UNION ALL
    SELECT *
    FROM ( VALUES 1, 2, 3 )
    UNION ALL
    VALUES ( 1, 2, 3 ) )
;

SQL Text
 Statements: statement.select.ParenthesedSelect
    select: statement.select.SetOperationList
       selects: statement.select.PlainSelect
         selectItems: statement.select.SelectItem
           AllColumns: *
         fromItem: statement.select.ParenthesedSelect
            select: statement.select.PlainSelect
               selectItems: statement.select.SelectItem
                  LongValue: 1
       selects: statement.select.PlainSelect
         selectItems: statement.select.SelectItem
           AllColumns: *
         fromItem: statement.select.ParenthesedSelect
            select: statement.select.Values
               ExpressionList: 1, 2, 3
       selects: statement.select.Values
         ParenthesedExpressionList: (1, 2, 3)
       UnionOp: UNION ALL
       UnionOp: UNION ALL

ParenthesedSelect parenthesedSelect = (ParenthesedSelect)  CCJSqlParserUtil.parse(sqlStr);
SetOperationList setOperationList = parenthesedSelect.getSetOperationList();

PlainSelect select1 = (PlainSelect) setOperationList.getSelect(0);
PlainSelect subSelect1 = ((ParenthesedSelect) select1.getFromItem()).getPlainSelect();
Assertions.assertEquals( 1L, subSelect1.getSelectItem(0).getExpression(LongValue.class).getValue());

Values values = (Values) setOperationList.getSelect(2);
Assertions.assertEquals( 3, values.getExpressions().size());

Join

SELECT *
FROM a
  INNER JOIN (  b
                  LEFT JOIN c
                    ON b.d = c.d )
    ON a.e = b.e
;

SQL Text
 Statements: statement.select.PlainSelect
    selectItems: statement.select.SelectItem
      AllColumns: *
    Table: a
    joins: statement.select.Join
       rightItem: statement.select.ParenthesedFromItem
         Table: b
         joins: statement.select.Join
            Table: c
            onExpressions: expression.operators.relational.EqualsTo
               Column: b.d
               Column: c.d
       onExpressions: expression.operators.relational.EqualsTo
          Column: a.e
          Column: b.e

PlainSelect select = (PlainSelect)  CCJSqlParserUtil.parse(sqlStr);
Table aTable = (Table) select.getFromItem();

ParenthesedFromItem fromItem = (ParenthesedFromItem) select.getJoin(0).getFromItem();
Table bTable = (Table) fromItem.getFromItem();

Join join = fromItem.getJoin(0);
Table cTable = (Table) join.getFromItem();

assertEquals("c", cTable.getName());

Brackets

Any hasBrackets(), isUsingBrackets() and similar methods have been removed; instead the Parser will return a ParenthesedExpressionList or ParenthesedSelect or ParenthesedFromItem or Parenthesis wrapping the object within brackets.

This allows for much better bracket handling.

Parenthesis and Brackets example

( SELECT ( 1 ) )
;

SQL Text
 Statements: statement.select.ParenthesedSelect
    select: statement.select.PlainSelect
       selectItems: statement.select.SelectItem
          expression: expression.Parenthesis
             LongValue: 1

ParenthesedSelect parenthesedSelect = (ParenthesedSelect)  CCJSqlParserUtil.parse(sqlStr);
SetOperationList setOperationList = parenthesedSelect.getSetOperationList();

PlainSelect select1 = (PlainSelect) setOperationList.getSelect(0);
PlainSelect subSelect1 = ((ParenthesedSelect) select1.getFromItem()).getPlainSelect();
Assertions.assertEquals( 1L, subSelect1.getSelectItem(0).getExpression(LongValue.class).getValue());

Values values = (Values) setOperationList.getSelect(2);
Assertions.assertEquals( 3, values.getExpressions().size());

UpdateSet clause

A List<UpdateSet> is used for any Set clause within Insert, Update, Upsert or Merge statements.

UpdateSet example

UPDATE a
SET (   a
        , b
        , c ) = (   1
                    , 2
                    , 3 )
    , d = 4
;

SQL Text
 Statements: statement.update.Update
    Table: a
    updateSets: statement.update.UpdateSet
      ParenthesedExpressionList: (a, b, c)
      ParenthesedExpressionList: (1, 2, 3)
    updateSets: statement.update.UpdateSet
       ExpressionList: d
       ExpressionList: 4

Update update = (Update)  CCJSqlParserUtil.parse(sqlStr);
UpdateSet updateSet1 = update.getUpdateSet(0);
Assertions.assertEquals( 3, updateSet1.getColumns().size());
Assertions.assertEquals( 3, updateSet1.getValues().size());

UpdateSet updateSet2 = update.getUpdateSet(1);
Assertions.assertEquals( "d", updateSet2.getColumn(0).getColumnName());
Assertions.assertEquals( 4L,  ((LongValue) updateSet2.getValue(0)).getValue() );

Statements collection

The Statements class extends List<Statement> directly and so Statements.getStatements() is obsolete.