2 Way 1-1 relationship

["Petr V." <gr...@yahoo.com>]

I want to have two way relationship like Teacher contains reference of Student and Student contains reference of Teacher. Is it even possible in iBatis ?

class Teacher {
� �� Student sid;
���� ....
}

class Student {

� �� Teacher teacher;

���� ....

}



I wrote the following SQL config file and as expected I got stack over flow due to recursion.(Map referring each other)

<resultMap id="TeacherResultMap99" class="Teacher" groupBy="id" >
��� <result property="id"�� column="tea_id"/>
��� <result property="name" column="tea_name" />
��� <result property="sid" resultMap="JOB.StudentResultMap99"/>
</resultMap>

<resultMap id="StudentResultMap99" class="Student" >
� <result property="id" column="stu_id"/>
� <result property="name" column="stu_Name"/>
� <result property="teacher" resultMap="JOB.TeacherResultMap99"/>��� 
</resultMap>


<select id="findStudent"� parameterClass="int" resultMap="StudentResultMap99">
��� select t.id as tea_id , t.name as tea_name , s.id as stu_id, s.name as stu_Name , s.tid as stu_tid from Teacher t left join student s on t.id = s.tid where s.id = #value#
</select>

Thanks,

Petr



      

Re: 2 Way 1-1 relationship

[Rick <ri...@gmail.com>]

On Wed, Jan 21, 2009 at 12:34 PM, Petr V. <gr...@yahoo.com> wrote:
>
> Thanks Ingmar Lötzsch for the reply.
>
> Actually I was simplifying the context from 1-M to 1-1 for discussion so left groupBy attribute mistakenly.
>
> The problem at hand is that Teacher has students (1:M). Student must have some teacher assigned to him but teacher does not necessarily need to have some student.
>

I think you can do what you want by just making another
TeacherResultMap that doesn't include the collection of students.
Actually make that one without the collection first, and then just
extend it as a second map that does have the collection. You'd
probably then have to alias certain column names twice though for
teacher, as I don't think ibatis will map the same column name to
multiple result maps (I could be wrong there though.) But all that
being said, i'd have to ask why you would ever need this relationship
in one query?

You mention you also want the student information to have 'teacher'
information, but from a code standpoint wherever you are using your
list of teachers, you'll have that info available so do you really
need that info populated in the student as well?

I understand a result map that extends student that has a property for
"Teacher' information, for cases where you are viewing information on
a single student - but that would be a separate call usually for when
you really need that info about a teacher in a student. For when
selecting a single student.

I'd be curious from a UI or business standpoint what are you doing
where you'd need each student fully populated with teacher information
when viewing/using a List that all the teachers at your disposal.

For example in a real app, you might be displaying a list of teachers
and the students beneath each teacher. You click on a student to get
more detail on the student. At that point you can get the teacher info
if you really need it. I think that would be cleaner anyway. (Or if
you wanted pass the teacher record which you have along with the
student record in the call - obviously in a web app you probably
wouldn't do this and would be passing an "studentID" - at that point
I'd just use that studentID to get me a fully hydrated student -
possibly with the teacher info in the student if needed as well.)

If you really wanted what you were asking for maybe something like...

<resultMap id="TeacherResultMap" class="Teacher" groupBy="id" >
    <result property="id" />
    <result property="name" />
    <result property="students" resultMap="JOB.StudentResultMap"/>
</resultMap>

<resultMap id="StudentResultMap" class="Student" >
  <result property="id" column="stu_id"/>
  <result property="name" column="stu_Name"/>
  <result property="teacher.id" column="studentTeacherID" />
  <result property="teacher.name" columns="studenTeacherName" />
</resultMap>

<select id="findTeachersAndStudents"  parameterClass="int"
resultMap="TeacherResultMap">
     select
            t.id,
            t.id as studentTeacherID,
            t.name ,
            t.name as studentTeacherName,
             s.id as stu_id, s.name as stu_Name , s.tid as stu_tid
            from Teacher t left join student s on t.id = s.tid where
t.id = #value#
 </select>

Re: 2 Way 1-1 relationship

[Ingmar Lötzsch <il...@asci-systemhaus.de>]

> Actually I was simplifying the context from 1-M to 1-1 for discussion so 
> left groupBy attribute mistakenly.
> 
> The problem at hand is that Teacher has students (1:M). Student must 
> have some teacher assigned to him but teacher does not necessarily need 
> to have some student.
> 
> So I go ahead and load some teacher and it will load all students 
> associated with it as Teacher would have List<Student>. But what I want 
> is that student also have a reference to Teacher. Following code would 
> explain what I want:
> 
> class Teacher {
>    List<Student> students;
>    ....
> }
> 
> class Student {
>    Teacher teacher;
>    ....
> }
> 
> SQL Map config file
> 
> <resultMap id="TeacherResultMap" class="Teacher" groupBy="id" >
>     <result property="id" />
>     <result property="name" />   
>     <result property="students" resultMap="JOB.StudentResultMap"/>
> </resultMap>
> 
> <resultMap id="StudentResultMap" class="Student" >
>   <result property="id" column="stu_id"/>
>   <result property="name" column="stu_Name"/> 
>   <result property="teacher" resultMap="JOB.TeacherResultMap"/>   
> </resultMap>
> 
> 
> <select id="findTeacher"  parameterClass="int" 
> resultMap="TeacherResultMap11">
>     select t.id  , t.name , s.id as stu_id, s.name as stu_Name , s.tid 
> as stu_tid from Teacher t left join student s on t.id = s.tid where t.id 
> = #value#
> </select>
> 
> The line in red would make cyclic reference to associate teacher with 
> student and stack overflow so I can't do that but effectively I want to 
> do that.
> 
> I understand your point where you tell me I could associate teacher for 
> each student in business logic but I am wondering if there is any way 
> that both sides of relationship could hold a reference of other side of 
> relationship implicitly.

