MyBatis not mapping correct result to correct field

[dabacircle]

MyBatis version

3.5.7
Using mybatis-spring-boot-starter:2.2.0 on SDK16 with language level 11

Database vendor and version

AWS RDS MySql 8.0.23

Test case or example project

CreateDB.sql:

create table job
(
    jobId          int primary key,
    jobName        varchar(50)   not null,
    jobDescription varchar(1000) not null,
    jobGroup       varchar(50)   not null,
    creationDate   datetime      not null
);

insert into job (jobId, jobName, jobDescription, jobGroup, creationDate)
values (1, 'Web Dev', 'Do stuff', 'Software', '2021-08-30 23:42:25');

POJO: Job.java

public class Job {
    int jobId;
    String jobName;
    String jobDescription;
    String jobGroup;
    LocalDateTime creationDate;

    public Job(int jobId, String jobName, String jobDescription, String jobGroup, LocalDateTime creationDate) {
        this.jobId = jobId;
        this.jobName = jobName;
        this.jobDescription = jobDescription;
        this.jobGroup = jobGroup;
        this.creationDate = creationDate;
    }

@Override
    public boolean equals(Object o) {
        if (this == o) {
            return true;
        }
        if (o == null || getClass() != o.getClass()) {
            return false;
        }
        Job job = (Job) o;
        return Objects.equals(jobId, job.jobId) && Objects.equals(jobName, job.jobName) && Objects.equals(jobDescription, job.jobDescription) && Objects.equals(jobGroup, job.jobGroup) && Objects.equals(creationDate, job.creationDate);
    }

    @Override
    public int hashCode() {
        return Objects.hash(jobId, jobName, jobDescription, jobGroup, creationDate);
    }

  ...Setters and Getters and ToString

}

Mapper: JobMapper.java

package com.example.demo.mapper;


import com.example.demo.pojo.Job;
import org.apache.ibatis.annotations.*;

@Mapper
public interface JobMapper {

    @Select("SELECT jobId, jobName, jobDescription, jobGroup, creationDate FROM `web2021-dev`.job WHERE jobId = #{id}")
    Job getJobById1(@Param("id") int id);

    // Note that I only switched the order of jobId and jobName
    @Select("SELECT jobName, jobId, jobDescription, jobGroup, creationDate FROM `web2021-dev`.job WHERE jobId = #{id}")
    Job getJobById2(@Param("id") int id);
}

Config: application.yml

spring:
  datasource:
    url: (RDS access url)
    username: ---
    password: ---
    driver-class-name: com.mysql.cj.jdbc.Driver

UnitTest: JobMapperTest.java

@SpringBootTest
@ExtendWith(SpringExtension.class)
class JobMapperTest {

    @Autowired
    JobMapper jobMapper;

    @Test
    void getTest(){

        Job jobById1 = jobMapper.getJobById1(1);
        System.out.println(jobById1);

        Job jobById2 = jobMapper.getJobById2(1);
        System.out.println(jobById1);

        assertEquals(jobById1, jobById2);
    }
}

Steps to reproduce

Running the provided unit test

Expected result

According to the MyBatis documentation, when a ResultMap is not explicit, "In these cases MyBatis is automatically creating a ResultMap behind the scenes to auto-map the columns to the JavaBean properties based on name. "

Thus, getJobById1(1) and getJobById2(1) should return objects with the same fields. And this test would pass.

Actual result

By switching the order of jobName and jobId in the @Select statement of getJobById2, mybatis is trying to assign the value of jobName query to the jobId field which is an int. This results in a number format exception:

nested exception is org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column 'jobName' from result set.  Cause: java.lang.NumberFormatException: For input string: "Web Dev"

If we change the type of jobId field to a String, the test passes without any error.

[harawata]

Hello @samuelluohaoen1 ,

Currently, constructor auto-mapping relies on the column order.
There is an open PR #2196 that may solve your problem.
If you have some spare time, please try it and let us know what you think.
Thank you!

[dabacircle]

After reading through the related PR I have found the solution to my problem. By adding a constructor to my POJO, Java automatically gets rid of the no-arg constructor. It looks like when the no-arg constructor is non-existent MyBatis automatically switches to constructor auto-mapping. Thus, relying on the column order.

The solution is to manually add another no-arg constructor then MyBatis no longer uses constructor auto-mapping.

Thank you for your time.