Geek Answers Handbook

MyBatis does not return all query results

Problem

I have a query that returns 17 records. When I use MyBatis with a map with <association> , it returns 6 records. Please note that this does not happen with my other cards, I have many other cards with associations that work fine.

Query:

  with leave_counts as ( select leave_type_id, count(llm.leave_id) as count from lw_leave_master llm group by leave_type_id ) select llt.leave_type_id, llt.leave_type_abbr_tx, llt.leave_type_desc_tx, lc.count as count_nm from lw_leave_type llt join leave_counts lc on lc.leave_type_id=llt.leave_type_id order by llt.leave_type_abbr_tx

Map

 <resultMap id="typeCountMap" type="mypackage.myclass"> <result property="count" column="count_nm"/> <association property="type" resultMap="package.myMap"/> </resultMap> <resultMap id="myMap" type="mypackage.myclass2"> <result property="id" column="leave_type_id"/> <result property="abbr" column="leave_type_abbr_tx"/> <result property="description" column="leave_type_desc_tx"/> </resultMap>

<association> in typeCountMap refers to the myMap map.

This returns 6 records each time. Capturing the actual start of the request from the registrar and manually starting it returns 17 records.

Solutions?

I can do two things so that MyBatis can return all 17 records

# 1

If I remove lc.count as count_nm from my query, I will get all 17 records (only without associated values)

  with leave_counts as ( select leave_type_id, count(llm.leave_id) as count from lw_leave_master llm group by leave_type_id ) select llt.leave_type_id, llt.leave_type_abbr_tx, llt.leave_type_desc_tx from lw_leave_type llt join leave_counts lc on lc.leave_type_id=llt.leave_type_id order by llt.leave_type_abbr_tx

This is obviously not a good solution, but I wanted to include it if it helps you understand what I'm doing wrong.

# 2

If I replace the link with the contents of another card, everything will work as expected.

 <resultMap id="typeCountMap" type="mypackage.myclass1"> <result property="count" column="count_nm"/> <result property="type.id" column="leave_type_id"/> <result property="type.abbr" column="leave_type_abbr_tx"/> <result property="type.description" column="leave_type_desc_tx"/> </resultMap>

This is obviously what I will do if I cannot find another solution, as it works. It would be nice to use <association> , as on other maps.

I should note that I am using MyBatis 3.1.1

+4
source share
4 answers

I do not know if this is a problem, but it is something to try. I have to post here because I do not have enough reputation to leave a comment.

Try adding a column attribute to your association and mark the identifier column on the second map:

 <resultMap id="typeCountMap" type="mypackage.myclass"> <result property="count" column="count_nm"/> <association property="type" column="leave_type_id" resultMap="package.myMap"/> </resultMap> <resultMap id="myMap" type="mypackage.myclass2"> <id property="id" column="leave_type_id"/> <result property="abbr" column="leave_type_abbr_tx"/> <result property="description" column="leave_type_desc_tx"/> </resultMap>
+1
source

If you are using 3.1.1, you can only define <id column = "leave_type_id" /> without a property attribute
If you are not using 3.1.1, you can add the <result property = "abbr" column = "leave_type_id" /> property at the top of the list to include the field in the calculation of the cache key to combine, later redefinition will assign the correct value

+3
source

I recently ran into this problem. I believe the problem was related to my main resultMap formula with an association in it that did not have an id column, while my resultMap association had an identifier column. My results were grouped by a column of association identifiers.

To fix the error, I selected the row number in my query and added an identifier to my main result table pointing to the row number column.

+3
source

You must replace the string <result property="id" column="leave_type_id"/>

with <id property="id" column="leave_type_id"/> .

In mybatis, you should not miss out on specifying an identifier column that can ruin your result set.

0
source

More articles:


All Articles