Define the Data Model

Create a plain old Java object for employee records, ensuring it implements Serializable for potential caching or network transfer.

import java.io.Serializable;
import java.math.BigDecimal;
import java.time.LocalDate;

public class Employee implements Serializable {
    private Integer id;
    private String fullName;
    private String position;
    private Integer supervisorId;
    private LocalDate joinDate;
    private BigDecimal baseSalary;
    private BigDecimal bonus;
    private Integer departmentId;

    public Employee() {}

    public Employee(Integer id, String fullName, String position, Integer supervisorId, LocalDate joinDate, BigDecimal baseSalary, BigDecimal bonus, Integer departmentId) {
        this.id = id;
        this.fullName = fullName;
        this.position = position;
        this.supervisorId = supervisorId;
        this.joinDate = joinDate;
        this.baseSalary = baseSalary;
        this.bonus = bonus;
        this.departmentId = departmentId;
    }

    // Getters and Setters
    public Integer getId() { return id; }
    public void setId(Integer id) { this.id = id; }
    public String getFullName() { return fullName; }
    public void setFullName(String fullName) { this.fullName = fullName; }
    public String getPosition() { return position; }
    public void setPosition(String position) { this.position = position; }
    public Integer getSupervisorId() { return supervisorId; }
    public void setSupervisorId(Integer supervisorId) { this.supervisorId = supervisorId; }
    public LocalDate getJoinDate() { return joinDate; }
    public void setJoinDate(LocalDate joinDate) { this.joinDate = joinDate; }
    public BigDecimal getBaseSalary() { return baseSalary; }
    public void setBaseSalary(BigDecimal baseSalary) { this.baseSalary = baseSalary; }
    public BigDecimal getBonus() { return bonus; }
    public void setBonus(BigDecimal bonus) { this.bonus = bonus; }
    public Integer getDepartmentId() { return departmentId; }
    public void setDepartmentId(Integer departmentId) { this.departmentId = departmentId; }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", fullName='" + fullName + '\'' +
                ", position='" + position + '\'' +
                ", joinDate=" + joinDate +
                ", baseSalary=" + baseSalary +
                '}';
    }
}

Prepare the MyBatis Mapper XML

Define three select statements, mapping each to a unique identifier. Use resultType to specify the output data structure type. For collections, resultType points to the element type, not the container. Add type aliases in your configuration for brevity.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="company.hr.mapper.EmployeeMapper">
    <select id="fetchSingleById" resultType="employee">
        SELECT empno AS id, ename AS fullName, job AS position, mgr AS supervisorId, 
               hiredate AS joinDate, sal AS baseSalary, comm AS bonus, deptno AS departmentId
        FROM emp
        WHERE empno = 7369
    </select>

    <select id="fetchAllRecords" resultType="employee">
        SELECT empno AS id, ename AS fullName, job AS position, mgr AS supervisorId, 
               hiredate AS joinDate, sal AS baseSalary, comm AS bonus, deptno AS departmentId
        FROM emp
    </select>

    <select id="fetchMappedById" resultType="employee">
        SELECT empno AS id, ename AS fullName, job AS position, mgr AS supervisorId, 
               hiredate AS joinDate, sal AS baseSalary, comm AS bonus, deptno AS departmentId
        FROM emp
    </select>
</mapper>

Register the Mapper in MyBatis Configuration

Update the main MyBatis config to load the new EmployeeMapper XML file, ensuring the rseource path matches your project structure.

<configuration>
    <!-- ... existing type aliases, data source, environment config ... -->
    <mappers>
        <mapper resource="mapper/DepartmentMapper.xml"/>
        <mapper resource="mapper/EmployeeMapper.xml"/>
    </mappers>
</configuration>

Execute Tests for Each Method

Write JUnit 5 tests to initialize and clean up SqlSession, then call each of the three core query APIs.

package company.hr.test;

import company.hr.model.Employee;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.jupiter.api.*;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
public class SqlSessionQueryDemo {
    private static SqlSessionFactory sessionFactory;
    private SqlSession activeSession;

    @BeforeAll
    static void setupFactory() throws IOException {
        InputStream configStream = Resources.getResourceAsStream("mybatis-config.xml");
        sessionFactory = new SqlSessionFactoryBuilder().build(configStream);
    }

    @BeforeEach
    void openSession() {
        activeSession = sessionFactory.openSession();
    }

    @AfterEach
    void closeSession() {
        if (activeSession != null) {
            activeSession.close();
        }
    }

    @Test
    @Order(1)
    void testSelectSingleRecord() {
        Employee singleEmp = activeSession.selectOne("company.hr.mapper.EmployeeMapper.fetchSingleById");
        Assertions.assertNotNull(singleEmp);
        Assertions.assertEquals("SMITH", singleEmp.getFullName());
    }

    @Test
    @Order(2)
    void testSelectListCollection() {
        List<Employee> employeeList = activeSession.selectList("fetchAllRecords");
        Assertions.assertFalse(employeeList.isEmpty());
        employeeList.forEach(emp -> System.out.printf("Employee ID: %d, Name: %s%n", emp.getId(), emp.getFullName()));
    }

    @Test
    @Order(3)
    void testSelectMappedCollection() {
        Map<Integer, Employee> employeeIdMap = activeSession.selectMap("company.hr.mapper.EmployeeMapper.fetchMappedById", "id");
        Assertions.assertTrue(employeeIdMap.containsKey(7499));
        Employee allen = employeeIdMap.get(7499);
        Assertions.assertEquals(new BigDecimal("1600.00"), allen.getBaseSalary());
    }
}