• 1. MyBatis Overview
  • 1.1 Framework Introduction
  • 1.2 JDBC Limitations
  • 1.3 MyBatis Improvements
• 2. Quick Start Tutorial
• 3. Mapper Proxy Development
  • 3.1 Proxy Pattern Overview
  • 3.2 Implementation Requirements
  • 3.3 Practical Implementation
• 4. Core Configuration File
  • 4.1 Multi-Environment Setup
  • 4.2 Type Aliases
• 5. XML-Based CRUD Operations
  • 5.1 Environment Setup
  • 5.2 Query All Records
  • 5.3 Single Record Query
  • 5.4 Multi-Condition Query
  • 5.6 Insert Operations and Transactions
  • 5.7 Update Operations
  • 5.8 Delete Operations
  • 5.9 Parameter Handling
• 6. Annotation-Based CRUD

Prerequisites:
This guide assumes Maven is already installed in your development environment.

1. MyBatis Overview

---

1.1 Framework Introduction

MyBatis is a persistence layer framework designed to simplify JDBC development by providing a powerful abstraction layer over traditional database operations. It serves as a effective solution for mapping relational database records to Java objects.

Understanding the Persistence Layer:

• The persistence layer handles all code responsible for storing data to databases
• Java EE applications typically follow a three-tier architecture: presentation layer, business logic layer, and persistence layer

1.2 JDBC Limitations

While JDBC provides direct database access, it presents several challenges for modern application development:

• Hardcoded Values: Database connection parameters and SQL statements are embedded directly in code, making maintenance difficult and environment-specific configurations cumbersome
• Repetitive Boilerplate: Developers must manually set query parameters and transform result sets into objects, leading to significant code duplication across the application

1.3 MyBatis Improvements

MyBatis addresses these challenges through intelligent design choices:

• Configuration-Driven: Database connection details and SQL statements are externalized to configuration files, eliminating hardcoded values and improving flexibility
• Automated Operations: The framework handles parameter binding and result mapping automatically, reducing boilerplate code and minimizing human error potential

2. Quick Start Tutorial

---

Objective: Retrieve all records from the database table and display them in the console.

Database Setup

CREATE DATABASE IF NOT EXISTS mybatis_practice;
USE mybatis_practice;

DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_name VARCHAR(20) NOT NULL,
    pass_code VARCHAR(20) NOT NULL,
    gender CHAR(1),
    residence VARCHAR(30)
);

INSERT INTO users (user_name, pass_code, gender, residence) VALUES 
('john_doe', 'secure123', 'M', 'New York'),
('jane_smith', 'pass456', 'F', 'Los Angeles'),
('bob_wilson', 'abc789', 'M', 'Chicago');

Project Configuration

Add the following dependencies to your pom.xml file. These dependencies include MyBatis core libraries, database driver, testing framework, and logging implementation.

<dependencies>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.11</version>
    </dependency>
    
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.30</version>
    </dependency>
    
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13.2</version>
        <scope>test</scope>
    </dependency>
    
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>1.7.36</version>
    </dependency>
    
    <dependency>
        <groupId>ch.qos.logback</groupId>
        <artifactId>logback-classic</artifactId>
        <version>1.2.11</version>
    </dependency>
    
    <dependency>
        <groupId>ch.qos.logback</groupId>
        <artifactId>logback-core</artifactId>
        <version>1.2.11</version>
    </dependency>
</dependencies>

Note: Create a logback configuration file in the resources directory to enable proper logging output during development.

MyBatis Configuration File

Create mybatis-config.xml in the src/main/resources directory. This configuration file centralizes database connection settings and eliminates hardcoded connection parameters.

<?xml version="1.0" encoding="UTF-8" ?>
<configuration>
    <environments default="production">
        <environment id="production">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis_practice?useSSL=false&serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="your_password"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <mapper resource="com/example/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

SQL Mapping File

Create UserMapper.xml in src/main/resources/com/example/mapper/. This file centralizes SQL statements and separates them from Java code.

