MyBatis TooManyResultsException for seemingly correct mapping

Question

For the sake of resolving this problem I have pared down a lot of my code.

I continue to get this error as I try different things to get this collection working:

nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2

Relevant objects are as follows:

class Recipe {
    String name
    List<RecipeIngredient> ingredients
}
class RecipeIngredient {
    Double measurementAmount
}

I have an interface with my method call:

public interface CookbookDao {
    public Recipe getRecipe(@Param("id")int id)
}

And my result mapper:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="cookbook.daos.CookbookDao">
    <select id="getRecipe" resultMap="Recipe">
        SELECT
          r.name,
          ri.measurement_amount
        FROM
          recipe r
          INNER JOIN recipe_ingredient ri on ri.recipe_id = r.id
        WHERE
          r.id = #{id}
    </select>

    <resultMap id="Recipe" type="cookbook.domain.Recipe">
        <result property="name" column="r.name" />
        <collection property="ingredients" ofType="cookbook.domain.RecipeIngredient">
            <result property="measurementAmount" column="ri.measurement_amount"/>
        </collection>
    </resultMap>
</mapper>

The query returns the following results (Note: While the above code only has "measurement_amount" I've included what the actual, end-result set looks like to help show why I want/need to get these 2 rows back):

 name | measurement_amount | name | abbreviation | name  
------+--------------------+------+--------------+-------
 Rice |                  1 | cup  |              | rice
 Rice |                  2 | cups |              | water

I can get the mapper to work when I take the collection out. I have tried using javaType and I have tried using the composite key in the collection, but it still didn't work. I have run out ideas and have looked in a ton of help posts but nothing stood out.

I'm using Spring Boot with UTD versions of mybatis and mybatis-spring

Your object map looks like it is trying to insert multiple records into a single object. Either the class resultMap needs to change to an object with a collection or the query needs to return a single result. Is the table you have with multiple Rice what you expect as a result? — Tah, Mar 18 '16 at 20:08
@TahTatsumoto I updated the results in my original question to show why there are 2 rows returns. Imagine a Recipe in real life - there are multiple ingredients. So the relational table returns multiple rows from RecipeIngredient per each recipe queried. The Recipe object that I am mapping to _does_ have a List type, so it - assumedly - should map the collection there, as far as I've understood nested collections to work. — Missy Williams, Mar 18 '16 at 21:22
Not sure, but how should MyBatis recognize that both your rows are **one** recipe with two ingredients and not **two** recipes with one ingredient each? First thing I would try is to set the name as id instead of simply ``: `` to give it at least a chance to do so. — Florian Schaetz, Mar 19 '16 at 06:56
@FlorianSchaetz Hmm. My understanding is that MyBatis infers from the specified "type" object the particular structure of what you're returning, including the presence of a list or collection. Ultimately, this is a one-to-many relationship, which shows up as multiple rows when you query in MySQL. I assumed that the whole point of MyBatis is to make that mapping of a one-to-many relationship very simple, so it should ... just... work. - I have tried using an as well, with no luck. — Missy Williams, Mar 19 '16 at 15:58
Surprisingly enough, the whole thing actually works for me (which I didn't expect). I just had to remove the r. and ri. prefixes (from — Florian Schaetz, Mar 19 '16 at 16:45
So, good news everyone... Your code works. Just not for you. Erm. Sorry. Versions are Spring Boot 1.3.2, MyBatis 3.3.0, MyBatis 1.2.3 and mybatis-spring-boot-starter 1.0 — Florian Schaetz, Mar 19 '16 at 16:47
@FlorianSchaetz It works!! You solved it, despite not knowing it. The problem is that I was accessing the columns with their table aliases, and apparently MyBatis doesn't understand that. SO - I aliased each column with a unique name and it worked like a charm! Thanks for helping me along :D — Missy Williams, Mar 19 '16 at 17:37

score 4 · Accepted Answer · answered Mar 19 '16 at 17:44

It turns out that MyBatis doesn't understand my associations with aliased tables, so I changed my query to:

SELECT
  r.name as recipe_name,
  ri.measurement_amount as measurement_amount
FROM
  recipe r
  INNER JOIN recipe_ingredient ri on ri.recipe_id = r.id
WHERE
  r.id = #{id}

and updated the mapper to use the alias for column (recipe_name instead of ri.name, etc), as such:

<resultMap id="Recipe" type="cookbook.domain.Recipe">
    <result property="name" column="recipe_name" />
    <collection property="ingredients" ofType="cookbook.domain.RecipeIngredient">
        <result property="measurementAmount" column="measurement_amount"/>
    </collection>
</resultMap>

and it worked!

Thanks to those who commented to help

how would you do using annotations? – Coder17 May 24 '17 at 12:32 — Coder17, May 24 '17 at 12:32

score -2 · Answer 2 · answered Jun 11 '17 at 14:32

-2

you can try use List.

public interface CookbookDao {
    public List<Recipe> getRecipe(@Param("id")int id);
}

answered Jun 11 '17 at 14:32

Wayss

1
2

MyBatis TooManyResultsException for seemingly correct mapping

2 Answers2