Chapter 06

CRUD operations, done properly

Create, read, update and delete are five minutes of work each. The interesting parts are generated keys, bulk writes, concurrency and paging — which is where this chapter spends its time.

⏱ 20 min readLevel: intermediateHands-on

The complete mapper interface

UserMapper.java
package com.bitsnippets.shop.mapper;

import com.bitsnippets.shop.domain.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Optional;

@Mapper
public interface UserMapper {

    /* ---- create ---- */
    int insert(User user);
    int insertBatch(@Param("users") List<User> users);
    int upsert(User user);

    /* ---- read ---- */
    Optional<User> findById(Long id);
    List<User> findPage(@Param("limit") int limit, @Param("offset") int offset);
    long countAll();
    boolean existsByEmail(String email);

    /* ---- update ---- */
    int update(User user);                       // optimistic, checks version
    int updateSelective(User user);              // only non-null fields
    int touchLastLogin(@Param("id") Long id);

    /* ---- delete ---- */
    int deleteById(Long id);
    int deleteByIds(@Param("ids") List<Long> ids);
    int softDelete(@Param("id") Long id);
}

Create

Generated keys

UserMapper.xml
<insert id="insert" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
  INSERT INTO app_user (user_name, email, status)
  VALUES (#{userName}, #{email}, #{status})
</insert>
UserService.java
User user = new User();
user.setUserName("neha");
user.setEmail("neha@example.com");
user.setStatus("ACTIVE");

int rows = userMapper.insert(user);   // rows == 1
Long newId = user.getId();            // populated in place by MyBatis
The return value is a row count, not an id

insert(...) returns int. If you write Long id = mapper.insert(user) it will not compile — and if your mapper returns long, you will silently get 1 instead of the key. Always read the id from the object.

Bulk inserts: two different techniques

These are frequently confused. One builds a single multi-row INSERT statement; the other uses JDBC batching. They have different limits and different failure modes.

UserMapper.xml
<insert id="insertBatch" useGeneratedKeys="true" keyProperty="id">
  INSERT INTO app_user (user_name, email, status) VALUES
  <foreach collection="users" item="u" separator=",">
    (#{u.userName}, #{u.email}, #{u.status})
  </foreach>
</insert>

One statement, one round trip, very fast. But every value is a bind parameter, so you hit the driver limit (65,535 on PostgreSQL, 2,100 on SQL Server) — chunk the list at around 500–1,000 rows.

BulkLoader.java
try (SqlSession session = factory.openSession(ExecutorType.BATCH)) {

    UserMapper mapper = session.getMapper(UserMapper.class);

    int i = 0;
    for (User user : users) {
        mapper.insert(user);
        if (++i % 1000 == 0) {
            session.flushStatements();   // send this chunk to the database
        }
    }
    session.commit();                    // flushes the remainder too
}

Statements are queued and sent in batches. Note that mapper.insert() returns a meaningless -2147482646 (Statement.SUCCESS_NO_INFO) here — real counts only arrive from flushStatements(). Generated keys are often unavailable in batch mode depending on the driver.

Multi-row VALUESBatch executor
Round trips1 per chunk1 per batch
Parameter limitYes, strictNo
Generated keysUsually yesDriver-dependent
Row countsAccurateOnly after flush
Mixed statement typesN/ACauses an implicit flush per switch
Good forUp to ~1,000 rowsTens of thousands of rows

Upsert

UserMapper.xml — vendor variants
<insert id="upsert" databaseId="postgres">
  INSERT INTO app_user (user_name, email, status)
  VALUES (#{userName}, #{email}, #{status})
  ON CONFLICT (user_name)
  DO UPDATE SET email = EXCLUDED.email,
                status = EXCLUDED.status
</insert>

<insert id="upsert" databaseId="mysql">
  INSERT INTO app_user (user_name, email, status)
  VALUES (#{userName}, #{email}, #{status})
  ON DUPLICATE KEY UPDATE email = VALUES(email),
                          status = VALUES(status)
</insert>

Read

Optional and empty results

selectOne returns null when nothing matches and throws TooManyResultsException when more than one row comes back. Declaring Optional<User> makes the empty case explicit at the call site.

UserService.java
public User require(Long id) {
    return userMapper.findById(id)
                     .orElseThrow(() -> new UserNotFoundException(id));
}

Pagination

MyBatis ships RowBounds, but it pages in memory: the driver still returns every row. Use it only for small result sets. Real pagination belongs in the SQL.

UserMapper.xml
<select id="findPage" resultType="User">
  SELECT id, user_name, email, status
  FROM app_user
  ORDER BY id
  LIMIT #{limit} OFFSET #{offset}
</select>

<select id="countAll" resultType="long">
  SELECT COUNT(*) FROM app_user
</select>
PageResult.java
public record PageResult<T>(List<T> content, long total, int page, int size) {

    public int totalPages() {
        return (int) Math.ceil((double) total / size);
    }
}

// in the service
public PageResult<User> page(int page, int size) {
    List<User> content = userMapper.findPage(size, page * size);
    long total = userMapper.countAll();
    return new PageResult<>(content, total, page, size);
}
Deep paging is a trap

OFFSET 500000 forces the database to walk half a million rows before returning anything. For infinite scrolling use keyset pagination instead — remember the last id and continue from it:

SELECT id, user_name FROM app_user
WHERE id > #{lastSeenId}
ORDER BY id
LIMIT #{limit}

Update

Optimistic locking

The version column in our schema exists for this. The update only succeeds if the version still matches what the caller read; a zero row count means someone else got there first.

UserMapper.xml
<update id="update">
  UPDATE app_user
  SET user_name = #{userName},
      email     = #{email},
      status    = #{status},
      version   = version + 1
  WHERE id = #{id}
    AND version = #{version}
</update>
UserService.java
@Transactional
public void rename(Long id, int expectedVersion, String newName) {

    User user = userMapper.findById(id)
                          .orElseThrow(() -> new UserNotFoundException(id));
    user.setUserName(newName);
    user.setVersion(expectedVersion);

    if (userMapper.update(user) == 0) {
        throw new OptimisticLockException(
                "User " + id + " was modified by someone else");
    }
    user.setVersion(expectedVersion + 1);   // keep the in-memory object honest
}

Selective (partial) update

A PATCH endpoint should not overwrite fields the caller never sent. <set> emits only the assignments whose values are present, and strips the trailing comma.

UserMapper.xml
<update id="updateSelective">
  UPDATE app_user
  <set>
    <if test="userName != null">user_name = #{userName},</if>
    <if test="email    != null">email     = #{email},</if>
    <if test="status   != null">status    = #{status},</if>
    version = version + 1
  </set>
  WHERE id = #{id} AND version = #{version}
</update>
Null means “unchanged” — until it doesn't

This pattern makes it impossible to set a column to null. If nullability is meaningful in your API, wrap the fields in a container that distinguishes “absent” from “explicitly null” (an Optional field, or a Map of supplied keys).

Delete

UserMapper.xml
<delete id="deleteById">
  DELETE FROM app_user WHERE id = #{id}
</delete>

<delete id="deleteByIds">
  DELETE FROM app_user
  WHERE id IN
  <foreach collection="ids" item="id" open="(" separator="," close=")">
    #{id}
  </foreach>
</delete>

<update id="softDelete">
  UPDATE app_user
  SET status = 'DELETED', version = version + 1
  WHERE id = #{id} AND status &lt;&gt; 'DELETED'
</update>
Empty collections generate invalid SQL

If ids is empty, the foreach produces IN (), which is a syntax error on most databases. Guard it in Java — an empty input should mean “do nothing”, and returning early is both faster and safer.

public int deleteAll(List<Long> ids) {
    if (ids == null || ids.isEmpty()) {
        return 0;
    }
    return userMapper.deleteByIds(ids);
}

Putting it together

UserService.java
@Service
public class UserService {

    private static final Logger log = LoggerFactory.getLogger(UserService.class);

    private final UserMapper userMapper;

    public UserService(UserMapper userMapper) {
        this.userMapper = userMapper;
    }

    @Transactional
    public User register(String userName, String email) {
        if (userMapper.existsByEmail(email)) {
            throw new DuplicateEmailException(email);
        }
        User user = new User();
        user.setUserName(userName);
        user.setEmail(email);
        user.setStatus("ACTIVE");

        userMapper.insert(user);
        log.info("Registered user {} with id {}", userName, user.getId());
        return user;
    }

    @Transactional(readOnly = true)
    public PageResult<User> page(int page, int size) {
        int safeSize = Math.min(Math.max(size, 1), 200);
        List<User> content = userMapper.findPage(safeSize, page * safeSize);
        return new PageResult<>(content, userMapper.countAll(), page, safeSize);
    }

    @Transactional
    public void deactivate(Long id) {
        if (userMapper.softDelete(id) == 0) {
            throw new UserNotFoundException(id);
        }
    }
}

Pitfalls recap

MistakeSymptomFix
Forgetting commit()Row vanishes after the session closesCommit, or let @Transactional do it
Ignoring the affected-row countSilent no-op updatesCheck for 0 and throw
Batch inserts in SIMPLE executorLoading 50k rows takes minutesUse ExecutorType.BATCH or multi-row VALUES
Unbounded foreachDriver parameter-limit exceptionChunk the list
OFFSET in the millionsQuery gets slower page by pageKeyset pagination
No ORDER BY with LIMITRows repeat or disappear across pagesAlways order by a unique column

Inside a BATCH session, what does mapper.insert(user) return?

Batched statements are only sent when you flush or commit, so no row count exists yet. MyBatis returns Statement.SUCCESS_NO_INFO (-2147482646). Real counts come back from flushStatements() as a list of BatchResult.