<?xml version="1.0" encoding="UTF-8" ?>
<mapper namespace="com.example.mapper.UserMapper">
    <select id="findAllUsers" resultType="com.example.entity.UserEntity">
        SELECT id, user_name, pass_code, gender, residence
        FROM users
    </select>
</mapper>

Entity Class

package com.example.entity;

public class UserEntity {
    private Integer userId;
    private String userName;
    private String passCode;
    private String gender;
    private String residence;
    
    public UserEntity() {}
    
    public UserEntity(Integer userId, String userName, String passCode, 
                      String gender, String residence) {
        this.userId = userId;
        this.userName = userName;
        this.passCode = passCode;
        this.gender = gender;
        this.residence = residence;
    }
    
    public Integer getUserId() {
        return userId;
    }
    
    public void setUserId(Integer userId) {
        this.userId = userId;
    }
    
    public String getUserName() {
        return userName;
    }
    
    public void setUserName(String userName) {
        this.userName = userName;
    }
    
    public String getPassCode() {
        return passCode;
    }
    
    public void setPassCode(String passCode) {
        this.passCode = passCode;
    }
    
    public String getGender() {
        return gender;
    }
    
    public void setGender(String gender) {
        this.gender = gender;
    }
    
    public String getResidence() {
        return residence;
    }
    
    public void setResidence(String residence) {
        this.residence = residence;
    }
    
    @Override
    public String toString() {
        return "UserEntity{userId=" + userId + ", userName='" + userName + 
               "', passCode='" + passCode + "', gender='" + gender + 
               "', residence='" + residence + "'}";
    }
}

Execution Class

package com.example.main;

import com.example.entity.UserEntity;
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 java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class Application {
    public static void main(String[] args) throws IOException {
        InputStream configStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(configStream);
        
        SqlSession session = factory.openSession();
        List<UserEntity> users = session.selectList("com.example.mapper.UserMapper.findAllUsers");
        System.out.println(users);
        
        session.close();
    }
}

3. Mapper Proxy Development

---

3.1 Proxy Pattern Overview

The direct approach shown above requires hardcoding fully qualified method names as strings when executing queries, which is error-prone and difficult to maintain. Mapper proxy development addresses these concerns by leveraging Java interfaces to represent database operations, enabling type-safe and refactorable database access code.

3.2 Implementation Requirements

To implement mapper proxy pattern successfully, several conditions must be satisfied:

• The mapper interface must be placed in the same package as its corresponding XML mapping file
• The namespace attribute in the XML mapper must exactly match the fully qualified interface name
• Method signatures in the interface must align with SQL statement definitions, including parameter types and return values

Note for Maven Projects: Since Maven separates source code from resources, create parallel package structures in src/main/resources to maintain co-location of mapper interfaces and XML files.

3.3 Practical Implementation

Updated SQL Mapping File

<?xml version="1.0" encoding="UTF-8" ?>
<mapper namespace="com.example.mapper.UserMapper">
    <select id="findAllUsers" resultType="com.example.entity.UserEntity">
        SELECT id, user_name, pass_code, gender, residence
        FROM users
    </select>
</mapper>

Mapper Interface

package com.example.mapper;

import com.example.entity.UserEntity;
import java.util.List;

public interface UserMapper {
    List<UserEntity> findAllUsers();
}

Updated Configuration

Modify the mapper loading section in mybatis-config.xml:

<mappers>
    <mapper class="com.example.mapper.UserMapper"/>
</mappers>

Improved Execution Code

package com.example.main;

import com.example.entity.UserEntity;
import com.example.mapper.UserMapper;
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 java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class Application {
    public static void main(String[] args) throws IOException {
        InputStream configStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(configStream);
        
        SqlSession session = factory.openSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        List<UserEntity> users = mapper.findAllUsers();
        System.out.println(users);
        
        session.close();
    }
}

Package Scanning Enhancement

For projects with multiple mapper interfaces, package scanning simplifies configuration management significantly.

<mappers>
    <package name="com.example.mapper"/>
</mappers>

4. Core Configuration File

---

4.1 Multi-Environment Setup

MyBatis supports configuration of multiple database environments, allowing seamless switching between development, testing, and production environments. The active environment is controlled through the default attribute.

