Fetching nested object using mybatis annotations

Question

I have a POJO like this

class foo
{
private String id;
private String attribute;
private Map<String, String> dataMap;
}

And my data model is

Table Item
- INT id
- CHAR attribute

//storing dataMap as key-value pair

Table Data
- INT id
- CHAR key
- CHAR value

Now, I want to combines below 2 queries

1st query:

@Select("select * from Item where attribute=#{attribute}"
public List<Item> getItemList(@Param("attribute") String attribute);

another query to get all the key-value pairs for given id

How to have a single query, which given attribute, fetches list of id and populates nested object (dataMap)

//have gone through @Results, @Result ..

Answer 1

This sort of situation can be resolved in myBatis in two ways:

• nested selects - you retrieve a list of items and for each you run a separate select to get the data;
• nested results - you retrieve items with associated data by running a single join query and let myBatis deal with the repeating subsets within the results;

The first option is very bad for performance and causes what is called the "n+1 selects problem". The second one is the preferred way of doing this sort of thing.

MyBatis User guide contains an example using the nested results but it's for XML configuration. Java annotations have some limitations but theoretically it should be possible to do it (I have never tried id because I don't like having my selects in Java code but the following article provides some useful information: IBatis (MyBatis): Handling Joins: Advanced Result Mapping, Association, Collections, N+1 Select Problem).