hi
selecting on many to many relation is giving duplicate records of first table.
it is tough to ask this in simple one line, so i am giving example here.
i have 2 tables candidate and job with following many to many relation.
candidatecandidateSeq(PK)
firstName
lastName
JobjobSeq(PK)
jobTitle
candidateJobcandidateSeq(FK)
jobSeq(FK)
status
i have created many to many hibernate mapping for these tables as below.
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="beans">
<class name="Job" table="CSS_job">
<id name="id" column="jobSeq">
<generator class="native"/>
</id>
<property name="jobId"/>
<property name="jobTitle"/>
<property name="description"/>
<property name="department"/>
<property name="status"/>
<property name="createDate"/>
<property name="modifiedDate"/>
<set name="candidates" table="css_candidatejob">
<key column="jobSeq"/>
<many-to-many column="candidateSeq" class="Candidate"/>
</set>
</class>
</hibernate-mapping>
=============
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="beans">
<class name="Candidate" table="css_candidate">
<id name="id" column="candidateSeq">
<generator class="native"/>
</id>
<property name="firstName"/>
<property name="lastName"/>
<property name="emailId"/>
<property name="businessPhone"/>
<property name="homePhone"/>
<property name="cellPhone"/>
<property name="street"/>
<property name="apt"/>
<property name="city"/>
<property name="state"/>
<property name="zip"/>
<property name="country"/>
<property name="userId"/>
<property name="createDate"/>
<property name="modifiedDate"/>
<set name="jobs" table="css_candidatejob" inverse="true">
<key column="candidateSeq"/>
<many-to-many column="jobSeq" class="Job"/>
</set>
</class>
</hibernate-mapping>
Here is the code that fetches all jobs provided they are candidates who applied for it. that is inner join on these tables.
Code:
public List getCandidateUpdates() {
Session session = HibernateUtil.getSessionFactory().getCurrentSession();
session.beginTransaction();
List<Candidate> candidateUpdates = session.createQuery("from Candidate as c inner join fetch c.jobs as j").list();
session.getTransaction().commit();
for(Candidate c:candidateUpdates){
for(Job j:c.getJobs()){
System.out.println("Name:"+ c.getFirstName()+"Job Title " + j.getJobTitle());
}
}
return candidateUpdates;
}
here is the data in candidateJob table.
2 1 initialInterview new
2 2 initialInterview new
3 1 initialInterview new
4 3 initialInterview new
5 3 initialInterview new
5 8 initialInterview new
Output of iteration in the above java code is giving following duplicate records.
14:14:18,985 INFO [STDOUT] Name:RobertJob Title Software Engineer
14:14:18,985 INFO [STDOUT] Name:RobertJob Title Senior Software Engineer
14:14:18,985 INFO [STDOUT] Name:RobertJob Title Software Engineer
14:14:18,985 INFO [STDOUT] Name:RobertJob Title Senior Software Engineer
14:14:18,985 INFO [STDOUT] Name:VaniJob Title Software Engineer
14:14:18,985 INFO [STDOUT] Name:DavinderJob Title Staff Software Engineer
14:14:18,985 INFO [STDOUT] Name:SantoshJob Title Director
14:14:18,985 INFO [STDOUT] Name:SantoshJob Title Staff Software Engineer
14:14:18,985 INFO [STDOUT] Name:SantoshJob Title Director
14:14:18,985 INFO [STDOUT] Name:SantoshJob Title Staff Software Engineer
should n't it just give unique candidate objects from this table? ais there any setting for that in hibernate. I can iterate on jobs set and get all the jobs that each candidate has applied for, but these records are fetched as two rows instead of one. Please calrify my doubt here.