多表级联mybatis实现

JavaBean

xml resultMap配置

select写法

<select id="queryDishById" resultMap="CompanyAdmin_ShopMap">

    select admin_id from aicanteen.company_admin where admin_id = #{id}

</select>

<select id="findShop" resultMap="Shop_CategoryMap">

    select shop_id,shop_compaynAdmin_key

    from aicanteen.shop s left join aicanteen.company_admin ca

    on s.shop_compaynAdmin_key = ca.admin_id

</select>

<select id="findCategory" resultMap="Category_DishMap" >

    select category_id,category_shop_key,category_name

    from aicanteen.category c left join aicanteen.shop s

    on c.category_shop_key = s.shop_id

</select>

<select id="findDish" resultMap="DishMap" >

    select

    d.dish_id, d.dish_shop_key, d.dish_category_key, d.dish_name, d.dish_code,     d.price, d.discount, d.is_sell_hot, d.sell_num,d.is_deleted, d.detail

    from aicanteen.dish d left join aicanteen.category c on d.dish_category_key =     c.category_id

</select>

通过公司管理员查询下属商家菜单，四张表级联

写法二

<resultMap type="com.sxsh.aicanteen.entity.Training" id="TrainingMap">

    <result property="trainingId" column="training_id" jdbcType="INTEGER"/>

    <result property="trainingName" column="training_name" jdbcType="VARCHAR"/>

    <result property="trainingShopKey" column="training_shop_key" jdbcType="INTEGER"/>

    <result property="trainingCompanyKey" column="training_company_key" jdbcType="INTEGER"/>

    <result property="trainingManagerKey" column="training_manager_key" jdbcType="INTEGER"/>

    <result property="startTime" column="start_time" jdbcType="TIMESTAMP"/>

    <result property="endTime" column="end_time" jdbcType="TIMESTAMP"/>

    <result property="status" column="status" jdbcType="VARCHAR"/>

    <collection property="traningDishList" ofType="com.sxsh.aicanteen.entity.TraningDishlist" resultMap="TraningDishlistMap" javaType="ArrayList">

</resultMap>

<resultMap id="TraningDishlistMap" type="com.sxsh.aicanteen.entity.TraningDishlist">

    <id column="dishlist_id" property="dishlistId"/>

    <result property="dishlistTrainidKey" column="dishlist_trainID_key" jdbcType="INTEGER"/>

    <result property="dishlistDishidKey" column="dishlist_dishID_key" jdbcType="INTEGER"/>

    <result property="dishlistShopKey" column="dishlist_shop_key" jdbcType="INTEGER"/>

    <result property="imagelistInfo" column="imagelist_info" jdbcType="VARCHAR"/>

    <collection property="dishList" ofType="com.sxsh.aicanteen.entity.Dish" resultMap="DishMap" javaType="ArrayList">

</resultMap>

<resultMap id="DishMap" type="com.sxsh.aicanteen.entity.Dish">

    <id property="dishId" column="dish_id" jdbcType="INTEGER"/>

    <result property="dishShopKey" column="dish_shop_key" jdbcType="INTEGER"/>

    <result property="dishCategoryKey" column="dish_category_key" jdbcType="INTEGER"/>

    <result property="dishName" column="dish_name" jdbcType="VARCHAR"/>

    <result property="dishCode" column="dish_code" jdbcType="VARCHAR"/>

    <result property="price" column="price" jdbcType="NUMERIC"/>

    <result property="discount" column="discount" jdbcType="NUMERIC"/>

    <result property="isSellHot" column="is_sell_hot" jdbcType="VARCHAR"/>

    <result property="sellNum" column="sell_num" jdbcType="INTEGER"/>

    <result property="isDeleted" column="is_deleted" jdbcType="VARCHAR"/>

    <result property="detail" column="detail" jdbcType="VARCHAR"/>

</resultMap>

<sql id="queryTraining">

    t.training_id, t.training_name, t.training_shop_key, t.training_company_key, t.training_manager_key, t.start_time, t.end_time,t.status,

<sql id="queryTraningDishlist">

    td.dishlist_id, td.dishlist_trainID_key, td.dishlist_dishID_key, td.dishlist_shop_key, td.imagelist_info,

<sql id="queryDish">

    d.dish_id, d.dish_shop_key, d.dish_category_key, d.dish_name, d.dish_code, d.price, d.discount, d.is_sell_hot, d.sell_num,d.is_deleted, d.detail

</sql>

<select id="queryAll" resultMap="TrainingMap" parameterType="com.sxsh.aicanteen.entity.Training">

    select

<include refid="queryTraining">

    <include refid="queryTraningDishlist">

    <include refid="queryDish">

    from aicanteen.training t left join aicanteen.traning_dishlist td on t.training_id = td.dishlist_trainID_key

    left join aicanteen.dish d on d.dish_id = td.dishlist_dishID_key

    <where> 1 = 1

<if test="trainingId != null">

        and training_id = #{trainingId}

    <if test="trainingShopKey != null">

        and training_shop_key = #{trainingShopKey}

    <if test="trainingCompanyKey != null">

        and training_company_key = #{trainingCompanyKey}

    <if test="trainingManagerKey != null">

        and training_manager_key = #{trainingManagerKey}

</select>

<select id="queryTrainByCondition" resultMap="TrainingMap" parameterType="com.sxsh.aicanteen.entity.Training">

    select

<include refid="queryTraining">

    <include refid="queryTraningDishlist">

    <include refid="queryDish">

    from aicanteen.training t left join aicanteen.traning_dishlist td on t.training_id = td.dishlist_trainID_key

    left join aicanteen.dish d on d.dish_id = td.dishlist_dishID_key

    <where> 1 = 1

<if test="trainingId != null">

            and training_id = #{trainingId}

        <if test="trainingShopKey != null">

            and training_shop_key = #{trainingShopKey}

        <if test="trainingCompanyKey != null">

            and training_company_key = #{trainingCompanyKey}

        <if test="trainingManagerKey != null">

            and training_manager_key = #{trainingManagerKey}

        <if test="trainingName != null and trainingName != ''">

            and training_name like CONCAT('%',#{trainingName},'%')

        <if test="bottomTime != null and topTime == null">

            and start_time&gt;= #{bottomTime}

        <if test="bottomTime == null and topTime != null">

            and start_time&lt;= #{topTime}

        <if test="bottomTime != null and topTime != null">

            and start_time between #{bottomTime} and #{topTime}

</select>