Problems with Other Solutions

In the previous doc, we discussed dynamic queries. In this section, we will explore the problems with dynamic table joins.

Currently, other technologies that operate on SQL, whether ORM or non-ORM, have a blank area: they only consider dynamic where, and do not consider dynamic join.

Dynamic JOIN is defined as: If some dynamic query conditions are for tables other than the current table. This means

When the condition is met: First join to other tables through association properties, then add dynamic where conditions to the joined table
When the condition is not met: Cannot join other tables through association properties

Scene-1

Let's first look at the first scene, taking MyBatis oriented to native SQL as an example

Define MyBatis Mapper interface
```
@Mapper
public interface BookMapper {

    List<Book> findBooks(
        @Nullable String name,
        @Nullable String storeName, 
        @Nullable String storeWebsite
    );
}
```
Here, all query parameters may be null, which is clearly a dynamic query.

The last two query parameters: storeName and storeWebsite, their filtering conditions are not applied on the current table BOOK, but on the parent table BOOK_STORE. That is, when any of these two parameters is non-null, a JOIN to the BOOK_STORE table will be generated. This table join dynamically determined by the parameter value is referred to as dynamic JOIN in this article.

Define MyBatis SQL mapping XML

<?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="somepackage.BookMapper">
    <select id="findBooks" resultType="somepackage.Book">
        select * from BOOK as book
        <if test="storeName != null or storeWebsite != null"> ❶
            inner join BOOK_STORE as store
                on book.STORE_ID = store.ID
        </if>
        <where>
            <if test="name != null">
                and book.NAME = #{name}
            </if>
            <if test="storeName != null"> ❷
                and store.NAME = #{storeName}
            </if>
            <if test="storeWebsite != null">
                and store.WEBSITE = #{storeWebsite} ❸
            </if>
        </where>
    </select>
</mapper>

Here, ❶ is the dynamic JOIN. However, for developers, ❷ and ❸ are the purpose, and ❶ is the work that has to be done to support ❷ and ❸. Its judgment condition is a burden.

You may have noticed that ❶ uses or. This is easy to understand.

However, this is only the simplest dynamic connection between two tables. For deeper multi-table join operations, the complexity of dynamic table joins will increase dramatically!

Scene-2

Let's take a look at the second scene. With the previous example as the basis, this example is decoupled from any business.

<?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="somepackage.AMapper">
  <select id="findAObjects" resultType="somepackage.A">
    select distinct A.id
    from A
    <if test="bId != null or cId != null or dId != null or eId != null">
      inner join B on A.ID = B.A_ID 
    </if>
    <if test="cId != null or dId != null or eId != null">
      inner join C on B.ID = C.B_ID
    </if>
    <if test="dId != null or eId != null">
      inner join D on C.ID = D.C_ID 
    </if>
    <if test="eId != null">
      inner join E on D.ID = E.D_ID
    </if>
    <where>
      <if test="aId != null">
        and A.ID = #{aId}  
      </if>
      <if test="bId != null">
        and B.ID = #{bId}
      </if>
      <if test="cId != null">
        and C.ID = #{cId}
      </if>
      <if test="dId != null">
        and D.ID = #{dId}
      </if>
      <if test="eId != null">
        and E.ID = #{eId}
      </if>
    </where>
  </select>
</mapper>

The logic of this example is simple. The five tables A, B, C, D and E form a JOIN chain, and each table has a dynamic query condition. However, as you can see, the complexity of dynamic JOIN has become unacceptable.

Scene-1​

Scene-2​

Scene-1

Scene-2