CriteriaBuilder: literal values and variables!

[lafr]

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

    mbifepos1_.term_ende<=?

[chill_apple]

Hello Frank,

That is interesting. My first guess is that it would have kept it parametrized.  I was doing some research on what you found ( and have yet to test it ) but I thought I mentioned it anyway.

Where you have this code:

       Predicate condition = criteriaBuilder.equal( root.get( MbiFesta_.werkNr ), plantId );

Try this:

      ParameterExpression<Integer> parameter = criteriaBuilder.parameter( Integer.class, "plantId" ); 

      Predicate condition = criteriaBuilder.equal( root.get( MbiFesta_.werkNr ), parameter );

      ...

      query.setParameter( parameter, plantId );

Seems over done to have tell it to be parameter especially when the other conditions do. I imagine someone smarter than me can explain it.

Calvin

[lafr]

Found this: http://stackoverflow.com/questions/20161849/how-to-set-positional-named-parameters-dynamically-to-jpa-criteria-query

So behavior might also be dependent on the datatype.