1 Reply Latest reply on Dec 31, 2012 11:20 AM by Frank Langelage

    How to correctly write queries in JPA2?

    fabio valsecchi Newbie

      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:




                @Table(name = "carts")

                public class Cart implements Serializable {

                     private static final long serialVersionUID = 3L;


                     public Cart() {super();}



                     private String username;


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


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

                     private Map<Book,Integer> articles;

                     private float bill;



           - Book:

                this is the code:




                @Table(name = "books")

                public class Book implements Serializable {

                     private static final long serialVersionUID = 1L;


                    public Book() { super(); }



               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() + "'";