Using composition-based Java records with JOIN results is difficult without XML (Dynamic SQL)

[chastro3]

I’m using MyBatis Dynamic SQL without XML, and my domain models are mainly Java record.
Because record does not support inheritance, composition is the only way to reuse existing structures, e.g.:

public record OrderView(
    String id,
    String orderNo,
    User user,
    Address address
) {}

However, with the current annotation-based mappings:
It’s hard to map JOIN query results into composed records.
Nested mappings usually rely on nested selects, which is not suitable for common JOIN queries.
Constructor-based mapping for composition is difficult to express with annotations.

Questions:

Is this a known limitation of annotation-based mappings?
Are there recommended patterns for JOIN + record + composition?
Thanks for your time and guidance.

[harawata]

Hello @chastro3 ,

If it works using XML, it should be possible using annotations (I think).
Here is an example.

If you have difficulty, share a repro or test case and we will look into it.
It would be better to include a working XML version just to avoid misunderstanding of any kind.

[chastro3]

Hello @harawata,

I’ve looked at the example you provided, and it seems that it relies on an explicitly defined resultMap.

In my case, I’ve built a generic BaseMapper on top of MyBatis Dynamic SQL, and the result mapping currently depends on MyBatis’s automatic mapping (i.e. no explicit @ConstructorArgs / @results / resultMap declarations). Because of that, no resultMap is actually declared.

@Mapper
public interface QuestionBankPracticeSessionMapper extends SingleKeyBaseMapper<QuestionBankPracticeSession, QuestionBankPracticeSessionTable, Long> {

    @Override
    default QuestionBankPracticeSessionTable table() {
        return QUESTION_BANK_PRACTICE_SESSION;
    }

    @Arg(javaType = QuestionBankPracticeSession.class, column = "session", columnPrefix = "s_")
    @Arg(column = "answered_count", javaType = Integer.class)
    @Arg(column = "correct_count", javaType = Integer.class)
    @SelectProvider(type = SqlProviderAdapter.class, method = "select")
    QuestionBankPracticeSessionPO selectQuestionBankPracticeSessionPO(SelectStatementProvider selectStatement);
}

With this setup, attempting to use this kind of nested mapping results in an error.

Are nested mappings (e.g. associations/collections) not supported when using automatic result mapping? In other words, do nested result mappings always require an explicitly declared resultMap?

Thanks for the clarification!

[harawata]

Adding @Arg creates a result map, so it is not auto-mapping, actually.

I don't see the entire setup, but there is a mapper for QuestionBankPracticeSession, isn't there?
If there is, there probably is a similar result map declared using @Arg already and you may be able to reuse it.
You have to assign ID to it (e.g. @Results(id = "questionBankPracticeSessionRM")) and then reference it from the QuestionBankPracticeSessionMapper using fully-qualified ID.

p.s.
In version ≤ 3.5.x, JOINed results + collections were not supported in constructor mapping.
You may have to wait for the upcoming 3.6.0 release.
See #101 #3108

[jeffgbutler]

There is a known issue where we cannot express collection and association mappings with the annotations. Clinton mentioned this in the documentation he wrote about the annotations years ago. ^[1]

The @One and @Many annotations require a sub-query which is not optimal.

It would certainly make sense for us to revisit this and see if we could make an improvement here. To my knowledge, this is the only thing that forces XML in MyBatis.

I have a Kotlin based example of nested collections using constructor mappings here: https://github.com/jeffgbutler/mybatis-kotlin-examples/tree/master/src/main/kotlin/example08 (this does require the upcoming 3.6.0 release to work). It requires XML unfortunately.

[1] - https://mybatis.org/mybatis-3/java-api.html#:~:text=Java%20annotations%20are%20unfortunately%20limited%20in%20their%20expressiveness%20and%20flexibility

[jeffgbutler]

@harawata - I needed to add a @Select to the dummy function. It works now!

[jeffgbutler]

I added an example of doing a Many. As expected, it fails because of a missing resultOrdered. Seems like that's the only thing missing.

@harawata thanks - I've learned something today!

[harawata]

Thank you for the follow-up, @jeffgbutler !
I'll add the resultOrdered to @Select and @SelectProvider later (this weekend most likely).

[jeffgbutler]

@harawata I'm working on it now - will submit a PR for your review soon. I'm thinking of adding it to @Options.

[harawata]

Ah, OK, thank you!

