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:

                ......

       

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