Uploaded image for project: 'Cayenne'
  1. Cayenne
  2. CAY-2466

New internal API to build SQL

    XMLWordPrintableJSON

    Details

      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

            Activity

              People

              • Assignee:
                ntimofeev Nikita Timofeev
                Reporter:
                ntimofeev Nikita Timofeev
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: