package org.jooq.example.spring;
import static java.util.Arrays.asList;
import static org.jooq.example.db.h2.Tables.AUTHOR;
import static org.jooq.example.db.h2.Tables.BOOK;
import static org.jooq.example.db.h2.Tables.BOOK_STORE;
import static org.jooq.example.db.h2.Tables.BOOK_TO_BOOK_STORE;
import static org.jooq.impl.DSL.countDistinct;
import static org.junit.Assert.assertEquals;
import org.jooq.DSLContext;
import org.jooq.Record3;
import org.jooq.Result;
import org.jooq.example.db.h2.tables.Author;
import org.jooq.example.db.h2.tables.Book;
import org.jooq.example.db.h2.tables.BookStore;
import org.jooq.example.db.h2.tables.BookToBookStore;
import org.jooq.example.db.h2.tables.records.BookRecord;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.SpringApplicationConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
/**
* @author Lukas Eder
* @author Thomas Darimont
*/
@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = Application.class)
public class QueryTest {
@Autowired DSLContext create;
@Test
public void testJoin() throws Exception {
// All of these tables were generated by jOOQ's Maven plugin
Book b = BOOK.as("b");
Author a = AUTHOR.as("a");
BookStore s = BOOK_STORE.as("s");
BookToBookStore t = BOOK_TO_BOOK_STORE.as("t");
Result<Record3<String, String, Integer>> result = create.select(a.FIRST_NAME, a.LAST_NAME, countDistinct(s.NAME))
.from(a).join(b).on(b.AUTHOR_ID.equal(a.ID)).join(t).on(t.BOOK_ID.equal(b.ID)).join(s)
.on(t.BOOK_STORE_NAME.equal(s.NAME)).groupBy(a.FIRST_NAME, a.LAST_NAME).orderBy(countDistinct(s.NAME).desc())
.fetch();
assertEquals(2, result.size());
assertEquals("Paulo", result.getValue(0, a.FIRST_NAME));
assertEquals("George", result.getValue(1, a.FIRST_NAME));
assertEquals("Coelho", result.getValue(0, a.LAST_NAME));
assertEquals("Orwell", result.getValue(1, a.LAST_NAME));
assertEquals(Integer.valueOf(3), result.getValue(0, countDistinct(s.NAME)));
assertEquals(Integer.valueOf(2), result.getValue(1, countDistinct(s.NAME)));
}
@Test
public void testActiveRecords() throws Exception {
Result<BookRecord> result = create.selectFrom(BOOK).orderBy(BOOK.ID).fetch();
assertEquals(4, result.size());
assertEquals(asList(1, 2, 3, 4), result.getValues(0));
}
}