MyBatis is a persistence framework designed to simplify database operations within the Data Access Object (DAO) layer. It addresses issues such as repetitive resource connection management, query result mapping, and SQL hardcoding by decoupling SQL statements from Java code. SQL statements are defined in separate XML files known as SQL Mapper files.

Execution Flow The framework initializes by loading its core configuration file, which contains data source and connection settings. It then reads SQL Mapper files. A SqlSessionFactory uses this configuration to create SqlSession objects, which are responsible for executing SQL commands. Key APIs include SqlSessionFactory for session creation and SqlSession for SQL execution.

Quick Start Guide Add the MyBatis dependency to your project. Create a core configuration file, typically named mybatis-config.xml, in the resources directory.

<?xml version="1.0" encoding="UTF-8" ?>
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.user}"/>
                <property name="password" value="${jdbc.pass}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/example/mapper/EmployeeMapper.xml"/>
    </mappers>
</configuration>

Create a SQL Mapper file, for example EmployeeMapper.xml, to define queries.

<?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.mapper.EmployeeMapper">
    <select id="selectAll" resultType="com.example.model.Employee">
        SELECT id, name, department FROM employees
    </select>
</mapper>

Execute SQL using a SqlSession.

public class AppTest {
    public void executeQuery() {
        try (SqlSession session = MyBatisUtil.getSession()) {
            List<Employee> result = session.selectList("com.example.mapper.EmployeeMapper.selectAll");
            result.forEach(System.out::println);
        }
    }
}

Optimized Usage Pattern A utility class can centralize SqlSession creation.

public class MyBatisUtil {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            throw new RuntimeException("Configuration error", e);
        }
    }
    public static SqlSession getSession() {
        return sqlSessionFactory.openSession();
    }
}

A more intuitive approach is to use Mapper interfaces. Define an interface that matches the mapper namespace.

package com.example.mapper;
public interface EmployeeMapper {
    List<Employee> selectAll();
}

Insure the mapper XML's namespace attribute matches the interface's fully qualified name and SQL id attributes match method names. Retrieve and use the mapper as follows:

EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
List<Employee> list = mapper.selectAll();

Core Configuration Details The configuraton file has a specific element order: properties, settings, typeAliases, typeHandlers, objectFactory, plugins, environments, databaseIdProvider, mappers.

• Properties: Load external configuration files.
• Settings: Configure framework behavior like cache and lazy loading.
• Type Aliases: Simplify fully qualified class names.

  <typeAliases>
      <!-- Individual alias -->
      <typeAlias type="com.example.model.Employee" alias="Emp"/>
      <!-- Package scan -->
      <package name="com.example.model"/>
  </typeAliases>
  

• Environments: Define data sources and transaction managers. Multiple environments can be configured.
• Mappers: Register SQL mapper files.

  <mappers>
      <!-- Resource path -->
      <mapper resource="mapper/EmployeeMapper.xml"/>
      <!-- Interface class -->
      <mapper class="com.example.mapper.EmployeeMapper"/>
      <!-- Package scan -->
      <package name="com.example.mapper"/>
  </mappers>
  

SQL Mapper File CRUD Operations

