[jira] [Closed] (CAY-2466) New internal API to build SQL

["Nikita Timofeev (JIRA)" <ji...@apache.org>]

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

Nikita Timofeev closed CAY-2466.
--------------------------------
    Resolution: Fixed

https://github.com/apache/cayenne/commit/89439015fc7e3fd88e8a4846bea953b43f003717

> New internal API to build SQL
> -----------------------------
>
>                 Key: CAY-2466
>                 URL: https://issues.apache.org/jira/browse/CAY-2466
>             Project: Cayenne
>          Issue Type: Task
>          Components: Core Library, Database integration
>            Reporter: Nikita Timofeev
>            Assignee: Nikita Timofeev
>            Priority: Major
>             Fix For: 4.2.M1
>
>
> As a part of CAY-2465 we need a better way to construct a SQL strings, than a simple in-place string concatenation.
> It can build a SQL tree that can be modified later for specific DB dialect and easily processed into a final SQL.
> At first it will be used as part of new {{SelectTranslator}} but other usages (including some public API) should be kept in mind.
> In my prototype I came to something like this: 
> {code}
> // import static org.apache.cayenne.access.sqlbuilder.SQLBuilder.*;
> Node slqNode = select(table("a").column("ARTIST_ID").as("a_id"),
>         count(table("p").column("PAINTING_TITLE")).as("p_count"))
>         .distinct()
>         .from(table("ARTIST").as("a"))
>         .from(leftJoin(table("PAINTING").as("p"))
>                         .on(table("a").column("ARTIST_ID")
>                                 .eq(table("p").column("ARTIST_ID"))
>                                 .and(table("p").column("ESTIMATED_PRICE").gt(value(10)))))
>         .where(
>                 table("a").column("ARTIST_NAME")
>                         .eq(value("Picasso"))
>                         .and(exists(select(all())
>                                         .from(table("GALLERY").as("g"))
>                                .where(table("g").column("GALLERY_ID").eq(table("p").column("GALLERY_ID")))))
>                         .and(value(1).eq(value(1)))
>                         .or(value(false)))
>         .groupBy(table("a").column("ARTIST_ID"))
>         .having(not(count(table("p").column("PAINTING_TITLE")).gt(value(3))))
>         .orderBy(column("p_count").desc())
>         .build();
> SQLGenerationVisitor visitor = new SQLGenerationVisitor();
> slqNode.visit(visitor);
> String sql = visitor.getSQLString();
> {code}



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)