I have two tables: Project and Study. Their relationship is many-to-one. I want to write the following query:
select * from Project project
inner join Study study
on project.study_id = study.study_id and
project.name = projectname and
study.name = studyname;

The code I wrote for the query is:

Criteria crit = session.createCriteria(Project.class);

crit.add(Restrictions.eq("name", "B2")); // set restriction on project's name

crit.createCriteria(study).add(Restrictions.eq("name", "S2")); // perform the join and set restriction on study's name

However, the generated sql query looks like:

select
this_.project_id as project1_9_2_,
this_.name as name9_2_,
this_.study_id as study4_9_2_,
this_.description as descript3_9_2_,
this_.created_by_user_id as created5_9_2_,
study1_.study_id as study1_8_0_,
study1_.name as name8_0_,
study1_.description as descript3_8_0_,
study1_.created_by_user_id as created5_8_0_,
study1_.created_date as created4_8_0_,
studyproje4_.study_id as study4_4_,
studyproje4_.project_id as project1_4_,
studyproje4_.project_id as project1_9_1_,
studyproje4_.name as name9_1_,
studyproje4_.study_id as study4_9_1_,
studyproje4_.description as descript3_9_1_,
studyproje4_.created_by_user_id as created5_9_1_

from projects_v1 this_
inner join studies_v1 study1_
on this_.study_id=study1_.study_id
left outer join projects_v1 studyproje4_
on study1_.study_id=stud yproje4_.study_id
where this_.name like ? and study1_.name like ?

Anybody know why there is a "left outer join" and how to write criteria which works correctly?
Thank you !

I think you can change the above as needed according to your requirements.

I did the way you said. But it resulted the same query and the results were duplicate. I still don't understand why there is "left outer join".

(DEBUG SQL)
select
this_.project_id as project1_9_2_,
this_.name as name9_2_,
this_.description as descript3_9_2_,
this_.created_by_user_id as created4_9_2_,
this_.study_id as study5_9_2_,
mystudy1_.study_id as study1_8_0_,
mystudy1_.name as name8_0_,
mystudy1_.description as descript3_8_0_,
mystudy1_.created_by_user_id as created5_8_0_,
mystudy1_.created_date as created4_8_0_,
studyproje4_.study_id as study5_4_,
studyproje4_.project_id as project1_4_,
studyproje4_.project_id as project1_9_1_,
studyproje4_.name as name9_1_,
studyproje4_.description as descript3_9_1_,
studyproje4_.created_by_user_id as created4_9_1_,
studyproje4_.study_id as study5_9_1_
from projects_v1 this_
inner join studies_v1 mystudy1_
on this_.study_id=mystudy1_.study_id
left outer join projects_v1 studyproje4_
on mystudy1_.study_id=studyproje4_.study_id
where this_.study_id=mystudy1_.study_id and this_.name=? and mystudy1_.name=?

With the following code



I have the following SQL logged in console



and I have bi-directional relation defined on the mapping files. Dont know why you have a different SQL with the same code.

If you want distinct results, try setting
Criteria.setResultTransformer( Criteria.DISTINCT_ROOT_ENTITY );

The mapping between Study and Project is as following:
In Project.java

@ManyToOne( cascade={CascadeType.REFRESH})
@JoinColumn(name="study_id")
public Study getStudy () { return ( _study ); }
public void setStudy ( Study study ) { _study = study; }


In Study.java

@OneToMany(mappedBy="study", cascade=CascadeType.ALL, fetch=FetchType.EAGER)
public List<Project> getProjects() throws DataStoreException {
return _projects;
}
public void setProjects(List<Project> projects) {
this._projects = projects;
}
public void addProjects(Project proj) throws DataStoreException {
if (proj == null)
throw new IllegalArgumentException("Can't add a null Project.");
this.getProjects().add(proj);
}


Can you teach me how to map beans?

I havent used annotations yet, so atleast I will provide mapping files so that you can build on this example, if it helps.