10 MyBatis Interview Questions and Answers

MyBatis is a powerful persistence framework that simplifies the implementation of SQL databases in Java applications. It provides a flexible and efficient way to manage database interactions, bridging the gap between object-oriented programming and relational databases. MyBatis is particularly valued for its ability to handle complex SQL queries and its support for dynamic SQL, making it a preferred choice for developers who need fine-grained control over their database operations.

This article offers a curated selection of MyBatis interview questions designed to test and enhance your understanding of the framework. By working through these questions, you will gain deeper insights into MyBatis’ core concepts and best practices, preparing you to confidently tackle technical interviews and demonstrate your expertise.

MyBatis Interview Questions and Answers

1. Write a basic SQL mapping for a User entity with fields id, name, and email.

To map a User entity with fields id, name, and email in MyBatis, define a User class and create a MyBatis mapper XML file. The User class represents the entity, and the mapper XML file contains the SQL mapping.

User class:

public class User {
    private int id;
    private String name;
    private String email;

    // Getters and Setters
}

MyBatis mapper XML file (UserMapper.xml):

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.UserMapper">
    <resultMap id="UserResultMap" type="User">
        <id property="id" column="id" />
        <result property="name" column="name" />
        <result property="email" column="email" />
    </resultMap>

    <select id="selectUserById" resultMap="UserResultMap">
        SELECT id, name, email FROM users WHERE id = #{id}
    </select>
</mapper>

2. How do you handle dynamic SQL in MyBatis? Provide an example.

Dynamic SQL in MyBatis allows you to build queries based on conditions. This is useful when the SQL query needs to change based on user input or other runtime conditions. MyBatis provides several XML tags to handle dynamic SQL, such as <if>, <choose>, <when>, <otherwise>, and <foreach>.

Example:

<select id="findUsers" parameterType="map" resultType="User">
    SELECT * FROM users
    <where>
        <if test="username != null">
            AND username = #{username}
        </if>
        <if test="email != null">
            AND email = #{email}
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
    </where>
</select>

In this example, the SQL query is dynamically constructed based on the presence of username, email, and age parameters.

3. Write a MyBatis mapper interface for CRUD operations on a Product entity.

A MyBatis mapper interface for CRUD operations on a Product entity can be defined as follows:

public interface ProductMapper {

    <ul>
        <li>Create</li>
    </ul>
    @Insert("INSERT INTO Product (name, price, quantity) VALUES (#{name}, #{price}, #{quantity})")
    void insertProduct(Product product);

    <ul>
        <li>Read</li>
    </ul>
    @Select("SELECT * FROM Product WHERE id = #{id}")
    Product selectProductById(int id);

    <ul>
        <li>Update</li>
    </ul>
    @Update("UPDATE Product SET name = #{name}, price = #{price}, quantity = #{quantity} WHERE id = #{id}")
    void updateProduct(Product product);

    <ul>
        <li>Delete</li>
    </ul>
    @Delete("DELETE FROM Product WHERE id = #{id}")
    void deleteProduct(int id);
}

4. Explain the difference between # and $ in parameter mappings.

In MyBatis, the difference between # and $ in parameter mappings is important for understanding how parameters are handled in SQL statements.

• The # symbol is used for parameter substitution. It safely replaces the parameter with a value, preventing SQL injection by escaping special characters.
• The $ symbol is used for string substitution. It directly replaces the parameter with the value without any escaping, which can lead to SQL injection if not used carefully.

Example:

// Using #
<select id="selectUser" parameterType="int" resultType="User">
  SELECT * FROM users WHERE id = #{id}
</select>

// Using $
<select id="selectUser" parameterType="int" resultType="User">
  SELECT * FROM users WHERE ${columnName} = #{value}
</select>

In the first example, the # symbol ensures that the id parameter is safely substituted into the SQL statement. In the second example, the $ symbol is used to dynamically insert a column name into the SQL statement.

5. Write a MyBatis result map for a complex join query involving Order and Customer entities.

In MyBatis, a result map is used to map the results of SQL queries to Java objects. This is particularly useful for complex join queries where the result set may not directly match the structure of a single Java object.

Here is an example of a MyBatis result map for a complex join query involving Order and Customer entities:

<resultMap id="OrderCustomerResultMap" type="Order">
    <id property="orderId" column="order_id"/>
    <result property="orderDate" column="order_date"/>
    <association property="customer" javaType="Customer">
        <id property="customerId" column="customer_id"/>
        <result property="customerName" column="customer_name"/>
        <result property="customerEmail" column="customer_email"/>
    </association>
</resultMap>

<select id="getOrderWithCustomer" resultMap="OrderCustomerResultMap">
    SELECT o.order_id, o.order_date, c.customer_id, c.customer_name, c.customer_email
    FROM Orders o
    JOIN Customers c ON o.customer_id = c.customer_id
    WHERE o.order_id = #{orderId}
</select>

