CriteriaBuilder: literal values and variables!
lafr Jan 30, 2014 5:02 PMI 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<=?