MyBatis multiple table join

multiple table join

JOIN can divide into three types by function：

• INNER JOIN：get the commmon relational records of tables.
• LEFT JOIN：get the records of left table in spite of right table has not have records.
• RIGHT JOIN：get the records of right table in spite of left table has not have records.

In the process of development，in order to get * field of dish database and get name field of category database ,we should choose left join.

Q1：We can face the circumstance that the name of database is invalid when we use short-name of database.

• Before invalid：

<select id="getPage" resultType="com.sky.vo.DishVO">
        select d.* , c.name as categoryName from dish d left outer join category c on d.category_id = c.id
        <where>
            <if test="name != null">
                and name like concat('%',#{name},'%')
            </if>
            <if test="categoryId !=null">
                and category_id = #{categoryId}
            </if>
            <if test="status !=null">
                and status = #{status}
            </if>
        </where>
        order by create_time desc
    </select>

• After amendment：

<select id="getPage" resultType="com.sky.vo.DishVO">
        select d.* , c.name as categoryName from dish d left outer join category c on d.category_id = c.id
        <where>
            <if test="name != null">
                and d.name like concat('%',#{name},'%')
            </if>
            <if test="categoryId !=null">
                and d.category_id = #{categoryId}
            </if>
            <if test="status !=null">
                and d.status = #{status}
            </if>
        </where>
        order by create_time desc
    </select>

Q2：we can use resultMap to present the mapping in the multiple table join of one to many，and use collection tag to present "many" role.

<!-- MyBatis XML resultMap definition of mapping files-->
    <resultMap id="dishMap" type="com.sky.vo.DishVO">
        <id property="id" column="id" />
        <result property="name" column="name" />
        <result property="categoryId" column="category_id" />
        <result property="price" column="price" />
        <result property="image" column="image" />
        <result property="description" column="description" />
        <result property="status" column="status" />
        <result property="updateTime" column="update_time" />
        <result property="categoryName" column="category_name" />
        <!-- nested queries that map one-to-many relationships to attributes -->
        <collection property="flavors" ofType="DishFlavor">
            <id property="id" column="fid" />
            <result property="name" column="fname" />
            <result property="dishId" column="dish_id"></result>
            <result property="value" column="value"></result>
        </collection>
    </resultMap>

    <select id="getById" resultMap="dishMap">
        SELECT
            d.*,c.name category_name,f.id fid ,f.dish_id,f.name fname,f.value
        FROM
            dish d
                LEFT JOIN
            category c ON d.category_id = c.id
                LEFT JOIN
            dish_flavor f ON d.id = f.dish_id
        where d.id=#{id}
    </select>

Q3：when we faced field name confilct in the 👆 xml file,we can rename some fields.
Q4: what we should do when we want to get insert data immediately after insert record.

public void saveWithFlavor(DishDTO dishDTO) {
        Dish dish=new Dish();
        BeanUtils.copyProperties(dishDTO ,dish);
        dish.setCreateUser(BaseContext.getCurrentId());
        dish.setUpdateUser(BaseContext.getCurrentId());
        dish.setCreateTime(LocalDateTime.now());
        dish.setUpdateTime(LocalDateTime.now());
        dishMapper.save(dish);
        Long dishId=dish.getId();//such as we want to get id after save a dish.
        if(dishDTO.getFlavors().size()!=0&&dishDTO.getFlavors()!=null){
            List<DishFlavor> flavors=dishDTO.getFlavors();
            for (DishFlavor flavor : flavors) {
                flavor.setDishId(dishId);
            }
            dishFlavorMapper.save(flavors);
        }
    }

we can deal with this problem in this way:

 <insert id="save" parameterType="com.sky.entity.Dish" useGeneratedKeys="true" keyProperty="id">
        insert into dish(name,category_id,price,image,description,status,create_time, update_time, create_user, update_user)
        values(#{name},#{categoryId},#{price},#{image},#{description},#{status},#{createTime},#{updateTime},#{createUser},#{updateUser})
    </insert>

useGeneratedKeys="true"：
when we set useGeneratedKeys as true，this told MyBatis to open generate key function.The datavbase can generate an unique key for every insert record and set it to keyProperty.
keyProperty="id"：
this attributes specify the properties of the parameter object，MyBatis should set "GeneratedKeys" into this properties.In this case，id is a property of Dish.MyBatis can set this "GeneratedKeys"'s value into id field of Dish.