Fetching nested object using mybatis annotations
java, mybatis
Solution
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).
Problem
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 ..