-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 
Author Message
 Post subject: Query by example - left outer join
PostPosted: Thu Feb 25, 2010 10:52 am 
Newbie

Joined: Mon Nov 26, 2007 8:33 pm
Posts: 6
Hi Folks!

I have an entity called Person, with a Phone relationship:

@OneToMany(mappedBy="pessoa", cascade=CascadeType.ALL)
private Set<Phone> Phones;

And I'm using a query by example feature, like this:

Code:
   @SuppressWarnings("unchecked")
   @Override
   @Transactional(propagation=Propagation.SUPPORTS, readOnly=true)
   public List<T> findByExample(T entity) {      
      Example example = Example.create(entity).ignoreCase().enableLike(MatchMode.ANYWHERE);
      return getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(entity.getClass()).add(example).lis();
   }

Receiving a "new Person()" as the parameter, to find all Persons.

But the results are not what I'm looking for, because I want it to work exactly as a getHibernateTemplate.find("* from Person"). Let me exemplify:

Suposse if I have one Person and that Person have two phones, the getHibernateTemplate.find("* from Person") returns a List with one element, a Person, and that Person have a Set with 2 Phones. My Query by Example returns 2 Persons because of the left outer join it generates in the SQL!

What can I do?


Top
 Profile  
 
 Post subject: Re: Query by example - left outer join
PostPosted: Thu Feb 25, 2010 12:08 pm 
Beginner
Beginner

Joined: Tue Aug 25, 2009 11:42 am
Posts: 49
I would like to see ur mapping in detail. I didn't get the duplicates u mentioned.
Here is what I had:
Code:
@Entity
public class Person {
   @Override
   public String toString() {
      return "Person [phones=" + phones + ", id=" + id + "]";
   }

   @Id
   @GeneratedValue(strategy = GenerationType.SEQUENCE)
   private int id;

   @OneToMany(cascade = CascadeType.ALL, mappedBy = "owner", fetch = FetchType.LAZY)
   private Collection<Phone> phones = new ArrayList<Phone>();
//...setters and getters
}
@Entity
public class Phone {
   @Id
   @GeneratedValue(strategy = GenerationType.SEQUENCE)
   private int id;

   private String areaCode;
   private String number;

   @ManyToOne
   private Person owner;
//...setters and getters
}
      final SessionFactory sf = new AnnotationConfiguration().configure().buildSessionFactory();
      Session session = sf.openSession();
      session.beginTransaction();
      Person person = new Person();
      person.getPhones().add(new Phone("10", "344"));
      person.getPhones().add(new Phone("20", "444"));
      for(Phone p : person.getPhones()) p.setOwner(person);
      session.save(person);
      session.getTransaction().commit();
      session.clear();
      List l = session.createQuery("select distinct p from Person p left join fetch p.phones").list();
      System.out.println(l);
      l = session.createCriteria(Person.class).add(
            Example.create(new Person()).ignoreCase().enableLike(
                  MatchMode.ANYWHERE)).list();
      System.out.println(l);

My output was:
[Person [phones=[Phone [areaCode=10, id=10, number=344], Phone [areaCode=20, id=11, number=444]], id=1]]
[Person [phones=[Phone [areaCode=10, id=10, number=344], Phone [areaCode=20, id=11, number=444]], id=1]]
So both HQL and Criteria Query gave a single person.


Top
 Profile  
 
 Post subject: Re: Query by example - left outer join
PostPosted: Thu Feb 25, 2010 12:09 pm 
Beginner
Beginner

Joined: Tue Aug 25, 2009 11:42 am
Posts: 49
O and actually "select p from Person p left join fetch p.phones" gives copies. That's why I put a "distinct" in the previous HQL.


Top
 Profile  
 
 Post subject: Re: Query by example - left outer join
PostPosted: Thu Feb 25, 2010 12:52 pm 
Newbie

Joined: Mon Nov 26, 2007 8:33 pm
Posts: 6
Thanks tseringshrestha, but let me show you my mappings:

The actual names are Pessoa for Person and Telefone for Phone (sorry, I translated before)

Code:

@Entity
@Inheritance(strategy=InheritanceType.JOINED)
public class Pessoa {
   
   @Id
   @SequenceGenerator(name="pessoa_seq", sequenceName="PESSOA_SEQ", allocationSize=1)
   @GeneratedValue(strategy=GenerationType.AUTO, generator="pessoa_seq")
   private Long id;
   
