Introduction

In database persistence layers, it is common to encounter scenarios where a SQL query requires multiple input parameters that do not belong to a single specific entity class. When the parameters are disparate types or loose collections of data, specific binding strategies are required. Below are two standard approaches to handle multi-parameter queries in MyBatis.

1. Encapsulating Parameters in a Map

One effective strategy is to aggregate various parameters into a Map<String, Object>. This allows you to pass primitive types, strings, and even complex objects together. In the XML mapping, keys are used to reference these values.

DAO Interface Definition:

List<User> searchUsersUsingMap(Map<String, Object> context);

Test Implementation:

@Test
public void executeMapBasedSearch() {
    // Create a complex object
    UserProfile profile = new UserProfile("developer", 5000);
    
    // Aggregating parameters into a Map
    Map<String, Object> params = new HashMap<>();
    params.put("usernamePattern", "adm%");
    params.put("minAge", 21);
    params.put("userProfile", profile);

    List<User> results = userMapper.searchUsersUsingMap(params);
    results.forEach(System.out::println);
}

XML Mapping Configuration:

In the SQL statement, parameters are retrieved using the Map keys. For values that are primitive types, simply use #{key}. For values that are objects, use dot notation to access properties, such as #{key.propertyName}.

<select id="searchUsersUsingMap" resultType="com.example.entity.User">
    SELECT id, username, age, department_id
    FROM users
    WHERE username LIKE #{usernamePattern}
      AND age > #{minAge}
      AND salary > #{userProfile.salaryLimit}
</select>

2. Using Parameter Indexes

For queries with a small number of primitive parameters, you can pass them directly to the mapper method. MyBatis allows you to reference these parameters in the XML using their index positions (e.g., #{0}, #{1}), starting from 0.

DAO Interface Definition:

List<User> searchUsersByIndex(String name, int ageThreshold);

Test Implementation:

@Test
public void executeIndexBasedSearch() {
    // Passing two distinct parameters directly
    List<User> results = userMapper.searchUsersByIndex("john", 30);
    
    results.forEach(user -> System.out.println(user));
}

XML Mapping Configuration:

The parameters are accessed sequentially based on the method signature. #{0} corresponds to the first argument, and #{1} corresponds to the second.

<!-- Receiving multiple arguments via index -->
<select id="searchUsersByIndex" resultType="com.example.entity.User">
    SELECT id, username, age, department_id
    FROM users
    WHERE username LIKE #{0}
      AND age > #{1}
</select>

Best Practices and Comparison

When deciding between these two approaches, consider the complexity of the data:

• Index-based Parameters: Best suited for simple queries with a small number of arguments (typically two or three) where the parameters are simple types like Strings or Integers. It keeps the interface clean but reduces readability if the parameter list grows.
• Map-based Parameters: Ideal when dealing with a larger number of parameters or when the input includes nested objects requiring property access (e.g., user.profile.score). Maps offer better maintainability and flexibility, as the order of parameters does not matter, and adding new keys does not change the method signature.