Get one-to-one relationship objects with JOIN query with myBatis

Question

I try to use MyBatis to get from database object, which have another object as field. Like this:

public class Worker {
  private int id;
  private String firstName;
  private String lastName;

  private Bank bank;   
}

public class Bank  {
  private int id;
  private String name;
}

As can be seen, here should be one-to-one relationship. At the moment I found only two solutions:

1) Add @Result annotation to WorkerMapper with reference to the bank sql, like this:

public interface WorkerMapper {
  @Results({
    @Result(column = "id",          property = "id"),
    @Result(column = "first_name",  property = "firstName"),
    @Result(column = "last_name",   property = "lastName"),
    @Result(property="bank", javaType=Bank.class, column="id", many=@Many(select="getBank"))
  })
  @Select("worker select")
  Worker get(int id);

  @Select("bank select")
  Bank getBank(int id);
)

But, if I understand correctly, if I do this, me need execute two queries^ for bank and for workers. Which is not very good for the BD

2) I can write sql query with "inner join", create some "RowAdapter" class, which contains all query fields. After I get it from the database, I will parse on the my necessary objects. This solution is better than two queries, but it is necessary to write a lot of code.

Is there a more elegant solution to get two objects with one sql query with "inner join" in MyBatis (desirable use annotation)?

Answer 1

Note that using @Many involve that Worker class has Collection<Bank> property. In you case, you must use @One.

Just quoting API documentation:

@One: A mapping to a single property value of a complex type. Attributes: select, which is the fully qualified name of a mapped statement (i.e. mapper method) that can load an instance of the appropriate type,

The potential interest of multiple queries is lazyLoading to get the Bank date just in time.

fetchType, which supersedes the global configuration parameter lazyLoadingEnabled for this mapping.

It actually depends on your requirement.

• If this is limited to select worker by id then select bank by id, there is not much difference between 2 simple queries and a single JOIN query.

• If you select all workers (let say 1 million), then 1 million select-bank could be issued. A JOIN query could be preferable if lazy loading is not an option.

Anyway,

NOTE You will notice that join mapping is not supported via the Annotations API. This is due to the limitation in Java Annotations that does not allow for circular references.

Then defining result map in XML is required for JOIN query. With auto-mapping, following should be almost enough:

<resultMap id ="workerResultMap" type="Worker">
  <association property="bank" resultMap="bankResultMap"/>
</resultMap>
<resultMap id ="bankResultMap" type="Bank">
</resultMap>