How to correctly write queries in JPA2?

[fabiovalse]

Hi there,

I've some problems writing a correct query for extracting data from a MySql DB using JPA2.

Three tables are interested:

carts: with username as ID

books: with isbn as ID

cartsbooks: it's composed by three columns

     - username ID

     - isbn ID

     - quantity

My entities are:

     - Cart: three field (username, articles, bill)

          this is the code:

          ......

          @Entity

          @Table(name = "carts")

          public class Cart implements Serializable {

               private static final long serialVersionUID = 3L;

               public Cart() {super();}

               @Id

               private String username;

               @ElementCollection

               @CollectionTable(name="cartsbooks", joinColumns=@JoinColumn(name="username"))

               @Column(name="quantity")

               @MapKeyJoinColumn(name="isbn", referencedColumnName="isbn")

               private Map<Book,Integer> articles;

               private float bill;

          ......

     - Book:

          this is the code:

          ......

          @Entity

          @Table(name = "books")

          public class Book implements Serializable {

               private static final long serialVersionUID = 1L;

              public Book() { super(); }

         @Id

         private String isbn;

         private String title;

         private String author;

         private String year;

         private String publisher;

         private String prize;

         private String image_s;

         private String image_m;

         private String image_l;

         private Boolean newbook;

          ......

So the question is: if i want to write a query that take all the books that are in the join table cartsbooks for a specific user I would write something like:

select books.isbn, quantity from books right join cartsbooks on books.isbn = cartsbooks.isbn where username='fabiovalsecchi';

Now since Book is an entity how can I extract these information? I've tried this query but it is not correct:

select b from " + Book.class.getName() + " b join cartsbooks where b.isbn = cartsbooks.isbn and cartsbooks.username='" + cart.getUsername() + "'";

[lafr]

Don't know how it works with ElementCollection, no experience with Maps as fields.

As a starting point I'd use

TypedQuery<Map<Book,Integer>> query = em.createQuery("SELECT c.articles FROM Cart AS c WHERE c.username = :username", Map<Book,Integer>.class);

query.setParameter( "username",  'fabiovalsecchi' );

List<Map<Book,Integer>> results = query.getResultList();