Exemple #1
0
  @Test
  public void test() {
    //        SELECT FIRST_NAME, LAST_NAME, COUNT(*)
    //        FROM AUTHOR
    //        JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
    //       WHERE LANGUAGE = 'DE'
    //         AND PUBLISHED > '2008-01-01'
    //    GROUP BY FIRST_NAME, LAST_NAME
    //      HAVING COUNT(*) > 5
    //    ORDER BY LAST_NAME ASC NULLS FIRST
    //       LIMIT 2
    //      OFFSET 1
    //         FOR UPDATE
    //          OF FIRST_NAME, LAST_NAME

    QAuthor author = QAuthor.author;
    QBook book = QBook.book;
    MySQLQuery query = new MySQLQuery(null);
    query
        .from(author)
        .join(book)
        .on(author.id.eq(book.authorId))
        .where(book.language.eq("DE"), book.published.eq(new Date()))
        .groupBy(author.firstName, author.lastName)
        .having(Wildcard.count.gt(5))
        .orderBy(author.lastName.asc())
        .limit(2)
        .offset(1)
        .forUpdate();
    // of(author.firstName, author.lastName)

    query.getMetadata().addProjection(author.firstName);
    query.getMetadata().addProjection(author.lastName);
    query.getMetadata().addProjection(Wildcard.count);

    SQLSerializer serializer = new SQLSerializer(new Configuration(new MySQLTemplates()));
    serializer.serialize(query.getMetadata(), false);

    assertEquals(
        "select author.FIRST_NAME, author.LAST_NAME, count(*)\n"
            + "from AUTHOR author\n"
            + "join BOOK book\n"
            + "on author.ID = book.AUTHOR_ID\n"
            + "where book.LANGUAGE = ? and book.PUBLISHED = ?\n"
            + "group by author.FIRST_NAME, author.LAST_NAME\n"
            + "having count(*) > ?\n"
            + "order by author.LAST_NAME asc\n"
            + "limit ?\n"
            + "offset ?\n"
            + "for update",
        serializer.toString());
  }