• Insert: Use the useGeneratedKeys attribute to retrieve auto-increment keys.

  <insert id="insertRecord" parameterType="Emp" useGeneratedKeys="true" keyProperty="id">
      INSERT INTO employees(name, department) VALUES(#{name}, #{department})
  </insert>
  

• Update and Delete: Standard syntax.

  <update id="updateRecord" parameterType="Emp">
      UPDATE employees SET name=#{name} WHERE id=#{id}
  </update>
  <delete id="deleteById" parameterType="int">
      DELETE FROM employees WHERE id=#{id}
  </delete>
  

• Select: Define the result type.

  <select id="selectById" parameterType="int" resultType="Emp">
      SELECT * FROM employees WHERE id = #{id}
  </select>
  

Custom Result Mapping When database column names differ from Java object property names, use a resultMap.

<resultMap id="empResultMap" type="Employee">
    <id property="id" column="emp_id"/>
    <result property="name" column="emp_name"/>
    <result property="department" column="dept"/>
</resultMap>
<select id="selectWithMap" resultMap="empResultMap">
    SELECT emp_id, emp_name, dept FROM employees
</select>

Parameter Passing

• Single parameter: Use any name in the SQL placeholder.
• Multiple parameters: Use @Param annotation, a Map, or a JavaBean.

  // Using @Param
  Employee findByNameAndDept(@Param("empName") String name, @Param("deptName") String dept);
  // Using a Map
  Employee findByMap(Map<String, Object> params);
  

Dynamic SQL Build SQL dynamically based on input parameters.

• <if>: Conditional inclusion.

  <select id="search" parameterType="map" resultType="Emp">
      SELECT * FROM employees
      <where>
          <if test="name != null">
              AND name LIKE #{name}
          </if>
          <if test="dept != null">
              AND department = #{dept}
          </if>
      </where>
  </select>
  

• <where>: Adds WHERE and removes leading AND/OR.
• <set>: Adds SET and removes trailing commas in update statements.
• <foreach>: Iterates over collections for operations like IN clauses.

  <delete id="deleteMultiple">
      DELETE FROM employees WHERE id IN
      <foreach collection="list" item="itemId" open="(" separator="," close=")">
          #{itemId}
      </foreach>
  </delete>
  

Association and Collection Mappings

• One-to-One (<association>)

  <resultMap id="personDetailMap" type="Person">
      <id property="pid" column="person_id"/>
      <result property="pname" column="person_name"/>
      <association property="idCard" javaType="IdCard">
          <id property="cid" column="card_id"/>
          <result property="number" column="card_number"/>
      </association>
  </resultMap>
  <select id="selectPersonWithCard" resultMap="personDetailMap">
      SELECT p.id as person_id, p.name as person_name,
             c.id as card_id, c.number as card_number
      FROM person p JOIN id_card c ON p.card_id = c.id
      WHERE p.id = #{id}
  </select>
  

• One-to-Many (<collection>)

  <resultMap id="deptWithEmpsMap" type="Department">
      <id property="deptId" column="department_id"/>
      <result property="deptName" column="department_name"/>
      <collection property="employees" ofType="Employee">
          <id property="id" column="emp_id"/>
          <result property="name" column="emp_name"/>
      </collection>
  </resultMap>
  <select id="selectDeptWithEmployees" resultMap="deptWithEmpsMap">
      SELECT d.id as department_id, d.name as department_name,
             e.id as emp_id, e.name as emp_name
      FROM department d LEFT JOIN employees e ON d.id = e.dept_id
      WHERE d.id = #{id}
  </select>
  

Caching

• First Level Cache: Enabled by default, session-scoped. It's cleared on update operations or session closure.
• Second Level Cache: Namespace (Mapper) scoped, disabled by default. Enable it in the core config and mapper file. Cached objects must be serializable.

  <!-- In mybatis-config.xml -->
  <settings>
      <setting name="cacheEnabled" value="true"/>
  </settings>
  <!-- In Mapper XML -->
  <cache/>
  

Lazy Loading Delays loading of associated data until it's accessed. Enable globally and configure per association.

<settings>
    <setting name="lazyLoadingEnabled" value="true"/>
</settings>
<!-- In resultMap -->
<association property="detail" 
             select="com.example.mapper.DetailMapper.selectByPrimary"
             column="fk_id" fetchType="lazy"/>

Pagination Plugin Integrate the PageHelper plugin for simplified pagination.

1. Add depandency.
2. Configure the interceptor.

   <plugins>
       <plugin interceptor="com.github.pagehelper.PageInterceptor">
           <property name="helperDialect" value="mysql"/>
       </plugin>
   </plugins>
   

3. Use in code.

   PageHelper.startPage(1, 10); // Page 1, 10 records per page
   List<Employee> list = employeeMapper.selectAll();
   PageInfo<Employee> pageInfo = new PageInfo<>(list);