这是我的工作示例:
<resultMap id="categoryPreferenceValueMap" type="SyncCategoryPreferenceValueModel">
<id property="preferenceTypeId" column="preference_type_id" />
<result property="title" column="title"/>
<result property="index" column="type_index"/>
<result property="updatedAt" column="category_updated_at" />
<collection property="preferences" column="p_preference_id" ofType="SyncPreferenceModel" >
<id property="preferenceId" column="p_preference_id" />
<result property="title" column="p_preference_title" />
<result property="index" column="preference_index" />
<result property="updatedAt" column="preference_updated_at" />
<collection property="preferenceValues" column="p_v_preference_value_id" ofType="SyncPreferenceValueModel" >
<id property="preferenceValueId" column="p_v_preference_value_id" />
<result property="preferenceValue" column="p_v_preference_value" />
<result property="updatedAt" column="preference_value_updated_at" />
</collection>
</collection>
</resultMap>
这是我的查询
<select id="getPrefersenceWithValues" resultMap="categoryPreferenceValueMap">
SELECT
PT.preference_type_id, PT.title, PT.type_index,PT.updated_at as category_updated_at,
P.preference_id as p_preference_id , P.title as p_preference_title ,P.index as preference_index,
P.updated_at as preference_updated_at,
PV.preference_value_id as p_v_preference_value_id ,PV.preference_value as p_v_preference_value
FROM preference_types PT
INNER JOIN preferences P ON PT.preference_type_id=P.preference_type_id
INNER JOIN preference_values PV ON P.preference_id=PV.preference_id
ORDER BY type_index
</select>
输出是:
[
{
"preferenceTypeId": "1",
"title": "abc BASICS",
"index": "1",
"updatedAt": 1,
"preferences": [
{
"preferenceId": "1",
"title": "xyz xyz",
"preferenceTypeId": null,
"gender": null,
"index": 1,
"updatedAt": 1,
"preferenceValues": [
{
"preferenceId": null,
"preferenceValueId": "2",
"preferenceValue": "30-60",
"gender": null,
"updatedAt": 0
},
{
"preferenceId": null,
"preferenceValueId": "1",
"preferenceValue": "0-30",
"gender": null,
"updatedAt": 0
}
]
}
]
}
]