OK. As far as I know iBATIS cannot be used to make the association 
navigable in both directions without additional own code.

You should remove the "teacher" property from the StudenResultMap and 
after getting the Teacher instance iterate over the list of Student 
instances to invoke the setTeacher() method. The use of a RowHandler is 
an alternative.

For further discussion I share my experience. Our team prefere use 
LinkedHashMap<Integer, Student> instead of List<Student>. The advantage 
of the map is that you can get the student by ID without iterating. The 
ID is very often returned from some GUI component. We use LinkedHashMap 
instead of HashMap to keep the order and to sort the elements of the map 
when indicated. In some cases we use TreeMap, TreeMap<Date, ...> for 
example. To avoid LEFT JOINs and reduce the amount of statements needed 
to load complex objects in several variants we are loading objects from 
each table in a separate step. In your case this would look like.

<sqlMap namespace="teacher">

   <resultMap id="result" class="Teacher">
     <result property="id" />
     <result property="name" />
   </resultMap>

   <select id="select" parameterClass="int" resultMap="result">
     SELECT
       id,
       name
     FROM teacher
     WHERE id = #value#
   </select>
</sqlMap>

<sqlMap namespace="student">

   <resultMap id="result" class="Student" >
     <result property="id" />
     <result property="name" />
   </resultMap>

   <select id="selectByTeacher" parameterClass="int" resultMap="result">
     SELECT
       id,
       name
     FROM student
     WHERE tid = #value#
   </select>

</sqlMap>

StudentDAOImpl
{
	public List<Student> selectByTeacher(Integer teacherId)
	{
		return List<Student> 
getSqlMapClientTemplate().queryForList("student.selectByTeacher", 
teacherId)	
	}
}

TeacherDAOImpl
{
	public Teacher select(Integer id)
	{
		return (Teacher) 
getSqlMapClientTemplate().queryForList("teacher.select", id)	
	}
}

Integer teacherId = ...
Teacher teacher = this.teacherDAO.select(teacherId);
List<Student> studentList = this.studentDAO.selectByTeacher(teacherId);
Map<Integer, Student> studentMap = teacher.getStudentMap();
for (Student student : studentList)
{
	student.setTeacher(teacher);
	studentMap.put(student.getId(), student);
}

This is simplified because allows to query for 1 teacher only.

Re: 2 Way 1-1 relationship

["Petr V." <gr...@yahoo.com>]

Thanks Ingmar Lötzsch for the reply.

Actually I was simplifying the context from 1-M to 1-1 for discussion so left groupBy attribute mistakenly.

The problem at hand is that Teacher has students (1:M). Student must have some teacher assigned to him but teacher does not necessarily need to have some student.

So I go ahead and load some teacher and it will load all students associated with it as Teacher would have List<Student>. But what I want is that student also have a reference to Teacher. Following code would explain what I want:

class Teacher {
   List<Student> students;
   ....
} 

class Student {
   Teacher teacher;
   ....
}

SQL Map config file 

<resultMap id="TeacherResultMap" class="Teacher" groupBy="id" >
    <result property="id" />
    <result property="name" />    
    <result property="students" resultMap="JOB.StudentResultMap"/>
</resultMap>

<resultMap id="StudentResultMap" class="Student" >
  <result property="id" column="stu_id"/>
  <result property="name" column="stu_Name"/>  
  <result property="teacher" resultMap="JOB.TeacherResultMap"/>    
</resultMap>


<select id="findTeacher"  parameterClass="int" resultMap="TeacherResultMap11">
    select t.id  , t.name , s.id as stu_id, s.name as stu_Name , s.tid as stu_tid from Teacher t left join student s on t.id = s.tid where t.id = #value#
</select>

The line in red would make cyclic reference to associate teacher with student and stack overflow so I can't do that but effectively I want to do that.

I understand your point where you tell me I could associate teacher for each student in business logic but I am wondering if there is any way that both sides of relationship could hold a reference of other side of relationship implicitly.

Thanks,

Petr



--- On Wed, 1/21/09, Ingmar Lötzsch <il...@asci-systemhaus.de> wrote:
From: Ingmar Lötzsch <il...@asci-systemhaus.de>
Subject: Re: 2 Way 1-1 relationship
To: user-java@ibatis.apache.org
Date: Wednesday, January 21, 2009, 4:38 PM

