I have a two classes "JobPackageBean" and "PackageAirplaneBean" which has a relationship one-to-many between them as you can see in the mapping files. It means that one JobPackageBean can returns a set of PackageAirplaneBean. I have many fields from PackageAirplaneBean used on Search screen. I am using criteria API for search as it involves numerous field on Search screen.

Now I have difficult time to add any field from PackageAirplaneBean for search using criteria API. It'd be great if someone can guide me how to refer to any field in PackageAirplaneBean, say "engineEffectivity" when I create criteria on JobPackageBean class like session.createCriteria(JobPackageBean.class).
Thanks in advance.

[b]Hibernate version: [/b]
3.0.5

[b]Mapping documents:[/b]

JobPackageBean.hbm.xml mapping file
....
<many-to-one column="castle_group" lazy="false" class="CastleGroupBean" name="group"/>
<property name="component" column="component" not-null="false" type="string"/>
<set cascade="save-update" lazy="false" name="airplanes">
<key column="job_package"/>
<one-to-many class="PackageAirplaneBean"/>
</set>
<many-to-one column="type" class="TypeBean" name="type" lazy="false"/>

...and so on.

And PackageAirplaneBean.hbm.xml mapping

<class table="package_airplanes" name="PackageAirplaneBean">
<id name="packageAirplaneId" type="long" column="package_airplane_id">
<generator class="native"/>
</id>
<property name="maxTaxiGrossWeight" column="mtgw" type="integer"/>
<property name="engineEffectivity" column="engine_effectivity" type="string"/>
<property name="engineType" column="engine_type" type="string"/>
<property name="flightHours" column="flight_hours" type="integer"/>
...
and so on.


[b]Code between sessionFactory.openSession() and session.close():[/b]


[b]Full stack trace of any exception that occurs:[/b]

[b]Name and version of the database you are using: [/b]
Oracle 9.x

[b]The generated SQL (show_sql=true):[/b]

[b]Debug level Hibernate log excerpt:[/b]

sess.createCriteria(JobPackageBean.class)
.createAlias("component", "component")
.add( Restrictions.like("component.engineEffectivity", "F%") )
.list();

also refer to

http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#querycriteria-associations

Thank you. This is great. Now I can refer to any field in "PackageAirplaneBean". I was missing "component." while refering to "engineEffectivity" and was getting an error saying "engineEffectivity" isn't a valid attribute for "JobPackageBean".

Now I have another question.

I have "JobBean" and it has many-to-one relatioship with "JobPackageBean". It means one or more jobs associated with "JobPackageBean" and I return a list of "JobBean" from all Search criterias. So I create a criteria like

session.createCriteria(JoBean.class, "jb")
.createAlias("status", "sb")
...
.createCriteria("jobPackage", "jpb");

I have 2 "JobPackageBean" associated with "PackageAirplaneBean" and I have 2 "JobBean" associated with "JobPackageBean". When I put a valid criteria for any of "PackageAirplaneBean" field, say "engineEffectivity". It doesn't seem to return two "JobBean" as a result of this search as expected.

I don't know if I am missing anything to fetch 2 jobs as a result of the search.

Your response would be highly appreciated. Thank you.

I see the sql like this:

