@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());
}