[CAY-2466] New internal API to build SQL

• Type: Task
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.2.M1
• Component/s: Core Library, Database integration
• Assignee: Nikita Timofeev
• Reporter: Nikita Timofeev
• Created: 29/Aug/18 08:56
• Updated: 09/Jan/19 12:10
• Resolved: 09/Jan/19 12:10

Description

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:

// 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();

Issue Links

• is required by: CAY-2465 New SelectTranslator implementation