Passing multiple parameters on @Many annotation

[JohnsonYu]

In My Mapper:

@SelectProvider(type = CustomerSqlProvider.class, method = "getCustomerById")
@results(value = {
@Result(property="parters",javaType=List.class,column="id",
many=@many(select="getParterByCustomerId"))
})
public Customer getCustomerById(@param("dbSuffix") String dbSuffix,@param("customerId") Long customerId);

---

In My Provider:
public String getCustomerById(Map<String, Object> parameter) {
final String dbSuffix = (String) parameter.get("dbSuffix");
String sql= new SQL() {
{ SELECT("id,userName,firstName,lastName,middleName,cellPhone,phone")
FROM(dbSuffix+TABLE_NAME);
WHERE("id=#{customerId}");
}
}.toString();
return sql;
}

public String getParterByCustomerId(Map<String, Object> parameter) {
    final String dbSuffix = (String) parameter.get("dbSuffix");
    String sql = new SQL() {
        {
SELECT("sex,firstName,lastName,birthDay,passPortNum,relation,phone");
            FROM(dbSuffix+".t_parter");
            WHERE("customerId=#{customerId}");
        }
    }.toString();
    return sql;
}

---

In My Test:

   @Test
public void testGet() throws Exception {
    Customer result = customerMapper.getCustomerById(dbSuffix,id);
    System.out.println(result);
}

---

The Trace:
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.builder.BuilderException: Error invoking SqlProvider method (com.kideasoft.data.sqlprovider.CustomerSqlProvider.getParterByCustomerId). Cause: java.lang.IllegalArgumentException: argument type mismatch
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371)
at com.sun.proxy.$Proxy35.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:163)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)
at com.sun.proxy.$Proxy57.getCustomerById(Unknown Source)
at com.kideasoft.service.CustomerService.getCustomerById(CustomerService.java:46)
at CustomerTest.testGet(CustomerTest.java:33)

[Condor70]

You can specify multiple columns in the "column" attribute (separated by commas) for a @many association.
However, these columns have to be results from the master query and can't be input parameters from the master query (unless you modify the query to return the parameter as a column).

But is there a specific reason you're not using configuration properties to specify the schema, e.g.
FROM("${mySchema}.t_parter");

[sankalpnith]

How to send the input parameters from master query as the parameter is not present in the table.Thats why I cannot return the parameter as a column and I have to use parameter and column both in the @many and @one association

[deeshank]

Passing multiple columns from the master query to the association query doesn't work.

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter 'CustomerId' not found. Available parameters are []


@SelectProvider(type = CustomerProvider.class, method = "fetchAllById")
@Results({
        @Result(property="accounts",
                column="customerId, accountId",
                javaType=Account.class,
                one = @One(select="com.deeshank.dao.AccountMapper.fetchAccounts",
                        fetchType = FetchType.EAGER))
})
List<Customer> fetchAllById(CustomerQueryRequest queryRequest);

customerId and accountId comes from the select columsn of the master query.
Any help would be much appreciated.

[harawata]

Multiple columns must be specified as follows.

column="{param1=column1,param2=column2, ...}"

Please see this test case as an example.
And please make sure to use 3.4.1 or later because there was a bug prevented this from working (#649).

[deeshank]

@harawata Thank you the quick response. I did try the approach you had mentioned like key-value pair but it doesn't work. By the way I am using

  <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>1.1.1</version>
    </dependency>

Anyways I got it fixed once i added the 3.4.1 org.mybatis to my pom.xml.

Thank you @harawata

[harawata]

mybatis-spring-boot 1.1.1 depends on MyBatis 3.4.0, so you may need to add mybatis 3.4.1 dependency explicitly (the feature is provided by MyBatis core, not by mybatis-spring-boot).

<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>3.4.1</version>
</dependency>

if it does not work, please try 1.1.2-SNAPSHOT of mybatis-spring-boot-starter which depends on 3.4.1 by default.
HTH

[devalurum]

How to send the input parameters from master query as the parameter is not present in the table.Thats why I cannot return the parameter as a column and I have to use parameter and column both in the @many and @one association

You can pass the parameters to the final selection in the select block as constants and then pass them to the desired method, which is used for the one/many association

My example:

@Mapper
public interface ResolutionStatMBRepository {

    @Select({"<script>",
            "SELECT",
            "   organization_id,",
            "   tenant_id,",
            "   territory_id,",
            // others columns
            "   #{start}::timestamp as start,", // <-- set param as constants
            "   #{end}::timestamp as end ", // <-- set param as constants
            "FROM resolution_stat ",
            "WHERE ",
            "   id IS NOT NULL",
            // other where conditions
            "   AND order_date BETWEEN #{start} AND #{end}",
            "</script>"})
    @Results({
            // other mapping
            @Result(property = "services", column = "{organizationId=organization_id, tenantId=tenant_id, territoryId=territory_id, start=start, end=end}", // specify the list of passed params
                    javaType = List.class, many = @Many(select = "getRegisteredServices"))

    })
    @Options(resultSetType = ResultSetType.DEFAULT)
    List<ResolutionStatResponseDto> findAllBetweenOrder(UUID organizationId,
                                                        String tenantId,
                                                        LocalDate start,
                                                        LocalDate end,
                                                        EnumSet<OrderTypeEnum> orderTypeEnums);

    @Select({"<script>",
            "SELECT",
            "   organization_id as organizationId,",
            "   territory_id as territoryId,",
            // other columns
            "   income ",
            "FROM registered_service ",
            "WHERE ",
            "   id IS NOT NULL",
            "   <if test='tenantId != null'>and tenant_id = #{tenantId}</if>",
            "   AND organization_id = #{organizationId} ",
            "   AND territory_id = #{territoryId} ",
            "   AND order_date BETWEEN #{start} AND #{end}",
            "</script>"})
    List<RegisteredServiceResponseDto> getRegisteredServices(UUID organizationId,
                                                             String tenantId,
                                                             UUID territoryId,
                                                             LocalDate start,
                                                             LocalDate end);
}