In Object-Relational Mapping (ORM) development, a frequent challenge arises when the column names in the database schema do not match the field names in the Java entity classes. MyBatis provides flexible mechanisms to handle this mismatch. This guide explores two primary strategies: utilizing SQL aliases during query execution and defining explicit result mappings via configuration.

Database Schema Setup

First, establish a MySQL database environment. In this scenario, the table columns are prefixed differently compared to the Java properties. For instance, the primary key is uid instead of id, and the name column is uname.

CREATE DATABASE `account_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `user_account` (
  `uid` INT NOT NULL AUTO_INCREMENT,
  `uname` VARCHAR(50) NOT NULL,
  `uage` INT NOT NULL,
  `balance` DOUBLE NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB;

Java Entity Definition

The corresponding Java POJO uses standard naming conventions (camelCase), creating the discrepancy.

public class UserAccount {
    private Integer id;
    private String name;
    private int age;
    private double balance;

    public UserAccount() {}

    public UserAccount(String name, int age, double balance) {
        this.name = name;
        this.age = age;
        this.balance = balance;
    }

    // Getters and Setters
    public Integer getId() { return id; }
    public void setId(Integer id) { this.id = id; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public int getAge() { return age; }
    public void setAge(int age) { this.age = age; }
    public double getBalance() { return balance; }
    public void setBalance(double balance) { this.balance = balance; }

    @Override
    public String toString() {
        return "UserAccount{id=" + id + ", name='" + name + "', age=" + age + ", balance=" + balance + "}";
    }
}

Project Configuration

Ensure the necessary dependencies are included in your build file (e.g., Maven). Key libraries include MyBatis core, the MySQL connector, and logging utilities.

The core MyBatis configuration file (mybatis-config.xml) sets up the environment and registers mappers.

<configuration>
    <properties resource="database.config.properties"/>
    
    <typeAliases>
        <package name="com.example.model"/>
    </typeAliases>

    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${db.driver}"/>
                <property name="url" value="${db.url}"/>
                <property name="username" value="${db.user}"/>
                <property name="password" value="${db.pass}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/UserMapper.xml"/>
    </mappers>
</configuration>

Daatbase connection details are stored in database.config.properties:

db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/account_db?useSSL=false&serverTimezone=UTC
db.user=root
db.pass=secret_password

Mapper Interface and Implementation

Define a Mapper interface to handle data access. MyBatis will dynamically implement this interface.

public interface UserMapper {
    List<UserAccount> fetchAllAccounts();
    UserAccount findAccountById(int accountId);
}

There are two distinct approaches to configure the XML mapper file.

Approach 1: SQL Column Aliases

Modify the SQL query to alias the database columns so they match the Java property names exactly. This allows MyBatis to auto-map the results without extra configuration.

<mapper namespace="com.example.dao.UserMapper">
    <select id="fetchAllAccounts" resultType="UserAccount">
        SELECT uid AS id, uname AS name, uage AS age, balance 
        FROM user_account
    </select>
    
    <select id="findAccountById" resultType="UserAccount">
        SELECT uid AS id, uname AS name, uage AS age, balance 
        FROM user_account 
        WHERE uid = #{accountId}
    </select>
</mapper>

Approach 2: Explicit ResultMap

Define a resultMap to explicitly instruct MyBatis on how to map columns to properties. This is more maintainable for complex queries where changing SQL aliases might be cumbersome.

<mapper namespace="com.example.dao.UserMapper">
    <resultMap id="AccountResultMap" type="UserAccount">
        <id column="uid" property="id"/>
        <result column="uname" property="name"/>
        <result column="uage" property="age"/>
        <result column="balance" property="balance"/>
    </resultMap>

    <select id="fetchAllAccounts" resultMap="AccountResultMap">
        SELECT uid, uname, uage, balance FROM user_account
    </select>
    
    <select id="findAccountById" resultMap="AccountResultMap">
        SELECT uid, uname, uage, balance 
        FROM user_account 
        WHERE uid = #{accountId}
    </select>
</mapper>

Key Configuration Elements

When defining a resultMap, several attributes and sub-elements are critical:

• id: A unique identifier for the resultMap, referenced by select statements.
• type: The fully qualified class name or alias of the Java entity.
• id (element): Marks a column as the primary key, optimizing performance during caching and retrieval.
• result: Maps standard columns to JavaBean properties.
• association: Handles complex types or nested objects (one-to-one relationships).
• collection: Handles collections of objects (one-to-many relationships).

Note that if a column name matches the property name exactly, explicit mapping is not required for that specific field.

Execution and Testing

A utility class manages the SqlSessionFactory lifecycle.

public class SessionHelper {
    private static SqlSessionFactory factory;

    static {
        try {
            InputStream stream = Resources.getResourceAsStream("mybatis-config.xml");
            factory = new SqlSessionFactoryBuilder().build(stream);
        } catch (IOException e) {
            throw new RuntimeException("Failed to initialize SqlSessionFactory", e);
        }
    }

    public static SqlSession openSession() {
        return factory.openSession();
    }
}

The test class retrieves the Mapper proxy directly from the session and executes queries.

public class IntegrationTest {
    @Test
    public void testFetchAll() {
        try (SqlSession session = SessionHelper.openSession()) {
            UserMapper mapper = session.getMapper(UserMapper.class);
            List<UserAccount> accounts = mapper.fetchAllAccounts();
            accounts.forEach(System.out::println);
        }
    }

    @Test
    public void testFindById() {
        try (SqlSession session = SessionHelper.openSession()) {
            UserMapper mapper = session.getMapper(UserMapper.class);
            UserAccount account = mapper.findAccountById(1);
            System.out.println(account);
            session.commit();
        }
    }
}