2 Replies Latest reply on Nov 15, 2007 3:56 PM by Stoigniew Sztank

    Hibernate outer/inner join problem

    Stoigniew Sztank Newbie

      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!