2 Replies Latest reply on Jan 31, 2014 4:20 AM by Frank Langelage

    CriteriaBuilder: literal values and variables!

    Frank Langelage Master

      I create a dynamic query using the CriteriaBuilder.

      Looking a the SQL on the database I saw, that some of the condition parameters are added as literals (.equal) and others are variables (lessThan).


      CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();
      CriteriaQuery<MbiFesta> criteriaQuery = criteriaBuilder.createQuery( MbiFesta.class );
      Root<MbiFesta> root = criteriaQuery.from( MbiFesta.class );
      Join<MbiFesta, MbiFepos> mbiFepos = root.join( MbiFesta_.mbiFepos );


      List<Predicate> predicates = new ArrayList<Predicate>();


      // " WHERE mbiFesta.werkNr = :werkNr"
      Predicate condition = criteriaBuilder.equal( root.get( MbiFesta_.werkNr ), plantId );
      predicates.add( condition );


      // " AND mbiFesta.status < 16"
      condition = criteriaBuilder.lessThan( root.get( MbiFesta_.status ), ProductionEntityStatus.FINISHED );
      predicates.add( condition );


      // " AND mbiFepos.status < 16";
      condition = criteriaBuilder.lessThan( mbiFepos.get( MbiFepos_.status ), ProductionEntityStatus.FINISHED );
      predicates.add( condition );


      // " AND mbiFepos.mbiAplazAplazNr.aplazNr = :aplazNr"
      condition = criteriaBuilder.equal( mbiFepos.get( MbiFepos_.mbiAplaz ), workCenterId );
      predicates.add( condition );


      // " AND mbiFepos.termEnde < :x"
      if ( toDate != null ) {
      condition = criteriaBuilder.lessThanOrEqualTo( mbiFepos.get( MbiFepos_.termEnde ), toDate );
      predicates.add( condition );


      criteriaQuery.distinct( true );
      criteriaQuery.where( predicates.toArray( new Predicate[ predicates.size() ] ) );


      TypedQuery<MbiFesta> query = this.entityManager.createQuery( criteriaQuery );


      Last parsed SQL statement :

        select distinct mbifesta0_.festa_serial as festa_se1_99_,

          mbifesta0_.afstk_serial as afstk_se2_99_, mbifesta0_.anz_fnach as

          anz_fnac3_99_, mbifesta0_.chargen_nr as chargen_4_99_,

          mbifesta0_.dispo_serial as dispo_se5_99_, mbifesta0_.distk_serial as

          distk_se6_99_, mbifesta0_.dlz_ist as dlz_ist7_99_, mbifesta0_.dlz_soll as

          dlz_soll8_99_, mbifesta0_.element_nr as element_9_99_,

          mbifesta0_.fe_alternativ_nr as fe_alte10_99_, mbifesta0_.fe_art_nr as

          fe_art_11_99_, mbifesta0_.fe_lfd_nr as fe_lfd_12_99_, mbifesta0_.kp_status

          as kp_stat13_99_, mbifesta0_.lager_nr as lager_n14_99_,

          mbifesta0_.login_bez as login_b15_99_, mbifesta0_.bereich_nr as

          bereich49_99_, mbifesta0_.nachb_serial as nachb_s50_99_, mbifesta0_.tst_nr

          as tst_nr51_99_, mbifesta0_.menge_aus as menge_a16_99_,

          mbifesta0_.menge_aus_b as menge_a17_99_, mbifesta0_.menge_aus_c as

          menge_a18_99_, mbifesta0_.menge_ist as menge_i19_99_,

          mbifesta0_.menge_soll as menge_s20_99_, mbifesta0_.no_data as

          no_data21_99_, mbifesta0_.prod_lager_nr as prod_la22_99_,

          mbifesta0_.prod_rang_folge_nr as prod_ra23_99_, mbifesta0_.prod_rang_nr as

          prod_ra24_99_, mbifesta0_.prod_serien_nr as prod_se25_99_,

          mbifesta0_.serien_nr as serien_26_99_, mbifesta0_.sfa_knz as

          sfa_knz27_99_, mbifesta0_.sort_string as sort_st28_99_,

          mbifesta0_.sperr_knz as sperr_k29_99_, mbifesta0_.status as status30_99_,

          mbifesta0_.status_febdr as status_31_99_, mbifesta0_.status_fepos as

          status_32_99_, mbifesta0_.status_festr as status_33_99_,

          mbifesta0_.term_fert_ist1 as term_fe34_99_, mbifesta0_.term_fert_ist2 as

          term_fe35_99_, mbifesta0_.term_fert_soll1 as term_fe36_99_,

          mbifesta0_.term_fert_soll2 as term_fe37_99_, mbifesta0_.term_frei_soll as

          term_fr38_99_, mbifesta0_.term_start_soll as term_st39_99_,

          mbifesta0_.var_fam_nr as var_fam40_99_, mbifesta0_.var_id as var_id41_99_,

          mbifesta0_.werk_nr as werk_nr42_99_, mbifesta0_.x_sygrp_nr as

          x_sygrp43_99_, mbifesta0_.zpkt_erstell as zpkt_er44_99_,

          mbifesta0_.zpkt_fert_ist1 as zpkt_fe45_99_, mbifesta0_.zpkt_fert_ist2 as

          zpkt_fe46_99_, mbifesta0_.zpkt_frei_ist as zpkt_fr47_99_,

          mbifesta0_.zpkt_korr as zpkt_ko48_99_ from mbi_festa mbifesta0_ inner join

          mbi_fepos mbifepos1_ on mbifesta0_.festa_serial=mbifepos1_.festa_serial

          where mbifesta0_.werk_nr=10 and mbifesta0_.status<? and

          mbifepos1_.status<? and mbifepos1_.aplaz_nr=500210020 and