Hibernate version: 2.1.7

Mapping documents:

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping package="com.scientia.busdir.beans">
<class name="CodedRecordImpl" table="business_directory_coded">
<id name="Id" type="int" column="ID">
<generator class="assigned"/>
</id>
<property name="company_name" column="company_name" type="string" length="50"/>
<property name="address1" column="address1" type="string" length="150"/>
<property name="address2" column="address2" type="string" length="150"/>
<property name="address3" column="address3" type="string" length="150"/>
<property name="town" column="town" type="string" length="50"/>
<property name="county" column="county" type="string" length="50"/>
<property name="postcode" column="postcode" type="string" length="10"/>
<property name="web" column="web" type="string" length="150"/>
<property name="telephone" column="telephone" type="string" length="15"/>
<property name="postsect" column="postsect" type="string" length="10"/>
<property name="postdist" column="postdist" type="string" length="5"/>
<property name="postarea" column="postarea" type="string" length="2"/>
<property name="la_2002" column="la_2002" type="string" length="50"/>
<property name="CTPS" column="CTPS" type="char"/>
<property name="TPS" column="TPS" type="char"/>
<property name="FPS" column="FPS" type="char"/>
<property name="MPS" column="MPS" type="char"/>
<many-to-one name="primaryDescriptionDetails" class="PrimaryDescriptionImpl" column="sic_primary_code"/>
<many-to-one name="secondaryDescriptionDetails" class="SecondaryDescriptionImpl" column="sic_secondary_code"/>
<many-to-one name="tertiaryDescriptionDetails" class="TertiaryDescriptionImpl" column="sic_tertiary_code"/>
<many-to-one name="thomsonDescriptionDetails" class="ThomsonDescriptionImpl" column="thomson_code"/>
<many-to-one name="ward2004Details" class="Ward2004Impl" column="ward_2004"/>
</class>


<class name="PrimaryDescriptionImpl" table="1992_sic_primary_level1_description" lazy="true">
<id name="primary_code" type="char" column="primary_code">
<generator class="assigned"/>
</id>
<property name="primary_description" column="primary_description" type="string" length="150" />
</class>

<class name="SecondaryDescriptionImpl" table="1992_sic_secondary_description" lazy="true">
<id name="secondary_code" type="string" column="secondary_code">
<generator class="assigned"/>
</id>
<property name="secondary_description" column="secondary_description" type="string" length="150"/>
</class>

<class name="TertiaryDescriptionImpl" table="1992_sic_tertiary_description" lazy="true">
<id name="tertiary_code" type="string" column="tertiary_code">
<generator class="assigned"/>
</id>
<property name="tertiary_description" column="tertiary_description" type="string" length="150"/>
</class>

<class name="ThomsonDescriptionImpl" table="lu_thomson" lazy="true">
<id name="thomson_code" type="string" column="thomson_code">
<generator class="assigned"/>
</id>
<property name="thomson_description" column="thomson_description" type="string" length="150"/>
</class>

<class name="Ward2004Impl" table="lu_ward_2004" lazy="true">
<id name="ward_2004" type="string" column="ward_2004">
<generator class="assigned"/>
</id>
<property name="ward_2004_name" column="ward_2004_name" type="string" length="50"/>
<property name="la_2002" column="la_2002" type="string" length="50"/>
</class>

<query name="codedrecord.checkprimary">from PrimaryDescriptionImpl where primary_code = ?</query>
<query name="codedrecord.checksecondary">from SecondaryDescriptionImpl where secondary_code = ?</query>
<query name="codedrecord.checktertiary">from TertiaryDescriptionImpl where tertiary_code = ?</query>
<query name="codedrecord.checkthomson">from ThomsonDescriptionImpl where thomson_code = ?</query>
<query name="codedrecord.checkward">from Ward2004Impl where ward_2004 = ?</query>

<query name="codedrecord.search.companyname">from CodedRecordImpl where company_name like ?</query>
<query name="codedrecord.search.pcode">from CodedRecordImpl where sic_primary_code like ?</query>
<query name="codedrecord.search.ward">from CodedRecordImpl where ward_2004 like ?</query>
<query name="codedrecord.search.address">from CodedRecordImpl where address1 like ? || address2 like ? || address3 like ?</query>

<query name="codedrecord.search.addressandname">from CodedRecordImpl where company_name like ? &amp;&amp;(address1 like ? || address2 like ? || address3 like ?)</query>


<query name="primarydescription.getall">from PrimaryDescriptionImpl order by primary_description</query>

<query name="ward.getall">from Ward2004Impl order by ward_2004_name</query>

<query name="codedrecord.clear">delete from CodedRecordImpl</query>



</hibernate-mapping>


Name and version of the database you are using: Mysql

I am working on a 'Advanced Search' Page where a user can search records in a mysql database through hibernate.

This search allows them to search the records through one or more fields including company_name, address1, address2, address3,primary_description,secondary_description,tertiary_description, when multiple fields are search on they must be logically ANDed.

I cant think of a way of doing this in a clean manner. I could create a defined query for each permutation, but this could lead to 100s of defined queries.

I could write a piece of code that constructs the querys dynamically, given a set of perameters, but this also seems messy.

Is there a way to apply a sequence of statements to a find......i.e i get a collection that applys to my company_name search and then apply a sequence of filters to this collection for the subsequant field searches?

Or is there something i have missed?

Look at the Criteria api http://www.hibernate.org/hib_docs/refer ... a-criteria

perfect!....your a star

when calling this:

public ArrayList AdvancedSearch(String titleType,String titleSearch,String addressSearch,String primarySearch, String wardSearch) throws HibernateException {

if (titleType.equals("contains")){
titleSearch = "%"+titleSearch+"%";
}
else if (titleType.equals("startwith")){
titleSearch = "%"+titleSearch;
}

Session ses=super.getSession();


Criteria crit= ses.createCriteria(CodedRecord.class);


if(titleSearch!=null)crit.add( Expression.like("company_name",titleSearch ) );
if(addressSearch!=null)crit.add( Expression.like( "address1", "%"+addressSearch+"%" ) );
if(addressSearch!=null)crit.add( Expression.like( "address2", "%"+addressSearch+"%" ) );
if(addressSearch!=null)crit.add( Expression.like( "address3", "%"+addressSearch+"%" ) );
if(wardSearch!=null)crit.add( Expression.like( "ward_2004", wardSearch ) );
if(primarySearch!=null)crit.add( Expression.like( "sic_primary_code", primarySearch ) );

crit.addOrder( Order.asc("company_name"));
crit.setMaxResults(1000);

ArrayList al =(ArrayList) crit.list();



return al;
}



i get the error:

net.sf.hibernate.QueryException: could not resolve property: sic_primary_code of: com.scientia.busdir.beans.CodedRecordImpl


How do i use Criteria so that i can search the contained mapped objects aswell?