Using SQL and loading referenced objects

litterat · **Joined:** Tue Feb 07, 2006 2:43 am **Posts:** 7

Hibernate version:3

Name and version of the database you are using:Oracle 8.1

Hi all
I am using Hibernate to execute SQL queries
My problem is that it seems not to initialise the referenced objects in the returned object
What happens next is that each time I am accessing the returned object's referenced objects Hibernate execute a query against the database
I used the properties fetch="join" and outer-join="true" and lazy="false" in the many-to-one tag

my question is:
1. Does the fact that I use SQLand not HSQL prevent Hibernate from loading the referenced objects in one select

2. Is there a way after I get the collection of objects to cause Hibernate to reload all referenced object in one go?

Thanks in advance

abg1979 · **Joined:** Fri Sep 09, 2005 11:35 am **Posts:** 101

you can do this in 2 ways

1. Change your SQL to get the referenced objects also.
2. Change your mapping files to use batch-size in collection mappings.

litterat · **Joined:** Tue Feb 07, 2006 2:43 am **Posts:** 7

Some times I get the SQL from external resource like reporting system which I need to extract the SQL used for retrieving the data

So I might repeat the question which in interest for me
Can I reload a collection so that Hibernate will bring all referenced objects

abg1979 · **Joined:** Fri Sep 09, 2005 11:35 am **Posts:** 101

are you looking for something like session.refresh(..).

pksiv · **Posted:** Wed Feb 08, 2006 3:09 pm

Can you show the java code you're using to execute your SQL queries.

litterat · **Joined:** Tue Feb 07, 2006 2:43 am **Posts:** 7

abg1979 wrote:

are you looking for something like session.refresh(..).

Yes but for a whole collection of objects

litterat · **Joined:** Tue Feb 07, 2006 2:43 am **Posts:** 7

pksiv wrote:

Can you show the java code you're using to execute your SQL queries.

Yes

Code:

public List getData(QueryInfo queryInfo) {
      List result = null;
      Session session = HibernateUtil.getSession();
      //create query
      Query filterdQuery =
         session.createSQLQuery(queryInfo.getQueryStr()).addEntity(
            Constants.ALIAS_MAIN_ENTITY,
            queryInfo.getEntityClass().getName());

      setQueryParameters(filterdQuery, queryInfo);

      //if not showing all limit the result
      if (queryInfo.getNumRecordInPage() != 0) {
         filterdQuery.setFirstResult(queryInfo.getStartRecordIndex() - 1);
         filterdQuery.setMaxResults(queryInfo.getCurrentPageRecordNum());
      }

      result = filterdQuery.list();

      HibernateUtil.closeSession();
      return result;

   }

pksiv · **Posted:** Thu Feb 09, 2006 1:24 am

litterat wrote:

pksiv wrote:

Can you show the java code you're using to execute your SQL queries.

Yes

Have you looked at Section 16.3 in the docs ?

It seems to indicate that you need to specify a <return-alias> for each entity you wish to return.

If that doesn't help, can you show your SQL as well.

litterat · **Joined:** Tue Feb 07, 2006 2:43 am **Posts:** 7

pksiv wrote:

litterat wrote:

pksiv wrote:

Can you show the java code you're using to execute your SQL queries.

Yes

Have you looked at Section 16.3 in the docs ?

It seems to indicate that you need to specify a <return-alias> for each entity you wish to return.

If that doesn't help, can you show your SQL as well.

Thanks for the quick response
But I looked at section 16.3 and only found it to be about ordering the results (I might looking at a different version my version is 3.0.5)
I also looked for the return-alias and found no results

My sql is fine as I execute it agianst the database it runs and there is no problem I also get the objects from Hibernate.
What is seems to happen that the objects which being referenced are not initialized

but in any case here is the SQL

Code:

