CODEWITHSUNDEEP
javaselectmybatis
Anatrollik
Anatrollik

Reputation: 351

MyBatis: Select complex object using annotations

I stucked up with MyBatis select query. So, here are some details.

I have several java-classes (including constructors, getters, setters, etc):

class AttachmentHierarchy{
  int id;
  String title;
  Attachment originalAttach;
}

class Attachment{
  int id;
  String author;
}

Also, I have such MyBatis query

@Select("SELECT 
  a.id             id,
  a.title          title,
  hierarchy.id     hierarchy_id,
  hierarchy.author hierarchy_author
  FROM attachments a
  JOIN attachments hierarchy on hierarchy.id = a.parent_id
  WHERE a.id = #{attach_id}")
AttachmentHierarchy findAttachmentHierarchy(@Param(attach_id) int attachId);

Yes, I know, that current code looks strange and ugly, but I tried to simplify example. The main idea is that I want to Select complex object in one query using MyBatis annotations.

First, we can use @One annotation:

@Select("SELECT ...")
@Results(value = {
  @Result(property = "originalAttach", 
          column = "parent_id", 
          one = @One(select = "findOriginalAttach"))
  })
AttachmentHierarchy findAttachmentHierarchy(...); 

@Select("SELECT a.id     id, 
                a.author author 
         FROM   attachment a 
         WHERE  a.parent_id = #{parent_id}")
Attachment findOriginalAttach(@Param("parent_id") int parentId)

This solution looks good, but I don't want to invoke several queries to DB (in real life I want to fetch list of entries from several joined tables in one request).

Second, I know, that if Attachment class contains only one field, say author, I could do smth like:

@Results(value = {
  @Result(property = "originalAttach", 
          column = "hierarchy_author", 
          typeHandler = AttachmentTypeHandler.class)
})

class AttachmentTypeHandler implements TypeHandler<Attachment>{
  ...
   @Override
    public Attachment getResult(final ResultSet rs, final String columnName) {
        Attachment a = new Attachment();
        a.setAuthor(rs.getString(columnName));
        return a;
    }
  ...
}

Finally, I know that @Result annotation supports such syntax:

@Result(property = "originalAttach", 
        column = "{id=hierarchy_id, author=hierarchy_author}", 
        typeHandler = AttachmentTypeHandler.class)

But I have no idea how it could be used. It should somehow work with nested queries, creating "composites" object in mapping and passing null as column name to TypeHandler...

So, long story short, what's the best approach for fetching complex objects by single Select query using MyBatis and annotations? How should I use TypeHandler and @Result?

Upvotes: 3

Views: 3517

Answers (1)

Anatrollik
Anatrollik

Reputation: 351

Ok, I found working solution based on @ConstructorArgs.

So, for my example I have to add constructor:

 public AttachmentHierarchy(int id, String title, int originalId, String originalAuthor){
   this.id = id;
   this.title = title;
   this.originalAttach = new Attachment(originalId, originalAuthor);
 }

And for Mapper:

@Select("SELECT 
  a.id             id,
  a.title          title,
  hierarchy.id     hierarchy_id,
  hierarchy.author hierarchy_author
  FROM attachments a
  JOIN attachments hierarchy on hierarchy.id = a.parent_id
  WHERE a.id = #{attach_id}")
@ConstructorArgs(value = {
        @Arg(column = "id", javaType = int.class),
        @Arg(column = "title", javaType = String.class),
        @Arg(column = "hierarchy_id", javaType = int.class),
        @Arg(column = "hierarchy_author", javaType = String.class)
  })
AttachmentHierarchy findAttachmentHierarchy(@Param(attach_id) int attachId);

Please note, that you have to specify exact javaTypes in constructorArg.

This works for me, but I'm still looking for more simple and nice solution.

Upvotes: 3

Related Questions