CODEWITHSUNDEEP
javamybatis
Mohit Verma
Mohit Verma

Reputation: 2089

Fetching nested object using mybatis annotations

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 ..

Upvotes: 3

Views: 8546

Answers (1)

Bogdan
Bogdan

Reputation: 24580

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).

Upvotes: 9

Related Questions