/* Formatted on 2006/02/09 07:43 (Formatter Plus v4.8.0) */ 
SELECT * 
  FROM (SELECT mainentity.t1recid AS t1_0_, 
               mainentity.t1id_file_id AS t2_16_0_, 
               mainentity.t1sid_sending_site_id AS t3_16_0_, 
               mainentity.t1ssid_sending_system_id AS t4_16_0_, 
               mainentity.t1ldte_last_update_date AS t5_16_0_, 
               mainentity.t1comp_company AS t6_16_0_, 
               mainentity.t1tbu_teva_business_unit AS t7_16_0_, 
               mainentity.t1rtyp_record_type AS t8_16_0_, 
               mainentity.t1bilc_bill_to_customer AS t9_16_0_, 
               mainentity.t1cust_ship_to_customer AS t10_16_0_, 
               mainentity.t1shpn_ship_to_number AS t11_16_0_, 
               mainentity.t1cusn_customer_to_name AS t12_16_0_, 
               mainentity.t1addr_customer_to_address AS t13_16_0_, 
               mainentity.t1city_ship_to_city AS t14_16_0_, 
               mainentity.t1phon_ship_to_phone AS t15_16_0_, 
               mainentity.t1cont_contact_person AS t16_16_0_, 
               mainentity.t1accn_customer_accounting_num AS t17_16_0_, 
               mainentity.t1sscd_sales_person_code AS t18_16_0_, 
               mainentity.t1ssnm_sales_person_name AS t19_16_0_, 
               mainentity.t1fax_fax_number AS t20_16_0_, 
               mainentity.t1emal_email_address AS t21_16_0_, 
               mainentity.t1stat_customer_status_code AS t22_16_0_, 
               mainentity.t1nam2_name2 AS t23_16_0_, 
               mainentity.t1adr2_address2 AS t24_16_0_, 
               mainentity.t1refa_reference_field_a AS t25_16_0_, 
               mainentity.t1ref1_reference_field_1 AS t26_16_0_, 
               mainentity.change_code AS change27_16_0_, 
               mainentity.change_date AS change28_16_0_, 
               mainentity.origin_file AS origin29_16_0_, 
               mainentity.t1exbilid AS t30_16_0_, 
               mainentity.t1exisactive AS t31_16_0_, 
               mainentity.t1globcustod AS t32_16_0_, 
               mainentity.t1coun_ship_to_country_code AS t33_16_0_ 
          FROM (SELECT f_customers_active.* 
                  FROM customers_active f_customers_active, 
                       (SELECT countries_list.* 
                          FROM app_filters_groups filtergroups, 
                               app_filters filter, 
                               app_filter_entity filter_entity, 
                               app_groups GROUPS, 
                               app_groups_users usergroups, 
                               app_users users, 
                               app_filter_entity_data filter_entity_data, 
                               countries_list 
                         WHERE (    (filter.ID = filter_entity.filter_id) 
                                AND (filter.ID = filtergroups.filter_id) 
                                AND (GROUPS.ID = filtergroups.GROUP_ID) 
                                AND (GROUPS.ID = usergroups.GROUP_ID) 
                                AND (users.ID = usergroups.user_id) 
                                AND (users.ID = 2132) 
                                AND (filter_entity.entity_id = 2332) 
                                AND (filter_entity.ID = 
                                           filter_entity_data.filter_entity_id 
                                    ) 
                                AND (filter_entity_data.data_id_string = 
                                                           countries_list.code 
                                    ) 
                               )) f_countries_list 
                 WHERE (f_countries_list.code = 
                                f_customers_active.t1coun_ship_to_country_code 
                       ) 
                UNION 
                SELECT f_customers_active.* 
                  FROM customers_active f_customers_active, 
                       countries_list f_countries_list, 
                       area_manager_country f_area_manager_country, 
                       (SELECT area_manager.* 
                          FROM app_filters_groups filtergroups, 
                               app_filters filter, 
                               app_filter_entity filter_entity, 
                               app_groups GROUPS, 
                               app_groups_users usergroups, 
                               app_users users, 
                               app_filter_entity_data filter_entity_data, 
                               area_manager 
                         WHERE (    (filter.ID = filter_entity.filter_id) 
                                AND (filter.ID = filtergroups.filter_id) 
                                AND (GROUPS.ID = filtergroups.GROUP_ID) 
                                AND (GROUPS.ID = usergroups.GROUP_ID) 
                                AND (users.ID = usergroups.user_id) 
                                AND (users.ID = 2132) 
                                AND (filter_entity.entity_id = 2330) 
                                AND (filter_entity.ID = 
                                           filter_entity_data.filter_entity_id 
                                    ) 
                                AND (filter_entity_data.data_id_string = 
                                                        area_manager.areamanid 
                                    ) 
                               )) f_area_manager 
                 WHERE (f_countries_list.code = 
                                f_customers_active.t1coun_ship_to_country_code 
                       ) 
                   AND (f_area_manager_country.country_code = 
                                                         f_countries_list.code 
                       ) 
                   AND (f_area_manager.areamanid = 
                                             f_area_manager_country.manager_id 
                       )) mainentity, 
               sending_site f_site, 
               global_customer_type f_globalcustomertype 
         WHERE (f_globalcustomertype.custtypeid = 
                                                 mainentity.t1rtyp_record_type 
               ) 
           AND (f_site.sendingsiteid = mainentity.t1sid_sending_site_id)) 
 WHERE ROWNUM <= 100 