In this example, the result map OrderCustomerResultMap is defined to map the results of a join query between the Orders and Customers tables.

6. How do you implement pagination? Provide an example.

Pagination in MyBatis can be implemented using SQL queries with LIMIT and OFFSET clauses. This allows you to fetch a specific subset of records from the database.

Example:

<select id="selectUsers" resultType="User">
  SELECT * FROM users
  ORDER BY id
  LIMIT #{limit} OFFSET #{offset}
</select>

In your MyBatis mapper interface, you can define a method to pass the limit and offset parameters:

public interface UserMapper {
    List<User> selectUsers(@Param("limit") int limit, @Param("offset") int offset);
}

You can then call this method from your service layer, specifying the desired limit and offset values:

int limit = 10;
int offset = 20;
List<User> users = userMapper.selectUsers(limit, offset);

7. Explain how caching works and how to configure it.

MyBatis provides two levels of caching: first-level and second-level.

First-level caching is enabled by default and is scoped to a session. This means that during a session, MyBatis will cache the results of queries and reuse them if the same query is executed again. This cache is cleared when the session ends or is committed.

Second-level caching is more powerful and is scoped to the namespace of the mapper. It is not enabled by default and must be explicitly configured. This cache can be shared across sessions, making it more efficient for read-heavy applications.

To enable second-level caching, you need to add the <cache/> element in the mapper XML file. You can also configure various cache properties such as eviction policies, flush intervals, and size limits.

Example:

<mapper namespace="com.example.MyMapper">
    <cache eviction="LRU" flushInterval="60000" size="512" readOnly="true"/>
    
    <select id="selectUser" resultType="User">
        SELECT * FROM users WHERE id = #{id}
    </select>
</mapper>

In this example, the cache is configured with an LRU (Least Recently Used) eviction policy, a flush interval of 60 seconds, a maximum size of 512 objects, and is read-only.

8. Write a custom type handler for a LocalDateTime field.

In MyBatis, a custom type handler is used to convert between Java types and database types. This is particularly useful when dealing with types that are not directly supported by MyBatis, such as LocalDateTime.

Example:

import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.MappedTypes;

import java.sql.*;
import java.time.LocalDateTime;

@MappedTypes(LocalDateTime.class)
public class LocalDateTimeTypeHandler implements TypeHandler<LocalDateTime> {

    @Override
    public void setParameter(PreparedStatement ps, int i, LocalDateTime parameter, JdbcType jdbcType) throws SQLException {
        ps.setTimestamp(i, Timestamp.valueOf(parameter));
    }

    @Override
    public LocalDateTime getResult(ResultSet rs, String columnName) throws SQLException {
        Timestamp timestamp = rs.getTimestamp(columnName);
        return timestamp != null ? timestamp.toLocalDateTime() : null;
    }

    @Override
    public LocalDateTime getResult(ResultSet rs, int columnIndex) throws SQLException {
        Timestamp timestamp = rs.getTimestamp(columnIndex);
        return timestamp != null ? timestamp.toLocalDateTime() : null;
    }

    @Override
    public LocalDateTime getResult(CallableStatement cs, int columnIndex) throws SQLException {
        Timestamp timestamp = cs.getTimestamp(columnIndex);
        return timestamp != null ? timestamp.toLocalDateTime() : null;
    }
}

9. Explain advanced mapping techniques like nested results and collections.

Advanced mapping techniques in MyBatis, such as nested results and collections, are used to handle complex result sets and relationships between objects. These techniques are particularly useful when dealing with one-to-many or many-to-many relationships in database tables.

Nested results allow you to map the results of a query to nested objects. Collections, on the other hand, are used to map a collection of objects to a single property in a parent object.

Example:

<resultMap id="authorResultMap" type="Author">
    <id property="id" column="author_id"/>
    <result property="name" column="author_name"/>
    <collection property="books" ofType="Book">
        <id property="id" column="book_id"/>
        <result property="title" column="book_title"/>
    </collection>
</resultMap>

<select id="selectAuthorWithBooks" resultMap="authorResultMap">
    SELECT a.id as author_id, a.name as author_name, 
           b.id as book_id, b.title as book_title
    FROM authors a
    LEFT JOIN books b ON a.id = b.author_id
</select>

In this example, the authorResultMap defines a mapping for the Author object, which includes a collection of Book objects.

10. Describe how to implement batch processing.

Batch processing in MyBatis is a method to execute multiple SQL statements in a single batch, which can improve performance by reducing the number of database round-trips. MyBatis provides built-in support for batch processing through the SqlSession object.

Example:

try (SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
    Mapper mapper = session.getMapper(Mapper.class);
    for (int i = 0; i < 1000; i++) {
        mapper.insertData(new Data(i, "value" + i));
    }
    session.commit();
}

In this example, the SqlSession is opened with the ExecutorType.BATCH parameter, which enables batch processing. The insertData method is called multiple times within a loop, and the session.commit() method is called at the end to execute the batch.