<?xml version="1.0" encoding="UTF-8" ?>
<configuration>
    <typeAliases>
        <package name="com.example.entity"/>
    </typeAliases>
    
    <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:mysql://localhost:3306/mybatis_practice?useSSL=false&serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="dev_password"/>
            </dataSource>
        </environment>
        
        <environment id="production">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://prod-server:3306/mybatis_practice?useSSL=false&serverTimezone=UTC"/>
                <property name="username" value="prod_user"/>
                <property name="password" value="prod_password"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <package name="com.example.mapper"/>
    </mappers>
</configuration>

4.2 Type Aliases

Type aliases provide a convenient way to shorten fully qualified class names, reducing verbosity in mapper XML files and improving readability.

<typeAliases>
    <package name="com.example.entity"/>
</typeAliases>

With this configuration, the result type can be specified using the simple class name:

<select id="findAllUsers" resultType="UserEntity">
    SELECT id, user_name, pass_code, gender, residence
    FROM users
</select>

5. XML-Based CRUD Operations

---

5.1 Environment Setup

Database Table

DROP TABLE IF EXISTS products;

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(50),
    vendor_name VARCHAR(50),
    sort_order INT,
    product_description VARCHAR(200),
    availability_status INT
);

INSERT INTO products (product_name, vendor_name, sort_order, product_description, availability_status)
VALUES 
    ('Wireless Mouse', 'TechPeripherals Inc', 10, 'Ergonomic wireless mouse with long battery life', 1),
    ('Mechanical Keyboard', 'KeyMaster Corp', 5, 'RGB mechanical keyboard with Cherry switches', 1),
    ('USB-C Hub', 'ConnectPlus Ltd', 15, '7-in-1 USB-C hub with HDMI output', 0);

Entity Class

package com.example.entity;

public class Product {
    private Integer productId;
    private String productName;
    private String vendorName;
    private Integer sortOrder;
    private String productDescription;
    private Integer availabilityStatus;
    
    public Product() {}
    
    public Product(Integer productId, String productName, String vendorName,
                   Integer sortOrder, String productDescription, Integer availabilityStatus) {
        this.productId = productId;
        this.productName = productName;
        this.vendorName = vendorName;
        this.sortOrder = sortOrder;
        this.productDescription = productDescription;
        this.availabilityStatus = availabilityStatus;
    }
    
    // Getters and setters omitted for brevity
    
    @Override
    public String toString() {
        return "Product{productId=" + productId + ", productName='" + productName + 
               "', vendorName='" + vendorName + "', sortOrder=" + sortOrder + 
               ", availabilityStatus=" + availabilityStatus + "}";
    }
}

5.2 Query All Records

When database column names differ from entity property names, result mapping becomes essential for proper data retrieval.

Mapper Interface

package com.example.mapper;

import com.example.entity.Product;
import java.util.List;

public interface ProductMapper {
    List<Product> findAllProducts();
}

SQL Mapping File

<?xml version="1.0" encoding="UTF-8" ?>
<mapper namespace="com.example.mapper.ProductMapper">
    <resultMap id="productResultMap" type="product">
        <result column="product_name" property="productName"/>
        <result column="vendor_name" property="vendorName"/>
        <result column="sort_order" property="sortOrder"/>
        <result column="product_description" property="productDescription"/>
        <result column="availability_status" property="availabilityStatus"/>
    </resultMap>
    
    <select id="findAllProducts" resultMap="productResultMap">
        SELECT id, product_name, vendor_name, sort_order, product_description, availability_status
        FROM products
    </select>
</mapper>

Test Implementation

@Test
public void findAllProductsTest() throws IOException {
    InputStream configStream = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(configStream);
    
    SqlSession session = factory.openSession();
    ProductMapper mapper = session.getMapper(ProductMapper.class);
    
    List<Product> products = mapper.findAllProducts();
    System.out.println(products);
    
    session.close();
}

5.3 Single Record Query

MyBatis supports two parameter placeholder styles with distinct security characteristics.

Parameter Placeholders

• #{} placeholder: Replaced with prepared statement parameters, providing SQL injection protection. Use this for all user-supplied values.
• ${} placeholder: Direct string substitution without escaping. Use only for dynamic column or table names where prepared statement syntax cannot apply.

