many-to-one select behavior

movingconcept · **Joined:** Mon Mar 03, 2008 9:31 pm **Posts:** 4

Hibernate version: 3.2.1

Mapping documents:

Code:

<class name="Address" table="tst_address">
      <id name="pkID" type="long" column="id">
           <generator class="sequence">
            <param name="sequence">SEQ_ADDRESS</param>
         </generator>
        </id>
      <property name="zip" column="zip" type="string"/>
      <property name="city" column="city" type="string"/>   
      <set name="persons" inverse="true">
         <key column="id"/>
         <one-to-many class="Person"/>
      </set>
   </class>

<class name="Person" table="tst_person">
      <id name="pkID" type="long" column="id">
           <generator class="sequence">
            <param name="sequence">SEQ_PERSON</param>
         </generator>
        </id>
      <property name="name" type="string"/>
      <many-to-one name="address" 
         column="addressId"
         class="Address"
         fetch="join"
         not-null="true"
         />
   </class>

Domain model

Code:

// using Apache Commons Builders
public class Address implements Serializable{
   private static final long serialVersionUID = 2287001214608453028L;
   /** PK */
   private Long pkID;
   private String zip;
   private String city;
   private Set<Person> persons = new HashSet<Person>();
   public String getCity() {
      return city;
   }
   public void setCity(String city) {
      this.city = city;
   }
   public String getZip() {
      return zip;
   }
   public void setZip(String zip) {
      this.zip = zip;
   }
   public Set<Person> getPersons() {
      return persons;
   }
   public void setPersons(Set<Person> persons) {
      this.persons = persons;
   }
   
   @Override
   public int hashCode() {
      return HashCodeBuilder.reflectionHashCode(this);
   }
   @Override
   public boolean equals(Object obj) {
      return EqualsBuilder.reflectionEquals(obj,this);
   }
   @Override
   public String toString() {
      return ToStringBuilder.reflectionToString(this);
   }
   public Long getPkID() {
      return pkID;
   }
   public void setPkID(Long pkID) {
      this.pkID = pkID;
   }
}

public class Person implements Serializable{
   private static final long serialVersionUID = 7454680725393452920L;
   /** PK */
   private Long pkID;
   private String name;
   private Address address;
   public String getName() {
      return name;
   }
   public void setName(String name) {
      this.name = name;
   }
   public Address getAddress() {
      return address;
   }
   public void setAddress(Address address) {
      this.address = address;
   }
   public Long getPkID() {
      return pkID;
   }
   public void setPkID(Long pkID) {
      this.pkID = pkID;
   }
   @Override
   public int hashCode() {
      return HashCodeBuilder.reflectionHashCode(this);
   }
   
   @Override
   public boolean equals(Object obj) {
      return EqualsBuilder.reflectionEquals(obj,this);
   }
   @Override
   public String toString() {
      return ToStringBuilder.reflectionToString(this);
   }
}

Database:
JDBC driver: Oracle JDBC driver, version: 9.2.0.1.0
Oracle version: 10g

Code:

Code:

GenericDaoHibernateImpl personDao = (GenericDaoHibernateImpl)appContext.getBean("personDao");

Address a = new Address();
a.setCity("osaka");
a.setZip("9000");
Person p = new Person();
p.setAddress(a);
p.setName("hans");
for (Person prs : (List<Person>)personDao.find(p)) {
   logger.debug(prs);
}

The generated SQL (show_sql=true):
Hibernate: select this_.id as id5_1_, this_.name as name5_1_, this_.addressId as addressId5_1_, address2_.id as id6_0_, address2_.zip as zip6_0_, address2_.city as city6_0_ from tst_person this_ inner join tst_address address2_ on this_.addressId=address2_.id where (this_.name=?)
Hibernate: select persons0_.id as id2_, persons0_.id as id5_1_, persons0_.name as name5_1_, persons0_.addressId as addressId5_1_, address1_.id as id6_0_, address1_.zip as zip6_0_, address1_.city as city6_0_ from tst_person persons0_ inner join tst_address address1_ on persons0_.addressId=address1_.id where persons0_.id=?
15:01:48,279 DEBUG SimpleDaoTest7:53 - dev.test.dao.relation.Person@1117a20[pkID=1,name=hans,address=dev.test.dao.relation.Address@1afb0c7[pkID=30,zip=9000,city=osaka,persons=[]]]
Hibernate: select persons0_.id as id2_, persons0_.id as id5_1_, persons0_.name as name5_1_, persons0_.addressId as addressId5_1_, address1_.id as id6_0_, address1_.zip as zip6_0_, address1_.city as city6_0_ from tst_person persons0_ inner join tst_address address1_ on persons0_.addressId=address1_.id where persons0_.id=?
15:01:48,357 DEBUG SimpleDaoTest7:53 - dev.test.dao.relation.Person@92668c[pkID=2,name=hans,address=dev.test.dao.relation.Address@89e2f1[pkID=40,zip=1076015,city=tokyo,persons=[]]]

My lack of understanding
Why is it doing the extra select?

Why is it returning both city=osaka and city=tokyo when I set a.setCity("osaka");
a.setZip("9000");

If I change behaviour on the Person many-to-one relationship to fetch="join" it still ignores a.setCity("osaka") and returns 2 objects.

I've added
a.getPersons().add(p);

and even tried unidirectional, eliminating the <set> on Address but without desired result.

Desired result
I would like to see a query being generated like this:

SELECT * FROM tst_person, tst_Address WHERE tst_person.addressid = tst_Address.id AND tst_person.name = 'hans' AND tst_address.city = 'osaka' AND tst_address.zip = '9000';

What am I doing wrong here?
thnx

movingconcept · **Joined:** Mon Mar 03, 2008 9:31 pm **Posts:** 4

I would like to restrict results and have Hibernate create a compact query.
Anyone see what I am doing wrong here?

Sukirtha · **Posted:** Wed Mar 05, 2008 5:05 am

U want to filter the result based on some values. Is that all u want? Try using criteria. Check the documentation for Criteria. I guess that will solve ur problem.

movingconcept · **Joined:** Mon Mar 03, 2008 9:31 pm **Posts:** 4

Thanks. I was thinking about Criteria, but it shouldn't be necessary. Will try it out anyway.

Basically, the inner join (in the above example implicit) should limit results, but it doesn't. Even setting outer-join = false doesn't do the trick.

Just want to understand why it's actually doing an outer-join and why the extra select happens. Any feedback or advice much appreciated.

movingconcept · **Joined:** Mon Mar 03, 2008 9:31 pm **Posts:** 4

Any feedback on why inner join doesn't work?

V Jayakumar · **Posted:** Tue Mar 18, 2008 2:52 pm

Based on understanding your table structure and your scenario.
I have created the query. just try it.

Code:

Criteria criteria = session.createCriteria(Address.class);
criteria.createAlias("persons","Persons");
criteria.add(Expression.eq("Persons.name","hans"));
criteria.add(Expression.eq("zip","9000"));
criteria.add(Expression.eq("city","osaka"));

List resultSet = criteria.list();

resultSet will have the list of table object of type Address.class

Hope it helps you. pls let me know abt the solution for your problem.
If this is not getting the result u expecting. then mail to me.