Avoiding casts when narrowing jooq selects

Question

Let's say I have a book database and I want to check whether the CLRS book has the correct authors.

Assuming private static final String CLRS_title = "Introduction to Algorithms";

    @Test
    public void CLRS_is_written_by_CLRS(){
        //given        
        SelectConditionStep<Record> query = create
                .select()
                    .from(
                            (
                                    BOOK.leftOuterJoin(BOOK_AUTHOR).on(BOOK.ID.eq(BOOK_AUTHOR.BOOKID))
                            ).leftOuterJoin(AUTHOR).on(AUTHOR.ID.eq(BOOK_AUTHOR.AUTHORID))
                    )
                    .where(BOOK.TITLE.eq(CLRS_title))
                ;

        //when
        Result<Record> result = query.fetch();
        List<String> authorNames = result.stream().map(r-> r.getValue(AUTHOR.LASTNAME)).collect(Collectors.toList());

        //then
        assertThat(authorNames.size(),is(4));
        assertThat(authorNames.containsAll(Arrays.asList("Cormen","Leiserson","Rivest","Stein")), is(true));

    }

(Please ignore that it's very inefficient to join a whole table when we're just interested in one book, I'll make a separate question about that, if/when necessary.)

I now just want to select the AUTHOR.LASTNAME property instead of just everything.

SelectConditionStep<Record1<String>> query = create
        .select(AUTHOR.LASTNAME.as("AuthorName"))
            .from(
                    (
                            BOOK.leftOuterJoin(BOOK_AUTHOR).on(BOOK.ID.eq(BOOK_AUTHOR.BOOKID))
                    ).leftOuterJoin(AUTHOR).on(AUTHOR.ID.eq(BOOK_AUTHOR.AUTHORID))
            )
            .where(BOOK.TITLE.eq(CLRS_title))
        ;

//when
Result<Record1<String>> result = query.fetch();
List<String> authorNames = result.stream().map(r-> (String)r.getValue("AuthorName")).collect(Collectors.toList());

//then
assertThat(authorNames.size(),is(4));
assertThat(authorNames.containsAll(Arrays.asList("Cormen","Leiserson","Rivest","Stein")), is(true));

the cast to String in the authorNames generation is required because without it, I cannot compile because

Incompatible types. Required List but 'collect' was inferred to R: no instance(s) of type variable(s) exist so that Object conforms to String inference variable T has incompatible bounds: equality constraints: String lower bounds: Object

Is there a way to avoid this cast and still get the more narrow select?

Dear God!This code looks like making a burrito just for one byte! @User1291 — MS90, Mar 19 '19 at 09:06
@MS90 Thanks for the laugh. :) I am trying to get into JOOQ, so the queries may be a tiny bit more complicated than would be strictly necessary. (e.g, I am aware we could do away with the entire join on the `BOOK` table, but it's necessary when we do not assume we know the ID to be 1). — User1291, Mar 19 '19 at 09:12
No problem, can you tell me what are you trying to do, step-by-step approach would be the best in order to do it together. — MS90, Mar 19 '19 at 09:27
Well, I have `Book`, `Author` and `Book_Author` tables. `Book`s and `Author`s have IDs and the `Book_Author` contains the mapping from `Book.ID`s to the `Author.ID`s. So the query joins the `Book` with the `Book_Author` tables and then the resulting table with the `Author` table, using the IDs to tie it all together. Then we select the book we want and get a the last names of all the authors of that book. I now want to turn `SELECT *` into `SELECT author.lastname as AuthorName`, which is what the 2nd version does. But doing so requires me to change from `Record` to `Record1` and... — User1291, Mar 19 '19 at 09:33
... that in turn requires me to insert a `(String)` when I extract the names into the `authorNames` list. I now want to get rid of that cast. @MS90 — User1291, Mar 19 '19 at 09:34
@MS90 also replaced the `where` to now use the book title rather than its id, to make it clearer why the darn query is so big. — User1291, Mar 19 '19 at 09:41
I was wrong, stew was prepared while waiting for a byte of burrito. @User1291. — MS90, Mar 19 '19 at 09:47
OK, so first question with this approach would by, why three tables for two entities and your cause? Book and Author would be suffice in your case with many-to-many mapping. Have you tried implementing it that way? @User1291 — MS90, Mar 19 '19 at 09:48
@MS90 Actually, I am not aware of any N:M mapping scheme that would allow me to get away with 2 entities unless I want to duplicate HUGE amounts of data. So no, I haven't, I went straight for the junction table. — User1291, Mar 19 '19 at 09:53
OK, lets go with your approach. Now how can we pull out all authors of selected book from book_author table ( book_id, author_id )? @User1291 — MS90, Mar 19 '19 at 10:06
@MS90 We get the `book_id` from the book we want, then get all the entries in `book_author` that have it and return all authors in `author` whose `author_id`s are mentioned in these entries... I did mention that GETTING the entries wasn't the issue? — User1291, Mar 19 '19 at 10:11
OK, one Q. Why going thru book_author entity if we would like to get every author whose book_id is 9? Can we somehow do processing over author table since I assume there is a book_id in author table. So lets say we query 3 authors with book_id=9. What should we do now? @User1291 — MS90, Mar 19 '19 at 10:25
@MS90 there is no `book_id` in the `author` table because an author could have written multiple books. That's the point of having a junction table. — User1291, Mar 19 '19 at 10:27
OK, so why do we make same mistake by book_author table then? :-) What about inside your Author entity puttings smt like @ManyToMany(mappedBy = "authors") private final List books = new ArrayList<>(); and inside your Book putting @ManyToMany(mappedBy = "books") private final List authors = new ArrayList<>(); ? @User1291 — MS90, Mar 19 '19 at 10:45

Lukas Eder · Answer 1 · 2019-03-20T08:31:28.040

Without aliasing

I don't see why you rename your column. Just...

.select(AUTHOR.LASTNAME)

And then

List<String> authorNames = result.getValues(AUTHOR.LASTNAME);

With aliasing

If you must alias it, then it helps assigning the aliased column expression to a local variable:

Field<String> lastname = AUTHOR.LASTNAME.as("lastname");

// ...
.select(lastname)

... and then:

List<String> authorNames = result.getValues(lastname);

Alternatively, you can repeat the aliasing expression twice

.select(AUTHOR.LASTNAME.as("lastname"))

... and then:

List<String> authorNames = result.getValues(AUTHOR.LASTNAME.as("lastname"));

Avoiding casts when narrowing jooq selects

1 Answers1

Without aliasing

With aliasing

Linked