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.
The complete mapper interface
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
<insert id="insert" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
INSERT INTO app_user (user_name, email, status)
VALUES (#{userName}, #{email}, #{status})
</insert>
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
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.
<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.
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 VALUES | Batch executor | |
|---|---|---|
| Round trips | 1 per chunk | 1 per batch |
| Parameter limit | Yes, strict | No |
| Generated keys | Usually yes | Driver-dependent |
| Row counts | Accurate | Only after flush |
| Mixed statement types | N/A | Causes an implicit flush per switch |
| Good for | Up to ~1,000 rows | Tens of thousands of rows |
Upsert
<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.
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.
<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>
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);
}
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.
<update id="update">
UPDATE app_user
SET user_name = #{userName},
email = #{email},
status = #{status},
version = version + 1
WHERE id = #{id}
AND version = #{version}
</update>
@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.
<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>
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
<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 <> 'DELETED'
</update>
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
@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
| Mistake | Symptom | Fix |
|---|---|---|
Forgetting commit() | Row vanishes after the session closes | Commit, or let @Transactional do it |
| Ignoring the affected-row count | Silent no-op updates | Check for 0 and throw |
Batch inserts in SIMPLE executor | Loading 50k rows takes minutes | Use ExecutorType.BATCH or multi-row VALUES |
Unbounded foreach | Driver parameter-limit exception | Chunk the list |
OFFSET in the millions | Query gets slower page by page | Keyset pagination |
No ORDER BY with LIMIT | Rows repeat or disappear across pages | Always 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.