Hello,

I have 2 tables PERSON and PROJECT :

PERSON
PK person_id
Person_name

PROJECT
PK project_id
Project_name

I have a relation PROJECT_PERSON :
PROJECT_PERSON
PK(project_id, person_id, role_id)

Table ROLE is not link to PROJECT_PERSON.
ROLE
PK role_id
role_name


My domain-classes.hbm.xml configuration file :
<class name="com.capgemini.deliveryreport.entities.Project" table="PROJECT">
<id name="projectId" column="PROJECT_ID" type="int" unsaved-value="null">
<generator class="sequence">
<param name="sequence">PROJECT_PROJECT_ID_seq</param>
</generator>
</id>
<set name="projectsHistory" table="PROJECT_HISTORY" inverse="true" lazy="true" >
<key column="PROJECT_ID" />
<one-to-many class="com.capgemini.deliveryreport.entities.ProjectHistory"/>
</set>
<set name="persons" table="PROJECT_PERSON" inverse="false" lazy="true" cascade="all">
<key>
<column name="PROJECT_ID" not-null="true"/>
</key>
<many-to-many class="com.capgemini.deliveryreport.entities.Person">
<column name="PERSON_ID" not-null="true"/>
</many-to-many>
</set>

<property name="domain" column="DOMAIN" type="string" />
<property name="customer_name" column="CUSTOMER_NAME" type="string" />
<property name="name" column="NAME" type="string" />
<property name="gfs" column="GFS" type="int" />
<property name="status" column="STATUS" type="short" />
<property name="trend" column="TREND" type="short" />
<property name="planning" column="PLANNING" type="short" />
<property name="finance" column="FINANCE" type="short" />
<property name="customer" column="CUSTOMER" type="short" />
<property name="human" column="HUMAN" type="short" />
<property name="international" column="INTERNATIONAL" type="short" />
<property name="comment" column="COMMENT" type="string" />

<property name="actions" column="ACTIONS" type="string" />
<property name="co_div" column="CO_DIV" type="string" />
<property name="suivi" column="SUIVI" type="string" />

<property name="person_id" column="PERSON_ID" type="int" />
<property name="last_edit" column="LAST_EDIT" type="calendar" />
<property name="week_num" column="WEEK_NUM" type="calendar" />
</class>

<class name="com.capgemini.deliveryreport.entities.Person" table="PERSON">
<id name="personId" column="PERSON_ID" type="int" unsaved-value="null">
<generator class="sequence">
<param name="sequence">PERSON_PERSON_ID_seq</param>
</generator>
</id>
<many-to-one name="profil" class="com.capgemini.deliveryreport.entities.Profil" >
<column name="PROFIL_ID" not-null="true"/>
</many-to-one>
<many-to-one name="role" class="com.capgemini.deliveryreport.entities.Role" >
<column name="ROLE_ID" not-null="true"/>
</many-to-one>

<set name="projects" table="PROJECT_PERSON" inverse="false" lazy="true" cascade="all">
<key>
<column name="PERSON_ID" not-null="true"/>
</key>
<many-to-many class="com.capgemini.deliveryreport.entities.Project">
<column name="PROJECT_ID" not-null="true"/>
</many-to-many>
</set>

<property name="personName" column="PERSON_NAME" type="string" />
<property name="firstName" column="FIRST_NAME" type="string" />
<property name="lastName" column="LAST_NAME" type="string" />
<property name="password" column="PASSWORD" type="string" />
</class>
<class name="com.capgemini.deliveryreport.entities.Role" table="ROLE">
<id name="roleId" column="ROLE_ID" type="int" unsaved-value="null">
<generator class="sequence">
<param name="sequence">ROLE_ROLE_ID_seq</param>
</generator>
</id>
<property name="roleName" column="ROLE_NAME" type="string" />
<set name="persons" table="PERSON" inverse="true" lazy="true" >
<key column="PERSON_ID" />
<one-to-many class="com.capgemini.deliveryreport.entities.Person"/>
</set>
</class>

To get all projects such as person_id = xx, I use criteria API hibernate :

public List <Project> getAllProject(int person_id, int role_id) {
Session session = sessionFactory.getCurrentSession();

Criteria criteria = session.createCriteria(Project.class);
criteria.addOrder(Order.desc("last_edit"));

//association many-to-many Set<Person> persons
Criteria criteriaP = criteria.createCriteria("persons");
criteriaP.add(Restrictions.eq("personId", person_id));

List<Project> listAllProject = criteria.list();
return listAllProject;
}
It is ok for me.

BUT I would like get list<PROJECT> such as (person_id = xx and role_id = yy) .

How get column <role_id> from table relation PROJECT_PERSON with criteria ?

Can you help me ?