Petr V. schrieb:
> I want to have two way relationship like Teacher contains reference of
> Student and Student contains reference of Teacher. Is it even possible
> in iBatis ?
> 
> class Teacher {
>      Student sid;
>      ....
> }
> 
> class Student {
>      Teacher teacher;
>      ....
> }
> 
> 
> I wrote the following SQL config file and as expected I got stack over
> flow due to recursion.(Map referring each other)
> 
> <resultMap id="TeacherResultMap99" class="Teacher"
groupBy="id" >
>     <result property="id"   column="tea_id"/>
>     <result property="name" column="tea_name" />
>     <result property="sid"
resultMap="JOB.StudentResultMap99"/>
> </resultMap>
> 
> <resultMap id="StudentResultMap99" class="Student"
>
>   <result property="id" column="stu_id"/>
>   <result property="name" column="stu_Name"/>
>   <result property="teacher"
resultMap="JOB.TeacherResultMap99"/>   
> </resultMap>
> 
> 
> <select id="findStudent"  parameterClass="int"
> resultMap="StudentResultMap99">
>     select t.id as tea_id , t.name as tea_name , s.id as stu_id, s.name
> as stu_Name , s.tid as stu_tid from Teacher t left join student s on
> t.id = s.tid where s.id = #value#
> </select>

SELECT
	t.id AS tea_id,
	t.name AS tea_name,
	s.id AS stu_id,
	s.name AS stu_name,
	s.tid AS stu_tid
FROM teacher t
LEFT JOIN student s ON t.id = s.tid
WHERE s.id = #value#

I don't understand, why you use a LEFT JOIN to table student. You
provide the ID of the student as a parameter. If there exists a
corresponding row, you get a result, if not, you don't. You can use a
RIGHT JOIN instead, if there exist students without teacher.

If you have a real 1 to 1 relationship, omit the groupBy attribut and
the result element for property "sid". Otherwise use
List<Student> in
class Teacher.

StudentDAOImpl
{
	public Student findStudent(Integer id)
	{
		return (Student)
getSqlMapClientTemplate().queryForObject("<NAMESPACE>.findStudent",
id)	
	}
}

Somewhere in your business code you can write something like that:

Integer id = ...
Student student = this.studentDAO.findStudent(id);
if (student != null)
{
	Teacher teacher = student.getTeacher();
	if (teacher != null)
	{
		teacher.setStudent(student);
	}
}



      

Re: 2 Way 1-1 relationship

[Ingmar Lötzsch <il...@asci-systemhaus.de>]

Petr V. schrieb:
> I want to have two way relationship like Teacher contains reference of
> Student and Student contains reference of Teacher. Is it even possible
> in iBatis ?
> 
> class Teacher {
>      Student sid;
>      ....
> }
> 
> class Student {
>      Teacher teacher;
>      ....
> }
> 
> 
> I wrote the following SQL config file and as expected I got stack over
> flow due to recursion.(Map referring each other)
> 
> <resultMap id="TeacherResultMap99" class="Teacher" groupBy="id" >
>     <result property="id"   column="tea_id"/>
>     <result property="name" column="tea_name" />
>     <result property="sid" resultMap="JOB.StudentResultMap99"/>
> </resultMap>
> 
> <resultMap id="StudentResultMap99" class="Student" >
>   <result property="id" column="stu_id"/>
>   <result property="name" column="stu_Name"/>
>   <result property="teacher" resultMap="JOB.TeacherResultMap99"/>   
> </resultMap>
> 
> 
> <select id="findStudent"  parameterClass="int"
> resultMap="StudentResultMap99">
>     select t.id as tea_id , t.name as tea_name , s.id as stu_id, s.name
> as stu_Name , s.tid as stu_tid from Teacher t left join student s on
> t.id = s.tid where s.id = #value#
> </select>

SELECT
	t.id AS tea_id,
	t.name AS tea_name,
	s.id AS stu_id,
	s.name AS stu_name,
	s.tid AS stu_tid
FROM teacher t
LEFT JOIN student s ON t.id = s.tid
WHERE s.id = #value#

I don't understand, why you use a LEFT JOIN to table student. You
provide the ID of the student as a parameter. If there exists a
corresponding row, you get a result, if not, you don't. You can use a
RIGHT JOIN instead, if there exist students without teacher.

If you have a real 1 to 1 relationship, omit the groupBy attribut and
the result element for property "sid". Otherwise use List<Student> in
class Teacher.

StudentDAOImpl
{
	public Student findStudent(Integer id)
	{
		return (Student)
getSqlMapClientTemplate().queryForObject("<NAMESPACE>.findStudent", id)	
	}
}

Somewhere in your business code you can write something like that:

Integer id = ...
Student student = this.studentDAO.findStudent(id);
if (student != null)
{
	Teacher teacher = student.getTeacher();
	if (teacher != null)
	{
		teacher.setStudent(student);
	}
}