Details
-
Task
-
Status: Closed
-
Major
-
Resolution: Fixed
-
None
-
None
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();
Attachments
Issue Links
- is required by
-
CAY-2465 New SelectTranslator implementation
- Closed