Skip to main content
Advertisement

10.2 Practical MyBatis CRUD Example

Let's look into the full flow of handling basic data retrieval as well as insertion, modification, and deletion (CRUD) using MyBatis with detailed examples.

1. Domain Model (VO/DTO)​

This is the Java object that will map to the database columns. Using the map-underscore-to-camel-case: true setting in application.yml is convenient.

@Getter @Setter
public class PostVo {
    private Long id;
    private String title;
    private String content;
    private String author;
    private LocalDateTime createdAt;
}

2. Mapper Interface (@Mapper)​

Instead of writing SQL directly in abstract methods, we connect them to the SQL ID in the XML using the method name.

import org.apache.ibatis.annotations.Mapper;
import java.util.List;

@Mapper
public interface PostMapper {
    // 1. Create (C)
    void insertPost(PostVo post);

    // 2. Read One (R)
    PostVo findPostById(Long id);

    // 3. Read All (R)
    List<PostVo> findAllPosts();

    // 4. Update (U)
    void updatePost(PostVo post);

    // 5. Delete (D)
    void deletePost(Long id);
}

3. MyBatis XML Mapper (PostMapper.xml)​

This is the XML file where the SQL statements are defined. We use the #{} syntax for parameter passing.

<?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.PostMapper">

    <!-- 1. Create: Map the ID generated by the DB back to the object using useGeneratedKeys -->
    <insert id="insertPost" parameterType="PostVo" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO posts (title, content, author, created_at)
        VALUES (#{title}, #{content}, #{author}, NOW())
    </insert>

    <!-- 2. Read One -->
    <select id="findPostById" resultType="PostVo">
        SELECT * FROM posts WHERE id = #{id}
    </select>

    <!-- 3. Read All -->
    <select id="findAllPosts" resultType="PostVo">
        SELECT * FROM posts ORDER BY id DESC
    </select>

    <!-- 4. Update: Use dynamic queries to update only specific fields -->
    <update id="updatePost" parameterType="PostVo">
        UPDATE posts
        <set>
            <if test="title != null">title = #{title},</if>
            <if test="content != null">content = #{content},</if>
        </set>
        WHERE id = #{id}
    </update>

    <!-- 5. Delete -->
    <delete id="deletePost">
        DELETE FROM posts WHERE id = #{id}
    </delete>

</mapper>

4. Advanced Options and Tag Usage Examples​

Practical code examples of key options and tags frequently used in production.

1) useGeneratedKeys & keyProperty​

After inserting data (INSERT), it immediately populates the auto-incremented ID value generated by the DB into the Java object.

<insert id="insertUser" parameterType="UserVo" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO users (name, email) VALUES (#{name}, #{email})
</insert>
  • Result: After execution, calling userVo.getId() will immediately return the ID value saved in the DB.

2) Using <selectKey> (Oracle or Sequence-based DBs)​

Used to retrieve a specific value before or after an insertion in a DB without auto-increment columns.

<insert id="insertLog" parameterType="LogVo">
    <selectKey keyProperty="id" resultType="long" order="BEFORE">
        SELECT log_seq.NEXTVAL FROM DUAL
    </selectKey>
    INSERT INTO system_logs (id, message) VALUES (#{id}, #{message})
</insert>
  • order="BEFORE": Retrieves the sequence number before the main query and populates the id field of the object.

3) <sql> and <include> (Reducing Code Duplication)​

Defines common column lists or fragments used across multiple queries for reuse.

<!-- Define common SQL fragment -->
<sql id="postColumns">
    id, title, content, author, created_at
</sql>

<select id="findAllPosts" resultType="PostVo">
    SELECT <include refid="postColumns" />
    FROM posts
</select>

<select id="findActivePosts" resultType="PostVo">
    SELECT <include refid="postColumns" />
    FROM posts
    WHERE status = 'ACTIVE'
</select>

5. Detailed Explanation of Mapper Tags and Attributes​

The following are the meanings of the main tags and attributes used in MyBatis XML mappers.

1) <mapper> Tag​

  • namespace: Specifies the full path (Full Qualifier) of the Mapper interface to connect. If this path is incorrect, the interface and XML will not be linked.

2) Common Attributes (select, insert, update, delete)​

  • id: Must match the method name of the Mapper interface.
  • parameterType: Class name of the parameter (Optional).
<select id="getName" parameterType="long" resultType="string">
    SELECT name FROM users WHERE id = #{id}
</select>

3) Attributes for <select>​

  • resultType: Java object type to map a result row.
  • resultMap: Used for complex mapping or joins.

4) Attributes for <insert>​

  • useGeneratedKeys: Whether to use auto-incremented keys.
  • keyProperty: Field name to hold the generated key.
<insert id="join" useGeneratedKeys="true" keyProperty="no">
    INSERT INTO members (name) VALUES (#{name})
</insert>

5) Dynamic SQL Tags​

  • <if>: Conditional SQL fragment inclusion.
<where>
    <if test="email != null">AND email = #{email}</if>
</where>
  • <set>: Manages commas automatically for updates.
  • <where>: Automatically manages the WHERE clause and AND/OR.
  • <foreach>: Iterates over a collection (like a List or array) to generate SQL.

<foreach> Practical Example (Batch Insert & IN Clause)​

<!-- 1. Batch Delete using a List (IN Clause) -->
<delete id="deleteUsers">
    DELETE FROM users
    WHERE id IN
    <foreach item="id" collection="list" open="(" separator="," close=")">
        #{id}
    </foreach>
</delete>

