0

I have a problem of creating NamedQuery with Hibernate. The problem is I need to select a list of Books not appearing in Orders. My classes looks something like this:

@Entity
@NamedQueries({ @NamedQuery(name = "Book.findAvailable",
        query = "SELECT b FROM Book b WHERE b.id not in ????????") })
public class Book {

    @Id
    @GeneratedValue
    private Long id;

......

and Order:

@Entity(name = "orders")
public class Order {

    @Id
    @GeneratedValue
    private Long id;

    @ElementCollection
    private List<Book> items;
.....

As you see, I keep my Books in order in a list. The Query I need should pull out all the books from the DB which don't apear in any order. Any help is appreciated. Many thanks.

DruidKuma
  • 2,352
  • 2
  • 17
  • 20
  • well i am suggesting a simple thing to do , add a boolean column to book and if at least one order List has it in it then make that flag true, so later you will find all books that have that flag false , i am not quite sure though cause when you delete a book from an order you have to check 1st if it exists in another order list before you put that flag to false , – AntJavaDev Sep 26 '14 at 15:01
  • 1
    Well, that's a good idea. And the way I have it implemented now is exactly this) though my lead said it is not the best decision to keep another Boolean variable to check book's availability. Better way is to put indexes on book's id and do the thing via namedquery :) – DruidKuma Sep 26 '14 at 16:30

1 Answers1

0

Try

SELECT b
FROM Book b
WHERE NOT EXISTS (
    SELECT o 
    FROM Order o
    WHERE b MEMBER OF o.items
)

to find books for which there is no order such that the book is a member of the order's items list.

(I should note that this is probably not very efficient due to the negations. Flagging Books once they occur in an Order is actually more efficient.)

Simon Fischer
  • 1,154
  • 6
  • 22