Criteria: SQL fetches data from all the tables in the joins

kiransg · **Joined:** Fri Aug 04, 2006 1:38 am **Posts:** 4

Hi All,

I am using Criteria API to fetch a domain object based on some conditions.
But the SQL query fired by Hibernate contains all the columns from all the tables it is doing the join on.

Sql Query being fired:

Code:

SELECT this_.teacher_id AS person1_1_5_,
  this_1_.first_name AS first2_1_5_,
  this_1_.middle_name AS middle3_1_5_,
  this_1_.last_name AS last4_1_5_,
  this_1_.birth_date AS birth5_1_5_,
  this_1_.sex AS sex1_5_,
  this_1_.email AS email1_5_,
  this_1_.phone_num AS phone8_1_5_,
  this_.dept_id AS dept2_2_5_,
  this_.salary AS salary2_5_,
  this_.designation AS designat4_2_5_,
  this_.is_hod AS is5_2_5_,
  alias5x5_.dept_id AS dept1_6_0_,
  alias5x5_.dept_name AS dept2_6_0_,
  alias5x5_.dept_code AS dept3_6_0_,
  courses8_.teacher_id AS teacher1_,
  alias1x1_.course_id AS course2_,
  alias1x1_.course_id AS course1_7_1_,
  alias1x1_.course_cd AS course2_7_1_,
  alias1x1_.course_desc AS course3_7_1_,
  alias2x2_.enrollment_id AS enrollment1_8_2_,
  alias2x2_.audit_number AS audit2_8_2_,
  alias2x2_.course_id AS course3_8_2_,
  alias2x2_.student_id AS student4_8_2_,
  alias3x3_.student_id AS person1_1_3_,
  alias3x3_1_.first_name AS first2_1_3_,
  alias3x3_1_.middle_name AS middle3_1_3_,
  alias3x3_1_.last_name AS last4_1_3_,
  alias3x3_1_.birth_date AS birth5_1_3_,
  alias3x3_1_.sex AS sex1_3_,
  alias3x3_1_.email AS email1_3_,
  alias3x3_1_.phone_num AS phone8_1_3_,
  alias3x3_.mentor_id AS mentor2_4_3_,
  alias3x3_.room_id AS room3_4_3_,
  alias3x3_.roll_num AS roll4_4_3_,
  alias4x4_.enrollment_history_id AS enrollment1_9_4_,
  alias4x4_.audit_number AS audit2_9_4_,
  alias4x4_.enrl_date AS enrl3_9_4_,
  alias4x4_.grade AS grade9_4_,
  alias4x4_.enrollment_id AS enrollment5_9_4_
FROM sf_teacher this_
INNER JOIN sf_person this_1_ ON this_.teacher_id = this_1_.person_id
INNER JOIN sf_department alias5x5_ ON this_.dept_id = alias5x5_.dept_id
INNER JOIN sf_teacher_course_mapping courses8_ ON this_.teacher_id = courses8_.teacher_id
INNER JOIN sf_course alias1x1_ ON courses8_.course_id = alias1x1_.course_id
INNER JOIN sf_enrollment alias2x2_ ON alias1x1_.course_id = alias2x2_.course_id
INNER JOIN sf_student alias3x3_ ON alias2x2_.student_id = alias3x3_.student_id LEFT
OUTER JOIN sf_person alias3x3_1_ ON alias3x3_.student_id = alias3x3_1_.person_id
INNER JOIN sf_enrollment_history alias4x4_ ON alias2x2_.enrollment_id = alias4x4_.enrollment_id
WHERE alias3x3_1_.first_name = 'Test3'
 AND alias4x4_.grade = 'A+'
 AND alias4x4_.audit_number = alias2x2_.audit_number

But, I am only interested in data from table sf_teacher.
This might help to reduce the network traffic from the Database to the Application Server, as the data transferred will be significantly reduced.

Is there any way specify that only columns from the specified domain is fetched ?

--Kiran

aleruz · **Joined:** Wed May 03, 2006 5:28 am **Posts:** 2

I think you have to post some code with the proper forum tag. Post the mapping and the criteria code.
Personally, when dealing with complex query, I prefere using the query object.

kiransg · **Joined:** Fri Aug 04, 2006 1:38 am **Posts:** 4

I pasted the SQL just to suggest the complexity of the Query and the nature of the select fields.
Instead of attaching the HBM mappings and Code .. I am briefly giving the relationsphips among the tables involved

Hope this helps to understand the query.

I am more interested in getting rid of the extra colums that are in Query, if we are using Criteria API.

-- Kiran

bkmr_77 · **Joined:** Tue Apr 25, 2006 12:04 pm **Posts:** 260

