Examples with AUTHOR org.jooq.example.db.h2.Tables.AUTHOR used on opensource projects

Example 1 with AUTHOR

use of org.jooq.example.db.h2.Tables.AUTHOR in project jOOQ by jOOQ.

the class SparkCRUD method upsertAuthor.

private static AuthorRecord upsertAuthor(final DSLContext ctx, Request request) {
    AuthorRecord author = ctx.selectFrom(AUTHOR).where(AUTHOR.NAME.eq(request.queryParams("author"))).fetchOne();
    if (author == null)
        author = ctx.newRecord(AUTHOR);
    author.setName(request.queryParams("author"));
    author.store();
    return author;
}

Example 2 with AUTHOR

use of org.jooq.example.db.h2.Tables.AUTHOR 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 3 with AUTHOR

use of org.jooq.example.db.h2.Tables.AUTHOR in project jOOQ by jOOQ.

the class Tests method testName.

@Test
public void testName() {
    Author author = service.getAuthor(1);
    assertEquals("George", author.getFirstName());
    assertEquals("Orwell", author.getLastName());
}

Example 4 with AUTHOR

use of org.jooq.example.db.h2.Tables.AUTHOR in project jOOQ by jOOQ.

the class SparkCRUD method main.

public static void main(String[] args) throws Exception {
    final BasicDataSource ds = new BasicDataSource();
    final Properties properties = new Properties();
    properties.load(SparkCRUD.class.getResourceAsStream("/config.properties"));
    ds.setDriverClassName(properties.getProperty("db.driver"));
    ds.setUrl(properties.getProperty("db.url"));
    ds.setUsername(properties.getProperty("db.username"));
    ds.setPassword(properties.getProperty("db.password"));
    final DSLContext ctx = DSL.using(ds, SQLDialect.H2);
    // Creates a new book resource, will return the ID to the created resource
    // author and title are sent as query parameters e.g. /books?author=Foo&title=Bar
    post("/books", (request, response) -> {
        AuthorRecord author = upsertAuthor(ctx, request);
        BookRecord book = ctx.newRecord(BOOK);
        book.setAuthorId(author.getId());
        book.setTitle(request.queryParams("title"));
        book.store();
        // 201 Created
        response.status(201);
        return book.getId();
    });
    // Gets the book resource for the provided id
    get("/books/:id", (request, response) -> {
        Record2<String, String> book = ctx.select(BOOK.TITLE, AUTHOR.NAME).from(BOOK).join(AUTHOR).on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)).where(BOOK.ID.eq(BOOK.ID.getDataType().convert(request.params(":id")))).fetchOne();
        if (book != null) {
            return "Title: " + book.value1() + ", Author: " + book.value2();
        } else {
            // 404 Not found
            response.status(404);
            return "Book not found";
        }
    });
    // Updates the book resource for the provided id with new information
    // author and title are sent as query parameters e.g. /books/<id>?author=Foo&title=Bar
    put("/books/:id", (request, response) -> {
        BookRecord book = ctx.selectFrom(BOOK).where(BOOK.ID.eq(BOOK.ID.getDataType().convert(request.params(":id")))).fetchOne();
        if (book != null) {
            AuthorRecord author = upsertAuthor(ctx, request);
            String newAuthor = request.queryParams("author");
            String newTitle = request.queryParams("title");
            if (newAuthor != null) {
                book.setAuthorId(author.getId());
            }
            if (newTitle != null) {
                book.setTitle(newTitle);
            }
            book.update();
            return "Book with id '" + book.getId() + "' updated";
        } else {
            // 404 Not found
            response.status(404);
            return "Book not found";
        }
    });
    // Deletes the book resource for the provided id
    delete("/books/:id", (request, response) -> {
        BookRecord book = ctx.deleteFrom(BOOK).where(BOOK.ID.eq(BOOK.ID.getDataType().convert(request.params(":id")))).returning().fetchOne();
        if (book != null) {
            return "Book with id '" + book.getId() + "' deleted";
        } else {
            // 404 Not found
            response.status(404);
            return "Book not found";
        }
    });
    // Gets all available book resources (id's)
    get("/books", (request, response) -> {
        return ctx.select(BOOK.ID).from(BOOK).fetch(BOOK.ID).stream().map(Object::toString).collect(Collectors.joining(" "));
    });
}

Example 5 with AUTHOR

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