MyBatis Simple CRUD Example

Before delving into CRUD (Create, Read, Update, Delete) operations, it is crucial to ensure the integrity of your local development environment. MyBatis, as an excellent persistence framework, offers a core advantage by decoupling SQL statements from Java code. However, many beginners overlook fundamental configuration file settings, leading to mapping failures or connection exceptions later on.

Firstly, please create an application.yml (or .properties) file in the src/main/resources directory to configure database connection information. For a MySQL environment, core parameters include the driver class, URL, username, and password. Additionally, you must enable MyBatis's automatic physical namespace feature, which can be achieved by adding a <namespace> tag within your Mapper XML files; this tag typically points to the corresponding Java Interface.

<?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.UserMapper">
    <!-- Core Concept: Physical Namespace -->
</mapper>

Do not forget to configure MyBatis's global properties in application.yml, such as enabling second-level caching or setting the log level. Proper initialization is a prerequisite for all subsequent operations to proceed smoothly.
MyBatis follows "Object-Relational" mapping principles, therefore clear Java entity classes and corresponding Mapper interfaces need to be defined. When writing code, strictly adhere to camelCase naming conventions and add necessary annotations to identify characteristics such as primary keys or auto-increment fields.

For the user table, we can create a standard User class:

public class User {
    private Long id; // Corresponds to the database's Primary Key ID
    
    @TableId(type = Type.AUTO) // MyBatis-Plus annotation example; if using native XML, this may be omitted or replaced with JdbcType
    private String username; 
    
    private Integer age; 
}

Next, define the Mapper interface. The return type of these methods should be specified as User (for single table queries) or List<User> (for multiple result sets), while method names follow database field naming conventions or use custom XML mappings to override default behaviors. For example:

  • selectById: Find a user by ID;
  • insert: Add a new user record;
  • updateByUsername: Update attributes such as age for the user with a specific username;
  • selectAllByCondition: Query all users satisfying specific conditions (requires XML support here).

MyBatis Mapper XML Configuration Details

XML mapping files serve as the bridge connecting Java code to database SQL. Inside the <mapper> tag, we use various nested elements to define specific operation logic. Core concepts include resultMap, select, insert, update, and delete.

1. ResultMap (Result Set Mapping)

When entity class fields do not match database column names, or when handling results from multi-table join queries, <resultMap> must be used. It binds Java object properties to SQL columns via the id attribute. For example:

<resultMap id="UserResult" type="com.example.mapper.User">
    <id property="id" column="user_id"/>
    <result property="username" column="usr_name"/> <!-- Assume database column is usr_name -->
</resultMap>

2. Select (Query Operation)

The select tag defines SELECT statements. The <where> tag can be used to simplify condition concatenation, avoiding manual handling of AND/OR logic. For example:

<select id="selectAllByCondition" resultMap="UserResult">
    select * from user where age > #{age} and status = 'active'
</select>

3. Insert (Insert Operation)

MyBatis provides the <insert> tag along with its sub-elements. The keyProperty attribute specifies the primary key field to automatically return the new record's primary key value after transaction commit. Using #{} placeholders is critical for preventing SQL injection:

<insert id="addUser" parameterType="com.example.mapper.User">
    insert into user (username, age) values (#{username}, #{age})
</insert>

4. Update (Update Operation)

The update tag also supports the <set> sub-element to automatically ignore null values, thereby reducing manual writing of AND conditions. Note the distinction between field names and property name mappings:

<update id="modifyUser" parameterType="com.example.mapper.User">
    update user set age = #{age} where username = #{username}
</update>

5. Delete (Delete Operation)

The delete tag is the most direct; usually, only specifying a primary key condition is required:

<delete id="removeUser" parameterType="Long">
    delete from user where id = #{id}
</delete>

Java Code Practice for Executing CRUD Operations

After configuration, via Spring Boot's @MapperScan annotation or manual injection of the Mapper Bean, these interfaces can be called within the Service layer. Below is a complete example flow for Create, Read, Update, and Delete:

  1. Create User: Construct a User object and pass it to the Mapper's insert method;
  2. Query Single Record: Directly call selectById to retrieve a single entity;
  3. Batch Delete: Although MyBatis XML supports loops, in Java code it is more recommended to use batchDelete with collection lists for efficient operations;
  4. Complex Condition Query: Utilize the <where> tag to dynamically concatenate SQL.
public class UserService {
    private UserMapper userMapper; // Spring auto-injection
    
    public void createUser(User user) throws Exception {
        long id = userMapper.addUser(user);
        System.out.println("Created successfully, ID: " + id);
    }
    
    @Transactional(rollbackFor = RuntimeException.class)
    public boolean deleteUser(Long id) {
        try {
            int rowsAffected = userMapper.removeUser(id);
            return rowsAffected > 0; // Check if record was actually deleted
        } catch (Exception e) {
            throw new BusinessException("User not found or operation failed");
        }
    }
}

Summary and Best Practice Recommendations

Through the steps above, we have completed the entire CRUD process from environment configuration to code implementation. In actual development, keep the following points in mind: maintain XML conciseness and avoid overly long SQL statements; use tags like <where> and <set> reasonably to improve maintainability; always add pagination support (e.g., offset, limit) for large data volume queries; enable slow query logs in production environments to monitor performance bottlenecks.

MyBatis, with its flexibility and powerful extensibility capabilities, has become an indispensable tool in Java Web development. Mastering these basic CRUD operations will lay a solid foundation for learning subsequent topics such as multi-table joins, dynamic SQL, and complex transaction management.