I have a many to many relationship of doctors to specialties, where the join table includes a registration date for that specialty.
I am trying to use the Criteria API to return all doctors who have a specialty description matching a like clause.
I have tried various combinations of criteria, subcriteria and aliases, but cannot make it work. The generated SQL has a join missing, and thus the database throws an SQLGrammerException of "Unknown column in where clause".
I can do it in SQL and HQL.
The HQL would be:Code:
from Doctor d, SpecialtyRegistration sr, Specialty s where sr.doctor = d and sr.specialty = s and s.description like '%child%'"
An example of criteria API which I have tried is:Code:
DetachedCriteria dc = DetachedCriteria.forClass(Doctor.class);
// dc.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
dc.createCriteria("specialtyRegistrations")
.createAlias("specialty", "s")
.add(Restrictions.ilike("s.description", "child", MatchMode.ANYWHERE));
List list = this.getHibernateTemplate().findByCriteria(dc);
Generated SQL for this criteria query:Code:
select
*-- snip --*
from doctor this_
inner join doctor_specialty specialtyr1_ on this_.id=specialtyr1_.doctor_id
where lower(s2_.specialty) like ?
Where is table s2 ?Can anyone tell me what I'm doing wrong, or impart an understanding that I obviously don't have?
Many thanks.
Mapping:Code:
<class name="Doctor" table="doctor">
<id name="id" type="long" column="id">
<generator class="assigned" />
</id>
<set name="specialtyRegistrations" mutable="true" lazy="false" cascade="all-delete-orphan" inverse="true">
<key column="doctor_id" not-null="true"/>
<one-to-many class="SpecialtyRegistration"/>
</set>
</class>
<class name="SpecialtyRegistration" table="doctor_specialty">
<composite-id>
<key-many-to-one name="doctor" class="Doctor" column="doctor_id"/>
<key-many-to-one name="specialty" class="Specialty" column="specialty_id"/>
</composite-id>
<property name="registration" type="date" column="registration_date" not-null="true"/>
</class>
<class name="Specialty" table="specialty" lazy="false" mutable="false">
<id name="id" type="long" column="id">
<generator class="assigned" />
</id>
<property name="description" type="string" column="specialty" not-null="true"/>
</class>
Code:
mysql> desc doctor;
+-------------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
*-- snip --*
+-------------------------------+--------------+------+-----+---------+----------------+
22 rows in set (0.31 sec)
mysql> desc doctor_specialty;
+-------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------+------+-----+---------+-------+
| doctor_id | int(11) | NO | PRI | NULL | |
| specialty_id | int(11) | NO | PRI | NULL | |
| registration_date | date | YES | | NULL | |
+-------------------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> desc specialty;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| description | varchar(100) | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
Login
FAQ
Search
