simple hibernate join example

pbDatabase · **Joined:** Sat Feb 19, 2011 9:07 am **Posts:** 8

Hi,
I am new to hibernate and databases in general. So please excuse the simple request.

I have setup an eclipse project, with the appropriate .hbm and .cfg files. I have a couple of tables that I can successfully query. The problem I am having is using the join query. I am not sure what query I should have??? I tried a number of variations but I am getting nowhere! Also do I need to update my .hbm files. I am not sure after reading various sites. Anyway, I have listed the simple mysql query below.

Code:

select inventory.title from inventory join store on inventory.ID=store.inventoryId;

Any help would be greatly accepted.

Thanks.

owenlindsell · **Joined:** Sun Feb 20, 2011 7:08 am **Posts:** 7

Hi there,

If you're new to databases I'd suggest learning SQL first and getting some serious practice before diving into Hibernate, otherwise the learning curve may be a bit much. I'm not sure the SQL query below is what you intended. It will do an inner join that will return only those inventory items that have a corresponding entry in the store table.

If your SQL is ok, and it is an HQL query you are trying to make, I'd suggest starting here...
http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html

Generally people use HQL to return one or more hibernate entities, rather than individual fields as you are trying to below (although this maybe possible, I've never tried it).
For the query you are making, the select can be left out. I would write it as:

Code:

String query = "from inventory inv join store";

You do not need to specify which property to join on as the relationship between inventory and store should be defined in the entity mapping. I would also suggest that you use annotations rather than hbm mapping files, as that is the more modern way.

If you have more questions, please post a bit more code to give us some context. Also give a detail about what you trying to achieve.

Thanks,
Owen

pbDatabase · **Joined:** Sat Feb 19, 2011 9:07 am **Posts:** 8

owenlindsell wrote:

Hi there,

If you're new to databases I'd suggest learning SQL first and getting some serious practice before diving into Hibernate, otherwise the learning curve may be a bit much. I'm not sure the SQL query below is what you intended. It will do an inner join that will return only those inventory items that have a corresponding entry in the store table.

If your SQL is ok, and it is an HQL query you are trying to make, I'd suggest starting here...
http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html

Generally people use HQL to return one or more hibernate entities, rather than individual fields as you are trying to below (although this maybe possible, I've never tried it).
For the query you are making, the select can be left out. I would write it as:

Code:

String query = "from inventory inv join store";

You do not need to specify which property to join on as the relationship between inventory and store should be defined in the entity mapping. I would also suggest that you use annotations rather than hbm mapping files, as that is the more modern way.

If you have more questions, please post a bit more code to give us some context. Also give a detail about what you trying to achieve.

Thanks,
Owen

Hi owenlindsell,
Thanks for your reply. I thought I should quickly try using the query you posted last time and substituting what I have as a class.

Code:

String query = "from inventory inv join store";

Bu it failed... I got the message:

Code:

inventory is not mapped. [from inventory ItemPojo join store]

I got my examples from http://www.roseindia.net/hibernate/index.shtml as this was the top in google search. Thought this was the way forward. So you can use annotations rather than hbm mapping files. Got any good tutorials?

Thanks,
Paul

owenlindsell · **Joined:** Sun Feb 20, 2011 7:08 am **Posts:** 7

Can you send the code please?
Java entities, DAOs and mapping files.

pbDatabase · **Joined:** Sat Feb 19, 2011 9:07 am **Posts:** 8

owenlindsell wrote:

Can you send the code please?
Java entities, DAOs and mapping files.

I think the query should be something like...

Quote:

"Select item.title from ItemPojo as item join StorePojo as store"

I have read the article I dont know how many times now and still not sure what the correct syntax is!!!! I keep getting the same error though everytime.
unexpected AST node

I have hopefully included everything here.

inventory.hbm.xml

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
   <class name="com.store.product.inventory.pojo.ItemPojo"
      table="inventory">
      <id name="id" type="long" column="ID">
         <generator class="increment" />
      </id>
      <property name="title" />
      <property name="url" />
      <property name="price" />
   </class>
</hibernate-mapping>

ItemPojo.java

Code:

/**
 * Simple pojo for hibernate
 */
public class ItemPojo {
   private long id;
   
   private String title;
   private String url;
   private String price;

   public long getId() {
      return id;
   }

   public void setId(long id) {
      this.id = id;
   }

   public String getTitle() {
      return title;
   }

   public void setTitle(String title) {
      this.title = title;
   }

   public String getUrl() {
      return url;
   }

   public void setUrl(String url) {
      this.url = url;
   }

   public String getPrice() {
      return price;
   }

   public void setPrice(String price) {
      this.price = price;
   }
}

store.hbm.xml

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
   <class name="com.store.product.sell.thirdParty.pojo.StorePojo"
      table="store">
      <id name="storeId" type="long" column="STORE_ID">
         <generator class="increment" />
      </id>
      <property name="productName" />
      <property name="productNameUrl" />
      <property name="price" />
      <property name="inventoryId"/>
   </class>
</hibernate-mapping>

StorePojo.java

Code:

/**
 * Simple pojo for hibernate
 */
public class StorePojo {
   /*
    *  This ties in with the order id
    */
   private long storeId;
   
   private String productName;
   private String productNameUrl;
   private String price;
   
   private long inventoryId=0;


   public String getProductName() {
      return productName;
   }

   public void setProductName(String productName) {
      this.productName = productName;
   }

   public String getProductNameUrl() {
      return productNameUrl;
   }

   public void setProductNameUrl(String productNameUrl) {
      this.productNameUrl = productNameUrl;
   }

   public String getPrice() {
      return price;
   }

   public void setPrice(String price) {
      this.price = price;
   }

   public void setInventoryId(long inventoryId) {
      this.inventoryId = inventoryId;
   }

   public long getInventoryId() {
      return inventoryId;
   }

}

owenlindsell · **Joined:** Sun Feb 20, 2011 7:08 am **Posts:** 7

Hi,

when you reference one entity from another, you need to include the whole entity in the class, not just the id. Also you need to define the relationship between your entities in your mapping files. In this case I have defined a many-to-one association because I assume your store can have many items. I've also made the relationship bi-directional by adding a one-to-many association to the item mapping file.

Here are the changes you need to your code:

inventory.hbm.xml

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
   <class name="com.store.product.inventory.pojo.ItemPojo"
      table="inventory">
      <id name="id" type="long" column="ID">
         <generator class="increment" />
      </id>
      <property name="title" />
      <property name="url" />
      <property name="price" />

      <many-to-one name="store" column="store_id" class="StorePojo" not-null="true"/>

   </class>
</hibernate-mapping>

ItemPojo.java

Code:

import .....Store;
/**
 * Simple pojo for hibernate
 */
public class ItemPojo {
   private long id;
   
   private String title;
   private String url;
   private String price;
   private Store store;

   public long getId() {
      return id;
   }

   public void setId(long id) {
      this.id = id;
   }

   public String getTitle() {
      return title;
   }

   public void setTitle(String title) {
      this.title = title;
   }

   public String getUrl() {
      return url;
   }

   public void setUrl(String url) {
      this.url = url;
   }

   public String getPrice() {
      return price;
   }

   public void setPrice(String price) {
      this.price = price;
   }

   public Store getStore() {
      return store;
   }

   public void setStore(Store store) {
      this.store = store;
   }
}

store.hbm.xml

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
   <class name="com.store.product.sell.thirdParty.pojo.StorePojo"
      table="store">
      <id name="storeId" type="long" column="STORE_ID">
         <generator class="increment" />
      </id>
      <property name="productName" />
      <property name="productNameUrl" />
      <property name="price" />
      <set name="items">
         <one-to-many class="ItemPojo"/>
      </set>
   </class>
</hibernate-mapping>

StorePojo.java

Code:

import .......ItemPojo;
/**
 * Simple pojo for hibernate
 */
public class StorePojo {
   /*
    *  This ties in with the order id
    */
   private long storeId;
   
   private String productName;
   private String productNameUrl;
   private String price;

   private Set items = new HashSet();


   public String getProductName() {
      return productName;
   }

   public void setProductName(String productName) {
      this.productName = productName;
   }

   public String getProductNameUrl() {
      return productNameUrl;
   }

   public void setProductNameUrl(String productNameUrl) {
      this.productNameUrl = productNameUrl;
   }

   public String getPrice() {
      return price;
   }

   public void setPrice(String price) {
      this.price = price;
   }

   public void setItems(Set items) {
      this.items = items;
   }

   public Set getItems() {
      return items;
   }

   public void addItem(ItemPojo item) {
      item.setStore(this);
      items.add(item);
   }
}

[/quote]

pbDatabase · **Joined:** Sat Feb 19, 2011 9:07 am **Posts:** 8

owenlindsell wrote:

Hi,

when you reference one entity from another, you need to include the whole entity in the class, not just the id. Also you need to define the relationship between your entities in your mapping files. In this case I have defined a many-to-one association because I assume your store can have many items. I've also made the relationship bi-directional by adding a one-to-many association to the item mapping file.

Here are the changes you need to your code:

inventory.hbm.xml

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
   <class name="com.store.product.inventory.pojo.ItemPojo"
      table="inventory">
      <id name="id" type="long" column="ID">
         <generator class="increment" />
      </id>
      <property name="title" />
      <property name="url" />
      <property name="price" />

      <many-to-one name="store" column="store_id" class="StorePojo" not-null="true"/>

   </class>
</hibernate-mapping>

ItemPojo.java

Code:

import .....Store;
/**
 * Simple pojo for hibernate
 */
public class ItemPojo {
   private long id;
   
   private String title;
   private String url;
   private String price;
   private Store store;

   public long getId() {
      return id;
   }

   public void setId(long id) {
      this.id = id;
   }

   public String getTitle() {
      return title;
   }

   public void setTitle(String title) {
      this.title = title;
   }

   public String getUrl() {
      return url;
   }

   public void setUrl(String url) {
      this.url = url;
   }

   public String getPrice() {
      return price;
   }

   public void setPrice(String price) {
      this.price = price;
   }

   public Store getStore() {
      return store;
   }

   public void setStore(Store store) {
      this.store = store;
   }
}

store.hbm.xml

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
   <class name="com.store.product.sell.thirdParty.pojo.StorePojo"
      table="store">
      <id name="storeId" type="long" column="STORE_ID">
         <generator class="increment" />
      </id>
      <property name="productName" />
      <property name="productNameUrl" />
      <property name="price" />
      <set name="items">
         <one-to-many class="ItemPojo"/>
      </set>
   </class>
</hibernate-mapping>

StorePojo.java

Code:

import .......ItemPojo;
/**
 * Simple pojo for hibernate
 */
public class StorePojo {
   /*
    *  This ties in with the order id
    */
   private long storeId;
   
   private String productName;
   private String productNameUrl;
   private String price;

   private Set items = new HashSet();


   public String getProductName() {
      return productName;
   }

   public void setProductName(String productName) {
      this.productName = productName;
   }

   public String getProductNameUrl() {
      return productNameUrl;
   }

   public void setProductNameUrl(String productNameUrl) {
      this.productNameUrl = productNameUrl;
   }

   public String getPrice() {
      return price;
   }

   public void setPrice(String price) {
      this.price = price;
   }

   public void setItems(Set items) {
      this.items = items;
   }

   public Set getItems() {
      return items;
   }

   public void addItem(ItemPojo item) {
      item.setStore(this);
      items.add(item);
   }
}

[/quote]

Thanks again for the reply. I dont think I explained myself properly before. The inventory(ItemPojo) is my store items and the store is actually another persons store items(StorePojo). Also there can be many of the other persons stores. This being the case I think my inventory has multiple objects as well as the other persons store items. And one item in my inventory maps to one item in the other persons store. With my code I expected to have a id(number), in the other persons store, that would map to mine inventory. Hope all that makes sense.

I notice you have reference to a Store??? I didnt think you could use objects in mysql? I thought everything had to be primitive types? And the whole point of doing joins... I am a little confused now : ( If this is the case why cant I have a reference to my items in the other persons inventory?

Sorry for all the questions.
PAul

owenlindsell · **Joined:** Sun Feb 20, 2011 7:08 am **Posts:** 7

I'm sorry I've not been able to help, but I'm not entirely sure what you're trying to do. I've shown you how to create a relationship between two entities, which should be a good template for you once you know what relationships you want to create.

Quote:

I notice you have reference to a Store??? I didnt think you could use objects in mysql? I thought everything had to be primitive types? And the whole point of doing joins... I am a little confused now : ( If this is the case why cant I have a reference to my items in the other persons inventory?

MySql is the name of database you are using. In this thread we have been looking at Java classes and XML mapping files - this is nothing to do with MySql. Hibernate is used to map your Java classes to tables in your database. However, you appear not to really understand how relational databases work.

I really think that using Hibernate maybe a bit beyond your current level at the moment. If you have no background in databases, learning it will take quite a long time. Many of the people on these forums have years of experience with databases and still have some difficulties setting up Hibernate for the first time. I recommend, you first read a book on relational databases, then create some tables by hand coding the SQL. Also, I'm not sure of your Java level. I'd recommend at least a couple of years commercial (or intensive) Java experience before attempting something like this.

pbDatabase · **Joined:** Sat Feb 19, 2011 9:07 am **Posts:** 8

owenlindsell wrote:

I'm sorry I've not been able to help, but I'm not entirely sure what you're trying to do. I've shown you how to create a relationship between two entities, which should be a good template for you once you know what relationships you want to create.

Quote:

I notice you have reference to a Store??? I didnt think you could use objects in mysql? I thought everything had to be primitive types? And the whole point of doing joins... I am a little confused now : ( If this is the case why cant I have a reference to my items in the other persons inventory?

MySql is the name of database you are using. In this thread we have been looking at Java classes and XML mapping files - this is nothing to do with MySql. Hibernate is used to map your Java classes to tables in your database. However, you appear not to really understand how relational databases work.

I really think that using Hibernate maybe a bit beyond your current level at the moment. If you have no background in databases, learning it will take quite a long time. Many of the people on these forums have years of experience with databases and still have some difficulties setting up Hibernate for the first time. I recommend, you first read a book on relational databases, then create some tables by hand coding the SQL. Also, I'm not sure of your Java level. I'd recommend at least a couple of years commercial (or intensive) Java experience before attempting something like this.

I appreciate your help. I really do.

I understand that mySql is the database that I am using. I also understand what I can use hibernate for. I was wanting some assistance in what first appeared to me to be a trivial problem. I dont think it is unreasonable to do that. What I dont understand is how I can do a search on the net to a join in MySql and find the answer and test it within minutes and it take me nearly a days effort to write something similar in HQL - which is still not working!

owenlindsell · **Joined:** Sun Feb 20, 2011 7:08 am **Posts:** 7

pbDatabase wrote:

I was wanting some assistance in what first appeared to me to be a trivial problem. I dont think it is unreasonable to do that. What I dont understand is how I can do a search on the net to a join in MySql and find the answer and test it within minutes and it take me nearly a days effort to write something similar in HQL - which is still not working!

Unfortunately setting up Hibernate for the first time cannot be considered a trivial problem. Learning how to write a sql query cannot be compared to what you are trying to do - it's not in the same ball park. If you had an already working Hibernate installation and all you had to do was write an HQL query then it would be comparable. Trying to set it up in a day when you are an inexperienced developer is highly unrealistic.

crish · **Joined:** Mon Apr 11, 2011 6:10 am **Posts:** 2

pbDatabase wrote:

Hi,
I am new to hibernate and databases in general. So please excuse the simple request.

I have setup an eclipse project, with the appropriate .hbm and .cfg files. I have a couple of tables that I can successfully query. The problem I am having is using the join query. I am not sure what query I should have??? I tried a number of variations but I am getting nowhere! Also do I need to update my .hbm files. I am not sure after reading various sites. Anyway, I have listed the simple mysql query below.

Code:

select inventory.title from inventory join store on inventory.ID=store.inventoryId;

Any help would be greatly accepted.

Thanks.

The hibernate problem I am facing is while retrieving the old Person class's objects from the Map. This is happening when I am retrieving a collection of Person Objects.

The collection seems to be detached from the session object. It is the way Quartz handles Hibernate sessions. I am not using Hibernate Sessions explicitly to do any of these operations. I am being safe by going via the Quartz API for everything. I think this is a bit similar to the problem that OSIV(Open Session In View) filter solves for the web apps. I may be wrong.

I am not able to understand why the Session is getting detached. Is there a timeout on the session/transaction object, where can I find more details about how Quartz uses Hibernate? I am loading a whole lot of objects in this program via the Quartz API. I had faced this issue earlier and solved it by using Hibermate.initialize, now that doesn't work either.

I haven't configured a sessionfactory or a transactionmanager etc, I am just using the Quartz API. Is that the issue?

There is a closed Jira issue which is similar to this:

http://jira.atlassian.com/browse/CWD-171

When I look around the web, this seems to be a common problem with the way Quartz handles Hibernate. The common solution seems to be configuring a Spring hibernate interceptor, I tried and failed as I am shamefully out of touch with Hibernate. Any help would be great.