Examples with BookStore org.jooq.example.db.h2.tables.BookStore used on opensource projects

Example 1 with BookStore

use of org.jooq.example.db.h2.tables.BookStore in project jOOQ by jOOQ.

the class QueryTest method testJoin.

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

Example 2 with BookStore

use of org.jooq.example.db.h2.tables.BookStore in project jOOQ by jOOQ.

the class JdbcTemplateTest method testExecuteQueryWithJdbcTemplate.

@Test
public void testExecuteQueryWithJdbcTemplate() 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");
    ResultQuery<Record3<String, String, Integer>> query = 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());
    JdbcTemplate template = new JdbcTemplate(dataSource);
    List<A> result = template.query(query.getSQL(), query.getBindValues().toArray(), (r, i) -> new A(r.getString(1), r.getString(2), r.getInt(3)));
    assertEquals(2, result.size());
    assertEquals("Paulo", result.get(0).firstName);
    assertEquals("George", result.get(1).firstName);
    assertEquals("Coelho", result.get(0).lastName);
    assertEquals("Orwell", result.get(1).lastName);
    assertEquals(3, result.get(0).books);
    assertEquals(2, result.get(1).books);
}

Example 3 with BookStore

use of org.jooq.example.db.h2.tables.BookStore in project jOOQ by jOOQ.

the class QueryTest method testJoin.

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