MyBatis XML Configuration Guide: From Setup to CRUD Operations

MyBatis is a Java persistence framework that encapsulates JDBC details, letting developers focus on SQL. Combined with Spring's dependency injection, it reduces boilerplate code and boosts productivity. MyBatis supports both XML and annotation-based SQL configuration; this guide covers the XML approach.

1. Import Maven Dependencies

Create a Maven project and add four dependencies to pom.xml:

<!-- MySQL connector -->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.28</version>
</dependency>

<!-- MyBatis core -->
<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>3.5.9</version>
</dependency>

<!-- Log4j for SQL logging -->
<dependency>
  <groupId>log4j</groupId>
  <artifactId>log4j</artifactId>
  <version>1.2.17</version>
</dependency>

<!-- JUnit for testing -->
<dependency>
  <groupId>junit</groupId>
  <artifactId>junit</artifactId>
  <version>4.13.2</version>
  <scope>test</scope>
</dependency>

Refresh the Maven window to download the JARs.

2. Project Structure

Key packages: com.jobs.bean – entity classes com.jobs.mapper – MyBatis mapper interfaces com.jobs.service – business logic using MyBatis

Resources directory contains: com/jobs/mapperXML/employeeMapper.xml – SQL mappings for the employee mapper jdbc.properties – database connection parameters log4j.properties – Log4j configuration MyBatisConfig.xml – MyBatis core configuration

Test directory holds com.jobs.employeeTest for JUnit tests.

3. Configuration Files

jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/testdb
username=root
password=123456

log4j.properties

# Output DEBUG and above logs to console
log4j.rootLogger=DEBUG, stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%p [%t] - %m%n

MyBatisConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
  <properties resource="jdbc.properties"/>
  <settings>
    <setting name="logImpl" value="log4j"/>
  </settings>
  <typeAliases>
    <package name="com.jobs.bean"/>
  </typeAliases>
  <environments default="mysql">
    <environment id="mysql">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="com/jobs/mapperXML/employeeMapper.xml"/>
  </mappers>
</configuration>

The configuration loads JDBC properties, enables Log4j, defines a type alias package for entities, sets up a pooled JDBC data source with transaction management, and registers the mapper XML.

4. Database Table and Entity Class

MySQL schema:

CREATE DATABASE IF NOT EXISTS `testdb`;
USE `testdb`;
CREATE TABLE IF NOT EXISTS `employee` (
  `e_id` int(11) NOT NULL AUTO_INCREMENT,
  `e_name` varchar(50) DEFAULT NULL,
  `e_age` int(11) DEFAULT NULL,
  PRIMARY KEY (`e_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT INTO `employee` (`e_id`, `e_name`, `e_age`)
VALUES (1, '侯胖胖', 25), (2, '杨磅磅', 23),
       (3, '李吨吨', 33), (4, '任肥肥', 35), (5, '乔豆豆', 32);

Entity class com.jobs.bean.employee deliberately uses different field names ( id, name, age) than the database columns ( e_id, e_name, e_age) to demonstrate result mapping.

package com.jobs.bean;
public class employee {
  private Integer id;
  private String name;
  private Integer age;
  // constructors, getters, setters, toString() omitted for brevity
}

5. Mapper Interface and SQL Mapping

MyBatis dynamic proxy development is used. The mapper interface and XML must follow conventions:

XML namespace equals the mapper interface's fully qualified name.

Mapper method names match XML statement id attributes.

Input parameter types match XML parameterType.

Return types match XML resultType or resultMap.

Use resultMap when column names differ from entity fields.

Mapper Interface: com.jobs.mapper.employeeMapper

package com.jobs.mapper;
import com.jobs.bean.employee;
import java.util.List;
public interface employeeMapper {
  List<employee> selectAll();
  employee selectById(Integer id);
  Integer insert(employee ele);
  Integer update(employee ele);
  Integer delete(Integer id);
  List<employee> selectCondition(employee ele);
  List<employee> selectByIds(List<Integer> ids);
}

Mapper XML: com/jobs/mapperXML/employeeMapper.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.jobs.mapper.employeeMapper">
  <resultMap id="employee_map" type="employee">
    <id column="e_id" property="id"/>
    <result column="e_name" property="name"/>
    <result column="e_age" property="age"/>
  </resultMap>
  <sql id="select">SELECT e_id,e_name,e_age FROM employee</sql>
  <select id="selectAll" resultMap="employee_map">
    <include refid="select"/> order by e_id;
  </select>
  <select id="selectById" resultMap="employee_map" parameterType="int">
    <include refid="select"/> WHERE e_id = #{id}
  </select>
  <insert id="insert" parameterType="employee">
    insert into employee(e_id,e_name,e_age) VALUES (#{id},#{name},#{age})
  </insert>
  <update id="update" parameterType="employee">
    UPDATE employee SET e_name = #{name}, e_age = #{age} WHERE e_id = #{id}
  </update>
  <delete id="delete" parameterType="int">
    DELETE FROM employee WHERE e_id = #{id}
  </delete>
  <select id="selectCondition" resultMap="employee_map" parameterType="employee">
    <include refid="select"/>
    <where>
      <if test="id != null"> e_id = #{id} </if>
      <if test="name != null"> AND e_name like CONCAT('%',#{name},'%') </if>
      <if test="age != null"> AND e_age = #{age} </if>
    </where>
    order by e_id desc
  </select>
  <select id="selectByIds" resultMap="employee_map" parameterType="list">
    <include refid="select"/>
    <where>
      <foreach collection="list" open="e_id IN (" close=")" item="id" separator=",">
        #{id}
      </foreach>
    </where>
  </select>
</mapper>

The XML defines a resultMap to bridge column/field name differences, a reusable SQL fragment ( <sql id="select">), and uses dynamic SQL tags ( <where>, <if>, <foreach>) for conditional queries and batch ID selection.

6. Service Layer

com.jobs.service.employeeService

encapsulates data access. Each method follows the same pattern: load MyBatisConfig.xml, build SqlSessionFactory, open a session with auto-commit ( true), get the mapper proxy, invoke the method, and close resources in a finally block. The article notes this boilerplate is eliminated when integrating with Spring.

public class employeeService {
  public List<employee> selectAll() { /* ... */ }
  public employee selectById(Integer id) { /* ... */ }
  public Integer insert(employee ele) { /* ... */ }
  public Integer update(employee ele) { /* ... */ }
  public Integer delete(Integer id) { /* ... */ }
  public List<employee> selectCondition(employee ele) { /* ... */ }
  public List<employee> selectByIds(List<Integer> ids) { /* ... */ }
}

7. JUnit Testing

com.jobs.employeeTest

verifies all CRUD operations and dynamic queries:

public class employeeTest {
  private employeeService service = new employeeService();
  @Test public void selectAll() { /* prints all employees */ }
  @Test public void selectById() { /* prints employee with id=3 */ }
  @Test public void insert() { /* inserts new employee, prints affected rows */ }
  @Test public void update() { /* updates employee id=6 age to 16 */ }
  @Test public void selectCondition() { /* fuzzy search by name '任' */ }
  @Test public void selectByIds() { /* queries ids 1,2,3 */ }
  @Test public void delete() { /* deletes employee id=6 */ }
}

Running these tests confirms the MyBatis XML configuration works correctly.