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

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

Nikita Timofeev created CAY-2466:
------------------------------------

             Summary: 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
             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)