Hibernate: select this_.job_id as job1_14_, this_.job_specific_question as job2_41_14_, this_.approver_clear as approver3_41_14_, this_.original_due_date as original4_41_14_, this_.temporary_record as temporary5_41_14_, this_.assignee_new as assignee6_41_14_, this_.sequence_in_package as sequence7_41_14_, this_.initiator_clear as initiator8_41_14_, this_.job_approved_date_due as job9_41_14_, this_.disposition_summary as disposi10_41_14_, this_.assignee as assignee41_14_, this_.job_name as job12_41_14_, this_.initiator as initiator41_14_, this_.job_number as job14_41_14_, this_.requesters_name as requesters15_41_14_, this_.assignee_clear as assignee16_41_14_, this_.response_date as response17_41_14_, this_.current_due_date as current18_41_14_, this_.date_into_boeing as date19_41_14_, this_.date_into_castle as date20_41_14_, this_.priority as priority41_14_, this_.status as status41_14_, this_.note as note41_14_, this_.approver as approver41_14_, this_.type as type41_14_, this_.initiator_new as initiator26_41_14_, this_.requester as requester41_14_, this_.job_subject as job28_41_14_, this_.status_date as status29_41_14_, this_.approver_new as approver30_41_14_, this_.job_package as job31_41_14_, this_1_.disposition as disposit2_42_14_, case when this_1_.disposition_job_id is not null then 1 when this_2_.faa_8100_job_id is not null then 2 when this_.job_id is not null then 0 end as clazz_14_, pb3_.job_priority_id as job1_0_, pb3_.object_type as object2_48_0_, pb3_.job_priority as job3_48_0_, sb2_.status_id as status1_1_, sb2_.abbreviation as abbrevia2_56_1_, sb2_.object_type as object3_56_1_, sb2_.status as status56_1_, tb1_.type_id as type1_2_, tb1_.type as type59_2_, tb1_.type_description as type3_59_2_, tb1_.object_type as object4_59_2_, jpb4_.job_package_id as job1_3_, jpb4_.owner_clear as owner2_44_3_, jpb4_.discrepancy_keyword as discrepa3_44_3_, jpb4_.library_book_volumne as library4_44_3_, jpb4_.ear_content as ear5_44_3_, jpb4_.job_package_name as job6_44_3_, jpb4_.requesters_document as requesters7_44_3_, jpb4_.revision_note as revision8_44_3_, jpb4_.comr_content as comr9_44_3_, jpb4_.initiator_clear as initiator10_44_3_, jpb4_.charge_number as charge11_44_3_, jpb4_.solution as solution44_3_, jpb4_.itar_content as itar13_44_3_, jpb4_.table_of_contents as table14_44_3_, jpb4_.ata as ata44_3_, jpb4_.published as published44_3_, jpb4_.archive_date as archive17_44_3_, jpb4_.chargeable as chargeable44_3_, jpb4_.initiator as initiator44_3_, jpb4_.castle_group as castle20_44_3_, jpb4_.component as component44_3_, jpb4_.type as type44_3_, jpb4_.owner as owner44_3_, jpb4_.library_location_code as library24_44_3_, jpb4_.revision_level as revision25_44_3_, jpb4_.status as status44_3_, jpb4_.problem_frequency as problem27_44_3_, jpb4_.note as note44_3_, jpb4_.owner_new as owner29_44_3_, jpb4_.initiator_new as initiator30_44_3_, jpb4_.write_locked_date as write31_44_3_, jpb4_.sirp as sirp44_3_, jpb4_.status_date as status33_44_3_, jpb4_.job_package_number as job34_44_3_, jpb4_.problem as problem44_3_, jpb4_.model as model44_3_, jpb4_.subject as subject44_3_, jpb4_.history as history44_3_, jpb4_.locked_by_user as locked39_44_3_, jpb4_.analysis_description as analysis40_44_3_, jpb4_.library_location as library41_44_3_, atabean15_.ata_id as ata1_4_, atabean15_.ata as ata66_4_, atabean15_.description as descript3_66_4_, atabean15_.model as model66_4_, modelbean16_.model_id as model1_5_, modelbean16_.base_model as base2_60_5_, modelbean16_.model as model60_5_, basemodelb17_.base_model_id as base1_6_, basemodelb17_.base_model as base2_67_6_, basemodelb17_.standard_statement as standard3_67_6_, basemodelb17_.site as site67_6_, basemodelb17_.make as make67_6_, pcgb8_.group_id as group1_7_, pcgb8_.active_status as active2_15_7_, pcgb8_.manager as manager15_7_, pcgb8_.group_name as group4_15_7_, jpbairplan9_.package_airplane_id as package1_8_, jpbairplan9_.mtgw as mtgw47_8_, jpbairplan9_.engine_effectivity as engine3_47_8_, jpbairplan9_.engine_type as engine4_47_8_, jpbairplan9_.flight_hours as flight5_47_8_, jpbairplan9_.primary_airplane_in_package as primary6_47_8_, jpbairplan9_.operator as operator47_8_, jpbairplan9_.registry_number as registry8_47_8_, jpbairplan9_.sequence_in_package as sequence9_47_8_, jpbairplan9_.thrust as thrust47_8_, jpbairplan9_.line_number as line11_47_8_, jpbairplan9_.data_block as data12_47_8_, jpbairplan9_.mro as mro47_8_, jpbairplan9_.base_number as base14_47_8_, jpbairplan9_.variable_number as variable15_47_8_, jpbairplan9_.flight_cycles as flight16_47_8_, jpbairplan9_.delivery_date as delivery17_47_8_, jpbairplan9_.engine_model_description as engine18_47_8_, jpbairplan9_.mzfw as mzfw47_8_, jpbairplan9_.model as model47_8_, jpbairplan9_.mtogw as mtogw47_8_, jpbairplan9_.serial_number as serial22_47_8_, jpbairplan9_.foreign_registered as foreign23_47_8_, ptb5_.type_id as type1_9_, ptb5_.type as type59_9_, ptb5_.type_description as type3_59_9_, ptb5_.object_type as object4_59_9_, psb6_.status_id as status1_10_, psb6_.abbreviation as abbrevia2_56_10_, psb6_.object_type as object3_56_10_, psb6_.status as status56_10_, sirpbean7_.sirp_id as sirp1_11_, sirpbean7_.base_model as base2_72_11_, sirpbean7_.sirp as sirp72_11_, sirpbean7_.description as descript4_72_11_, basemodelb23_.base_model_id as base1_12_, basemodelb23_.base_model as base2_67_12_, basemodelb23_.standard_statement as standard3_67_12_, basemodelb23_.site as site67_12_, basemodelb23_.make as make67_12_, modelbean24_.model_id as model1_13_, modelbean24_.base_model as base2_60_13_, modelbean24_.model as model60_13_ from jobs this_ left outer join disposition_jobs this_1_ on this_.job_id=this_1_.disposition_job_id left outer join faa_8100_jobs this_2_ on this_.job_id=this_2_.faa_8100_job_id inner join priorities pb3_ on this_.priority=pb3_.job_priority_id inner join status sb2_ on this_.status=sb2_.status_id inner join types tb1_ on this_.type=tb1_.type_id inner join job_packages jpb4_ on this_.job_package=jpb4_.job_package_id left outer join atas atabean15_ on jpb4_.ata=atabean15_.ata_id left outer join models modelbean16_ on atabean15_.model=modelbean16_.model_id left outer join base_models basemodelb17_ on modelbean16_.base_model=basemodelb17_.base_model_id inner join castle_groups pcgb8_ on jpb4_.castle_group=pcgb8_.group_id inner join package_airplanes jpbairplan9_ on jpb4_.job_package_id=jpbairplan9_.job_package inner join types ptb5_ on jpb4_.type=ptb5_.type_id inner join status psb6_ on jpb4_.status=psb6_.status_id inner join sirps sirpbean7_ on jpb4_.sirp=sirpbean7_.sirp_id left outer join base_models basemodelb23_ on sirpbean7_.base_model=basemodelb23_.base_model_id left outer join models modelbean24_ on jpb4_.model=modelbean24_.model_id where this_.job_name like ? and jpbairplan9_.engine_effectivity like ? order by this_.job_name asc, this_.job_subject asc