             @OneToMany(mappedBy="pessoa", fetch=FetchType.EAGER, cascade=CascadeType.ALL) // At this time, I want Eager fetching
             private Set<Telefone> telefones;

   public Pessoa(){

   }

            // Getters and setters

   @Override
   public int hashCode() {
      final int prime = 31;
      int result = 1;
      result = prime * result + ((id == null) ? 0 : id.hashCode());
      return result;
   }

   @Override
   public boolean equals(Object obj) {
      if (this == obj)
         return true;
      if (obj == null)
         return false;
      if (getClass() != obj.getClass())
         return false;
      Pessoa other = (Pessoa) obj;
      if (id == null) {
         if (other.id != null)
            return false;
      } else if (!id.equals(other.id))
         return false;
      return true;
   }




Code:

@Entity
public class Telefone {

   @Id
   @SequenceGenerator(name="telefone_seq", sequenceName="TELEFONE_SEQ", allocationSize=1)
   @GeneratedValue(strategy=GenerationType.AUTO, generator="telefone_seq")
   private Long id;

   @ManyToOne
   private Pessoa pessoa;

           // Getters and setters

   @Override
   public int hashCode() {
      final int prime = 31;
      int result = 1;
      result = prime * result + ((id == null) ? 0 : id.hashCode());
      return result;
   }

   @Override
   public boolean equals(Object obj) {
      if (this == obj)
         return true;
      if (obj == null)
         return false;
      if (getClass() != obj.getClass())
         return false;
      Telefone other = (Telefone) obj;
      if (id == null) {
         if (other.id != null)
            return false;
      } else if (!id.equals(other.id))
         return false;
      return true;
   }   




Then I have this code on my DAO

Code:

   @SuppressWarnings("unchecked")
   @Override
   @Transactional(propagation=Propagation.SUPPORTS, readOnly=true)
   public List<T> findByExample(T entity) {      
      Example example = Example.create(entity).ignoreCase().enableLike(MatchMode.ANYWHERE);
      return getHibernateTemplate().getSessionFactory().getCurrentSession().createCriteria(entity.getClass()).add(example).list();
   }



Now trying to create a Pessoa, and add two Telefones, and persist them, I run my findByExample method with new Pessoa() as a parameter, and I have 2 rows on my SQL being returned, instead of one row like the query find("* pessoa"). My findByExample made a left outer join on table Telefone, but I don't want this to happen!

Thanks in advance.


Top
 Profile  
 
 Post subject: Re: Query by example - left outer join
PostPosted: Thu Feb 25, 2010 1:36 pm 
Beginner
Beginner

Joined: Tue Aug 25, 2009 11:42 am
Posts: 49
I found
session.createCriteria(Pessoa.class).add(example).setResultTransformer(new DistinctRootEntityResultTransformer()).list());

It works (albeit the sql result already has excess rows) but it's deprecated.


Top
 Profile  
 
 Post subject: Re: Query by example - left outer join
PostPosted: Thu Feb 25, 2010 4:16 pm 
Newbie

Joined: Mon Nov 26, 2007 8:33 pm
Posts: 6
Thanks again tseringshrestha, yes it worked well, but as you said the sql still returns excessive rows, different from the find("* from Pessoa) method, that I can have the lazy load feature.
I'd like to have the findByExample method being called because I could have only one method, to do all my searches on the system, only depending on the populated properties, I could save a lot of time.
There is another option? Is this a bug?

Thanks


Top
 Profile  
 
 Post subject: Re: Query by example - left outer join
PostPosted: Wed Mar 03, 2010 1:59 pm 
Beginner
Beginner

Joined: Tue Aug 25, 2009 11:42 am
Posts: 49
The unpopulated properties are NULLs - and for Hibernate, it cannot differentiate whether a NULL means anything or exclude from matching criteria (or in case of collections, empty).
I believe if u really need to tweak the matching, u might need to do some research into how Hibernate implements such matching and see if it offers any extension points where u could plug in your matching code.

On another note, DistinctRootEntityResultTransformer is deprecated so I would like someone to tell us what is the preferred alternative for that.


Top
 Profile  
 
 Post subject: Re: Query by example - left outer join
PostPosted: Tue Mar 09, 2010 9:15 pm 
Newbie

Joined: Mon Nov 26, 2007 8:33 pm
Posts: 6
Right tseringshrestha, I'll dive into a search for that, and if I find a solution, I'll post here what I discovered.
Any other sugestions would be very appreciated.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.