Mapper Interface

Product findProductById(Integer productId);

SQL Mapping

<select id="findProductById" resultMap="productResultMap">
    SELECT id, product_name, vendor_name, sort_order, product_description, availability_status
    FROM products
    WHERE id = #{productId}
</select>

5.4 Multi-Condition Query

MyBatis provides multiple approaches for handling parameters when queries require multiple conditions.

Approach 1: Individual Parameters with Annotations

// Mapper Interface
List<Product> findProductsByFilters(
    @Param("status") Integer availabilityStatus,
    @Param("vendor") String vendorName,
    @Param("name") String productName
);

// SQL Mapping
<select id="findProductsByFilters" resultMap="productResultMap">
    SELECT id, product_name, vendor_name, sort_order, product_description, availability_status
    FROM products
    WHERE availability_status = #{status}
    AND vendor_name LIKE CONCAT('%', #{vendor}, '%')
    AND product_name LIKE CONCAT('%', #{name}, '%')
</select>

Approach 2: Object Parameter

// Mapper Interface
List<Product> findProductsByFilters(Product filter);

// SQL Mapping
<select id="findProductsByFilters" resultMap="productResultMap">
    SELECT id, product_name, vendor_name, sort_order, product_description, availability_status
    FROM products
    WHERE availability_status = #{availabilityStatus}
    AND vendor_name LIKE CONCAT('%', #{vendorName}, '%')
    AND product_name LIKE CONCAT('%', #{productName}, '%')
</select>

Dynamic Query Conditions

The <where> element with conditional <if> tags enables flexible query building based on provided parameters.

<select id="findProductsByFilters" resultMap="productResultMap">
    SELECT id, product_name, vendor_name, sort_order, product_description, availability_status
    FROM products
    <where>
        <if test="availabilityStatus != null">
            AND availability_status = #{availabilityStatus}
        </if>
        <if test="vendorName != null and !vendorName.isEmpty()">
            AND vendor_name LIKE CONCAT('%', #{vendorName}, '%')
        </if>
        <if test="productName != null and !productName.isEmpty()">
            AND product_name LIKE CONCAT('%', #{productName}, '%')
        </if>
    </where>
</select>

Single-Choice Dynamic Query

The <choose> element provides switch-like behavior for scenarios where only one condition should apply.

<select id="findProductBySingleCriterion" resultMap="productResultMap">
    SELECT id, product_name, vendor_name, sort_order, product_description, availability_status
    FROM products
    <where>
        <choose>
            <when test="availabilityStatus != null">
                AND availability_status = #{availabilityStatus}
            </when>
            <when test="vendorName != null and !vendorName.isEmpty()">
                AND vendor_name LIKE CONCAT('%', #{vendorName}, '%')
            </when>
            <when test="productName != null and !productName.isEmpty()">
                AND product_name LIKE CONCAT('%', #{productName}, '%')
            </when>
            <otherwise>
                AND 1 = 2
            </otherwise>
        </choose>
    </where>
</select>

5.6 Insert Operations and Transactions

Insert Implementation

// Mapper Interface
void insertProduct(Product newProduct);

// SQL Mapping
<insert id="insertProduct">
    INSERT INTO products (product_name, vendor_name, sort_order, product_description, availability_status)
    VALUES (#{productName}, #{vendorName}, #{sortOrder}, #{productDescription}, #{availabilityStatus})
</insert>

Transaction Management

MyBatis operates in manual transaction mode by default. Two approaches exist for transaction control:

// Approach 1: Auto-commit mode
SqlSession session = factory.openSession(true); // Auto-commit enabled

// Approach 2: Manual commit
SqlSession session = factory.openSession();
try {
    ProductMapper mapper = session.getMapper(ProductMapper.class);
    mapper.insertProduct(product);
    session.commit(); // Explicit commit
} catch (Exception e) {
    session.rollback();
    throw e;
} finally {
    session.close();
}

Retrieving Generated Keys

For databases supporting auto-generated keys, MyBatis can populate the generated identifier back into the entity object.

<insert id="insertProduct" useGeneratedKeys="true" keyProperty="productId">
    INSERT INTO products (product_name, vendor_name, sort_order, product_description, availability_status)
    VALUES (#{productName}, #{vendorName}, #{sortOrder}, #{productDescription}, #{availabilityStatus})
</insert>

<!-- After insertion -->
Integer generatedId = newProduct.getProductId();

5.7 Update Operations

Selective Field Updates

The <set> element ensures proper SQL syntax when updating only specified fields.

// Mapper Interface
void updateProduct(Product productData);

// SQL Mapping
<update id="updateProduct">
    UPDATE products
    <set>
        <if test="productName != null and !productName.isEmpty()">
            product_name = #{productName},
        </if>
        <if test="vendorName != null and !vendorName.isEmpty()">
            vendor_name = #{vendorName},
        </if>
        <if test="sortOrder != null">
            sort_order = #{sortOrder},
        </if>
        <if test="productDescription != null and !productDescription.isEmpty()">
            product_description = #{productDescription},
        </if>
        <if test="availabilityStatus != null">
            availability_status = #{availabilityStatus},
        </if>
    </set>
    WHERE id = #{productId}
</update>

5.8 Delete Operations

Single Record Deletion

// Mapper Interface
void deleteProduct(Integer productId);

// SQL Mapping
<delete id="deleteProduct">
    DELETE FROM products WHERE id = #{productId}
</delete>

Batch Deletion

The <foreach> element handles dynamic list expansion for batch operations.

// Mapper Interface
void deleteProductsByIds(@Param("idList") List<Integer> productIds);

// SQL Mapping
<delete id="deleteProductsByIds">
    DELETE FROM products
    WHERE id IN
    <foreach collection="idList" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</delete>

5.9 Parameter Handling Deep Dive

MyBatis processes method parameters differently based on their types and annotations:

Multiple Parameters

Without @Param annotation, parameters are accessible via arg0, arg1 or param1, param2 keys. Using annotations provides meaningful names:

Product findByCredentials(@Param("userName") String username, @Param("passCode") String password);

// Accessible as #{userName} and #{passCode}

Collection Types

Arrays and collections are wrapped in a Map with default keys that can be overridden with annotations:

// Arrays
void deleteProducts(@Param("idArray") Integer[] ids); // Access via #{idArray}

// Lists
void findProducts(@Param("idList") List<Integer> ids); // Access via #{idList}

// Map collections
void findProducts(Map<String, Object> criteria); // Access via criteria keys

6. Annotation-Based CRUD

---

For simpler operations, MyBatis supports annotation-based SQL definitions directly on mapper interfaces. Complex queries are still better suited for XML mapping files.

Annotation Examples

package com.example.mapper;

import com.example.entity.Product;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface ProductMapper {
    
    @Results({
        @Result(column = "product_name", property = "productName"),
        @Result(column = "vendor_name", property = "vendorName"),
        @Result(column = "sort_order", property = "sortOrder"),
        @Result(column = "product_description", property = "productDescription"),
        @Result(column = "availability_status", property = "availabilityStatus")
    })
    @Select("SELECT id, product_name, vendor_name, sort_order, product_description, availability_status FROM products WHERE id = #{id}")
    Product findProductById(Integer productId);
    
    @Insert("INSERT INTO products (product_name, vendor_name, sort_order, product_description, availability_status) VALUES (#{productName}, #{vendorName}, #{sortOrder}, #{productDescription}, #{availabilityStatus})")
    void insertProduct(Product product);
    
    @Update("UPDATE products SET product_name = #{productName}, vendor_name = #{vendorName}, sort_order = #{sortOrder}, product_description = #{productDescription}, availability_status = #{availabilityStatus} WHERE id = #{productId}")
    void updateProduct(Product product);
    
    @Delete("DELETE FROM products WHERE id = #{id}")
    void deleteProduct(Integer id);
}

Reusing Result Maps

Named result maps defined in XML can be referenced in annotations:

@ResultMap("productResultMap")
@Select("SELECT * FROM products WHERE id = #{id}")
Product findProductById(Integer productId);