Jooq insert one2one and one2many relational data into 2 tables at once. How to deal with issues and rollback

F.e. model

class Author {
  id, name
}

class Book {
  id, title, author
}


create table author (
    id bigint not null auto_increment,
    name varchar,
    CONSTRAINT author_pk PRIMARY KEY (id),
);
create table book (
    id bigint not null auto_increment,
    title varchar,
    author_id bigint,
    CONSTRAINT book_pk PRIMARY KEY (id),
    CONSTRAINT book_fk_author FOREIGN KEY (author_id) REFERENCES author (id) ON DELETE NO ACTION ON UPDATE NO ACTION
)

How to insert it at once in jooq? How to deal with rollback if some query will fail? Is it has to be programated on my side? I can not find a word about such case in documentation 🙁

Should Id do it manually like:

1. find author id in db by name
2. if author not exist insert new author
3. insert book with author id
4. if book insertion fail then delete author if was inserted this time?

How to deal with it? Do You have any examples for that?

EDIT: I use h2 database

Answer

Simon has already provided you with feedback about the transactionality of your logic. Your 4 steps can be encoded as follows (assuming you’re using the code generator):

// You probably have some DSLContext injected to your repository/dao/whatever somewhere
DSLContext ctx = ...

// 4. Start the transaction to make the contents atomic and roll back on failure
// In order to use a transactional configuration and DSLContext, make sure you don't use
// the "outer" DSLContext, which, depending on its Configuration might not be 
// transactional. E.g. it isn't transactional when it references a DataSource. It might
// be transactional (by accident), if it wraps a JDBC Connection.
ctx.transaction(c -> {

    // 1. try to find the author by ID. Alternatively, write a query
    AuthorRecord author = c.dsl().fetchOne(AUTHOR, AUTHOR.ID.eq(id));

    // 2. If the author wasn't found, create it
    if (author == null) {
        author = c.dsl().newRecord(AUTHOR);
        author.setName(name);

        // 2. This will store the author and by default, automatically fetch the 
        // generated ID
        author.store();
    }

    // 3. Insert the book with the previously fetched author ID
    BookRecord book = c.dsl().newRecord(BOOK);
    book.setAuthorId(author.getId());
    book.setTitle(title);
    book.store();
}

Instead of using the jOOQ transaction API, of course, you can use any other means of providing transactionality to your code, including Spring, Java EE, or JDBC directly.