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

Example 1 with Book

use of org.jooq.example.db.h2.tables.Book 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 Book

use of org.jooq.example.db.h2.tables.Book in project SimpleFlatMapper by arnaudroger.

the class Example_One_To_Many method authorsAndBooks.

@Test
public void authorsAndBooks() throws SQLException {
    // All we need to execute a query is provide it with a connection and then
    // call fetch() on it.
    Tools.title("Selecting authorsAndBooks");
    JdbcMapper<Tuple2<AuthorRecord, List<BookRecord>>> mapper = JdbcMapperFactory.newInstance().addKeys("id").newMapper(new TypeReference<Tuple2<AuthorRecord, List<BookRecord>>>() {
    });
    try (ResultSet rs = DSL.using(connection()).select(AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, AUTHOR.DATE_OF_BIRTH, BOOK.ID, BOOK.TITLE).from(AUTHOR).leftJoin(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)).orderBy(AUTHOR.ID).fetchResultSet()) {
        mapper.stream(rs).forEach(Tools::print);
    }
}

Example 3 with Book

use of org.jooq.example.db.h2.tables.Book in project SimpleFlatMapper by arnaudroger.

the class Example_One_To_Many method authorsAndBooksAndBookStore.

@Test
public void authorsAndBooksAndBookStore() throws SQLException {
    // All we need to execute a query is provide it with a connection and then
    // call fetch() on it.
    Tools.title("Selecting authorsAndBooksAndBookStore");
    JdbcMapper<Tuple2<AuthorRecord, List<Tuple2<BookRecord, List<BookToBookStoreRecord>>>>> mapper = JdbcMapperFactory.newInstance().addKeys("ID", "BOOK_STORE_NAME").newMapper(new TypeReference<Tuple2<AuthorRecord, List<Tuple2<BookRecord, List<BookToBookStoreRecord>>>>>() {
    });
    try (ResultSet rs = DSL.using(connection()).select(AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, AUTHOR.DATE_OF_BIRTH, BOOK.ID, BOOK.TITLE, BOOK_TO_BOOK_STORE.BOOK_STORE_NAME, BOOK_TO_BOOK_STORE.STOCK).from(AUTHOR).leftJoin(BOOK).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)).leftJoin(BOOK_TO_BOOK_STORE).on(BOOK_TO_BOOK_STORE.BOOK_ID.eq(BOOK.ID)).orderBy(AUTHOR.ID).fetchResultSet()) {
        mapper.stream(rs).forEach(Tools::print);
    }
}

Example 4 with Book

use of org.jooq.example.db.h2.tables.Book 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 5 with Book

use of org.jooq.example.db.h2.tables.Book 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)));
}