@Override
public BookModel selectBook(int id) {
return create
.select(
BOOK.ID_BOOK,
BOOK.TITLE,
BOOK.SHORT_DESCRIPTION,
BOOK.DATE_PUBLISH,
PostgresDSL.arrayAgg(
concat(
AUTHOR.ID_AUTHOR,
val(","),
AUTHOR.FIRST_NAME,
val(","),
AUTHOR.SECOND_NAME))
.as("authors"))
.from(AUTHOR)
.join(AUTHOR_BOOK)
.on(AUTHOR_BOOK.ID_AUTHOR.equal(AUTHOR.ID_AUTHOR))
.join(BOOK)
.on(BOOK.ID_BOOK.equal(AUTHOR_BOOK.ID_BOOK))
.where(BOOK.STATUS.equal(true))
.and(BOOK.ID_BOOK.equal(id))
.groupBy(BOOK.ID_BOOK)
.fetchOneInto(BookModel.class);
}
@Override
public List<Book> selectBooksByIdAuthor(int idAuthor) {
return create
.select()
.from(BOOK)
.join(AUTHOR_BOOK)
.on(BOOK.ID_BOOK.equal(AUTHOR_BOOK.ID_BOOK))
.join(AUTHOR)
.on(AUTHOR.ID_AUTHOR.equal(AUTHOR_BOOK.ID_AUTHOR))
.where(AUTHOR.ID_AUTHOR.equal(idAuthor))
.and(BOOK.STATUS.equal(true))
.fetchInto(Book.class);
}
@Override
public List<BookModel> selectAll() {
List<BookModel> books =
create
.select(
BOOK.ID_BOOK,
BOOK.TITLE,
BOOK.SHORT_DESCRIPTION,
BOOK.DATE_PUBLISH,
PostgresDSL.arrayAgg(concat(AUTHOR.FIRST_NAME, val(" "), AUTHOR.SECOND_NAME))
.as("authors"))
.from(AUTHOR)
.join(AUTHOR_BOOK)
.on(AUTHOR_BOOK.ID_AUTHOR.equal(AUTHOR.ID_AUTHOR))
.join(BOOK)
.on(BOOK.ID_BOOK.equal(AUTHOR_BOOK.ID_BOOK))
.where(BOOK.STATUS.equal(true))
.groupBy(BOOK.ID_BOOK)
.fetchInto(BookModel.class);
logger.debug("Books: " + books);
return books;
}