java and mybatis - misunderstanding one-to-many relation - only annotations

Question

I have a two tables: Person and Dog. You know that person may have more than one dog.
My model is:

public class {
  int personId;
  List <Dog> dogs;
  String name;
  String surname;
}
public class Dog{
   String name;
   int dogId;
}

When it comes to database it is fairly similar:

PersonID(PK), Name (String), surname(String)   
dogId(PK), name(String), IdOwner(FK)   

Can you help me write select in mybatis ? I tried to read about @one and @many.

Answer 1

If you are using MyBatis you have two options described in the reference documentation:

• Nested Select: By executing another mapped SQL statement that returns the complex type desired.
• Nested Results: By using nested result mappings to deal with repeating subsets of joined results.

In your case, as you want to load the many association you must use the Nested Select, because you can't load a many association using a fetch outer join (just associations with one row fetched)

Nested Select

In this option you should need to add a reference to the select, which loads the daya by the foreign key relationship (in your case foreign key of person), of the relationship which in your case is dogs in the `ResultMap.

So you should have a query which loads the Person table:

<select id="findById" resultMap="personResult">
  SELECT * FROM PERSON WHERE NAME = #{name}
</select>

Its method:

Person findById(String name); 

Then a query which loads the dogs by person key relationship:

<select id="findDogsByPerson" resultType="Dog">
  SELECT * FROM DOG WHERE ID_PERSON = #{namePerson}
</select>

And its method:

List<Dog> findDogsByPerson(String namePerson);

Then you must add the select as association in the resultmap referencing to the select by foreign key (findDogsByPerson). In your case is a many association, so you should use the collection tag instead of association.

<resultMap id="personResult" type="Person">
  <!--Other properties maps -->
  <!-- ..... -->
  <collection property="dogs" column="id_person" javaType="Dog" select="selectDogByPerson"/>
  <!-- ..... -->
</resultMap>

Annotation alternative

If you want you can use annotations to do it. It's almost the same but the ResultMap and Select goes above the methods. It would using the anotation @Many referencing to the many relationship.

@Select("SELECT * FROM PERSON WHERE NAME = #{name}")
    @Results(value = {
          @Result(property="name", column="name"),
          @Result(property="surname", column="surname"),
          @Result(property="dogs", javaType=List.class, column="name",
                             many=@Many(select="findDogsByPerson"))})
Person findById(String name); 

@Select("SELECT * FROM DOG WHERE ID_PERSON = #{namePerson}")
@Results(value = {
          @Result(property="name", column="name"),
          @Result(property="dogId", column="dogId")})
List<Dog> findDogsByPerson(String namePerson);