many to many relationship with sorting

Question

i am trying to achieve a MANY_TO_MANY relationship between two entities with an additional attribute in the join table. i found the following answer: how-to-do-a-many-to-many-relationship-in-spring-roo-with-attributes-within-de-relationship but i failed trying to adapt the offered solution to my scenario:

currently i have a straight forward MANY_TO_MANY relationship:

 # book:
 entity jpa --class ~.domain.Book --testAutomatically
 field string --fieldName title --notNull
 field string --fieldName subtitle --notNull
 field string --fieldName description
 # n:m relationship to author
 field list --fieldName authors --type ~.domain.Author --cardinality MANY_TO_MANY

 # author:
 entity jpa --class ~.domain.Author --testAutomatically
 field string --fieldName firstName --notNull
 field string --fieldName lastName --notNull

that works as expected, but i need to have the authors ordered. i would like to achieve this by defining the relationship table and adding a integer field like 'sequence' to it, but i got stuck when i try to define the many-to-many relationship in Book:

 entity jpa --class ~.domain.BookAuthorOrdered  --table book_author_ordered
 # the additional field to store sequence of authors:
 field number --fieldName authorSequence --type java.lang.Integer --notNull
 # reference to book:
 field reference --fieldName bookId --type ~.domain.Book --cardinality MANY_TO_ONE 

 # reference to author:
 field reference --fieldName authorId --type ~.domain.Author --cardinality MANY_TO_ONE

can anyone give me a hint how to define the attribute in Book so that i get a list of sorted authors using the above defined join table? here is something i tried:

 # complete the relationship
 focus --class ~.domain.Book
 field list --type ~.domain.BookAuthorOrdered --fieldName orderedAuthors --cardinality ONE_TO_MANY --mappedBy bookId

Answer 1

i found a more or less working solution for my problem, but it still has some drawbacks. it is mostly derived from the above mentioned answer: How to do a many-to-many relationship in spring Roo, with attributes within de relationship?

create join table manually with ids of the tables book and author as composed primary key: roo> entity jpa --class ~.domain.BookAuthorOrdered --table book_author_ordered --identifierType ~.domain.BookAuthorOrderedId field number --fieldName authorSequence --type java.lang.Integer --notNull

then edit the generated id class BookAuthorOrderedId and add the composed primary key:

@RooToString
@RooEquals
@RooIdentifier
public final class BookAuthorOrderedId {
   @ManyToOne
    @JoinColumn(name="book_id", insertable = false, updatable = false)
    private Book book;

   @ManyToOne
    @JoinColumn(name="author_id", insertable = false, updatable = false)
    private Author author;
}

now comes the part where i have a bad feeling, because it looks like a workaround to me. in the book entity i pushed in the getter method for authors and replaced it with the query of the join table:

   public List<Author> getAuthors()
    {

        // return Author.findAllAuthorsOfBook(this);


        System.out.println("getAuthors()");
        EntityManager em = new Book().entityManager;
        TypedQuery<Author> q = em.createQuery("SELECT o FROM Author AS o WHERE o.id IN ( SELECT OA.id.author FROM eu.books2ebooks.roomanova.domain.BookAuthorOrdered OA where OA.id.book = :book ) ", Author.class);
        q.setParameter("book", this);
        List<Author> authorList = q.getResultList(); 
        //this.setAuthors(authorList);
        return authorList;
    }

and a method to get the bean value directly for fresh added authors:

public List<Author> getAddedAuthors() {
    return this.authors;
}

then i had to manipulate the book controller to call a self written method to insert/update the join table (at create/update/..):

public static void setOrderedBookAuthors(Book book, List<Author> authors) {
    // delete all associated authors of book:
    List<BookAuthorOrdered> bookAuthorOrdereds = BookAuthorOrdered.findAllBookAuthorOrdersOfBook(book);
    for (BookAuthorOrdered bookAuthorOrdered : bookAuthorOrdereds) {
        log.info("removing book author: " + printAuthor(bookAuthorOrdered.getId().getAuthor()));
        bookAuthorOrdered.remove();
    }
    if ( authors == null )
    {
        log.info("ordered authors: null. nothing to insert.");
        return;
    }
    log.info("inserting sorted authors: ");
    Integer authorSequence = 1;
    for (Author author : authors) {
        log.info("inserting book author sorted: " + printAuthor(author) + " as no " + authorSequence);
        BookAuthorOrdered bookAuthorOrdered = new BookAuthorOrdered();
        bookAuthorOrdered.setAuthorSequence(authorSequence);
        BookAuthorOrderedId id = new BookAuthorOrderedId(book, author);
        bookAuthorOrdered.setId(id);
        log.info("book author ordered: " + bookAuthorOrdered);
        bookAuthorOrdered.persist();

        authorSequence++;
    }
}

so far that works but it have the feeling that there must be a much more elegant way...

Answer 2

Try to add the @OrderBy annotation to entity field (in .java file). By example:

   @ManyToMany
   @OrderBy("lastName ASC, firstName ASC")
   private List<Author> authors;