[jira] [Updated] (GROOVY-9159) [GEP] Support LINQ, aka GINQ

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

[jira] [Updated] (GROOVY-9159) [GEP] Support LINQ, aka GINQ

JIRA jira@apache.org

     [ https://issues.apache.org/jira/browse/GROOVY-9159?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Daniel Sun updated GROOVY-9159:
-------------------------------
    Description:
h2. *Ⅰ. Background*

In order to make querying different types of data sources convenient, we need a unified querying interface, i.e. GINQ
h2. *Ⅱ. Solution*

The basic rationale can be shown as follows:
 *Groovy User* ==_writes GINQ code_==> *Parrot Parser* ==generates AST==> *GINQ Engine* ==_translates AST to Stream-Like method invocations_==> *Bytecode Writer*
h3. {{translates AST to Stream-Like method invocations}} will be designed for different cases:
h4. 1) target objects are all collections

translates AST to Java 8+ stream method invocations
h4. 2) target objects are all DB related objects

translates AST to *JOOQ* method invocations( [https://github.com/jOOQ/jOOQ] ), which would be implemented as a {{GINQ provider}} in a seperate sub-project(e.g. {{groovy-linq-jooq}}). _Note: *JOOQ* is licensed under *APL2* too_( [https://github.com/jOOQ/jOOQ/blob/master/LICENSE] )
h4. 3) target objects are XML, CSV, etc. related objects, or even mixed types of objects

We can treate the case as a special sub-case of case 1
h3. *Note:*

{color:#d04437}1. The exact syntax might be altered before introduction, currently working on the general principle.{color}
 2.GINQ will reuse most of standard SQL syntax, which can make the learning curve smooth and avoid infringing the patent of Microsoft.
 3. All GINQ related keywords are uppercase to avoid breaking existing source code as possible as we can, e.g. {{FROM}}, {{WHERE}}, {{SELECT}}, etc.
 4. In order to support type inference better, {{SELECT}} clause is placed at the end of GINQ expression.
 5. {{alias.VALUE}} is a virtual property and is used to reference the whole record as value. It can be simplified as {{alias}}.
 6. {{SELECT P1, P2 ... Pn}} is a simplifed syntax of {{SELECT Tuple.tuple(P1, P2 ... Pn)}} and will create a {{List}} of {{Tuple}} sub-class instances when and only when {{n >= 2}}
h2. *Ⅲ. EBNF*
h3.   TBD
h2. *Ⅳ. Examples*
h3. 1. Filtering
{code:java}
@groovy.transform.EqualsAndHashCode
class Person {
        String name
        int age
}

def persons = [new Person(name: 'Daniel', age: 35), new Person(name: 'Peter', age: 10), new Person(name: 'Alice', age: 22)]
{code}
h4. 1.1
{code:java}
def result =
        FROM persons p
        WHERE p.age > 15 && p.age <= 35
        SELECT p.name

assert ['Daniel', 'Alice'] == result
{code}
{code:java}
persons.stream().filter(p -> p.age > 15 && p.age <= 35).map(p -> p.name).collect(Collectors.toList())
{code}
h4. 1.2
{code:java}
def result =
        FROM persons p
        WHERE p.age > 15 && p.age <= 35
        SELECT p

assert [new Person(name: 'Daniel', age: 35), new Person(name: 'Alice', age: 22)] == result
{code}
{code:java}
persons.stream().filter(p -> p.age > 15 && p.age <= 35).collect(Collectors.toList())
{code}
h4. 1.3
{code:java}
def numbers = [1, 2, 3]

def result =
        FROM numbers t
        WHERE t <= 2
        SELECT t

assert [1, 2] == result
{code}
{code:java}
numbers.stream().filter(t -> t <= 2).collect(Collectors.toList())
{code}
h3. 2. Joining
{code:java}
import static groovy.lang.Tuple.*

@groovy.transform.EqualsAndHashCode
class Person {
        String name
        int age
        City city
}

@groovy.transform.EqualsAndHashCode
class City {
        String name
}

def persons = [new Person(name: 'Daniel', age: 35, city: new City('Shanghai')), new Person(name: 'Peter', age: 10, city: new City('Beijing')), new Person(name: 'Alice', age: 22, city: new City('Hangzhou'))]

def cities = [new City('Shanghai'), new City('Beijing'), new City('Guangzhou')]
{code}
h4. 2.1
{code:java}
// inner join
def result =
        FROM persons p INNER JOIN cities c
        ON p.city.name == c.name
        SELECT p.name, c.name

assert [tuple('Daniel', 'Shanghai'), tuple('Peter', 'Beijing')] == result
{code}
{code:java}
persons.stream()
        .flatMap(p -> cities.stream().filter(c -> p.city.name == c.name).map(c -> tuple(p.name, c.name)))
        .collect(Collectors.toList())
{code}
h4. 2.2
{code:java}
def result =
        FROM persons p, cities c
        WHERE p.city.name == c.name
        SELECT p.name, c.name

assert [tuple('Daniel', 'Shanghai'), tuple('Peter', 'Beijing')] == result
{code}
{code:java}
persons.stream()
        .flatMap(p -> cities.stream().filter(c -> p.city.name == c.name).map(c -> tuple(p.name, c.name)))
        .collect(Collectors.toList())
{code}
h4. 2.3
{code:java}
def result =
        FROM persons p, cities c
        WHERE p.city == c
        SELECT p.name

assert ['Daniel', 'Peter'] == result
{code}
{code:java}
persons.stream()
        .flatMap(p -> cities.stream().filter(c -> p.city == c).map(c -> p.name))
        .collect(Collectors.toList())
{code}
h4. 2.4
{code:java}
// left outer join
def result =
        FROM persons p LEFT JOIN cities c  //  same to LEFT OUTER JOIN
        ON p.city.name == c.name
        SELECT p.name, c.name

assert [tuple('Daniel', 'Shanghai'), tuple('Peter', 'Beijing'), tuple('Alice', null)] == result
{code}
h4. 2.5
{code:java}
// right outer join
def result =
        FROM persons p RIGHT JOIN cities c  //  same to RIGHT OUTER JOIN
        ON p.city.name == c.name
        SELECT p.name, c.name

assert [tuple('Daniel', 'Shanghai'), tuple('Peter', 'Beijing'), tuple(null, 'Guangzhou')] == result
{code}
h3. 3. Projection
{code:java}
import static groovy.lang.Tuple.*

@groovy.transform.EqualsAndHashCode
class Person {
        String name
        int age
}

def persons = [new Person(name: 'Daniel', age: 35), new Person(name: 'Peter', age: 10), new Person(name: 'Alice', age: 22)]
{code}
h4. 3.1
{code:java}
def result =
        FROM persons p
        SELECT p.name

assert ['Daniel', 'Peter', 'Alice'] == result
{code}
h4. 3.2
{code:java}
def result =
        FROM persons p
        SELECT p.name, p.age

assert [tuple('Daniel', 35), tuple('Peter', 10), tuple('Alice', 22)] == result
{code}
h4. 3.3
{code:java}
def result =
        FROM persons p
        SELECT [name: p.name, age: p.age]

assert [ [name: 'Daniel', age: 35], [name: 'Peter', age: 10], [name: 'Alice', age: 22] ] == result
{code}
h4. 3.4
{code:java}
def result =
        FROM persons p
        SELECT new Person(name: p.name, age: p.age)

assert persons == result
{code}
h4. 3.5
{code:java}
def result =
        FROM persons p
        SELECT p.VALUE

assert persons == result
{code}
h4. 3.6
{code:java}
def result =
        FROM persons p
        SELECT p

assert persons == result
{code}
h3. 4. Grouping
{code:java}
import static groovy.lang.Tuple.*

@groovy.transform.EqualsAndHashCode
class Person {
        String name
        int age
        String gender
}

def persons = [new Person(name: 'Daniel', age: 35, gender: 'Male'), new Person(name: 'Peter', age: 10, gender: 'Male'), new Person(name: 'Alice', age: 22, gender: 'Female')]
{code}
h4. 4.1
{code:java}
def result =
        FROM persons p
        GROUP BY p.gender
        SELECT p.gender, MAX(p.age)

assert [tuple('Male', 35), tuple('Female', 22)] == result
{code}

h3. 5. Having
{code:java}
import static groovy.lang.Tuple.*

@groovy.transform.EqualsAndHashCode
class Person {
        String name
        int age
        String gender
}

def persons = [new Person(name: 'Daniel', age: 35, gender: 'Male'), new Person(name: 'Peter', age: 10, gender: 'Male'), new Person(name: 'Alice', age: 22, gender: 'Female')]
{code}
h4. 5.1
{code:java}
def result =
        FROM persons p
        GROUP BY p.gender
        HAVING p.gender == 'Male'
        SELECT p.gender, MAX(p.age)

assert [tuple('Male', 35)] == result
{code}

h3. 6. Sorting
{code:java}
@groovy.transform.EqualsAndHashCode
class Person {
        String name
        int age
}

def persons = [new Person(name: 'Daniel', age: 35), new Person(name: 'Peter', age: 10), new Person(name: 'Alice', age: 22)]
{code}
h4. 6.1
{code:java}
def result =
        FROM persons p
        ORDER BY p.age
        SELECT p.name

assert ['Peter', 'Alice', 'Daniel'] == result
{code}
h4. 6.2
{code:java}
def result =
        FROM persons p
        ORDER BY p.age desc
        SELECT p.name

assert ['Daniel', 'Alice', 'Peter'] == result
{code}
h3. 7. Pagination
{code:java}
def numbers = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
{code}
h4. 7.1
{code:java}
def result =
        FROM numbers n
        LIMIT 5 OFFSET 2
        SELECT n

assert [2, 3, 4, 5, 6] == result
{code}
h4. 7.2
{code:java}
def result =
        FROM numbers n
        LIMIT 5
        SELECT n

assert [0, 1, 2, 3, 4] == result
{code}
h3. 8. Nested Queries
{code:java}
def numbers = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
{code}
h4. 8.1
{code:java}
def result =
        FROM (
                FROM numbers n
                WHERE n <= 5
                SELECT n
        ) v
        LIMIT 5 OFFSET 2
        SELECT v

assert [2, 3, 4, 5] == result
{code}
h3. 9. WITH-Clause
{code:java}
def numbers = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
{code}
h4. 9.1
{code:java}
def result =
        WITH v AS (
                FROM numbers n
                WHERE n <= 5
                SELECT n
        )
        FROM  v
        LIMIT 5 OFFSET 2
        SELECT v

assert [2, 3, 4, 5] == result
{code}
h3. 10. Union
{code:java}
def numbers1 = [0, 1, 2]
def numbers2 = [2, 3, 4]
{code}
h4. 10.1
{code:java}
def result =
        FROM  numbers1 n
        SELECT n
        UNION ALL
        FROM  numbers2 n
        SELECT n

assert [0, 1, 2, 2, 3, 4] == result
{code}
h4. 10.2
{code:java}
def result =
        FROM  numbers1 n
        SELECT n
        UNION
        FROM  numbers2 n
        SELECT n
       
assert [0, 1, 2, 3, 4] == result
{code}

  was:
h2. *Ⅰ. Background*

In order to make querying different types of data sources convenient, we need a unified querying interface, i.e. GINQ
h2. *Ⅱ. Solution*

The basic rationale can be shown as follows:
 *Groovy User* ==_writes GINQ code_==> *Parrot Parser* ==generates AST==> *GINQ Engine* ==_translates AST to Stream-Like method invocations_==> *Bytecode Writer*
h3. {{translates AST to Stream-Like method invocations}} will be designed for different cases:
h4. 1) target objects are all collections

translates AST to Java 8+ stream method invocations
h4. 2) target objects are all DB related objects

