[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Hibernate outer/inner join problem

[sztank]

Hello,
I trying to use Criteria to build some conditional query and I could not manage to set it up well.

The query is:

  | Criteria crit = session.createCriteria(Contract.class);
  |       crit.setFetchMode("product", FetchMode.JOIN);
  |       crit.createAlias("status", "stat");
  |       crit.createAlias("client", "cli");
  |       crit.createAlias("product", "prod");
  |       
  |       for(int i=0; i < searchArray.length ; i++) {
  |     	  crit.add( Restrictions.disjunction()
  |     	      .add( Restrictions.ilike("comment", searchArray, MatchMode.ANYWHERE))
  |     	      .add( Restrictions.ilike("clientContractId", searchArray, MatchMode.ANYWHERE))
  |     	      .add( Restrictions.ilike("cli.name", searchArray, MatchMode.ANYWHERE))
  |     	      
  |     	  );  
  | 	  }
  | 
  | crit.addOrder( Order.asc("contractId"));
  |       crit.addOrder( Order.asc("contractItem"));
  |       
  |       contracts = crit.list();
  | 

product is defined in the Contract entity as follows:

  | @ManyToOne
  |    @JoinColumn(name="product")
  |    public Product getProduct()
  |    {
  |       return product;
  |    }
  | 

and it can be a null value. 

The Sql is as follows:

  | select
  |         this_.contract as contract132_3_,
  |         this_.client as client132_3_,
  |         this_.client_contract_id as client2_132_3_,
  |         this_.comment as comment132_3_,
  |         this_.contract_id as contract4_132_3_,
  |         this_.contract_item as contract5_132_3_,
  |         this_.date_client as date6_132_3_,
  |         this_.date_closed as date7_132_3_,
  |         this_.date_irga as date8_132_3_,
  |         this_.date_opened as date9_132_3_,
  |         this_.price as price132_3_,
  |         this_.product as product132_3_,
  |         this_.quantity as quantity132_3_,
  |         this_.quantity_closed as quantity12_132_3_,
  |         this_.source_contract as source13_132_3_,
  |         this_.status as status132_3_,
  |         this_.status_assembly as status14_132_3_,
  |         this_.status_pcb as status15_132_3_,
  |         this_.status_print as status16_132_3_,
  |         this_.wz as wz132_3_,
  |         cli2_.client as client134_0_,
  |         cli2_.name as name134_0_,
  |         prod3_.product as product143_1_,
  |         prod3_.casual_name as casual2_143_1_,
  |         prod3_.client as client143_1_,
  |         prod3_.close_date as close3_143_1_,
  |         prod3_.code_name as code4_143_1_,
  |         prod3_.comments as comments143_1_,
  |         prod3_.creator as creator143_1_,
  |         prod3_.group_name as group7_143_1_,
  |         prod3_.start_date as start8_143_1_,
  |         stat1_.status as status133_2_,
  |         stat1_.name as name133_2_ 
  |     from
  |         public.contract this_ 
  |     inner join
  |         public.client cli2_ 
  |             on this_.client=cli2_.client 
  |     inner join
  |         public.product prod3_ 
  |             on this_.product=prod3_.product 
  |     inner join
  |         public.contractstatus stat1_ 
  |             on this_.status=stat1_.status 
  |     where
  |         (
  |             this_.comment ilike ? 
  |             or this_.client_contract_id ilike ? 
  |             or cli2_.name ilike ?
  |         ) 
  |     order by
  |         this_.contract_id asc,
  |         this_.contract_item asc
  | 

 Now the select don't get the results with product set to null value.I can't manage to set Criteria to use outer join with product. I tried to put the setFetchMode("product", FetchMode.JOIN); in few places but it doesn't change anything in result sql. If someone could give me some light on it i would be gratefully!

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4104784#4104784

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4104784