Hibernate mapping

meileng · **Joined:** Wed Jan 25, 2006 7:32 am **Posts:** 34

Hi,

Am a newbie to hibernate. I have just created two tables namely Subscriber and Listing. Each Subscriber can have many Listings. Therefore is a one-to-many mapping.

Or anoher way round, many Listings belongs to a Subscriber.Tehrefore is a many-to-one mapping.

I am actually having a problem of mapping this relationship.either one-to-many or many-to-one.

In table Listing, it consist of a foreign key named subscriber_id which is a reference to the username unique ID in table Subscriber.

here is my code to define many-to-one mapping:

============Listing table=======================

<?xml version="1.0" encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping>
<class name="mlisting.entity.Listing" table="listing">
<id name="id" column="id" type="int">
<generator class="assigned"/>
</id>
<property name="category" column="category" type="string" />
<property name="description" column="description" type="string" />
<property name="price" column="price" type="double" />
<property name="intros" column="intros" type="int" />
<property name="type" column="type" type="string" />
<property name="subscriber_id" column="subscriber_id" type="string" />
<many-to-one name="subscriber" class="mlisting.entity.Subscriber" property-ref="username" column="subscriber_id" />
</class>
</hibernate-mapping>

===========================================

======Subscriber table=============================
<?xml version="1.0" encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping>


<class name="mlisting.entity.Subscriber" table="subscriber">
<id name="username" column="username" type="string">
<generator class="assigned"/>
</id>
<property name="firstname" column="firstname" type="string" />
<property name="lastname" column="lastname" type="string" />
<property name="password" column="password" type="string" />
<property name="mobile_number" column="mobile_number" type="string" />

</class>
</hibernate-mapping>
============================================
When my server starts, it returned this error:

org.hibernate.MappingException: property-ref not found: username in class: mlisting.entity.Subscriber

Please guide.Thank you!

scesbron · **Joined:** Thu Sep 04, 2003 8:23 am **Posts:** 368

You don't have to set the "property-ref" attribute on your many to one as your reference is the id of the other table

see http://www.hibernate.org/hib_docs/v3/reference/en/html/mapping.html#mapping-declaration-manytoone
for more details

meileng · **Joined:** Wed Jan 25, 2006 7:32 am **Posts:** 34

I have changed this in Listing table as :

======================
<many-to-one name="subscriber" class="mlisting.entity.Subscriber" column="subscriber_id" insert="false" update="false"/>
=======================

and defined the Listing class as:

===========================
public Subscriber getSubscriber(){
return this.subscriber;
}
public void setSubscriber(Subscriber subscriber){
this.subscriber = subscriber;
}

===========================
Occasioanlly the server started without having any problem. But when I start to retrieve the data using this query:

==========================
select * from listing , subscriber where (listing.category = 'A') AND ((subscriber.username = listing.subscriber_id) AND (subscriber.state = 'perak'))
==========================

it returned the error:

===============================
[ERROR] PARSER - *** ERROR: listing is not mapped.
[ERROR] PARSER - *** ERROR: Invalid path: 'listing.category'
[ERROR] PARSER - *** ERROR: <AST>:0:0: unexpected end of subtree
[ERROR] PARSER - *** ERROR: Invalid path: 'subscriber.username'
[ERROR] PARSER - *** ERROR: <AST>:0:0: unexpected end of subtree
[ERROR] PARSER - *** ERROR: Invalid path: 'subscriber.state'
[ERROR] PARSER - *** ERROR: <AST>:0:0: unexpected end of subtree
[ERROR] PARSER - *** ERROR: Invalid path: 'listing.id'
listing is not mapped. [select * from listing , subscriber where (listing.category = 'A') AND ((subscriber.username = listing.subscriber_id) AND (subscriber.state = 'perak')) order by listing.id desc]; nested exception is org.hibernate.hql.ast.QuerySyntaxError: listing is not mapped. [from listing , subscriber where (listing.category = 'A') AND ((subscriber.username = listing.subscriber_id) AND (subscriber.state = 'perak')) order by listing.id desc]

==============================

I wonder why the listing is not map.Infact when I execute the query from the database itself, it can return but not when am using hibernate to do so.

Please advice.Thank you!

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Your query is written in some hybrid of SQL and HQL. Assuming that you're using HQL, you want something like this:

Code:

from Listing l
  join l.Subscriber s
  where l.Category = "A"
    and s.State = "perak"

Note that hibernate already knows to join on subscriber_id and username because your mapping defines the relationship.

meileng · **Joined:** Wed Jan 25, 2006 7:32 am **Posts:** 34

The SQL join somehow not working for me. So I tried this,

String stmt = "from Listing l ,Subscriber s where ( l.subscriber_id = s.username) and s.state='perak'"

I execute this statement using dao.find(stmt)

and it works.

But how can I select just a column for example column description from listing instead of returning all the column from listing and subscriber?

I tried this:
(i) String stmt = "description from Listing l ,Subscriber s where ( l.subscriber_id = s.username) and s.state='perak'"

and

(ii) String stmt = "l.description from Listing l ,Subscriber s where ( l.subscriber_id = s.username) and s.state='perak'"

using dao.find(stmt)

both didn't works which return the following errors:

The stmt: description from Listing l ,Subscriber s where ((l.subscriber_id = s.username) and (s.state = 'selangor')) order by id desc
[ERROR] PARSER - *** ERROR: line 1:1: unexpected token: description
unexpected token: description near line 1, column 1 [description from mlisting.entity.Listing l ,mlisting.entity.Subscriber s where ((l.subscriber_id = s.username) and (s.state = 'selangor')) order by id desc]; nested exception is org.hibernate.hql.ast.QuerySyntaxError: unexpected token: description near line 1, column 1 [description from mlisting.entity.Listing l ,mlisting.entity.Subscriber s where ((l.subscriber_id = s.username) and (s.state = 'selangor')) order by id desc]

Please guide.Thank you

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

You can use "select l.property from Listing l,..." in HQL. You probably want an sql-query for this, not a HQL query. As mentioned earlier, HQL is designed to map result set columns to POJO objects. You just want a few scalars, so SQL is more appropriate.

meileng · **Joined:** Wed Jan 25, 2006 7:32 am **Posts:** 34

So How can I deal with this. Hope that you can assist me on this as am a new by to POJO, and Hibernate.

Thank you!

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

I normally recommend putting queries in you mapping files, but you're using the in-java way so I'll stick with that.

Code:

Query qry =
  sess.createSQLQuery("select l.description from Listing l "
                      + "join Subscriber s on l.subscriber_id = s.username "
                      + "where s.state = :StateName");
  qry.addScalar("description", Hibernate.STRING);
  qry.addString("StateName", "perak");
  return (List<String>) qry.list();