join on foreign key with hibernate

Question

Hi, i am a hibernate-beginner and have a problem when trying to join 2 tables with hibernate. What i am trying to do is get the list of products a certain store has depending on the store id, but what i am getting is the list of all available products in the database listed under every store.

Here's the code for Product.java :

@Entity
@Table (name = "products")
public class Product implements Serializable{

/**
 * 
 */
private static final long serialVersionUID = -1001086120280322279L;

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column (name = "product_id")
private int product_id;

@Column(name = "product_name", unique=true)
private String product_name;

@JoinColumn(name = "store", referencedColumnName="store_id")
@ManyToOne(cascade=CascadeType.ALL)
private Store store;

etc..

and here's the code for Store.java :

@Entity
@Table(name = "stores")
public class Store implements Serializable{
/**
 * 
 */
private static final long serialVersionUID = 4497252090404342019L;

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column (name = "store_id")
private int store_id;

@Column(name = "store_name", unique=true)
private String store_name;

@JoinColumn(name="store", referencedColumnName= "store_id")
@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.EAGER)
private List<Product> productList;

etc..

Here's the output: (products A should be under Butik A and products B under Butik B)

Butik: Butik A
Produkt: Banana A
Produkt: Morot A
Produkt: Banana B
Produkt: Apple B

Butik: Butik B
Produkt: Banana A
Produkt: Morot A
Produkt: Banana B
Produkt: Spple B

I have 2 additional classes, ProductDAO and StoreDAO that take care of the query, the code is similar in both classes except the table-name/class-name.

public class ProductDAO { 
   public static List<Product> getStoreProductsList() { 
     Session hibernateSession = HibernateUtil.getSession(); 
     hibernateSession.beginTransaction();    
     Query query = hibernateSession.createQuery("from Product");
     hibernateSession.getTransaction().commit();     
     List<Product> storeProducts = query.list(); 
     return storeProducts;   
   }
}

Is there any way of solving this with hibernate only?

Thanks

Answer 1

The problem is your Query, it's just selecting all the products in the system, regardless of the store.

Try using hibernate criteria. It's easier to create queries for the database and you can always work from the Java "side" instead of the DB "side".

int storeid = 1 //whatever you want here.
Criteria criteria = session.createCriteria(Product.class);
criteria.add(Restrictions.eq("store_id",storeid));
List<Product> list = criteria.list();

list will contain a list of the products who belong to the Store with storeid equal to the storeid you provide.

But for this simple query it's even easier if you use session.get() or session.load() (The difference between them is in this answer or more info about them in the documentation)

int storeid = 1; //Whatever you want
Store store = (Store) session.get(Store.class,storeid);
List<Product> list = store.getProductList();

As you see, Hibernate takes care of doing the join for you :).

Answer 2

After going through your comment. It looks like you never set the condition there, of course then, you will end up getting all the products no matter which store they belong. No surprise. Where you specify the criteria?

You can do something like,

// One liner
List<Product> list = session.createQuery("from Product p where p.store.store_id = "
          +" :storeId").setInteger("storeId", storeId).list();

Or you can get the Store and then get the list of Products like below,

// Another one liner
List<Product> list = session.createCriteria(Store.class)
             .add(Restrictions.eq("store_id", storeId)).list().getProductList();

Another and simpler way, as we know store_id is the primary key, (Thanks to Pakore for reminding me)

// And another. Changed to use load() instead of get() here. 
// Assuming non-existance is an error.
List<Product> list = (Store) session.load(Store.class,storeId).getProductList();