Examples with DSLContext org.jooq.DSLContext used on opensource projects

Example 21 with DSLContext

use of org.jooq.DSLContext 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 22 with DSLContext

use of org.jooq.DSLContext 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(Date.valueOf("2000-01-01"));
        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(Date.valueOf("1899-08-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();
    }
}

Example 23 with DSLContext

use of org.jooq.DSLContext in project jOOQ by jOOQ.

the class Example_2_2_OptimisticLocking method run.

@Test
public void run() throws SQLException {
    Connection connection = connection();
    DSLContext dsl = DSL.using(connection, new Settings().withExecuteWithOptimisticLocking(true));
    try {
        Tools.title("Applying optimistic locking");
        BookRecord book1 = dsl.selectFrom(BOOK).where(BOOK.ID.eq(1)).fetchOne();
        BookRecord book2 = dsl.selectFrom(BOOK).where(BOOK.ID.eq(1)).fetchOne();
        book1.setTitle("New Title");
        book1.store();
        book2.setTitle("Another Title");
        book2.store();
    } catch (DataChangedException expected) {
        expected.printStackTrace();
    } finally // Don't store the changes
    {
        connection.rollback();
    }
}

Example 24 with DSLContext

use of org.jooq.DSLContext in project jOOQ by jOOQ.

the class DAOImpl method records.

private /* non-final */
List<R> records(Collection<P> objects, boolean forUpdate) {
    List<R> result = new ArrayList<R>();
    Field<?>[] pk = pk();
    for (P object : objects) {
        // [#2536] Upon store(), insert(), update(), delete(), returned values in the record
        //         are copied back to the relevant POJO using the RecordListener SPI
        DSLContext ctx = using(!FALSE.equals(configuration.settings().isReturnRecordToPojo()) ? configuration.derive(providers(configuration.recordListenerProviders(), object)) : configuration);
        R record = ctx.newRecord(table, object);
        if (forUpdate && pk != null)
            for (Field<?> field : pk) record.changed(field, false);
        Tools.resetChangedOnNotNull(record);
        result.add(record);
    }
    return result;
}

Example 25 with DSLContext

use of org.jooq.DSLContext in project jOOQ by jOOQ.

the class AbstractDMLQuery method execute.

@SuppressWarnings({ "rawtypes", "unchecked" })
@Override
protected final int execute(ExecuteContext ctx, ExecuteListener listener) throws SQLException {
    if (returning.isEmpty()) {
        return super.execute(ctx, listener);
    } else {
        int result = 1;
        ResultSet rs;
        switch(ctx.family()) {
            // SQLite can select _rowid_ after the insert
            case SQLITE:
                {
                    listener.executeStart(ctx);
                    result = ctx.statement().executeUpdate();
                    ctx.rows(result);
                    listener.executeEnd(ctx);
                    DSLContext create = DSL.using(ctx.configuration());
                    returned = create.select(returning).from(table).where(rowid().equal(rowid().getDataType().convert(create.lastID()))).fetchInto(table);
                    return result;
                }
            case CUBRID:
                {
                    listener.executeStart(ctx);
                    result = ctx.statement().executeUpdate();
                    ctx.rows(result);
                    listener.executeEnd(ctx);
                    selectReturning(ctx.configuration(), create(ctx.configuration()).lastID());
                    return result;
                }
            case DERBY:
            case H2:
            case MARIADB:
            case MYSQL:
                {
                    listener.executeStart(ctx);
                    result = ctx.statement().executeUpdate();
                    ctx.rows(result);
                    listener.executeEnd(ctx);
                    rs = ctx.statement().getGeneratedKeys();
                    try {
                        List<Object> list = new ArrayList<Object>();
                        // from getGeneratedKeys() sometimes
                        if (rs != null)
                            while (rs.next()) list.add(rs.getObject(1));
                        selectReturning(ctx.configuration(), list.toArray());
                        return result;
                    } finally {
                        JDBCUtils.safeClose(rs);
                    }
                }
            // in the Postgres JDBC driver
            case FIREBIRD:
            case POSTGRES:
                {
                    listener.executeStart(ctx);
                    rs = ctx.statement().executeQuery();
                    listener.executeEnd(ctx);
                    break;
                }
            case HSQLDB:
            default:
                {
                    listener.executeStart(ctx);
                    result = ctx.statement().executeUpdate();
                    ctx.rows(result);
                    listener.executeEnd(ctx);
                    rs = ctx.statement().getGeneratedKeys();
                    break;
                }
        }
        ExecuteContext ctx2 = new DefaultExecuteContext(ctx.configuration());
        ExecuteListener listener2 = new ExecuteListeners(ctx2);
        ctx2.resultSet(rs);
        returned = new CursorImpl<R>(ctx2, listener2, fieldArray(returning), null, false, true).fetch();
        // [#3682] Plain SQL tables do not have any fields
        if (table.fields().length > 0)
            returned = returned.into(table);
        //         execute this logic
        if (returned.size() > 0 || ctx.family() != HSQLDB) {
            result = returned.size();
            ctx.rows(result);
        }
        return result;
    }
}