You can use DetachedCriteria to solve your problem. Search for examples in this forum, definitely there is one link that I am aware of http://forum.hibernate.org/viewtopic.php?t=959176&highlight=

kiransg · **Joined:** Fri Aug 04, 2006 1:38 am **Posts:** 4

If Projection is used for fetching the columns of the sf_teacher then,

Code:

    DetachedCriteria criteria = DetachedCriteria.forClass(Teacher.class);
   
    // ... Apply all the condtions to filter on

    // Set the projection
    criteria.setProjection(Projections.projectionList().add(
        Projections.property("teacherId"), "teacherId").add(
        Projections.property("firstName"), "firstName").add(
        Projections.property("middleName"), "middleName").add(
       
        // ... For each of the fields if Teacher set projection
        
        Projections.property("hod"), "hod "));

    criteria.setResultTransformer(new AliasToBeanResultTransformer(Teacher.class));

This code is only repetative, as all the fields of Teacher needs to be specified in the Projections. Moreover, the Teacher object got by the above method will not have the proxy objects for the other relations.
ie, the teacher.getDepartment() or teacher.getCourses() will not give the objects.

Please let me know if there is any way of getting rid of the extra columns being fetched by the select query and still getting the Teacher objects with all the proxies set.

So that at later stage if Department or Courses info is required then insted of creating a new Criteria to fetch the department or courses details.

--Kiran

EDH · **Joined:** Mon Aug 23, 2004 12:44 am **Posts:** 25

Did you find a way to also get a hold of the proxied objects of Teacher ?
If yes, how ?
Thanks,
EDH

tinku2955 · **Posted:** Tue Dec 30, 2008 2:53 am

Hi Buddy,
I am using DetachedCriteria, I am new to this topic,i want to retrieve all the data from DataBase and Display in the JSF.HTML/JsP page. But how to retrive this i dont no please help me in solving my probleem.

my Table name is "User"
this is my method.through which i should retrive all the data from table.

public User display() {
User obj = null;
try {
DetachedCriteria criteria = DetachedCriteria.forClass(User.class);

criteria.
//your code here

HibernateTemplate tm = getHibernateTemplate();
List objs = tm.findByCriteria(criteria);
if ((objs != null) && (objs.size() > 0)) {
obj = (User) objs.get(0);
}
} catch (Exception e) {
e.printStackTrace();
}
return obj;
}

This should return List or User.... :-)
Thanks...in Advance

Vladimir Korolev · **Joined:** Mon Nov 08, 2010 9:20 am **Posts:** 2

kiransg wrote:

Please let me know if there is any way of getting rid of the extra columns being fetched by the select query and still getting the Teacher objects with all the proxies set.

Here is a possible solution if someone will need it. Works in Hibernate 3.3.2.
Result: select clause contains only columns of root entity, collections of root entity are set correctly.
Note 1: solution expects that all Hibernate entity properties have same names as corresponding Java properties.
Note 2: in my case all *-to-one associations are mapped with fetch = EAGER and they were loaded correctly without additional fixes. But same approach may be used for them too.
Note 3: I don't know how loaded entities will interact with persistence context if you try to update and save them. I use them in read-only transactions.

Code:

      final String entity = ((CriteriaImpl) criteria).getEntityOrClassName();
      final Class entityClass = Class.forName(entity);
      final SessionImplementor session = ((CriteriaImpl) criteria).getSession();
      final EntityPersister persister = session.getFactory().getEntityPersister(entity);

      // select clause will contain only properties of root entity
      ProjectionList resultProjection = Projections.projectionList();
      resultProjection.add(Projections.property(persister.getIdentifierPropertyName()));
      for (String property : persister.getPropertyNames()) {
         resultProjection.add(Projections.property(property), property);
      }
      criteria.setProjection(resultProjection);

      // fix collections after loading
      criteria.setResultTransformer(new AliasToBeanResultTransformer(entityClass) {
         @Override
         public Object transformTuple(Object[] tuple, String[] aliases) {
            Object entity = super.transformTuple(tuple, aliases);
            setCollections(entity, aliases);
            return entity;
         }

         protected void setCollections(Object entity, String[] aliases) {
            for (int i = 0, aliasesLength = aliases.length; i < aliasesLength; i++) {
               // we expect aliases with same names as in properties
               String property = aliases[i];
               Type type = persister.getPropertyType(property);
               if (type instanceof CollectionType) {
                  Serializable id = persister.getIdentifier(entity, EntityMode.POJO);
                  Object collection = ((CollectionType) type).getCollection(id, session, entity);
                  // commons-beanutils lib, can be replaced with PropertyAccessor (see AliasToBeanResultTransformer) 
                  PropertyUtils.setProperty(entity, property, collection); 
               }
            }
         }
      });