<!-- 2. Bulk Insert -->
<insert id="insertUserList">
    INSERT INTO users (name, email)
    VALUES
    <foreach item="user" collection="list" separator=",">
        (#{user.name}, #{user.email})
    </foreach>
</insert>
  • collection: The name of the collection parameter passed (list, array, etc.).
  • item: The variable name to use inside the loop.
  • separator: The separator between items (usually a comma).
  • open / close: Characters to wrap the entire generated string.

6. Service Logic Integration​

Inject the Mapper into a Controller or Service to use it.

@Service
@RequiredArgsConstructor
public class PostService {
    private final PostMapper postMapper;

    @Transactional
    public void create(PostVo vo) {
        postMapper.insertPost(vo);
        System.out.println("Generated ID: " + vo.getId());
    }

    public List<PostVo> getList() {
        return postMapper.findAllPosts();
    }
}

7. MyBatis Pagination​

Since MyBatis doesn't provide automatic pagination like JPA, you manually handle SQL's LIMIT and OFFSET or use a dedicated plugin like PageHelper. Below is the standard LIMIT/OFFSET approach.

7.1 Mapper Interface​

@Mapper
public interface PostMapper {
    // Paginated list retrieval
    List<PostVo> findPostsWithPaging(@Param("offset") int offset, @Param("size") int size);
    
    // Total count retrieval (for metadata)
    int countPosts();
}

7.2 XML Mapper (MySQL/PostgreSQL)​

<select id="findPostsWithPaging" resultType="PostVo">
    SELECT * FROM posts
    ORDER BY created_at DESC
    LIMIT #{size} OFFSET #{offset}
</select>

<select id="countPosts" resultType="int">
    SELECT COUNT(*) FROM posts
</select>

7.3 Logic and Formula​

  • offset = (page - 1) * size
  • Example: For page 2 (page=2) with 10 items per page (size=10), offset = 10 (starting from the 11th data).
@Service
public class PostService {
    public Map<String, Object> getPagedPosts(int page, int size) {
        int offset = (page - 1) * size;
        List<PostVo> posts = postMapper.findPostsWithPaging(offset, size);
        int totalCount = postMapper.countPosts();

        Map<String, Object> result = new HashMap<>();
        result.put("items", posts);
        result.put("total", totalCount);
        result.put("currentPage", page);
        result.put("totalPages", (int) Math.ceil((double) totalCount / size));
        
        return result;
    }
}

If manually calculating OFFSET and writing COUNT queries is too tedious, it's highly recommended to use PageHelper, the most famous pagination plugin for MyBatis.

Add Dependency (build.gradle)​

implementation 'com.github.pagehelper:pagehelper-spring-boot-starter:2.1.0'

Service Logic Implementation​

You can implement pagination with just one line of code without modifying your SQL.

public PageInfo<PostVo> getPostsWithPageHelper(int page, int size) {
    // 1. Initialize pagination (Automatically appends LIMIT/OFFSET to the next executing SQL)
    PageHelper.startPage(page, size);
    
    // 2. Perform regular list retrieval (No need for LIMIT in SQL)
    List<PostVo> posts = postMapper.findAllPosts();
    
    // 3. Convert to a PageInfo object (Includes all metadata like total pages and total records)
    return new PageInfo<>(posts);
}
  • Advantages: Handle pagination without touching your SQL. The PageInfo object provides a comprehensive set of metadata in a single response.

6) Other Useful Tags​

  • <selectKey>: Retrieves a specific value before or after executing insert or update and sets it to a field in the parameter object. Commonly used for querying Oracle Sequences or getting the ID of a newly inserted row.
    • order="BEFORE": Executed before the main query.
    • order="AFTER": Executed after the main query.
  • <sql> & <include>: Defines repetitive SQL fragments (e.g., table column lists) with <sql> and reuses them with <include> to reduce code duplication.

7. Parameter Binding: #{} vs ${}​

There are two ways to pass parameters to SQL in MyBatis, with significant differences in security and performance.

1) #{parameter} (PreparedStatement)​

  • Characteristics: Processes data as ** binding variables (?)**.
  • Security: Values are automatically wrapped in quotes (' '), which ** defends against SQL Injection attacks**.
  • Usage: General user input (names, IDs, post content, etc.).
  • Generated SQL: SELECT * FROM users WHERE id = ?

2) ${parameter} (Statement)​

  • Characteristics: Merges the value into the SQL statement as a ** Literal string**.
  • Risks: Quotes are not added automatically, leading to ** SQL Injection vulnerabilities**where malicious SQL code can be executed.
  • Purpose: Used limitedly when the SQL syntax itself needs to change dynamically, such as column names, table names, or ORDER BY clauses.
  • Generated SQL: SELECT * FROM users WHERE id = 1 (Inserted as is if it's a number).

⚠️ Exceptions and Precautions​

  • Missing Quote Error: Passing string data through ${} will result in a SQL syntax error because of the missing quotes (e.g., WHERE name = User1 → Error).
  • Security Incidents: If user-entered search terms are put into ${}, the database could be compromised by inputs like '; DROP TABLE users; --.
  • Conclusion: ** Adhere to using #{} as a rule**. Use ${} only for special cases like changing sort conditions, and only allow pre-validated values.

🎯 Key Points​

  • useGeneratedKeys: Used to immediately reflect the automatically generated ID from an INSERT operation back into the Java object.
  • ResultType: Specifies the class path to hold the retrieval results.
  • # vs $: For security, always prioritize using #.
  • <set>, <if>: Allows for writing efficient and flexible SQL through dynamic queries.
Advertisement
Advertisement