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

Example 1 with AuthorRecord

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

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

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

use of org.jooq.example.db.h2.tables.records.AuthorRecord 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 ConnectionProvider cp = new DataSourceConnectionProvider(ds);
    final Configuration configuration = new DefaultConfiguration().set(cp).set(SQLDialect.H2).set(new ThreadLocalTransactionProvider(cp, true));
    final DSLContext ctx = DSL.using(configuration);
    final JSONFormat format = new JSONFormat().format(true).header(false).recordFormat(RecordFormat.OBJECT);
    // 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) -> {
        return ctx.transactionResult(() -> {
            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.formatJSON(format);
        });
    });
    // 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 book.formatJSON(format);
        } else {
            // 404 Not found
            response.status(404);
            return "{\"error\":\"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) -> {
        return ctx.transactionResult(() -> {
            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.formatJSON(format);
            } else {
                // 404 Not found
                response.status(404);
                return "{\"error\":\"Book not found\"}";
            }
        });
    });
    // Deletes the book resource for the provided id
    delete("/books/:id", (request, response) -> {
        return ctx.transactionResult(() -> {
            BookRecord book = ctx.deleteFrom(BOOK).where(BOOK.ID.eq(BOOK.ID.getDataType().convert(request.params(":id")))).returning().fetchOne();
            if (book != null) {
                return book.formatJSON(format);
            } else {
                // 404 Not found
                response.status(404);
                return "{\"error\":\"Book not found\"}";
            }
        });
    });
    // Gets all available book resources
    get("/books", (request, response) -> {
        return ctx.select(BOOK.ID, BOOK.TITLE).from(BOOK).fetch().formatJSON(format);
    });
}

Example 5 with AuthorRecord

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

the class Example_2_1_ActiveRecords method run.

@Test
public void run() throws SQLException {
    Connection connection = connection();
    DSLContext dsl = DSL.using(connection);
    AuthorRecord author;
    try {
        Tools.title("Loading and changing active records");
        author = dsl.selectFrom(AUTHOR).where(AUTHOR.ID.eq(1)).fetchOne();
        author.setDateOfBirth(LocalDate.of(2000, 1, 1));
        author.store();
        Tools.print(author);
        Tools.title("Creating a new active record");
        author = dsl.newRecord(AUTHOR);
        author.setId(3);
        author.setFirstName("Alfred");
        author.setLastName("Hitchcock");
        author.store();
        Tools.print(author);
        Tools.title("Refreshing an active record");
        author = dsl.newRecord(AUTHOR);
        author.setId(3);
        author.refresh();
        Tools.print(author);
        Tools.title("Updating an active record");
        author.setDateOfBirth(LocalDate.of(1899, 8, 13));
        author.store();
        Tools.print(author);
        Tools.title("Deleting an active record");
        author.delete();
        Tools.print(dsl.selectFrom(AUTHOR).fetch());
    } finally // Don't store the changes
    {
        connection.rollback();
    }
}