0

Hi all i'm new to Hibernate an i've got error using SQL query using HQL. I use spring mvc app with hibernate and Postgres DBMS.

SQL query "SELECT c.quantity, p.name, p.price FROM cart AS c INNER JOIN products AS p ON c.product_articul = p.articul WHERE user_id = 1"

As u see i've got cart table with columns: cart_id, user_id, product_articul, quantity. Appropriate Cart class: 

@Entity
@Table(name = "cart")
public class Cart {
    @Id
    @Column(name = "cart_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int cartId;

    @Column(name = "user_id")
    private int userId;

    @Column(name = "product_articul")
    private String productArticul;

    @Column(name = "quantity")
    private int quantity;

    @OneToOne
    private Product product;

    // constructors,get,set
}

Product table consists of product_id, name etc. Product class:

@Entity
@Table(name = "products")
public class Product {
    @Id
    @Column(name = "product_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private String productId;

    @Column(name = "articul")
    private String articul;

    @Column(name = "name")
    private String name;

    @Column(name = "price")
    private BigDecimal price;

    //other fields, constructor, get, set
}

When i try to get list from cart using this:

List<Cart> list = session.createQuery("SELECT c FROM Cart c INNER JOIN c.product WHERE user_id = :id")
            .setParameter("id", id).list();

I catch this exception ->
javax.persistence.PersistenceException:org.hibernate.exception.SQLGrammarException: could not extract ResultSet

in stacktrace i found this -> org.postgresql.util.PSQLException: ERROR: column cart0_.product_product_id does not exist

Maybe it's problem with postgres sequence (product_id)

ps. without one-to-one annotation all works fine.

How can i fix this. I'll be thankful for all yr answers!

Illya Slobozhanin
  • 57
  • 9
  • product_product_id is the default join column name constructed by hibernate - is it really the foreign key column name in your db ? – janek Sep 04 '16 at 20:52
  • can you try using this query , SELECT c FROM Cart c JOIN products p on c.product_id = p.id WHERE c.user_id = :id if you are using create sql query don't use entity class names. I think using hibernate criteria will solve the join problems, try that also and its faster than normal join queries – Akhil S Kamath Sep 05 '16 at 06:38
  • https://stackoverflow.com/questions/197474/hibernate-criteria-vs-hql?rq=1 – Akhil S Kamath Sep 05 '16 at 06:42
  • Yep. product_id is primary and foreign key.I tried to use dat query, but the result's the same. I don't found answer here: https://stackoverflow.com/questions/197474/hibernate-criteria-vs-hql?rq=1 – Illya Slobozhanin Sep 06 '16 at 11:37

0 Answers0