This could be a matter of preference, but I would avoid @Options in general.
As annotations do not allow null as the default value, it could change the configuration implicitly (e.g. #1334 ).
And to avoid it, we have to add extra choice and/or check logic in implementation (e.g. #1344 ).

I am not 100% sure about this, but it might be better to create one annotation for each option, or one annotation for each related options (e.g. options related to generated keys, options only used for SELECT, etc.).

[chastro3]

Hello @harawata @jeffgbutler,
Thank you very much for taking the time to look into this issue.

This is my current solution for handling complex and nested mappings.
In order to support nested constructor-based mappings, I currently have to introduce XML and explicitly define a BaseResultMap.

Both User and UserSecurity are ordinary Java entities, and the aggregate result is modeled as a record:

public record UserWithSecurity(

        User user,

        String roleIds,

        UserSecurity security
) {
}

To make this work, I need the following XML mappings:

<resultMap id="BaseResultMap" type="top.quizbutler.model.po.User">
    <constructor>
      <idArg column="id" javaType="java.lang.Long" />
      <arg column="user_no" javaType="java.lang.String" />
      <arg column="name" javaType="java.lang.String" />
      <arg column="password" javaType="java.lang.String" />
      <arg column="email" javaType="java.lang.String" />
      <arg column="phone" javaType="java.lang.String" />
      <arg column="id_card" javaType="java.lang.String" />
      <arg column="avatar" javaType="java.lang.String" />
      <arg column="status" javaType="java.lang.Short" />
      <arg column="banned_until" javaType="java.time.LocalDateTime" />
      <arg column="remark" javaType="java.lang.String" />
      <arg column="created_by" javaType="java.lang.Long" />
      <arg column="updated_by" javaType="java.lang.Long" />
      <arg column="created_at" javaType="java.time.LocalDateTime" />
      <arg column="updated_at" javaType="java.time.LocalDateTime" />
    </constructor>
  </resultMap>
  <resultMap id="WithSecurityResultMap" type="top.quizbutler.model.po.UserWithSecurity">
    <constructor>
      <arg resultMap="BaseResultMap" javaType="top.quizbutler.model.po.User" />
      <arg column="role_ids" javaType="java.lang.String" />
      <arg resultMap="top.quizbutler.mybatis.mapper.UserSecurityMapper.BaseResultMap"
           javaType="top.quizbutler.model.po.UserSecurity"
           columnPrefix="us_" />
    </constructor>
  </resultMap>

This configuration works correctly, but it requires explicitly defining and referencing a BaseResultMap even though both User and UserSecurity are simple Java entities.

For comparison, MyBatis already supports automatic mapping for simple Java beans without requiring any ResultMap:

@Mapper
public interface UserMapper extends SingleKeyBaseMapper<User, UserTable, Long> {

    @Override
    default UserTable table() {
        return USER;
    }

    /**
     * Even without explicitly specifying a ResultMap,
     * MyBatis is able to correctly map the result
     * for a simple Java bean.
     */
    @Override
    default Optional<User> selectByPrimaryKey(Long primaryKey) {
        return SingleKeyBaseMapper.super.selectByPrimaryKey(primaryKey);
    }

    @ResultMap("WithSecurityResultMap")
    @SelectProvider(type = SqlProviderAdapter.class, method = "select")
    UserWithSecurity selectUserWithSecurity(SelectStatementProvider selectStatement);
}

Given that MyBatis already supports automatic mapping for ordinary Java beans, it seems unnecessary that nested constructor arguments must always reference a BaseResultMap.

Ideally, the following simplified XML mapping could also be supported:

<resultMap id="WithSecurityResultMap" type="top.quizbutler.model.po.UserWithSecurity">
    <constructor>
      <arg javaType="top.quizbutler.model.po.User" />
      <arg column="role_ids" javaType="java.lang.String" />
      <arg javaType="top.quizbutler.model.po.UserSecurity" columnPrefix="us_" />
    </constructor>
  </resultMap>

And similarly for annotation-based mappings:

    @Arg(javaType = User.class)
    @Arg(column = "role_ids", javaType = String.class)
    @Arg(javaType = UserSecurity.class)
    @SelectProvider(type = SqlProviderAdapter.class, method = "select")
    UserWithSecurity selectUserWithSecurity(SelectStatementProvider selectStatement);

[jeffgbutler]

@chastro3 thanks for opening this discussion. It helped us find a missing feature in the upcoming release.

I'd like to summarize where we are...

1. With the upcoming 3.6 release it will be possible to build deeply nested object graphs that have collections or other associated objects in their constructors. This is a new feature that has long been requested by our community.
2. The initial implementation of this new feature was for XML only. As a result of this discussion, we will enable it for Annotations as well (see the PR here: Add resultOrdered Function for Annotated Selects (and SelectProviders) #3608)
3. These new features will allow you to created result mappings for deeply nested object graphs without any XML!
4. I understand that you were also hoping to enable this feature without having to code a result map at all - either in XML or with annotations. That it not a goal for us for the 3.6 release.