translates AST to *JOOQ* method invocations( [https://github.com/jOOQ/jOOQ] ), which would be implemented as a {{GINQ provider}} in a seperate sub-project(e.g. {{groovy-linq-jooq}}). _Note: *JOOQ* is licensed under *APL2* too_( [https://github.com/jOOQ/jOOQ/blob/master/LICENSE] )
h4. 3) target objects are XML, CSV, etc. related objects, or even mixed types of objects

We can treate the case as a special sub-case of case 1
h3. *Note:*

{color:#d04437}1. The exact syntax might be altered before introduction, currently working on the general principle.{color}
 2.GINQ will reuse most of standard SQL syntax, which can make the learning curve smooth and avoid infringing the patent of Microsoft.
 3. All GINQ related keywords are uppercase to avoid breaking existing source code as possible as we can, e.g. {{FROM}}, {{WHERE}}, {{SELECT}}, etc.
 4. In order to support type inference better, {{SELECT}} clause is placed at the end of GINQ expression.
 5. {{alias.VALUE}} is a virtual property and is used to reference the whole record as value. It can be simplified as {{alias}}.
 6. {{SELECT P1, P2 ... Pn}} is a simplifed syntax of {{SELECT Tuple.tuple(P1, P2 ... Pn)}} and will create a {{List}} of {{Tuple}} sub-class instances when and only when {{n >= 2}}
h2. *Ⅲ. EBNF*
h3.   TBD
h2. *Ⅳ. Examples*
h3. 1. Filtering
{code:java}
@groovy.transform.EqualsAndHashCode
class Person {
        String name
        int age
}

def persons = [new Person(name: 'Daniel', age: 35), new Person(name: 'Peter', age: 10), new Person(name: 'Alice', age: 22)]
{code}
h4. 1.1
{code:java}
def result =
        FROM persons p
        WHERE p.age > 15 && p.age <= 35
        SELECT p.name

assert ['Daniel', 'Alice'] == result
{code}
{code:java}
persons.stream().filter(p -> p.age > 15 && p.age <= 35).map(p -> p.name).collect(Collectors.toList())
{code}
h4. 1.2
{code:java}
def result =
        FROM persons p
        WHERE p.age > 15 && p.age <= 35
        SELECT p

assert [new Person(name: 'Daniel', age: 35), new Person(name: 'Alice', age: 22)] == result
{code}
{code:java}
persons.stream().filter(p -> p.age > 15 && p.age <= 35).collect(Collectors.toList())
{code}
h4. 1.3
{code:java}
def numbers = [1, 2, 3]

def result =
        FROM numbers t
        WHERE t <= 2
        SELECT t

assert [1, 2] == result
{code}
{code:java}
numbers.stream().filter(t -> t <= 2).collect(Collectors.toList())
{code}
h3. 2. Joining
{code:java}
import static groovy.lang.Tuple.*

@groovy.transform.EqualsAndHashCode
class Person {
        String name
        int age
        City city
}

@groovy.transform.EqualsAndHashCode
class City {
        String name
}

def persons = [new Person(name: 'Daniel', age: 35, city: new City('Shanghai')), new Person(name: 'Peter', age: 10, city: new City('Beijing')), new Person(name: 'Alice', age: 22, city: new City('Hangzhou'))]

def cities = [new City('Shanghai'), new City('Beijing'), new City('Guangzhou')]
{code}
h4. 2.1
{code:java}
// inner join
def result =
        FROM persons p INNER JOIN cities c
        ON p.city.name == c.name
        SELECT p.name, c.name

assert [tuple('Daniel', 'Shanghai'), tuple('Peter', 'Beijing')] == result
{code}
{code:java}
persons.stream()
        .flatMap(p -> cities.stream().filter(c -> p.city.name == c.name).map(c -> tuple(p.name, c.name)))
        .collect(Collectors.toList())
{code}
h4. 2.2
{code:java}
def result =
        FROM persons p, cities c
        WHERE p.city.name == c.name
        SELECT p.name, c.name

assert [tuple('Daniel', 'Shanghai'), tuple('Peter', 'Beijing')] == result
{code}
{code:java}
persons.stream()
        .flatMap(p -> cities.stream().filter(c -> p.city.name == c.name).map(c -> tuple(p.name, c.name)))
        .collect(Collectors.toList())
{code}
h4. 2.3
{code:java}
def result =
        FROM persons p, cities c
        WHERE p.city == c
        SELECT p.name

assert ['Daniel', 'Peter'] == result
{code}
{code:java}
persons.stream()
        .flatMap(p -> cities.stream().filter(c -> p.city == c).map(c -> p.name))
        .collect(Collectors.toList())
{code}
h4. 2.4
{code:java}
// left outer join
def result =
        FROM persons p LEFT JOIN cities c  //  same to LEFT OUTER JOIN
        ON p.city.name == c.name
        SELECT p.name, c.name

assert [tuple('Daniel', 'Shanghai'), tuple('Peter', 'Beijing'), tuple('Alice', null)] == result
{code}
h4. 2.5
{code:java}
// right outer join
def result =
        FROM persons p RIGHT JOIN cities c  //  same to RIGHT OUTER JOIN
        ON p.city.name == c.name
        SELECT p.name, c.name

assert [tuple('Daniel', 'Shanghai'), tuple('Peter', 'Beijing'), tuple(null, 'Guangzhou')] == result
{code}
h3. 3. Projection
{code:java}
import static groovy.lang.Tuple.*

@groovy.transform.EqualsAndHashCode
class Person {
        String name
        int age
}

def persons = [new Person(name: 'Daniel', age: 35), new Person(name: 'Peter', age: 10), new Person(name: 'Alice', age: 22)]
{code}
h4. 3.1
{code:java}
def result =
        FROM persons p
        SELECT p.name

assert ['Daniel', 'Peter', 'Alice'] == result
{code}
h4. 3.2
{code:java}
def result =
        FROM persons p
        SELECT p.name, p.age

assert [tuple('Daniel', 35), tuple('Peter', 10), tuple('Alice', 22)] == result
{code}
h4. 3.3
{code:java}
def result =
        FROM persons p
        SELECT [name: p.name, age: p.age]

assert [ [name: 'Daniel', age: 35], [name: 'Peter', age: 10], [name: 'Alice', age: 22] ] == result
{code}
h4. 3.4
{code:java}
def result =
        FROM persons p
        SELECT new Person(name: p.name, age: p.age)

assert persons == result
{code}
h4. 3.5
{code:java}
def result =
        FROM persons p
        SELECT p.VALUE

assert persons == result
{code}
h4. 3.6
{code:java}
def result =
        FROM persons p
        SELECT p

assert persons == result
{code}
h3. 4. Grouping
{code:java}
import static groovy.lang.Tuple.*

@groovy.transform.EqualsAndHashCode
class Person {
        String name
        int age
        String gender
}

def persons = [new Person(name: 'Daniel', age: 35, gender: 'Male'), new Person(name: 'Peter', age: 10, gender: 'Male'), new Person(name: 'Alice', age: 22, gender: 'Female')]
{code}
h4. 4.1
{code:java}
def result =
        FROM persons p
        GROUP BY p.gender
        SELECT p.gender, MAX(p.age)

assert [tuple('Male', 35), tuple('Female', 22)] == result
{code}
h3. 5. Sorting
{code:java}
@groovy.transform.EqualsAndHashCode
class Person {
        String name
        int age
}

def persons = [new Person(name: 'Daniel', age: 35), new Person(name: 'Peter', age: 10), new Person(name: 'Alice', age: 22)]
{code}
h4. 5.1
{code:java}
def result =
        FROM persons p
        ORDER BY p.age
        SELECT p.name

assert ['Peter', 'Alice', 'Daniel'] == result
{code}
h4. 5.2
{code:java}
def result =
        FROM persons p
        ORDER BY p.age desc
        SELECT p.name

assert ['Daniel', 'Alice', 'Peter'] == result
{code}
h3. 6. Pagination
{code:java}
def numbers = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
{code}
h4. 6.1
{code:java}
def result =
        FROM numbers n
        LIMIT 5 OFFSET 2
        SELECT n

assert [2, 3, 4, 5, 6] == result
{code}
h4. 6.2
{code:java}
def result =
        FROM numbers n
        LIMIT 5
        SELECT n

assert [0, 1, 2, 3, 4] == result
{code}
h3. 7. Nested Queries
{code:java}
def numbers = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
{code}
h4. 7.1
{code:java}
def result =
        FROM (
                FROM numbers n
                WHERE n <= 5
                SELECT n
        ) v
        LIMIT 5 OFFSET 2
        SELECT v

assert [2, 3, 4, 5] == result
{code}
h3. 8. WITH-Clause
{code:java}
def numbers = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
{code}
h4. 8.1
{code:java}
def result =
        WITH v AS (
                FROM numbers n
                WHERE n <= 5
                SELECT n
        )
        FROM  v
        LIMIT 5 OFFSET 2
        SELECT v

assert [2, 3, 4, 5] == result
{code}
h3. 9. Union
{code:java}
def numbers1 = [0, 1, 2]
def numbers2 = [2, 3, 4]
{code}
h4. 9.1
{code:java}
def result =
        FROM  numbers1 n
        SELECT n
        UNION ALL
        FROM  numbers2 n
        SELECT n

assert [0, 1, 2, 2, 3, 4] == result
{code}
h4. 9.2
{code:java}
def result =
        FROM  numbers1 n
        SELECT n
        UNION
        FROM  numbers2 n
        SELECT n
       
assert [0, 1, 2, 3, 4] == result
{code}


> [GEP] Support LINQ, aka GINQ
> ----------------------------
>
>                 Key: GROOVY-9159
>                 URL: https://issues.apache.org/jira/browse/GROOVY-9159
>             Project: Groovy
>          Issue Type: New Feature
>            Reporter: Daniel Sun
>            Priority: Major
>              Labels: features
>             Fix For: 4.x
>
>
> h2. *Ⅰ. Background*
> In order to make querying different types of data sources convenient, we need a unified querying interface, i.e. GINQ
> h2. *Ⅱ. Solution*
> The basic rationale can be shown as follows:
>  *Groovy User* ==_writes GINQ code_==> *Parrot Parser* ==generates AST==> *GINQ Engine* ==_translates AST to Stream-Like method invocations_==> *Bytecode Writer*
> h3. {{translates AST to Stream-Like method invocations}} will be designed for different cases:
> h4. 1) target objects are all collections
> translates AST to Java 8+ stream method invocations
> h4. 2) target objects are all DB related objects
> translates AST to *JOOQ* method invocations( [https://github.com/jOOQ/jOOQ] ), which would be implemented as a {{GINQ provider}} in a seperate sub-project(e.g. {{groovy-linq-jooq}}). _Note: *JOOQ* is licensed under *APL2* too_( [https://github.com/jOOQ/jOOQ/blob/master/LICENSE] )
> h4. 3) target objects are XML, CSV, etc. related objects, or even mixed types of objects
> We can treate the case as a special sub-case of case 1
> h3. *Note:*
> {color:#d04437}1. The exact syntax might be altered before introduction, currently working on the general principle.{color}
>  2.GINQ will reuse most of standard SQL syntax, which can make the learning curve smooth and avoid infringing the patent of Microsoft.
>  3. All GINQ related keywords are uppercase to avoid breaking existing source code as possible as we can, e.g. {{FROM}}, {{WHERE}}, {{SELECT}}, etc.
>  4. In order to support type inference better, {{SELECT}} clause is placed at the end of GINQ expression.
>  5. {{alias.VALUE}} is a virtual property and is used to reference the whole record as value. It can be simplified as {{alias}}.
>  6. {{SELECT P1, P2 ... Pn}} is a simplifed syntax of {{SELECT Tuple.tuple(P1, P2 ... Pn)}} and will create a {{List}} of {{Tuple}} sub-class instances when and only when {{n >= 2}}
> h2. *Ⅲ. EBNF*
> h3.   TBD
> h2. *Ⅳ. Examples*
> h3. 1. Filtering
> {code:java}
> @groovy.transform.EqualsAndHashCode
> class Person {
> String name
> int age
> }
> def persons = [new Person(name: 'Daniel', age: 35), new Person(name: 'Peter', age: 10), new Person(name: 'Alice', age: 22)]
> {code}
> h4. 1.1
> {code:java}
> def result =
> FROM persons p
> WHERE p.age > 15 && p.age <= 35
> SELECT p.name
> assert ['Daniel', 'Alice'] == result
> {code}
> {code:java}
> persons.stream().filter(p -> p.age > 15 && p.age <= 35).map(p -> p.name).collect(Collectors.toList())
> {code}
> h4. 1.2
> {code:java}
> def result =
> FROM persons p
> WHERE p.age > 15 && p.age <= 35
> SELECT p
> assert [new Person(name: 'Daniel', age: 35), new Person(name: 'Alice', age: 22)] == result
> {code}
> {code:java}
> persons.stream().filter(p -> p.age > 15 && p.age <= 35).collect(Collectors.toList())
> {code}
> h4. 1.3
> {code:java}
> def numbers = [1, 2, 3]
> def result =
> FROM numbers t
> WHERE t <= 2
> SELECT t
> assert [1, 2] == result
> {code}
> {code:java}
> numbers.stream().filter(t -> t <= 2).collect(Collectors.toList())
> {code}
> h3. 2. Joining
> {code:java}
> import static groovy.lang.Tuple.*
> @groovy.transform.EqualsAndHashCode
> class Person {
> String name
> int age
> City city
> }
> @groovy.transform.EqualsAndHashCode
> class City {
> String name
> }
> def persons = [new Person(name: 'Daniel', age: 35, city: new City('Shanghai')), new Person(name: 'Peter', age: 10, city: new City('Beijing')), new Person(name: 'Alice', age: 22, city: new City('Hangzhou'))]
> def cities = [new City('Shanghai'), new City('Beijing'), new City('Guangzhou')]
> {code}
> h4. 2.1
> {code:java}
> // inner join
> def result =
> FROM persons p INNER JOIN cities c
> ON p.city.name == c.name
> SELECT p.name, c.name
> assert [tuple('Daniel', 'Shanghai'), tuple('Peter', 'Beijing')] == result
> {code}
> {code:java}
> persons.stream()
> .flatMap(p -> cities.stream().filter(c -> p.city.name == c.name).map(c -> tuple(p.name, c.name)))
> .collect(Collectors.toList())
> {code}
> h4. 2.2
> {code:java}
> def result =
> FROM persons p, cities c
> WHERE p.city.name == c.name
> SELECT p.name, c.name
> assert [tuple('Daniel', 'Shanghai'), tuple('Peter', 'Beijing')] == result
> {code}
> {code:java}
> persons.stream()
> .flatMap(p -> cities.stream().filter(c -> p.city.name == c.name).map(c -> tuple(p.name, c.name)))
> .collect(Collectors.toList())
> {code}
> h4. 2.3
> {code:java}
> def result =
> FROM persons p, cities c
> WHERE p.city == c
> SELECT p.name
> assert ['Daniel', 'Peter'] == result
> {code}
> {code:java}
> persons.stream()
> .flatMap(p -> cities.stream().filter(c -> p.city == c).map(c -> p.name))
> .collect(Collectors.toList())
> {code}
> h4. 2.4
> {code:java}
> // left outer join
> def result =
> FROM persons p LEFT JOIN cities c  //  same to LEFT OUTER JOIN
> ON p.city.name == c.name
> SELECT p.name, c.name
> assert [tuple('Daniel', 'Shanghai'), tuple('Peter', 'Beijing'), tuple('Alice', null)] == result
> {code}
> h4. 2.5
> {code:java}
> // right outer join
> def result =
> FROM persons p RIGHT JOIN cities c  //  same to RIGHT OUTER JOIN
> ON p.city.name == c.name
> SELECT p.name, c.name
> assert [tuple('Daniel', 'Shanghai'), tuple('Peter', 'Beijing'), tuple(null, 'Guangzhou')] == result
> {code}
> h3. 3. Projection
> {code:java}
> import static groovy.lang.Tuple.*
> @groovy.transform.EqualsAndHashCode
> class Person {
> String name
> int age
> }
> def persons = [new Person(name: 'Daniel', age: 35), new Person(name: 'Peter', age: 10), new Person(name: 'Alice', age: 22)]
> {code}
> h4. 3.1
> {code:java}
> def result =
> FROM persons p
> SELECT p.name
> assert ['Daniel', 'Peter', 'Alice'] == result
> {code}
> h4. 3.2
> {code:java}
> def result =
> FROM persons p
> SELECT p.name, p.age
> assert [tuple('Daniel', 35), tuple('Peter', 10), tuple('Alice', 22)] == result
> {code}
> h4. 3.3
> {code:java}
> def result =
> FROM persons p
> SELECT [name: p.name, age: p.age]
> assert [ [name: 'Daniel', age: 35], [name: 'Peter', age: 10], [name: 'Alice', age: 22] ] == result
> {code}
> h4. 3.4
> {code:java}
> def result =
> FROM persons p
> SELECT new Person(name: p.name, age: p.age)
> assert persons == result
> {code}
> h4. 3.5
> {code:java}
> def result =
> FROM persons p
> SELECT p.VALUE
> assert persons == result
> {code}
> h4. 3.6
> {code:java}
> def result =
> FROM persons p
> SELECT p
> assert persons == result
> {code}
> h3. 4. Grouping
> {code:java}
> import static groovy.lang.Tuple.*
> @groovy.transform.EqualsAndHashCode
> class Person {
> String name
> int age
> String gender
> }
> def persons = [new Person(name: 'Daniel', age: 35, gender: 'Male'), new Person(name: 'Peter', age: 10, gender: 'Male'), new Person(name: 'Alice', age: 22, gender: 'Female')]
> {code}
> h4. 4.1
> {code:java}
> def result =
> FROM persons p
> GROUP BY p.gender
> SELECT p.gender, MAX(p.age)
> assert [tuple('Male', 35), tuple('Female', 22)] == result
> {code}
> h3. 5. Having
> {code:java}
> import static groovy.lang.Tuple.*
> @groovy.transform.EqualsAndHashCode
> class Person {
> String name
> int age
> String gender
> }
> def persons = [new Person(name: 'Daniel', age: 35, gender: 'Male'), new Person(name: 'Peter', age: 10, gender: 'Male'), new Person(name: 'Alice', age: 22, gender: 'Female')]
> {code}
> h4. 5.1
> {code:java}
> def result =
> FROM persons p
> GROUP BY p.gender
> HAVING p.gender == 'Male'
> SELECT p.gender, MAX(p.age)
> assert [tuple('Male', 35)] == result
> {code}
> h3. 6. Sorting
> {code:java}
> @groovy.transform.EqualsAndHashCode
> class Person {
> String name
> int age
> }
> def persons = [new Person(name: 'Daniel', age: 35), new Person(name: 'Peter', age: 10), new Person(name: 'Alice', age: 22)]
> {code}
> h4. 6.1
> {code:java}
> def result =
> FROM persons p
> ORDER BY p.age
> SELECT p.name
> assert ['Peter', 'Alice', 'Daniel'] == result
> {code}
> h4. 6.2
> {code:java}
> def result =
> FROM persons p
> ORDER BY p.age desc
> SELECT p.name
> assert ['Daniel', 'Alice', 'Peter'] == result
> {code}
> h3. 7. Pagination
> {code:java}
> def numbers = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
> {code}
> h4. 7.1
> {code:java}
> def result =
> FROM numbers n
> LIMIT 5 OFFSET 2
> SELECT n
> assert [2, 3, 4, 5, 6] == result
> {code}
> h4. 7.2
> {code:java}
> def result =
> FROM numbers n
> LIMIT 5
> SELECT n
> assert [0, 1, 2, 3, 4] == result
> {code}
> h3. 8. Nested Queries
> {code:java}
> def numbers = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
> {code}
> h4. 8.1
> {code:java}
> def result =
> FROM (
> FROM numbers n
> WHERE n <= 5
> SELECT n
> ) v
> LIMIT 5 OFFSET 2
> SELECT v
> assert [2, 3, 4, 5] == result
> {code}
> h3. 9. WITH-Clause
> {code:java}
> def numbers = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
> {code}
> h4. 9.1
> {code:java}
> def result =
> WITH v AS (
> FROM numbers n
> WHERE n <= 5
> SELECT n
> )
> FROM  v
> LIMIT 5 OFFSET 2
> SELECT v
> assert [2, 3, 4, 5] == result
> {code}
> h3. 10. Union
> {code:java}
> def numbers1 = [0, 1, 2]
> def numbers2 = [2, 3, 4]
> {code}
> h4. 10.1
> {code:java}
> def result =
> FROM  numbers1 n
> SELECT n
> UNION ALL
> FROM  numbers2 n
> SELECT n
> assert [0, 1, 2, 2, 3, 4] == result
> {code}
> h4. 10.2
> {code:java}
> def result =
> FROM  numbers1 n
> SELECT n
> UNION
> FROM  numbers2 n
> SELECT n
>
> assert [0, 1, 2, 3, 4] == result
> {code}



--
This message was sent by Atlassian JIRA
(v7.6.14#76016)