此示例只是我的数据库的架构。我有以下表格称为食谱:
id | author_name | recipe1_id | recipe2_id | recipe3_id
所有配方都与recipes_ingredients表的关系:
id | recipe_id | ingredient1 | ingredient2 | ingredient3
现在我在recipe1_id上做了三个联接,recipe3_id像这样:
SELECT recipe1.ingredient1 as recipe1ingredient1, recipe1.ingredient2 as recipe1ingredient2, recipe1.ingredient3 as recipe1ingredient3, [...]
FROM recipes
INNER JOIN recipe recipe1 ON (recipes.recipe1_id = recipes.id)
INNER JOIN recipe recipe2 ON (recipes.recipe2_id = recipes.id)
INNER JOIN recipe recipe3 ON (recipes.recipe3_id = recipes.id)
为了在 MyBatis 中将 3 个食谱映射到一个 bean 中,我有三个 resultMaps,将每个食谱映射到包含 3 个成分的配方对象(recipe1、recipe2、recipe3)。
但是:我想把食谱放在一个列表中。我怎样才能做到这一点?
PS:可以肯定的是,我在餐桌食谱和餐桌recipes_ingredients中只有 3 个实体。这永远不会改变,所以我看不出有理由把它放在 n:m 表中。
我认为这会起作用。
<resultMap id="recipe1" type="recipe1">
...
</resultMap>
<resultMap id="recipe2" type="recipe2">
...
</resultMap>
<resultMap id="recipe3" type="recipe3">
...
</resultMap>
<select id="getRecipes" parameterType="list" resultMap="recipe1, recipe2, recipe3">
SELECT recipe1.ingredient1 as recipe1ingredient1, recipe1.ingredient2 as recipe1ingredient2, recipe1.ingredient3 as recipe1ingredient3, [...]
FROM recipes
INNER JOIN recipe recipe1 ON (recipes.recipe1_id = recipes.id)
INNER JOIN recipe recipe2 ON (recipes.recipe2_id = recipes.id)
INNER JOIN recipe recipe3 ON (recipes.recipe3_id = recipes.id)
</select>
来源:MyBatis 多个结果集